ORACLE 튜닝 대상 조회
DB CAFE
(ORACLE 튜닝에서 넘어옴)
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 튜닝 대상 조회
- 1.1 튜닝 대상 찾기
- 1.2 GV$SQL(V$SQL) 이용 튜닝 대상 조회
- 1.3 CPU를 많이 사용하는 세션의 식별 V$SESSTAT V$SESSION
- 1.4 Disk Read 가 많은 SQL문 찾기 V$SQLAREA
- 1.5 Rollback Segment를 사용하고 있는 SQL문 조회 V$ROLLSTAT
- 1.6 오래도록 수행되는 Full Table Scan를 모니터링 V$SESSION_LONGOPS
- 1.7 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)
- 1.8 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절) V$SQLAREA
- 1.9 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리
- 2 동일 SQL 구분 / 분류 방법
- 3 v$sql_plan 이용한 SQL 플랜 보기
1 튜닝 대상 조회[편집]
1.1 튜닝 대상 찾기[편집]
WITH TMP_SQLSTAT_HIST AS(
SELECT /*+ LEADING(X B) USE_NL(B) USE_HASH(A) FULL(B) */
B.DBID, B.SQL_ID, B.INSTANCE_NUMBER
, TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') SNAP_DATE
, MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME
, B.PLAN_HASH_VALUE
, MAX(MODULE) MODULE
, SUM(EXECUTIONS_DELTA) EXECUTIONS
, SUM(BUFFER_GETS_DELTA) BUFFER_GETS
, ROUND(SUM(BUFFER_GETS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA))) EXEC_BUFFER_GETS
, SUM(DISK_READS_DELTA) DISK_READS
, ROUND(SUM(DISK_READS_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA))) EXEC_DISK_READS
, SUM(ROWS_PROCESSED_DELTA) ROWS_PROCESSED
, ROUND(SUM(ROWS_PROCESSED_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA))) EXEC_ROWS
, SUM(CPU_TIME_DELTA) CPU_TIME
, ROUND(SUM(CPU_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CPU_TIME
, SUM(CLWAIT_DELTA) CLUSTER_WAIT_TIME
, ROUND(SUM(CLWAIT_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_CLWAIT_TIME
, SUM(ELAPSED_TIME_DELTA) ELAPSED_TIME
, ROUND(SUM(ELAPSED_TIME_DELTA) / DECODE(SUM(EXECUTIONS_DELTA), 0, 1,
SUM(EXECUTIONS_DELTA)) / 1000000, 6) EXEC_ELAPSED_TIME
, ROUND(MAX(EXECUTIONS_DELTA) / 600, 3) EXEC_FOR_SEC
--최종 수행된 SQL 조회, 여러 INSTANCE에서 수행시는 BUFER_GETS 높은 것 우선
, ROW_NUMBER() OVER(PARTITION BY B.DBID, B.SQL_ID
ORDER BY TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD') DESC
, SUM(BUFFER_GETS_DELTA) DESC) RNK
FROM (SELECT /*+ NO_MERGE */
DBID, MIN(SNAP_ID) MIN_SNAP_ID, MAX(SNAP_ID) MAX_SNAP_ID
FROM SYS.WRM$_SNAPSHOT --DBA_HIST_SNAPSHOT --SQL 실행 구간
WHERE END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') -
AND END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW')
GROUP BY DBID) X, --DBA_HIST_SQLSTAT과 조인 시 해당 파티션만 SCAN하기 위해 만든 집합임
SYS.WRM$_SNAPSHOT A, --DBA_HIST_SNAPSHOT
SYS.WRH$_SQLSTAT B --DBA_HIST_SQLSTAT과
WHERE X.DBID = B.DBID
AND B.SNAP_ID BETWEEN X.MIN_SNAP_ID AND X.MAX_SNAP_ID
AND A.DBID = B.DBID
AND A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND A.END_INTERVAL_TIME >= TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW') - 7
AND A.END_INTERVAL_TIME < TRUNC(TO_DATE('20130513', 'YYYYMMDD'), 'IW')
AND NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|
TOAD|golden|ERwin|PL.SQL Developer|
SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
AND PLAN_HASH_VALUE > 0
AND MODULE IS NOT NULL
GROUP BY B.DBID, B.SQL_ID, B.INSTANCE_NUMBER, B.PLAN_HASH_VALUE
, TO_CHAR(END_INTERVAL_TIME, 'YYYYMMDD')
)
SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현
DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE
, A.DBID
, A.INSTANCE_NUMBER --최종 수행된 INSTANCE
, A.SNAP_DATE --최종 수행 일자
, A.SQL_ID --SQL_ID
, A.PLAN_HASH_VALUE --실행 계획에 종속적인 값
, A.MODULE --실행 모듈
, A.EXECUTIONS --총 실행수
, A.EXEC_ROWS --실행당 결과 건수
, A.EXEC_BUFFER_GETS --실행당 BUFFER_GET
, A.BUFFER_GETS --총 BUFFER_GET
, A.EXEC_DISK_READS --실행당 DISK_READ
, A.EXEC_ELAPSED_TIME --실행당 수행 시간
, A.EXEC_CPU_TIME --실행당 CPU 시간
, A.EXEC_FOR_SEC --초당 실행수, 특정 시간에만 집중적으로 수행되는 SQL 판별위함
, A.ELAPSED_TIME --총 수행시간
, A.CPU_TIME --총 CPU 시간
, A.CLUSTER_WAIT_TIME --총 CLUSTER 대기 시간
, ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율
FROM (
SELECT SNAP_DATE, DBID, INSTANCE_NUMBER, SQL_ID, PLAN_HASH_VALUE, MODULE
, EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS
, ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME
, EXEC_ELAPSED_TIME, EXEC_CPU_TIME, EXEC_FOR_SEC
, RANK() OVER(PARTITION BY DBID ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK
, RANK() OVER(PARTITION BY DBID ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK
, RANK() OVER(PARTITION BY DBID ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK
, RANK() OVER(PARTITION BY DBID ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK
FROM TMP_SQLSTAT_HIST
WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR
CPU_TIME/ELAPSED_TIME <= 0.9) AND
(
(EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR
(EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR
(EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR
(EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR
(EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500) OR
(EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR
(EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR
(EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR
(EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR
(EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR
(EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR
(EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000)
)
) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외
--단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움
AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외
AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외
AND RNK = 1) A,
DBA_HIST_SQLTEXT B
WHERE A.DBID = B.DBID
AND A.SQL_ID = B.SQL_ID
AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30
A.BUFFER_GETS_RNK <= 30 OR --BUFFER_GET TOP 30
A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GET TOP 30
A.EXEC_FOR_SEC >= 10);1.2 GV$SQL(V$SQL) 이용 튜닝 대상 조회[편집]
- (실행수 TOP 30, BUFFER_GET TOP 30,실행당 BUFFER_GET TOP 30,EXEC_FOR_SEC >= 10 초당 10회 이상 실행 )
WITH TMP_SQLSTAT_HIST AS(
SELECT SQL_ID, INST_ID
, MAX(LAST_ACTIVE_TIME) SNAP_DATE
, MAX(PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME
, PLAN_HASH_VALUE
, MAX(MODULE) MODULE
, SUM(EXECUTIONS) EXECUTIONS
, SUM(BUFFER_GETS) BUFFER_GETS
, ROUND(SUM(BUFFER_GETS) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS))) EXEC_BUFFER_GETS
, SUM(DISK_READS) DISK_READS
, ROUND(SUM(DISK_READS) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS))) EXEC_DISK_READS
, SUM(ROWS_PROCESSED) ROWS_PROCESSED
, ROUND(SUM(ROWS_PROCESSED) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS))) EXEC_ROWS
, SUM(CPU_TIME) CPU_TIME
, ROUND(SUM(CPU_TIME) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS)) / 1000000, 6) EXEC_CPU_TIME
, SUM(CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME
, ROUND(SUM(CLUSTER_WAIT_TIME) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS)) / 1000000, 6) EXEC_CLWAIT_TIME
, SUM(ELAPSED_TIME) ELAPSED_TIME
, ROUND(SUM(ELAPSED_TIME) / DECODE(SUM(EXECUTIONS), 0, 1,
SUM(EXECUTIONS)) / 1000000, 6) EXEC_ELAPSED_TIME
FROM GV$SQL B
WHERE NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|
TOAD|golden|ERwin|PL.SQL Developer|
SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
AND PLAN_HASH_VALUE > 0
AND MODULE IS NOT NULL
GROUP BY SQL_ID, INST_ID, PLAN_HASH_VALUE
)
SELECT --같거나 비슷한 SQL은 같은 GROUP으로 표현
DENSE_RANK() OVER(ORDER BY DBMS_LOB.SUBSTR(B.SQL_TEXT, 100, 1)) SQL_GRP_TYPE
, A.INST_ID --최종 수행된 INSTANCE
, A.SNAP_DATE --최종 수행 일자
, A.SQL_ID --SQL_ID
, A.PLAN_HASH_VALUE --실행 계획에 종속적인 값
, A.MODULE --실행 모듈
, A.EXECUTIONS --총 실행수
, A.EXEC_ROWS --실행당 결과 건수
, A.EXEC_BUFFER_GETS --실행당 BUFFER_GET
, A.BUFFER_GETS --총 BUFFER_GET
, A.EXEC_DISK_READS --실행당 DISK_READ
, A.EXEC_ELAPSED_TIME --실행당 수행 시간
, A.EXEC_CPU_TIME --실행당 CPU 시간
, A.ELAPSED_TIME --총 수행시간
, A.CPU_TIME --총 CPU 시간
, A.CLUSTER_WAIT_TIME --총 CLUSTER 대기 시간
, ROUND(A.CPU_TIME / A.ELAPSED_TIME, 2) CPU_RATE -- 수행시간 대비 CPU 시간 비율
FROM (
SELECT SNAP_DATE, INST_ID, SQL_ID, PLAN_HASH_VALUE, MODULE
, EXECUTIONS, EXEC_ROWS, EXEC_BUFFER_GETS, BUFFER_GETS, EXEC_DISK_READS
, ELAPSED_TIME, CPU_TIME, CLUSTER_WAIT_TIME
, EXEC_ELAPSED_TIME, EXEC_CPU_TIME
, RANK() OVER(ORDER BY EXECUTIONS DESC) EXECUTIONS_RNK
, RANK() OVER(ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RNK
, RANK() OVER(ORDER BY EXEC_CPU_TIME DESC) EXEC_CPU_TIME_RNK
, RANK() OVER(ORDER BY EXEC_BUFFER_GETS DESC) EXEC_BUFFER_GETS_RNK
FROM TMP_SQLSTAT_HIST
WHERE NOT((CLUSTER_WAIT_TIME/DECODE(CPU_TIME, 0, 1, CPU_TIME) <= 0.95 OR
CPU_TIME/ELAPSED_TIME <= 0.9) AND
(
(EXECUTIONS >= 300000 AND EXEC_BUFFER_GETS <= 20) OR
(EXECUTIONS >= 100000 AND EXECUTIONS < 300000 AND EXEC_BUFFER_GETS <= 50) OR
(EXECUTIONS >= 50000 AND EXECUTIONS < 100000 AND EXEC_BUFFER_GETS <= 100) OR
(EXECUTIONS >= 10000 AND EXECUTIONS < 50000 AND EXEC_BUFFER_GETS <= 200) OR
(EXECUTIONS >= 5000 AND EXECUTIONS < 10000 AND EXEC_BUFFER_GETS <= 500) OR
(EXECUTIONS >= 3000 AND EXECUTIONS < 5000 AND EXEC_BUFFER_GETS <= 1000) OR
(EXECUTIONS >= 1000 AND EXECUTIONS < 3000 AND EXEC_BUFFER_GETS <= 2000) OR
(EXECUTIONS >= 500 AND EXECUTIONS < 1000 AND EXEC_BUFFER_GETS <= 3000) OR
(EXECUTIONS >= 100 AND EXECUTIONS < 500 AND EXEC_BUFFER_GETS <= 5000) OR
(EXECUTIONS >= 10 AND EXECUTIONS < 100 AND EXEC_BUFFER_GETS <= 10000) OR
(EXECUTIONS >= 3 AND EXECUTIONS < 10 AND EXEC_BUFFER_GETS <= 30000) OR
(EXECUTIONS < 3 AND EXEC_BUFFER_GETS <= 100000)
)
) --일간 실행수 대비 실행당 BUFFER_GETS가 크지 않은 경우 튜닝 대상 제외
--단 CPU_TIME 비율이 높으면 포함. 제외 기준은 변경 및 삭제가 자유로움
AND NOT(EXEC_ELAPSED_TIME <= 0.0005) --실행당 ELASED_TIME이 0.0005초 미만 제외
AND NOT(EXEC_ROWS*2 >= EXEC_BUFFER_GETS)--실행당 건수가 실행당 BUFFER_GET*2이상 제외
) A,
GV$SQL B
WHERE A.INST_ID = B.INST_ID
AND A.SQL_ID = B.SQL_ID
AND B.CHILD_NUMBER = 0
AND (A.EXECUTIONS_RNK <= 30 OR --실행수 TOP 30
A.BUFFER_GETS_RNK <= 30 OR --TOTAL BUFFER_GETS TOP 30
A.EXEC_BUFFER_GETS_RNK <= 30 OR --실행당 BUFFER_GETS TOP 30
(A.EXECUTIONS >= 100 AND A.EXEC_ELAPSED_TIME >= 10)); --실행수 100이상, 수행시간 10초 이상1.3 CPU를 많이 사용하는 세션의 식별 V$SESSTAT V$SESSION[편집]
SELECT A.SID , C.SERIAL# , A.VALUE
, C.USERNAME , C.STATUS , C.PROGRAM
FROM V$SESSTAT A
, V$STATNAME B
, V$SESSION C
WHERE A.STATISTIC# = B.STATISTIC#
AND A.SID = C.SID
AND B.NAME = 'CPU used by this session'
AND A.VALUE > 0 ORDER BY A.VALUE DESC;1.4 Disk Read 가 많은 SQL문 찾기 V$SQLAREA[편집]
SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS > 100
ORDER BY DISK_READS DESC;1.5 Rollback Segment를 사용하고 있는 SQL문 조회 V$ROLLSTAT[편집]
SELECT A.NAME , B.XACTS , C.SID
, C.SERIAL# , C.USERNAME , D.SQL_TEXT
FROM V$ROLLNAME A , V$ROLLSTAT B
, V$SESSION C , V$SQLTEXT D
, V$TRANSACTION E
WHERE A.USN = B.USN
AND B.USN = E.XIDUSN
AND C.TADDR = E.ADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
ORDER BY A.NAME, C.SID, D.PIECE;1.6 오래도록 수행되는 Full Table Scan를 모니터링 V$SESSION_LONGOPS[편집]
SELECT SID
, SERIAL#
, OPNAME
, TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START"
, (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE"
FROM V$SESSION_LONGOPS;1.7 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)[편집]
SELECT A.*
, (SELECT SS.SQL_TEXT
FROM V$SQLAREA SS
WHERE SS.ADDRESS = A.SQL_ADDRESS
AND ROWNUM <= 1
) AS SQL_TEST
FROM (
SELECT A.SID , C.SERIAL# , A.VALUE
, C.USERNAME , C.STATUS , C.PROGRAM
, C.SQL_ADDRESS
, ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN
FROM V$SESSTAT A
, V$STATNAME B
, V$SESSION C
WHERE A.STATISTIC# = B.STATISTIC#
AND A.SID = C.SID
AND B.NAME = 'CPU used by this session'
AND A.VALUE > 0
AND C.STATUS = 'ACTIVE'
AND C.USERNAME IS NOT NULL
) A
WHERE A.RN <= 10;1.8 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절) V$SQLAREA[편집]
SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.*
FROM V$SQLAREA A
, V$SESSION B
WHERE A.SQL_TEXT LIKE '%SELECT%'
AND A.ADDRESS = B.SQL_ADDRESS
AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000
-- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.)
AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
AND B.USERNAME IS NOT NULL;1.9 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리[편집]
SELECT B.USERNAME , A.SID , A.PGA_USAGE
, A.UGA_USAGE , A.CPU_USAGE_SECONDS , B.MACHINE
, B.PROGRAM , B.MODULE
FROM (SELECT B.SID
, MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE
, MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE
, MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS
FROM V$SESSTAT B
, V$STATNAME C
WHERE B.STATISTIC# = C.STATISTIC#
GROUP BY B.SID) A
, V$SESSION B
WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL;2 동일 SQL 구분 / 분류 방법[편집]
- SQL문 시작 100자 내외가 같으면 같은 SQL로 판단
- 동일한 업무별/동일한 신청자 별로 구분하여 튜닝 업무 진행
3 v$sql_plan 이용한 SQL 플랜 보기[편집]
select a.sql_id, a.child_number child,
a.id
, a.parent_id pid
----------------------------------------------------------------
, lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
|| decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
|| decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name
|| ' ('|| a.object_type ||')')
----------------------------------------------------------------(
|| decode(a.cost||a.cardinality||a.bytes,null,null,' (')
|| decode(a.cost, null, null, ' Cost='||a.cost)
|| decode(a.cardinality, null, null, ' Card='||a.cardinality)
|| decode(a.bytes, null, null, ' Bytes='||a.bytes)
|| decode(a.cost||a.cardinality||a.bytes,null,null,' )')
----------------------------------------------------------------)
|| decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
---------------------------------------------------------------
, a.object_type "Type"
----------------------------------------------------------------
, a.cardinality "E-Rows"
, a.bytes "E-Bytes"
, a.cost
, a.time "E-Time"
----------------------------------------------------------------
, a.partition_start "Pstart"
, a.partition_stop "Pstop"
, a.partition_id "Pid"
----------------------------------------------------------------
, a.qblock_name "QB"
, a.access_predicates
, a.filter_predicates
----------------------------------------------------------------
from v$sql_plan a
where 1 = 1
and a.sql_id = '5q1b8cn1s123u' --and plan_hash_value = ''
-- and a.child_number = 1
order by a.sql_id, a.id, a.child_number;