질문
오라클 성능 트러블슈팅 에서 가장 기본적인 툴(Tool)은 무엇일까?
필자의 정답은 SQL*Plus 이다.
성능 트러블슈팅 자료작성시 주의사항
간혹 특정 GUI툴이 출력한 화면의 결과를 캡처해서 테스트 결과를 공개하는 경우가 있는데 좋지 않는 습관임
치환변수란 말 그래로 특정 상수 값으로 치환되는 변수를 의미이며, SQL*Plus 에서는 & 문자를 이용해서 치환변수를 사용함
치환변수의 사용법을 설명하는 간단한 예제
-- Note: 다음과 같이 치환변수 &SID 를 선언하고 그 값을 12로 지정함
SQL> define sid = 12
SQL> select sid, event
2 from v$session
3 where sid = &sid;
구 3: where sid = &sid
신 3: where sid = 12
SID EVENT
---------- ----------------------------------------------------------------
12 SQL*Net message from client
치환 변수의 가장 큰 장점은 너무 단순해서 SQL문내의 어디에서나 제약없이 사용할 수 있다는 것임
SELECT문의 컬럼 리스트를 치환 변수로 정의하는 예제
SQL> define columns = "sid, event, p1";
SQL> select &columns
2 from v$session
3 where sid = &sid;
구 1: select &columns
신 1: select sid, event, p1
구 3: where sid = &sid
신 3: where sid = 12
SID EVENT P1
---------- ---------------------------------------------------------------- ----------
12 SQL*Net message from client 1111838976
위와 같은 기법을 잘 활용하면 복잡한 PL/SQL 프로그래밍 없이도 동적인 SQL문을 자유롭게 구사할수 있음
SQL*Plus를 통해서 파일을 호출할 때 파라미터를 줄 수 있음
만일 세 개의 파라미터를 사용했다면, 첫번째 파라미터는 치환변수 &1을 통해 사용할 수 있음 두번째 파라미터는 치환변수 &2, 세번째 파라미터는 치환변수 &3을 통해 사용함
파라미터의 사용법을 설명하는 간단한 예제
/**
-- STEP1. 아래와 같이 SESSION.SQL 파일을 정의함
-- 1) 첫번째 파라미터(&1)의 값을 다시 치환변수 &SID 저장함
-- 2) 그리고 그 값을 이용해서 V$SESSION 뷰를 검색함
**/
SQL> --ed session.sql
SQL> /*
SQL> define sid = &1
SQL>
SQL> select sid, event
SQL> from v$session
SQL> where sid = &sid;
SQL> */
-- STEP2. SESSION.SQL 파일을 호출하면서(@ 명령), 12라는 값을 첫번째 파라미터로 사용함
SQL> @session 12
-- STEP3. 아래와 같이 12의 값이 사용되어 원하는 결과를 얻게됨
SQL> define sid = &1
SQL>
SQL> select sid, event
2 from v$session
3 where sid = &sid;
구 3: where sid = &sid
신 3: where sid = 12
SID EVENT
---------- ----------------------------------------------------------------
12 SQL*Net message from client
ACCEPT명령은 사용자에게 값을 입력받는 용도로 사용됨
ACCEPT명령을 DEFAULT옵션과 함께 사용하면 디폴트 값을 지정할 수 있기 때문에 사용 편의성을 극대화할 수 있음
디폴트 값이 있는 경우에는 값을 입력할 필요가 없이 엔터(Enter) 키를 입력하는 것만으로 값을 지정할 수 있기 때문임
ACCEPT 명령을 설명하는 간단한 예제
/**
-- STEP1. 아래와 같이 SESSION2.SQL 파일을 정의함
-- 1) ACCEPT SID2 DEFAULT &SID 명령에 의해 치환변수 &SID에 의해 지정된 디폴트 값을 사용할 수 있도록 함
**/
SQL> /*
SQL> define sid = &1
SQL> accept sid2 number default &sid prompt 'sid to monitor[&sid]: '
SQL>
SQL> select sid, event
SQL> from v$session
SQL> where sid = &sid2;
SQL> */
-- STEP2. 그리고 SESSION2.SQL 파일을 호출하면서 12라는 값을 첫번째 파라미터로 사용함
SQL> @session2 12
-- STEP3. 그러면 다음과 같이 디폴트 값이 12로 지정된 상태로 ACCEPT 명령이 수행됨
SQL> define sid = &1
SQL> accept sid2 number default &sid prompt 'sid to monitor[&sid]: '
sid to monitor[12]:
SQL> select sid, event
2 from v$session
3 where sid = &sid2;
구 3: where sid = &sid2
신 3: where sid = 12
SID EVENT
---------- ----------------------------------------------------------------
12 SQL*Net message from client
ACCEPT명령은 사용자 대화형 스크립트(대화방식으로 동작)를 작성할 때 유용하게 사용할 수 있지만 자동화된 실행이 어려움
그래서 필자는 ACCEPT 명령보다는 파라미터를 직접지정하는 방식을 더 선호함
COLUMN 명령을 NEW_VALUE 옵션과 함께 사용하면 SELECT에 의해 얻어진 특정 컬럼의 값을 치환변수로 저장할수 있음
COLUMN NEW_VALUE 명령을 설명하는 간단한 예제
-- STEP1. 아래와 같이 COLUMN 명령을 실행하면 SID라는 컬럼의 결과 값을 SID 라는 치환변수에 저장하라는 의미가됨
SQL> -- "column new_value" command
SQL> col sid new_value sid
-- STEP2. 아래와 같은 쿼리를 수행하면 치환변수 &SID에 135의 값이 저장됨
SQL> select sid from v$session where client_info = 'session1';
SID
----------
12
-- STEP3. 저장된 치환변수는 아래와 같이 사용하면 됨
SQL> select sid, event
2 from v$session
3 where sid = &sid;
구 3: where sid = &sid
신 3: where sid = 12
SID EVENT
---------- ----------------------------------------------------------------
12 SQL*Net message from client
보통 이런 요구사항을 접하면 PL/SQL을 이용해 동적인 SQL문을 생성하는 방법을 떠올리지만,
SQL*Plus 의 치환변수 기능을 이용하면 어렵지 않게 이런 조건부 쿼리를 생성할수 있음
치환 변수를 이용하여 조건부 쿼리를 구현하는 예제
-- STEP1. 아래와 같이 COLUMN .. NEW_VALUE 명령을 이용해서 치환변수 _IS_11G 와 _IS_10G를 선언함
SQL> -- conditional logic using substitution variable
SQL> col is_11g new_value _IS_11G format a10
SQL> col is_10g new_value _IS_10G format a10
-- STEP2. 그리고 V$VERSION 뷰에서 버전 값을 읽어서 IS_11G 컬럼과 IS_10G 컬럼이 11g이상일 경우 에는 " "과 "--" , 10g 이하일 경우에는 각각 "--" , " "의 값을 지니도록함
SQL> with v as (
2 select to_number(substr(banner, instr(banner, 'Release ')+8
3 ,instr(banner, '.') - instr(banner,'Release ')-8)) as version
4 from v$version
5 where rownum = 1
6 ) select case when version >= 11 then '' else '--' end as is_11g
7 ,case when version <= 10 then '' else '--' end as is_10g
8 from v;
IS_11G IS_10G
---------- ----------
--
/**
-- STEP3. "--" 값은 SQL*Plus에서는 주석을 나타내는 문자임
-- 따라서 10g 이하일 경우에는 치환변수 _IS_11G 의 값이 "--"이 되어 주석으로 처리되게 할수 있으며, 이 원리를 이용해서 다음과 같이 쿼리를 작성함
**/
SQL> select sid, event, sql_id &_IS_11G , (sysdate - sql_exec_start)*24*60*60 as elapsed
2 from v$session
3 where sid = &sid;
구 1: select sid, event, sql_id &_IS_11G , (sysdate - sql_exec_start)*24*60*60 as elapsed
신 1: select sid, event, sql_id , (sysdate - sql_exec_start)*24*60*60 as elapsed
구 3: where sid = &sid
신 3: where sid = 12
SID EVENT SQL_ID ELAPSED
---------- ---------------------------------------------------------------- ------------- ----------
12 SQL*Net message from client 9tz4qu4rj9rdp
-- STEP4. 즉 11g 이상일 경우에만 SQL_EXEC_START 컬럼의 값을 사용하게끔 조건부 쿼리를 구현한것임
위의 기능을 PL/SQL을 이용해서 구현할 수 있겠지만 SQL*Plus의 치환변수를 잘 이용하는 것이 더 직관적이고 더 사용하기 편함
복합한 SQL*Plus 스크립트를 작성하다보면 동적으로 생성된 SQL을 파일에 기록하고 그 파일을 다시 호출하는 과정을 필요로 할때가있음
즉 SQL*Plus가 제공하는 SPOOL기능을 이용할 필요가 생김
조건부 쿼리의 구현을 치환 변수가 아닌 SPOOL을 이용해서 구현하는 예제
-- STEP1. 다음과 같이 MAKE_TEMP.SQL 파일을 작성함
-- 1) SPOOL 결과를 완벽한 형태의 SQL*Plus 스크립트로 사용할수 있도록 SET명령을 사용해서 TERMOUT, HEADING, VERIFY, FEEDBACK, TIMING, SCAN 등의 불필요한 옵션을 모두 OFF시킴
-- 2) 그리고 SPOOL 명령을 이용해서 출력결과를 TEMP.SQL 파일에 기록함
-- 3) DBMS_DB_VERSION 패키지를 이용해서 11g인지 10g 인지를 구분함
set echo off
set termout off
set pagesize 0
set heading off
set verify off
set feedback off
set serveroutput on
set timing off
set scan off
spool temp.sql
begin
dbms_output.put_line('select sid, event, sql_id ');
if dbms_db_version.version >= 11 then
dbms_output.put_line(', (sysdate - sql_exec_start)*24*60*60 as elapsed ');
end if;
dbms_output.put_line('from v$session ');
dbms_output.put_line('where sid = &1; ');
end;
/
spool off
set echo on
set termout on
set pagesize 100
set heading on
set verify on
set feedback on
set serveroutput off
set timing on
set scan on
-- STEP2. 이제 다음과 같이 MAKE_TEMP.SQL 파일이 실행함
SQL> @make_temp
-- STEP3. 성공적으로 실행이 끝나면 다음과 같은 내용을 가진 TEMP.SQL 파일이 생성될 것임
select sid, event, sql_id
,(sysdate - sql_exec_start)*24*60*60 as elapsed
from v$session
where sid = &1;
-- STEP4. 만일 실행 환경이 11g 가 아니라 10g라면 TEMP.SQL 파일의 내용은 다음과 같이 바뀔 것임
select sid, event, sql_id
from v$session
where sid = &1;
-- STEP5. 이렇게 생성된 TEMP.SQL파일을 새로운 SQL*Plus 스크립트 파일로 사용할 수 있음
SQL> @temp &sid
SID EVENT SQL_ID ELAPSED
---------- ---------------------------------------------------------------- ------------- ----------
12 SQL*Net message from client 9tz4qu4rj9rdp
-- STEP6. 즉 다음과 같은 단순한 호출 만으로 복잡해 보이는 조건부 쿼리를 구현할 수 있음
SQL> @make_temp
SQL> @temp &sid
여기서 예로 들은 SQL*Plus를 통한 스크립팅 기법 중 가장 일반적이고 단순한 것들만을 소개한것임
치환변수와 SPOOL명령을 적절히 조합하면 SQL*Plus의 단순한 스크립팅만으로 전혀 불가능하다고 여겨지는 복잡한 작업도 손쉽게 구현할수 있음