-
Notifications
You must be signed in to change notification settings - Fork 20
/
TCF_LIO_explain_plan.sql
123 lines (121 loc) · 6.62 KB
/
TCF_LIO_explain_plan.sql
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
-- script by Kyle Hailey
-- see http://datavirtualizer.com/power-of-display_cursor/
-- ELAPSED is incremental not the obfuscated default cumulative
-- LIO_RATIO number of buffers accessed per row returned. Ideally 1 buffer or less is accessed per row returned.
-- Enter value for sql_id: g2w9n4gksyys6
-- old 59: stats.sql_id='&v_sql_id' and
-- new 59: stats.sql_id='g2w9n4gksyys6' and
--
-- CN ELAPSED LIO_RATIO TCF_GRAPH E_ROWS A_ROWS operation
-- --- ------------ --------- ------ ------------ ------------ ------------------------------------------------------------
-- 0 0 0 1 SELECT STATEMENT
-- 5,720,456 0 1 1 HASH GROUP BY
-- 29,711 0 1,909 NESTED LOOPS
-- 0 0 +++ 1 1,909 NESTED LOOPS
-- 1,969,304 0 +++ 1 1,909 NESTED LOOPS
-- 0 0 +++ 1 2,027 NESTED LOOPS
-- 7,939,649 0 +++ 1 1,656 NESTED LOOPS
-- 716,054 0 +++ 1 1,657 NESTED LOOPS
-- 270,201 0 ++ 39 23,171 HASH JOIN
-- 23 0 5 1 JOIN FILTER CREATE :BF0000
-- 31 1 5 1 TABLE ACCESS BY INDEX ROWID PS_PAY_CALENDAR
-- 14 2 5 1 INDEX RANGE SCAN PS0PAY_CALENDAR
-- 141,467 0 18,503 23,171 VIEW VW_SQ_1
-- 3,032,120 0 18,503 23,171 HASH GROUP BY
-- 152,564 0 163,420 33,020 JOIN FILTER USE :BF0000
-- 407,746 0 163,420 33,020 MERGE JOIN
-- 55 0 5 1 SORT JOIN
-- 12 2 5 1 INDEX RANGE SCAN PS0PAY_CALENDAR
-- 79,435 0 40,000 33,020 SORT JOIN
-- 119,852 0 40,000 40,000 INDEX FAST FULL SCAN WB_JOB
-- 2,959,031 13 - 23,171 1,657 TABLE ACCESS BY INDEX ROWID WB_JOB
-- 944,887 1 23,171 23,174 INDEX RANGE SCAN WB_JOB
-- 102,650 0 1,657 1,656 VIEW PUSHED PREDICATE VW_SQ_2
-- 73,769 0 1,657 1,657 SORT AGGREGATE
-- 25,617 0 1,657 1,657 FIRST ROW
-- 225,497 1 1,657 1,657 INDEX RANGE SCAN (MIN/MAX) WB_JOB
-- 357,872 0 3,312 2,027 TABLE ACCESS BY INDEX ROWID WB_RETROPAY_EARNS
-- 3,655,774 1 3,312 2,027 INDEX RANGE SCAN WB_RETROPAY_EARNS_IDX1
-- 199,884 0 2,027 1,909 TABLE ACCESS BY INDEX ROWID PS_RETROPAY_RQST
-- 317,793 1 2,027 1,909 INDEX RANGE SCAN PS_RETROPAY_RQST
-- 71,534 0 1,909 1,909 INDEX RANGE SCAN PS#RETROPAYPGM_TBL
-- 18,396 0 1,909 1,909 TABLE ACCESS BY INDEX ROWID PS_RETROPAYPGM_TBL
--
--
col cn format 99
col ratio format 99
col ratio1 format A6
--set pagesize 1000
set linesize 140
break on sql_id on cn
col lio_rw format 999
col "operation" format a60
col a_rows for 999,999,999
col e_rows for 999,999,999
col elapsed for 999,999,999
Def v_sql_id=&SQL_ID
select
-- sql_id,
--hv,
childn cn,
--ptime, stime,
case when stime - nvl(ptime ,0) > 0 then
stime - nvl(ptime ,0)
else 0 end as elapsed,
nvl(trunc((lio-nvl(plio,0))/nullif(a_rows,0)),0) lio_ratio,
--id,
--parent_id,
--starts,
--nvl(ratio,0) TCF_ratio,
' '||case when ratio > 0 then
rpad('-',ratio,'-')
else
rpad('+',ratio*-1 ,'+')
end as TCF_GRAPH,
starts*cardinality e_rows,
a_rows,
--nvl(lio,0) lio, nvl(plio,0) parent_lio,
"operation"
from (
SELECT
stats.LAST_ELAPSED_TIME stime,
p.elapsed ptime,
stats.sql_id sql_id
, stats.HASH_VALUE hv
, stats.CHILD_NUMBER childn
, to_char(stats.id,'990')
||decode(stats.access_predicates,null,null,'A')
||decode(stats.filter_predicates,null,null,'F') id
, stats.parent_id
, stats.CARDINALITY cardinality
, LPAD(' ',depth)||stats.OPERATION||' '||
stats.OPTIONS||' '||
stats.OBJECT_NAME||
DECODE(stats.PARTITION_START,NULL,' ',':')||
TRANSLATE(stats.PARTITION_START,'(NRUMBE','(NR')||
DECODE(stats.PARTITION_STOP,NULL,' ','-')||
TRANSLATE(stats.PARTITION_STOP,'(NRUMBE','(NR') "operation",
stats.last_starts starts,
stats.last_output_rows a_rows,
(stats.last_cu_buffer_gets+stats.last_cr_buffer_gets) lio,
p.lio plio,
trunc(log(10,nullif
(stats.last_starts*stats.cardinality/
nullif(stats.last_output_rows,0),0))) ratio
FROM
v$sql_plan_statistics_all stats
, (select sum(last_cu_buffer_gets + last_cr_buffer_gets) lio,
sum(LAST_ELAPSED_TIME) elapsed,
child_number,
parent_id,
sql_id
from v$sql_plan_statistics_all
group by child_number,sql_id, parent_id) p
WHERE
stats.sql_id='&v_sql_id' and
p.sql_id(+) = stats.sql_id and
p.child_number(+) = stats.child_number and
p.parent_id(+)=stats.id
)
order by sql_id, childn , id
/