-
Notifications
You must be signed in to change notification settings - Fork 318
/
Copy pathxb.sql
358 lines (341 loc) · 19.9 KB
/
xb.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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- File name: xb (eXplain Better)
--
-- Purpose: Explain a SQL statements execution plan with execution
-- profile directly from library cache - for the last
-- SQL executed in current session (see also xbi.sql)
--
-- Author: Tanel Poder
-- Copyright: (c) https://blog.tanelpoder.com
--
-- Usage: 1) alter session set statistics_level = all;
-- 2) Run the statement you want to explain
-- 3) @xb.sql
--
-- Other: You can add a GATHER_PLAN_STATISTICS hint to the statement instead
-- if you dont want to use "alter session set statistics_level"
-- for some reason (this hint works on Oracle 10.2 and higher)
--
-- TODO: Noteworthy outstanding items are:
-- * formatting, decide what columns to show by default
-- * clone to an @xbx.sql (eXtended version) with wider output and stuff like plan outline hints shown etc
-- currently you can just comment/uncomment sections in this script
-- * clone to @xbad.sql for showing full adaptive plans
-- * add an option to gather/report metrics of all PX slaves
--
--------------------------------------------------------------------------------
prompt -- xb.sql: eXplain Better v1.01 for prev SQL in the current session - by Tanel Poder (https://blog.tanelpoder.com)
set verify off pagesize 5000 tab off lines 999
column xbi_child_number heading "Ch|ld" format 999
column xbi_sql_id heading "SQL_ID" for a13
column xbi_sql_child_number heading "CHLD" for 9999
column xbi_sql_addr heading "ADDRESS"
column xbi_sql_id_text heading ""
column xbi_seconds_ago heading "First Load Time"
column xbi_id heading "Op|ID" for 9999 justify right
column xbi_parent_id heading "Par.|ID" for 9999 justify right
column xbi_id2 heading "Op|ID" for a5 justify right
column xbi_pred heading "Pred|#Col" for a5 justify right
column xbi_pos heading "#Sib|ling" for 9999
column xbi_optimizer heading "Optimizer|Mode" format a10
column xbi_plan_step heading "Operation" for a55
column xbi_plan_line heading "Row Source" for a72
column xbi_qblock_name heading "Query Block|name" for a20 PRINT
column xbi_object_name heading "Object|Name" for a30
column xbi_object_node heading "Object|Node" for a10
column xbi_opt_cost heading "Optimizer|Cost" for 9999999999
column xbi_opt_card heading "Est. rows|per Start" for 999999999999
column xbi_opt_card_times_starts heading "Est. rows|total" for 999999999999
column xbi_opt_card_misestimate heading "Opt. Card.|misestimate" for a15 justify right
column xbi_opt_bytes heading "Estimated|output bytes" for 999999999999
column xbi_predicate_info heading "Predicate Information (identified by operation id):" format a100 word_wrap
column xbi_cpu_cost heading "CPU|Cost" for 9999999
column xbi_io_cost heading "IO|Cost" for 9999999
column xbi_last_output_rows heading "Real #rows|returned" for 9999999999
column xbi_last_starts heading "Rowsource|starts" for 999999999
column xbi_last_rows_start heading "#Rows ret/|per start" for 999999999
column xbi_last_cr_buffer_gets heading "Consistent|gets" for 999999999
column xbi_last_cr_buffer_gets_row heading "Consistent|gets/row" for 999999999
column xbi_last_cu_buffer_gets heading "Current|gets" for 999999999
column xbi_last_cu_buffer_gets_row heading "Current|gets/row" for 999999999
column xbi_last_disk_reads heading "Physical|read blks" for 999999999
column xbi_last_disk_writes heading "Physical|write blks" for 999999999
column xbi_last_elapsed_time_ms heading "cumulative ms|spent in branch" for 9,999,999.99 noprint
column xbi_self_elapsed_time_ms heading "ms spent in|this operation" for 9,999,999.99
column xbi_self_cr_buffer_gets heading "Consistent|gets" for 999999999
column xbi_self_cr_buffer_gets_row heading "Consistent|gets/row" for 999999999
column xbi_self_cu_buffer_gets heading "Current|gets" for 999999999
column xbi_self_cu_buffer_gets_row heading "Current|gets/row" for 999999999
column xbi_self_disk_reads heading "Physical|read blks" for 999999999
column xbi_self_disk_writes heading "Physical|write blks" for 999999999
column xbi_last_memory_used heading "Memory|used (MB)" for 9,999,999.99
column xbi_last_execution heading "Workarea|Passes" for a13
column xbi_sql_plan_hash_value heading "Plan Hash Value" for 9999999999
column xbi_plan_hash_value_text noprint
column xbi_outline_hints heading "Outline Hints" for a120 word_wrap
column xbi_notes heading "Plan|Notes" for a120 word_wrap
column xbi_sql_id heading "SQL_ID" for a13 new_value xbi_sql_id
column xbi_sql_child_number heading "CHLD" for 9999 new_value xbi_sql_child_number
column xbi_sql_addr heading "ADDRESS" new_value xbi_sql_addr
set feedback off
select
'Cursor: ' xbi_sql_id_text,
sql.sql_id xbi_sql_id,
sql.child_number xbi_sql_child_number,
sql.address xbi_sql_addr,
' PLAN_HASH_VALUE: ' xbi_plan_hash_value_text,
sql.plan_hash_value xbi_sql_plan_hash_value,
'Statement first parsed at: '|| sql.first_load_time ||' - '||
round( (sysdate - to_date(sql.first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*86400 ) || ' seconds ago' xbi_seconds_ago
from
v$sql sql,
all_users usr
where
sql.parsing_user_id = usr.user_id
and (sql.sql_id,sql.child_number,sql.address) = (SELECT prev_sql_id,prev_child_number,prev_sql_addr
FROM v$session WHERE sid = USERENV('SID'))
/
WITH sq AS (
SELECT
sp.id, sp.parent_id, sp.operation, sp.options, sp.object_owner, sp.object_name
, ss.last_elapsed_time, ss.last_cr_buffer_gets, ss.last_cu_buffer_gets, ss.last_disk_reads, ss.last_disk_writes
FROM v$sql_plan_statistics_all ss INNER JOIN
v$sql_plan sp
ON (
sp.sql_id=ss.sql_id
AND sp.child_number=ss.child_number
AND sp.address=ss.address
AND sp.id=ss.id
)
AND sp.sql_id='&xbi_sql_id'
AND sp.child_number = TO_NUMBER('&xbi_sql_child_number')
AND sp.address = hextoraw('&xbi_sql_addr')
), deltas AS (
SELECT
par.id
, par.last_elapsed_time - SUM(chi.last_elapsed_time ) self_elapsed_time
, par.last_cr_buffer_gets - SUM(chi.last_cr_buffer_gets) self_cr_buffer_gets
, par.last_cu_buffer_gets - SUM(chi.last_cu_buffer_gets) self_cu_buffer_gets
, par.last_disk_reads - SUM(chi.last_disk_reads ) self_disk_reads
, par.last_disk_writes - SUM(chi.last_disk_writes ) self_disk_writes
FROM sq par LEFT OUTER JOIN
sq chi
ON chi.parent_id = par.id
GROUP BY
par.id
, par.last_elapsed_time, par.last_cr_buffer_gets, par.last_cu_buffer_gets, par.last_disk_reads, par.last_disk_writes
), combined AS (
SELECT sq.id, sq.parent_id, sq.operation, sq.options
, sq.object_owner, sq.object_name
, sq.last_elapsed_time
, sq.last_cr_buffer_gets
, sq.last_cu_buffer_gets
, sq.last_disk_reads
, sq.last_disk_writes
, NVL(deltas.self_elapsed_time , sq.last_elapsed_time) self_elapsed_time
, NVL(deltas.self_cr_buffer_gets , sq.last_cr_buffer_gets) self_cr_buffer_gets
, NVL(deltas.self_cu_buffer_gets , sq.last_cu_buffer_gets) self_cu_buffer_gets
, NVL(deltas.self_disk_reads , sq.last_disk_reads) self_disk_reads
, NVL(deltas.self_disk_writes , sq.last_disk_writes) self_disk_writes
FROM
sq, deltas
WHERE
sq.id = deltas.id
),
adaptive_display_map AS (
SELECT
TO_NUMBER(EXTRACT(column_value, '/row/@op' )) id
, TO_NUMBER(EXTRACT(column_value, '/row/@par')) parent_id
, TO_NUMBER(EXTRACT(column_value, '/row/@dis')) display_id
, TO_NUMBER(EXTRACT(column_value, '/row/@dep')) depth
, TO_NUMBER(EXTRACT(column_value, '/row/@skp')) skip
FROM
v$sql_plan
, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(other_xml),'/*/display_map/row')))
WHERE
sql_id = '&xbi_sql_id'
AND child_number = TO_NUMBER('&xbi_sql_child_number')
AND address = hextoraw('&xbi_sql_addr')
AND other_xml IS NOT NULL
)
select
LPAD(
CASE WHEN p.filter_predicates IS NOT NULL THEN 'F' ELSE ' ' END ||
CASE WHEN p.access_predicates IS NOT NULL THEN CASE WHEN p.options LIKE 'STORAGE %' THEN 'S' ELSE 'A' END ELSE '' END ||
CASE p.search_columns WHEN 0 THEN NULL ELSE '#'||TO_CHAR(p.search_columns) END
, 5) xbi_pred,
NVL(CASE WHEN adm.skip IS NULL THEN NULL
WHEN adm.skip = 0 THEN adm.display_id
WHEN adm.skip = 1 THEN NULL
END
, p.id) xbi_id,
nvl(adm2.parent_id, p.parent_id) xbi_parent_id,
CASE WHEN p.id != 0 THEN p.position END xbi_pos,
LPAD(' ',NVL(adm.depth,p.depth),' ')|| p.operation || ' ' || p.options ||' '
||nvl2(p.object_name, '['||p.object_name||']', null)
xbi_plan_line,
CASE WHEN p.id = 0 THEN '>>> Plan totals >>>' ELSE p.qblock_name END xbi_qblock_name,
-- p.object_node xbi_object_node,
-- p.distribution xbi_distribution,
-- lpad(decode(p.id,0,'T ','')||trim(to_char(round(decode(p.id,0,c.last_elapsed_time,c.self_elapsed_time) /1000,2),'9,999,999.00')), 14) xbi_self_elapsed_time_ms,
round(decode(p.id,0,c.last_elapsed_time,c.self_elapsed_time) /1000,2) xbi_self_elapsed_time_ms,
decode(p.id,0,c.last_cr_buffer_gets,c.self_cr_buffer_gets) xbi_self_cr_buffer_gets,
ps.last_starts xbi_last_starts,
ps.last_output_rows xbi_last_output_rows,
p.cardinality * ps.last_starts xbi_opt_card_times_starts,
regexp_replace(lpad(to_char(round(
CASE WHEN (DECODE(ps.last_output_rows,0,1,ps.last_output_rows) / DECODE(p.cardinality*ps.last_starts,0,1,p.cardinality*ps.last_starts)) > 1 THEN -(DECODE(ps.last_output_rows,0,1,ps.last_output_rows) / DECODE(p.cardinality*ps.last_starts,0,1,p.cardinality*ps.last_starts))
WHEN (DECODE(ps.last_output_rows,0,1,ps.last_output_rows) / DECODE(p.cardinality*ps.last_starts,0,1,p.cardinality*ps.last_starts)) < 1 THEN 1/(DECODE(ps.last_output_rows,0,1,ps.last_output_rows) / DECODE(p.cardinality*ps.last_starts,0,1,p.cardinality*ps.last_starts))
WHEN (DECODE(ps.last_output_rows,0,1,ps.last_output_rows) / DECODE(p.cardinality*ps.last_starts,0,1,p.cardinality*ps.last_starts)) = 1 THEN 1
ELSE null
END
,0))||'x',15),'^ *x$') xbi_opt_card_misestimate,
-- c.self_cr_buffer_gets xbi_self_cr_buffer_gets,
-- c.self_cr_buffer_gets / DECODE(ps.last_output_rows,0,1,ps.last_output_rows) xbi_self_cr_buffer_gets_row,
decode(p.id,0,c.last_cu_buffer_gets,c.self_cu_buffer_gets) xbi_self_cu_buffer_gets,
-- c.self_cu_buffer_gets / DECODE(ps.last_output_rows,0,1,ps.last_output_rows) xbi_self_cu_buffer_gets_row,
decode(p.id,0,c.last_disk_reads,c.self_disk_reads) xbi_self_disk_reads,
decode(p.id,0,c.last_disk_writes,c.self_disk_writes) xbi_self_disk_writes,
round(ps.last_elapsed_time/1000,2) xbi_last_elapsed_time_ms,
-- ps.last_cr_buffer_gets xbi_last_cr_buffer_gets,
-- ps.last_cr_buffer_gets / DECODE(ps.last_output_rows,0,1,ps.last_output_rows) xbi_last_cr_buffer_gets_row,
-- ps.last_cu_buffer_gets xbi_last_cu_buffer_gets,
-- ps.last_cu_buffer_gets / DECODE(ps.last_output_rows,0,1,ps.last_output_rows) xbi_last_cu_buffer_gets_row,
-- ps.last_disk_reads xbi_last_disk_reads,
-- ps.last_disk_writes xbi_last_disk_writes,
ps.last_memory_used/1048576 xbi_last_memory_used,
ps.last_execution xbi_last_execution,
p.cost xbi_opt_cost
-- p.bytes xbi_opt_bytes,
-- p.cpu_cost xbi_cpu_cost,
-- p.io_cost xbi_io_cost,
-- p.other_tag,
-- p.other,
-- p.access_predicates,
-- p.filter_predicates,
from
v$sql_plan p
, v$sql_plan_statistics_all ps
, combined c
, adaptive_display_map adm
, adaptive_display_map adm2
where
p.address = ps.address (+)
and p.sql_id = ps.sql_id (+)
and p.plan_hash_value = ps.plan_hash_value (+)
and p.child_number = ps.child_number (+)
and p.id = ps.id (+)
and p.id = adm.id (+)
and adm.id = adm2.id (+)
and (adm.id IS NULL or adm.skip = 0)
and p.sql_id = '&xbi_sql_id'
and p.address = hextoraw('&xbi_sql_addr')
and p.child_number = TO_NUMBER(&xbi_sql_child_number)
and ps.id = c.id (+)
order by
p.id asc
/
WITH adaptive_display_map AS (
SELECT
TO_NUMBER(EXTRACT(column_value, '/row/@op' )) id
, TO_NUMBER(EXTRACT(column_value, '/row/@dis')) display_id
, TO_NUMBER(EXTRACT(column_value, '/row/@skp')) skip
FROM
v$sql_plan
, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(other_xml),'/*/display_map/row')))
WHERE
sql_id = '&xbi_sql_id'
AND child_number = TO_NUMBER('&xbi_sql_child_number')
AND address = hextoraw('&xbi_sql_addr')
AND other_xml IS NOT NULL
AND TO_NUMBER(EXTRACT(column_value, '/row/@skp')) != 1
)
select
xbi_id2,
xbi_qblock_name,
substr(dummy,1,0)||'-' " ", -- there's an ugly reason (bug) for this hack
xbi_predicate_info
from (
select
sp.sql_id xbi_sql_id,
LPAD(NVL(CASE WHEN adm.skip IS NULL THEN NULL
WHEN adm.skip = 0 THEN adm.display_id
WHEN adm.skip = 1 THEN NULL
END
, sp.id), 5, ' ') xbi_id2,
sp.filter_predicates dummy, -- looks like there's a bug in 11.2.0.3 where both pred cols have to be selected
CASE WHEN sp.options LIKE 'STORAGE %' THEN 'storage' ELSE 'access' END||'('|| substr(sp.access_predicates,1,3989) || ')' xbi_predicate_info,
sp.qblock_name xbi_qblock_name
from
v$sql_plan sp
, adaptive_display_map adm
where
sp.sql_id = '&xbi_sql_id'
and sp.child_number = TO_NUMBER(&xbi_sql_child_number)
AND sp.address = HEXTORAW('&xbi_sql_addr')
and sp.access_predicates is not null
and sp.id = adm.id (+)
union all
select
sp.sql_id xbi_sql_id,
--lpad(sp.id, 5, ' ') xbi_id2,
LPAD(NVL(CASE WHEN adm.skip IS NULL THEN NULL
WHEN adm.skip = 0 THEN adm.display_id
WHEN adm.skip = 1 THEN NULL
END
, sp.id), 5, ' ') xbi_id2,
sp.access_predicates dummy,
'filter('|| substr(sp.filter_predicates,1,3989) || ')' xbi_predicate_info,
sp.qblock_name xbi_qblock_name
from
v$sql_plan sp
, adaptive_display_map adm
where
sp.sql_id = '&xbi_sql_id'
and sp.child_number = TO_NUMBER(&xbi_sql_child_number)
and sp.address = HEXTORAW('&xbi_sql_addr')
and sp.filter_predicates is not null
and sp.id = adm.id (+)
)
order by
xbi_id2 asc,
xbi_predicate_info asc
/
-- this query can return ORA-600 in Oracle 10.2 due to Bug 5497611 - OERI[qctVCO:csform] from Xquery using XMLType constructor (Doc ID 5497611.8)
WITH sq AS (
SELECT other_xml
FROM v$sql_plan p
WHERE
p.sql_id = '&xbi_sql_id'
AND p.child_number = &xbi_sql_child_number
AND p.address = hextoraw('&xbi_sql_addr')
AND p.other_xml IS NOT NULL -- (the other_xml is not guaranteed to always be on plan line 1)
)
SELECT ' *' " ", 'Cardinality feedback = yes' xbi_notes FROM sq WHERE extractvalue(xmltype(sq.other_xml), '/*/info[@type = "cardinality_feedback"]') = 'yes'
UNION ALL SELECT ' *', 'SQL Stored Outline used = ' ||extractvalue(xmltype(sq.other_xml), '/*/info[@type = "outline"]') FROM sq WHERE extractvalue(xmltype(sq.other_xml), '/*/info[@type = "outline"]') IS NOT NULL
UNION ALL SELECT ' *', 'SQL Patch used = ' ||extractvalue(xmltype(sq.other_xml), '/*/info[@type = "sql_patch"]') FROM sq WHERE extractvalue(xmltype(sq.other_xml), '/*/info[@type = "sql_patch"]') IS NOT NULL
UNION ALL SELECT ' *', 'SQL Profile used = ' ||extractvalue(xmltype(sq.other_xml), '/*/info[@type = "sql_profile"]') FROM sq WHERE extractvalue(xmltype(sq.other_xml), '/*/info[@type = "sql_profile"]') IS NOT NULL
UNION ALL SELECT ' *', 'SQL Plan Baseline used = ' ||extractvalue(xmltype(sq.other_xml), '/*/info[@type = "baseline"]') FROM sq WHERE extractvalue(xmltype(sq.other_xml), '/*/info[@type = "baseline"]') IS NOT NULL
UNION ALL SELECT ' *', 'Adaptive Plan = ' ||extractvalue(xmltype(sq.other_xml), '/*/info[@type = "adaptive_plan"]') FROM sq WHERE extractvalue(xmltype(sq.other_xml), '/*/info[@type = "adaptive_plan"]') IS NOT NULL
/
-- === Outline Hints ===
WITH sq AS (
SELECT other_xml
FROM v$sql_plan p
WHERE
p.sql_id = '&xbi_sql_id'
AND p.child_number = &xbi_sql_child_number
AND p.address = hextoraw('&xbi_sql_addr')
AND p.other_xml IS NOT NULL
)
SELECT
SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'),1,4000) xbi_outline_hints
FROM
sq
, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(sq.other_xml), '/*/outline_data/hint'))) D
/
set feedback on
PROMPT