by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

SQL*Plus 스크립팅




질문

오라클 성능 트러블슈팅 에서 가장 기본적인 툴(Tool)은 무엇일까?
필자의 정답은 SQL*Plus 이다.

SQL*Plus를 가장 기본적이면서도 누구나 배워야 할 툴이라고 믿는 이유
  • SQL*Plus가 제공하는 스크립팅 능력은 단순하면서도 강력해서 많은 작업을 자동화 할 수 있으며, 스크립팅 기능을 잘 활용하여 성능 트러블슈팅 과정을 잘할수 있음
  • SQL*Plus는 모든 사용자가 공유할 수 있는 유일한 툴임
    서로 다른 환경에서 작업하는 사용자들끼리 테스트 결과를 서로 주고 받을 때 SQL*Plus에서 실행 가능한 스크립트와 스풀 결과를 이용하는것이 가장 합리적임
성능 트러블슈팅 자료작성시 주의사항

간혹 특정 GUI툴이 출력한 화면의 결과를 캡처해서 테스트 결과를 공개하는 경우가 있는데 좋지 않는 습관임

치환변수

치환변수란 말 그래로 특정 상수 값으로 치환되는 변수를 의미이며, SQL*Plus 에서는 & 문자를 이용해서 치환변수를 사용함

  • SQL*Plus가 제공하는 스크립팅 기능의 핵심은 치환변수(Substitution Variable)를 효과적으로 사용하는 것임

:치환변수 기본

치환변수의 사용법을 설명하는 간단한 예제

-- 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명령은 사용자에게 값을 입력받는 용도로 사용됨
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 명령

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

:치환변수를 이용한 조건부 쿼리 구현

오라클 버전에 따라 동적으로 컬럼을 추가하거나 빼는 기능, 즉 조건부 쿼리를 구현하고자함
  • V$SESSION 뷰에서 SID, EVENT, SQL_ID를 컬럼을 읽음
  • 단, Oracle 11g일 경우에는 SQL_EXEC_START 컬럼을 이용해서 쿼리 소요 시간(Elasped Time)을 구함

보통 이런 요구사항을 접하면 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의 치환변수를 잘 이용하는 것이 더 직관적이고 더 사용하기 편함

SPOOL을 이용한 동적인 SQL 스크립팅 구현

복합한 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의 단순한 스크립팅만으로 전혀 불가능하다고 여겨지는 복잡한 작업도 손쉽게 구현할수 있음

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 3월 03, 2012

    강정식 says:

    sql plus 세팅 참고 사이트 http://radiocom.kunsan.ac.kr/lecture/oracle/ http://radioc...