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

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;​

1.2.2 프로시져 실행 전, 세션 통계정보 레벨 셋팅[편집]

- 아래 구문으로 셋팅을 해야만 실제 실행 후의 실행계획을 얻을수 있다. pagesize, linesize 조정은 가독성 좋게 하는 효과가 있다. ( sql> @setting 대체 가능 )
sql> alter session set statistics_level = all; 
sql> set pagesize 2000;
sql> set linesize 2000;

1.2.3 프로시져 실행[편집]

sql> exec sp_sample_batch_procedure;

​

1.2.4 Script 호출시 Sublime 편집기에 실행계획 출력하기[편집]

sql> @plsqlxplann SP_SAMPLE_BATCH_PROCEDURE;  ( @plsqlxplann 모듈명 )

1.2.5 참고[편집]

1. Settinig.sql 내용 
  alter session set statistics_level = all;
  set pagesize 2000;
  set linesize 2000;

2. 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