"PLSQL 성능분석"의 두 판 사이의 차이

1.1 실습 순서[편집]

1. 아래 TB_PLSQL_TEST 프로시져 컴파일 하기 ( 프로시져 내부 3개 쿼리 존재 )
   1. 1. 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수)
      2. 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션)
2. 프로시져 실행 전 alter session set statistics_level = all; 셋팅
3. 프로시져 실행
4. Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 출력 되게 하기

1.2 실습 하기[편집]

1) 프로시져 생성

CREATE OR REPLACE PROCEDURE SP_SAMPLE_BATCH_PROCEDURE IS

/* -- TEST TABLE 생성하기 

CREATE TABLE TB_PLSQL_TEST (

COL1 VARCHAR2(100)

,COL2 VARCHAR2(100) ,COL3 VARCHAR2(100) ,COL4 VARCHAR2(100) ); -- 권한 부여 하기 ( SYS로 접속하여 TBA_DBA(접속계정)에 권한을 주자. DBMS_LOCK.SLEEP 수행하기 위해서. ) GRANT EXECUTE ON DBMS_LOCK TO TBA_DBA;

*/

PROCID VARCHAR2(50) := 'SP_SAMPLE_BATCH_PROCEDURE'; V_CNT NUMBER;

BEGIN

 DBMS_APPLICATION_INFO.set_module( PROCID ,'Task Topic'); -- MODULE INPUT

 BEGIN
   DBMS_APPLICATION_INFO.set_action( PROCID || '_001' ); -- ACTION INPUT1
   SELECT /*+ SP_SAMPLE_BATCH_PROCEDURE_001 */ COUNT(*) 
     INTO V_CNT
     FROM TB_PLSQL_TEST;
     DBMS_LOCK.sleep(10);
 EXCEPTION
   WHEN OTHERS THEN
     NULL; 
 END;

 BEGIN
   DBMS_APPLICATION_INFO.set_action( PROCID || '_002' ); -- ACTION INPUT2 
   FOR V_REF IN 1 .. 10 LOOP
   INSERT /*+ SP_SAMPLE_BATCH_PROCEDURE_002 */ INTO TB_PLSQL_TEST
   SELECT LEVEL, LEVEL, LEVEL, LEVEL 
   FROM DUAL
   CONNECT BY LEVEL <= 1000; 
   END LOOP;
   DBMS_LOCK.sleep(10); 
 EXCEPTION
   WHEN OTHERS THEN
     NULL;
 END;

 BEGIN
   DBMS_APPLICATION_INFO.set_action( PROCID || '_003'); -- ACTION INPUT3 
   FOR V_REF IN 1 .. 10 LOOP 
     DELETE /*+ SP_SAMPLE_BATCH_PROCEDURE_003 */ TB_PLSQL_TEST
     WHERE ROWNUM <= 100; 
   END LOOP;
   DBMS_LOCK.sleep(10);
 EXCEPTION
   WHEN OTHERS THEN
     NULL;
 END;

 COMMIT;​

DBMS_APPLICATION_INFO.set_module( , ); -- MODULE INPUT EXCEPTION

 WHEN OTHERS THEN
   dbms_output.put_line ( sqlerrm );

END SP_SAMPLE_BATCH_PROCEDURE;​

2) 프로시져 실행 전, 세션 통계정보 레벨 셋팅

 :  셋팅을 해야지만 실제 실행계획을 얻을수 있고 pagesize, linesize 조정을 해야 실행계획이 가독성 좋게 출력된다.
   ( sql> @setting  으로  대체 가능 )

sql> alter session set statistics_level = all; sql> set pagesize 2000; sql> set linesize 2000;

3) 프로시져 실행 sql> exec sp_sample_batch_procedure; ​ 4) Script 호출 하여, Sublime 편집기에 바로 실행계획이 보이게 하기 sql>@plsqlxplann SP_SAMPLE_BATCH_PROCEDURE; ( @plsqlxplann 모듈명 ) - 4) 수행 결과 아래 이미지와 같이 서브라임 편집기에 프로시져의 모든 SQL 실행계획이 출력됨.

• Settinig.sql 내용

alter session set statistics_level = all; set pagesize 2000; set linesize 2000;

• plsqlxplann.sql 내용

SPOOL C:\app\myscript\xplan_list\plsqlxplan.txt

SELECT B.PLAN_TABLE_OUTPUT, TA.EXECUTIONS, TA.CHILD_NUMBER
  FROM 
     (
      SELECT  T.MODULE, T.ACTION, T.LAST_ACTIVE_TIME, T.SQL_ID, T.CHILD_NUMBER, T.EXECUTIONS,T.ELAPSED_TIME, T.BUFFER_GETS, T.LAST_ACTIVE_TIME              
        FROM 
           (  
             SELECT a.*
                  , RANK() OVER( PARTITION BY ACTION ORDER BY LAST_ACTIVE_TIME DESC ) AS RANK 
               FROM V$SQL A 
              WHERE MODULE = '&1' -- module명 바인딩
                AND PLAN_HASH_VALUE <> 0 
           ) T 
       WHERE RANK = 1  
    ) TA
    , TABLE( DBMS_XPLAN.DISPLAY_CURSOR( TA.SQL_ID, TA.CHILD_NUMBER, 'ADVANCED ALLSTATS LAST') ) B
    ;

SPOOL OFF
$$C:\Program Files\Sublime Text 3\sublime_text.exe C:\app\myscript\xplan_list\plsqlxplan.txt