/
xmsi.sql
169 lines (152 loc) · 5.69 KB
/
xmsi.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
-- 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: xmsi (eXplain from Memory with Statistics lookup by SQL ID)
--
-- Purpose: Explain a SQL statements execution plan with execution
-- profile directly from library cache
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage: 1) alter session set statistics_level = all;
-- 2) Run the statement you want to explain
-- 3) @xmsi <sqlid> <child#>
--
-- 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)
--
--------------------------------------------------------------------------------
set verify off heading off feedback off linesize 299 pagesize 5000 tab off
column xms_child_number heading "Ch|ld" format 99
break on xms_child_number skip 1
column xms_id heading Op|ID format 999
column xms_id2 heading Op|ID format a6
column xms_pred heading Pr|ed format a2
column xms_optimizer heading Optimizer|Mode format a10
column xms_plan_step heading Operation for a55
column xms_object_name heading Object|Name for a30
column xms_opt_cost heading Optimizer|Cost for 99999999999
column xms_opt_card heading "Estimated|output rows" for 999999999999
column xms_opt_bytes heading "Estimated|output bytes" for 999999999999
column xms_predicate_info heading "Predicate Information (identified by operation id):" format a100 word_wrap
column xms_cpu_cost heading CPU|Cost for 9999999
column xms_io_cost heading IO|Cost for 9999999
column xms_last_output_rows heading "Real #rows|returned" for 999999999
column xms_last_starts heading "Rowsource|starts" for 999999999
column xms_last_cr_buffer_gets heading "Consistent|gets" for 999999999
column xms_last_cu_buffer_gets heading "Current|gets" for 999999999
column xms_last_disk_reads heading "Physical|reads" for 999999999
column xms_last_disk_writes heading "Physical|writes" for 999999999
column xms_last_elapsed_time_ms heading "ms spent in|operation" for 9,999,999.99
--select
-- 'Warning: statistics_level is not set to ALL!'||chr(10)||
-- 'Run: alter session set statistics_level=all before executing your query...' warning
--from
-- v$parameter
--where
-- name = 'statistics_level'
--and lower(value) != 'all'
--/
select --+ ordered use_nl(mys ses) use_nl(mys sql)
'SQL ID: ' xms_sql_sql_id_text,
sql.sql_id xms_sql_id,
' Cursor address: ' xms_cursor_address_text,
sql.address xms_sql_address,
' | 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' xms_seconds_ago
from
v$sql sql,
all_users usr
where
sql.parsing_user_id = usr.user_id
and sql.sql_id = '&1'
and to_char(sql.child_number) like '&2'
order by
sql.sql_id asc,
sql.child_number asc
/
set heading on
select --+ ordered use_nl(p ps)
p.child_number xms_child_number,
case when p.access_predicates is not null then 'A' else ' ' end ||
case when p.filter_predicates is not null then 'F' else ' ' end xms_pred,
p.id xms_id,
lpad(' ',p.depth*1,' ')|| p.operation || ' ' || p.options xms_plan_step,
p.object_name xms_object_name,
-- p.search_columns,
-- p.optimizer xms_optimizer,
round(ps.last_elapsed_time/1000,2)
xms_last_elapsed_time_ms,
p.cardinality xms_opt_card,
ps.last_output_rows xms_last_output_rows,
ps.last_starts xms_last_starts,
ps.last_cr_buffer_gets xms_last_cr_buffer_gets,
ps.last_cu_buffer_gets xms_last_cu_buffer_gets,
ps.last_disk_reads xms_last_disk_reads,
ps.last_disk_writes xms_last_disk_writes,
p.cost xms_opt_cost
-- p.bytes xms_opt_bytes,
-- p.cpu_cost xms_cpu_cost,
-- p.io_cost xms_io_cost,
-- p.other_tag,
-- p.other,
-- p.distribution,
-- p.access_predicates,
-- p.filter_predicates,
from
v$sql_plan p,
v$sql_plan_statistics ps
where
p.address = ps.address(+)
and p.sql_id = ps.sql_id(+)
and p.child_number = ps.child_number(+)
and p.id = ps.operation_id(+)
and p.sql_id = '&1'
and to_char(p.child_number) like '&2' -- to_char is just used for convenient filtering using % for all children
order by
p.sql_id asc,
p.address asc,
p.child_number asc,
p.id asc
/
prompt
select
xms_child_number,
xms_id2,
xms_predicate_info
from (
select
sql_id xms_sql_id,
child_number xms_child_number,
lpad(id, 5, ' ') xms_id2,
' - access('|| substr(access_predicates,1,3989) || ')' xms_predicate_info
from
v$sql_plan
where
sql_id = '&1'
and to_char(child_number) like '&2'
and access_predicates is not null
union all
select
sql_id xms_sql_id,
child_number xms_child_number,
lpad(id, 5, ' ') xms_id2,
' - filter('|| substr(filter_predicates,1,3989) || ')' xms_predicate_info
from
v$sql_plan
where
sql_id = '&1'
and to_char(child_number) like '&2'
and filter_predicates is not null
)
order by
xms_sql_id asc,
xms_child_number asc,
xms_id2 asc,
xms_predicate_info asc
/
prompt
set feedback on