런타임 실행 계획 조회 함수

DBMS_XPLAN.DISPLAY_CURSOR 함수로 커서의 런타임 실행 계획을 조회할 수 있지만, statistics_level 파라미터의 기본값이 TYPICAL이라서 plan execution statistics를 확인할 수 없는 아쉬움이 있다. 실제 쿼리를 수행하고 런타임 실행 계획을 반환하는 함수를 작성해보자.

SYS 사용자로 로그인한 세션에서 아래 권한을 부여하자.

1 2 3 4

-- 1 : SYS GRANT SELECT ON v_$sql TO tuna; GRANT SELECT ON v_$sql_bind_metadata TO tuna; GRANT SELECT ON v_$sql_plan_statistics_all TO tuna;

아래와 같이 타입과 함수를 생성하자.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108

-- 2-1 CREATE OR REPLACE TYPE tnt_varchar2 IS TABLE OF VARCHAR2(4000); /   -- 2-2 CREATE OR REPLACE FUNCTION fnc_run_sql (     i_sql_id          IN    VARCHAR2   , i_child_number    IN    VARCHAR2 DEFAULT NULL   , i_format          IN    VARCHAR2 DEFAULT 'BASIC PARTITION PARALLEL PREDICATE ALIAS REMOTE NOTE OUTLINE PEEKED_BINDS ALLSTATS LAST' )     RETURN tnt_varchar2 PIPELINED IS     ---------------------------------------------------------------     -- name          : fnc_run_sql     -- author        : TunA     -- last modified : 2018-12-12     ---------------------------------------------------------------     l_session_user           v$sql.parsing_schema_name%TYPE := SYS_CONTEXT ('USERENV', 'SESSION_USER');     l_parsing_schema_name    v$sql.parsing_schema_name%TYPE;     l_sql_fulltext           v$sql.sql_fulltext%TYPE;     l_child_address          v$sql.child_address%TYPE;     l_bind_data              v$sql.bind_data%TYPE;     l_cur_id                 PLS_INTEGER;     l_row_num                PLS_INTEGER;     l_ret_val                PLS_INTEGER;     l_number                 NUMBER;     l_date                   DATE;     l_timestamp              TIMESTAMP;     l_child_number           v$sql.child_number%TYPE; BEGIN     SELECT   parsing_schema_name,   sql_fulltext,   child_address,   bind_data       INTO l_parsing_schema_name, l_sql_fulltext, l_child_address, l_bind_data       FROM (SELECT parsing_schema_name, sql_fulltext, child_address, bind_data                  , ROW_NUMBER () OVER (ORDER BY buffer_gets / NULLIF (executions, 0) DESC NULLS LAST) AS rn               FROM v$sql a              WHERE sql_id = i_sql_id                AND child_number = NVL (i_child_number, child_number)                AND command_type = 3)      WHERE rn = 1;       EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = ALL';     l_cur_id := DBMS_SQL.OPEN_CURSOR ();       EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || l_parsing_schema_name;     DBMS_SQL.PARSE (l_cur_id, l_sql_fulltext, DBMS_SQL.NATIVE);     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || l_session_user;       FOR c1 IN (SELECT a.bind_name, b.datatype, b.value_string, b.value_anydata                  FROM v$sql_bind_metadata a                     , TABLE (DBMS_SQLTUNE.EXTRACT_BINDS (l_bind_data)) b                 WHERE a.address = l_child_address                   AND b.position = a.position)     LOOP         CASE c1.datatype             WHEN   2             THEN                 l_ret_val := c1.value_anydata.GETNUMBER (l_number);                 DBMS_SQL.BIND_VARIABLE (l_cur_id, c1.bind_name, l_number);             WHEN  12             THEN                 l_ret_val := c1.value_anydata.GETDATE (l_date);                 DBMS_SQL.BIND_VARIABLE (l_cur_id, c1.bind_name, l_date);             WHEN 180             THEN                 l_ret_val := c1.value_anydata.GETTIMESTAMP (l_timestamp);                 DBMS_SQL.BIND_VARIABLE (l_cur_id, c1.bind_name, l_timestamp);             ELSE                 DBMS_SQL.BIND_VARIABLE (l_cur_id, c1.bind_name, c1.value_string);         END CASE;     END LOOP;       l_row_num := DBMS_SQL.EXECUTE (l_cur_id);       WHILE (DBMS_SQL.FETCH_ROWS (l_cur_id) > 0)     LOOP         NULL;     END LOOP;       DBMS_SQL.CLOSE_CURSOR (l_cur_id);     EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = TYPICAL';       SELECT child_number       INTO l_child_number       FROM (SELECT child_number                  , ROW_NUMBER () OVER (ORDER BY last_active_time DESC) AS rn               FROM v$sql a              WHERE a.sql_id = i_sql_id                AND EXISTS (SELECT 1                              FROM v$sql_plan_statistics_all x                             WHERE x.sql_id = a.sql_id                               AND x.child_number = a.child_number)) a      WHERE rn = 1;       FOR c1 IN (SELECT plan_table_output                  FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (i_sql_id, l_child_number, i_format)))     LOOP         PIPE ROW (c1.plan_table_output);     END LOOP; EXCEPTION     WHEN OTHERS     THEN         EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = TYPICAL';         EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = ' || l_session_user;         RAISE; END fnc_run_sql; /   SHOW ERROR

테스트할 쿼리를 수행한 후 sql_id와 child_number를 확인하자.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

-- 3-1 VAR v_deptno NUMBER; EXEC :v_deptno := 10;   SELECT * FROM emp WHERE deptno = :v_deptno;   -- 3-2 SELECT sql_id, child_number   FROM v$sql  WHERE sql_text = 'SELECT * FROM emp WHERE deptno = :v_deptno';   SQL_ID        CHILD_NUMBER ------------- ------------ 8um5jw59b9w0g            0   1개의 행이 선택되었습니다.

DBMS_XPLAN.DISPLAY_CURSOR 함수로 커서의 런타임 실행 계획을 조회하면 plan execution statistics가 표시되지 않는다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

-- 4 SELECT plan_table_output   FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('8um5jw59b9w0g', 0, 'BASIC ALLSTATS LAST'));   PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------- EXPLAINED SQL STATEMENT: ------------------------ SELECT * FROM emp WHERE deptno = :v_deptno   Plan hash value: 3956160932   ------------------------------------------- | Id  | Operation         | Name | E-Rows | ------------------------------------------- |   0 | SELECT STATEMENT  |      |        | |*  1 |  TABLE ACCESS FULL| EMP  |      5 | -------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("DEPTNO"=:V_DEPTNO)   Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level     24 행이 선택되었습니다.

생성한 함수를 사용하면 plan execution statistics가 표시되는 것을 확인할 수 있다. 실제 쿼리를 수행하므로 장시간 수행되는 쿼리의 경우 주의가 필요하다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

-- 5 SELECT * FROM TABLE (fnc_run_sql ('8um5jw59b9w0g', 0, 'BASIC ALLSTATS LAST'));   COLUMN_VALUE ------------------------------------------------------------------------------------ EXPLAINED SQL STATEMENT: ------------------------ SELECT * FROM emp WHERE deptno = :v_deptno   Plan hash value: 3956160932   ------------------------------------------------------------------------------------ | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.01 |       9 | |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      5 |      3 |00:00:00.01 |       9 | ------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("DEPTNO"=:V_DEPTNO)     18 행이 선택되었습니다.