Tibero DBMS XPLAN

READ"와 "BUFFER" 수치를 지원하지 않아 현재 아래와 같이 사용 중입니다.

( 물론 제가 모를 수도 있지만... ㅎㅎ )



HARD PARSING 처리 해야지만 "※ SQL INFORMATION " 정보가 누적되어 나오지 않으니 참고 바랄게요.



[ 예시 ]

1)  사전 작업

  alter session set gather_sql_exec_time = y;
  alter session set gather_sql_plan_stat = y;

 

 

2) 튜닝 대상 SQL 질의

 SELECT * FROM DUAL; -- 튜닝 대상 SQL 질의

 

3) XPLAN 추출 SQL 질의

[ 개별 SQL XPLAN ]

SELECT CASE WHEN ROWNUM = 9 -- [ 고정 위치 ]
THEN ' ※ SQL INFORMATION '|| chr(13) ||
' 1) Active Time : ' || TA.LAST_ACTIVE_TIME || chr(13) ||
' 2) Execution : ' || TA.EXECUTIONS || chr(13) ||
' 3) CPU Elapsed : ' || ROUND(TA.CPU_TIME / 1000000,4) || chr(13) ||
' 4) Elapsed Time : ' || ROUND(TA.ELAPSED_TIME / 1000000,4) || chr(13) ||
' 5) Buffers : ' || TA.BUFFER_GETS || chr(13) ||
' 6) Reads : ' || TA.DISK_READS || chr(13) ||
' 7) DISK I/O(%) : ' || ROUND(( DISK_READS / DECODE(BUFFER_GETS,0,1,BUFFER_GETS) ) * 100,1) ||'%'
ELSE B.COLUMN_VALUE
END AS "[ TIBERO XPLAN INFORMATION ]"
FROM
(
SELECT T.*
FROM
(
SELECT A.*
, ROW_NUMBER() OVER( ORDER BY A.LAST_ACTIVE_TIME DESC ) AS RN
FROM sys.V$SQL A
WHERE 1 = 1
AND UPPER(SQL_TEXT) LIKE UPPER('%DBIAN%') ---------------------------------- 수행한 SQL에 기입한 이정표 문자 넣기
AND UPPER(SQL_TEXT) NOT LIKE '%V$%'
) T
WHERE RN = 1
) TA
, TABLE( DBMS_XPLAN.DISPLAY_CURSOR( TA.SQL_ID, TA.CHILD_NUMBER, 'ALL LAST -SQL') ) B
;

 

 

[ 전체 SQL 목록 XPLAN ]

SELECT SQL_ID
, SQL_TEXT
, LAST_ACTIVE_TIME
, executions
, MODULE
,(SELECT LISTAGG(TEXT, CHR(10)) WITHIN GROUP(ORDER BY RN) AS TEXT
FROM (SELECT COLUMN_VALUE AS TEXT, ROWNUM AS RN
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(A.SQL_ID, A.CHILD_NUMBER, 'ALL')))) TEXT
FROM V$SQL A
WHERE 1 = 1
AND UPPER(SQL_TEXT) LIKE '%KOREA3%'
AND UPPER(SQL_TEXT) NOT LIKE '%V$SQL%'
ORDER BY LAST_ACTIVE_TIME DESC
;

 

4) 개별 SQL XPLAN 확인

[ TIBERO XPLAN INFORMATION ]
SQL ID         : 5dk3ypq1tjrrk
HASH VALUE     : 2207833842
PLAN HASH VALUE: 520620051
EXECUTIONS     : 1
FETCHES        : 1
LOADED AT      : 2023/08/24 10:58:13
TOT ELAPSED TIME: 00:00:00.1702
AVG ELAPSED TIME: 00:00:00.1702
※ SQL INFORMATION
  1) Active Time : 2023/08/24 10:58:13
  2) Execution : 1
  3) CPU Elapsed : .002
  4) Elapsed Time : .1702
  5) Buffers : 39
  6) Reads : 9
  7) DISK I/O(%) : 23.1%
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| ID  | Operation                          | Name                | Cost (%CPU)  | Cards   | Rows    | Elaps. Time  | CR Gets  | Starts   | Used Mem | Temp. Read | Temp. Write|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 | COUNT (STOP NODE) (STOP LIMIT 21)  |                     |  201      (0)|      20 |      20 |00:00:00.0000 |       0  |       1  |       0K |         0  |         0  |
|   2 |  TABLE ACCESS (ROWID)              |IDX01                |  201      (0)|    5577 |      20 |00:00:00.0538 |      25  |       1  |       0K |         0  |         0  |
|   3 |   INDEX (RANGE SCAN)               |TB_TABLE             |   17      (0)|    5577 |     383 |00:00:00.1146 |       8  |       1  |       0K |         0  |         0  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - access: ("T2"."YYMM" = '202307') (0.000)