오라클 19c PL/SQL 의 Xplan 실행계획 확인

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c PL/SQL 의 Xplan 실행계획 확인

오라클에서 plsql의 실행계획을 xplan으로 확인하려고 하면 제대로 나오지 않는 경우가 있음
이때 DBMS_APPLICATION_INFO 패키지를 이용하면 클라이언트의 모듈이름과 액션이름를 입력해놓을 수 있어
xplan 사용시 세션 정보를 불러올 때 유용하게 사용할 수 있음
참고 : 오라클 19c DBMS_APPLICATION_INFO 클라이언트 정보 설정 ( https://positivemh.tistory.com/1045 )

 

 

테스트

DBMS_APPLICATION_INFO 미사용시
PL/SQL 실행

1 2 3 4 5 6 7 8 9 10 11 12

SQL>  alter session set statistics_level = all; BEGIN     FOR i IN (SELECT * FROM dept) LOOP         FOR j IN (SELECT * FROM emp WHERE deptno = i.deptno) LOOP             NULL;         END LOOP j;     END LOOP i; END; /   PL/SQL procedure successfully completed.

 

 

xplan 확인

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

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  0zpt4y7cfuws9, child number 0   BEGIN     FOR i IN (SELECT * FROM dept) LOOP         FOR j IN (SELECT * FROM emp WHERE deptno = i.deptno) LOOP             NULL;         END LOOP j;     END LOOP i; END;   NOTE: cannot fetch plan for SQL_ID: 0zpt4y7cfuws9, CHILD_NUMBER: 0       Please verify value of SQL_ID and CHILD_NUMBER;       It could also be that the plan is no longer in cursor cache (check v$sql_plan)   10 rows selected.

xplan 실행계획이 제대로 나오지 않음

 

 

DBMS_APPLICATION_INFO 사용시
statistics_level all 설정

1 2 3

SQL> alter session set statistics_level = all;   Session altered.

 

 

모듈 및 액션 이름 설정

1 2 3

SQL> EXEC DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'PLSQL_TEST1', action_name => 'PLSQL_TEST1');   PL/SQL procedure successfully completed.

 

 

PL/SQL 실행

1 2 3 4 5 6 7 8 9 10 11

SQL>  BEGIN     FOR i IN (SELECT * FROM dept) LOOP         FOR j IN (SELECT * FROM emp WHERE deptno = i.deptno) LOOP             NULL;         END LOOP j;     END LOOP i; END; /   PL/SQL procedure successfully completed.

 

 

statistics_level typical 설정

1 2 3

SQL> alter session set statistics_level = typical;   Session altered.

 

 

xplan 확인

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

SQL> select b.plan_table_output  from  (select sql_id, child_number  from v$sql where module = 'PLSQL_TEST1' and action = 'PLSQL_TEST1' and plan_hash_value <> 0 order by last_active_time) a, (DBMS_XPLAN.DISPLAY_CURSOR(a.sql_id, a.child_number, 'ADVANCED ALLSTATS LAST')) b;   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  0ws4zp45rdbtm, child number 0 ------------------------------------- SELECT * FROM DEPT   Plan hash value: 3383998547   ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      4 |00:00:00.01 |       6 |      6 | |   1 |  TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |      6 | ----------------------------------------------------------------------------------------------------------------------------- . . SQL_ID  f81g49vq62w9u, child number 0 ------------------------------------- SELECT * FROM EMP WHERE DEPTNO = :B1   Plan hash value: 3956160932   -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      0 |00:00:00.01 |       6 | |*  1 |  TABLE ACCESS FULL| EMP  |      1 |      3 |   114 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       6 | --------------------------------------------------------------------------------------------------------------------

정상적으로 플랜 확인이 가능함

 

 

참조 : 

https://hrjeong.tistory.com/234
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_APPLICATION_INFO.html#GUID-14484F86-44F2-4B34-B34E-0C873D323EAD
https://dataonair.or.kr/?kboard_content_redirect=101862

https://positivemh.tistory.com/1045