PL/SQL 코드 성능 측정

DBMS_APPLICATION_INFO.SET_MODULE 프로시저를 사용하면 PL/SQL 코드의 성능을 간편하게 측정할 수 있다. 관련 내용을 살펴보자.

아래와 같은 패턴으로 PL/SQL 코드를 실행하면 된다.

-- 1
ALTER SESSION SET STATISTICS_LEVEL = ALL;
EXEC DBMS_APPLICATION_INFO.SET_MODULE ('M1', 'A1');

BEGIN
    FOR c1 IN (SELECT * FROM dept) LOOP
        FOR c2 IN (SELECT * FROM emp WHERE deptno = c1.deptno) LOOP
            NULL;
        END LOOP c2;
    END LOOP c1;
END;
/

EXEC DBMS_APPLICATION_INFO.SET_MODULE (NULL, NULL);
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

DBMS_XPLAN.DISPLAY_CURSOR 프로시저를 사용하면 PL/SQL 코드에서 수행된 SQL의 실행 통계를 확인할 수 있다.

-- 2
SELECT b.plan_table_output
  FROM (SELECT   sql_id, child_number
            FROM v$sql
           WHERE module = 'M1'
             AND action = 'A1'
             AND plan_hash_value <> 0
        ORDER BY last_active_time) a
     , TABLE (DBMS_XPLAN.DISPLAY_CURSOR (a.sql_id, a.child_number, 'ALLSTATS')) b;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  0ws4zp45rdbtm, child number 0

SELECT * FROM DEPT

Plan hash value: 3383998547

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      4 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------

SQL_ID  f81g49vq62w9u, child number 0
-------------------------------------
SELECT * FROM EMP WHERE DEPTNO = :B1

Plan hash value: 2141393120

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |      4 |        |     14 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |      4 |      3 |     14 |00:00:00.01 |       7 |
|*  2 |   INDEX RANGE SCAN                  | EMP_X1 |      4 |      3 |     14 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=:B1)
-------------------------------------

32 행이 선택되었습니다.