-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathxplan_objinfos_body.sql
385 lines (346 loc) · 15 KB
/
xplan_objinfos_body.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
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
--------------------------------------------------------------------------------
-- Author: Alberto Dell'Era
-- Copyright: (c) 2008-2021 Alberto Dell'Era http://www.adellera.it
--------------------------------------------------------------------------------
function calc_obj_info_seq (p_owner varchar2, p_seq_name varchar2)
return varchar2
is
l_str varchar2(200 char);
begin
for s in (select /*+ xplan_exec_marker */ sequence_name, min_value, max_value, increment_by,
cycle_flag, order_flag, cache_size, last_number
from sys.dba_sequences
where sequence_owner = p_owner
and sequence_name = p_seq_name)
loop
l_str := l_str || ' cache ' ||s.cache_size;
l_str := l_str || ' last_number=' ||s.last_number;
if s.min_value != 1 then l_str := l_str || ' minvalue '||s.min_value; end if;
if s.max_value != 999999999999999999999999999 then l_str := l_str || ' maxvalue '||s.max_value; end if;
if s.increment_by != 1 then l_str := l_str || ' increment by '||s.increment_by; end if;
if s.cycle_flag != 'N' then l_str := l_str || ' CYCLE'; end if;
if s.order_flag != 'N' then l_str := l_str || ' ORDER'; end if;
return l_str;
end loop;
return '* not found *';
end calc_obj_info_seq;
function calc_obj_info_syn (p_owner varchar2, p_syn_name varchar2)
return varchar2
is
l_str varchar2(100 char);
begin
-- print ('syn ' ||p_owner||' '||p_syn_name);
for s in (select /*+ ordered use_nl(o) xplan_exec_marker */ a.table_owner, a.table_name, a.db_link, o.object_type
from sys.dba_synonyms a, sys.dba_objects o
where a.owner = p_owner
and a.synonym_name = p_syn_name
and a.table_owner = o.owner
and a.table_name = o.object_name
and o.object_type not in ('SYNONYM','PACKAGE BODY','TYPE BODY','TABLE PARTITION')
order by 1,2,3,4)
loop
l_str := l_str || lower(s.object_type)||' '||s.table_owner||'.'||s.table_name;
if s.db_link is not null then
l_str := l_str || '@' || s.db_link;
end if;
l_str := l_str || ', ';
end loop;
return rtrim(trim(l_str),',');
end calc_obj_info_syn;
function calc_type_of_unknown(
p_owner varchar2,
p_name varchar2
)
return varchar2
is
l_type_string sys.dba_objects.object_type%type;
begin
--print( 'calc_type_of_unknown '||p_owner||'.'||p_name||' trying ...');
select /*+ xplan_exec_marker */ o.object_type
into l_type_string
from sys.dba_objects o
where owner = p_owner
and object_name = p_name;
print( 'resolved UNKNOWN_OBJECT_TYPE as '||p_owner||'.'||p_name||' -> '||l_type_string);
return l_type_string;
exception
when no_data_found then
return 'UNKNOWN_OBJECT_TYPE';
when too_many_rows then
return 'UNKNOWN_OBJECT_TYPE';
end calc_type_of_unknown;
procedure print_obj_dep_and_store_1 (
p_owner varchar2,
p_name varchar2,
p_type_string varchar2
)
is
l_object_str varchar2(100 char);
l_object_str_unk varchar2(100 char);
l_type_string varchar2(50 char);
l_veedollar_is_fixed_view varchar2(1 char);
l_append_str varchar2(200 char);
begin
-- many objects in 10g and 11g have UNKNOWN_OBJECT_TYPE as their type
-- the following block reconstructs the type if p_owner/p_name identify
-- it unambigously
if p_type_string = 'UNKNOWN_OBJECT_TYPE' then
l_object_str_unk := p_owner || '.' || p_name;
if m_all_non_tab_objects_unk.exists( l_object_str_unk ) then
l_type_string := m_all_non_tab_objects_unk( l_object_str_unk );
else
l_type_string := calc_type_of_unknown( p_owner, p_name );
m_all_non_tab_objects_unk( l_object_str_unk ) := l_type_string;
end if;
else
l_type_string := p_type_string;
end if;
l_object_str := l_type_string || '.' || p_owner || '.' || p_name;
if m_all_non_tab_objects_skip.exists (l_object_str) then
return;
end if;
if not m_all_non_tab_objects.exists (l_object_str) then
if p_type_string = 'PACKAGE' and p_owner = 'SYS' and p_name in ('DBMS_OUTPUT','STANDARD','DBMS_STANDARD') then
m_all_non_tab_objects_skip (l_object_str) := 'X';
return;
end if;
-- almost all of the gv$ and v$ are generically reported as views in 9i, 10g(?), 11g,
-- but they are fixed views, hence not present in dba_views
-- E.g. SYS.V$OBJECT_USAGE is a view, not a fixed view; almost all others are fixed views
if p_type_string = 'VIEW' and p_owner = 'SYS' and (p_name like 'V$%' or p_name like 'GV$%') then
select /*+ xplan_exec_marker */ decode (count(*), 0, 'N', 'Y')
into l_veedollar_is_fixed_view
from sys.v_$fixed_view_definition
where view_name = p_name
and rownum = 1;
if l_veedollar_is_fixed_view = 'Y' then
l_type_string := 'FIXED VIEW';
l_object_str := l_type_string || '.' || p_owner || '.' || p_name;
end if;
end if;
if l_type_string = 'SYNONYM' then
m_all_non_tab_objects (l_object_str) := calc_obj_info_syn (p_owner, p_name);
elsif l_type_string = 'SEQUENCE' then
m_all_non_tab_objects (l_object_str) := calc_obj_info_seq (p_owner, p_name);
else
m_all_non_tab_objects (l_object_str) := 'X';
end if;
end if;
if l_type_string = 'SYNONYM' then
l_append_str := ' -> ' || m_all_non_tab_objects (l_object_str);
elsif l_type_string = 'SEQUENCE' then
l_append_str := m_all_non_tab_objects (l_object_str);
end if;
-- print dependency
print ('- depends on ' || lower(l_type_string) || ' ' || p_owner || '.' || p_name || l_append_str);
end print_obj_dep_and_store_1;
procedure print_obj_dep_and_store (
p_inst_id sys.gv_$sql.inst_id%type,
p_address sys.gv_$sql.address%type,
p_hash_value sys.gv_$sql.hash_value%type
)
is
begin
-- found in pro :CURSOR,FUNCTION,LIBRARY,NON-EXISTENT,PACKAGE,PROCEDURE,SEQUENCE,SUMMARY,SYNONYM,TABLE,TRIGGER,TYPE,VIEW
for d in (select /*+ xplan_exec_marker */
to_owner,
to_name,
-- following decode() is from 11.1.0.7 gv$db_object_cache (same as in 9.2.0.8)
-- see 43767.1 for meaning of NON-EXISTENT and INVALID TYPE
decode(to_type, 0,'CURSOR',1,'INDEX',2,'TABLE', 3,'CLUSTER',4,'VIEW', 5,'SYNONYM',6,'SEQUENCE',
7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',10, 'NON-EXISTENT',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',
14, 'TYPE BODY', 15,'OBJECT',16,'USER',17,'DBLINK',18,'PIPE',19,'TABLE PARTITION', 20,'INDEX PARTITION',21,'LOB',
22,'LIBRARY',23,'DIRECTORY',24,'QUEUE', 25,'INDEX-ORGANIZED TABLE',26,'REPLICATION OBJECT GROUP',
27,'REPLICATION PROPAGATOR', 28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',31,'JAVA JAR',
32,'INDEX TYPE',33, 'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION', 36, 'REPLICATED TABLE OBJECT',
37,'REPLICATION INTERNAL PACKAGE', 38, 'CONTEXT POLICY',39,'PUB_SUB',40,'LOB PARTITION',41,'LOB SUBPARTITION',
42,'SUMMARY',43,'DIMENSION',44,'APP CONTEXT',45,'STORED OUTLINE',46,'RULESET', 47,'RSRC PLAN',
48,'RSRC CONSUMER GROUP',49,'PENDING RSRC PLAN', 50,'PENDING RSRC CONSUMER GROUP',51,'SUBSCRIPTION',
52,'LOCATION', 53,'REMOTE OBJECT', 54,'SNAPSHOT METADATA',55,'XDB', 56,'JAVA SHARED DATA',57,'SECURITY PROFILE',
'INVALID TYPE') as to_type_string,
to_type
from sys.gv_$object_dependency
where inst_id = p_inst_id
and from_address = p_address
and from_hash = p_hash_value
and to_type not in (0,2,10,25,34,35)
and to_type between 1 and 70
order by to_type, to_owner, to_name)
loop
print_obj_dep_and_store_1 (
p_owner => d.to_owner, p_name => d.to_name,
-- do not change UNKNOWN_OBJECT_TYPE string !!
p_type_string => case when d.to_type_string = 'INVALID TYPE' then 'UNKNOWN_OBJECT_TYPE' else d.to_type_string end
);
end loop;
end print_obj_dep_and_store;
procedure print_obj_info_view (p_owner varchar2, p_view_name varchar2)
is
l_cols_string long;
begin
for c in (select /*+ xplan_exec_marker */ column_id, column_name, data_type
from sys.dba_tab_cols
where owner = p_owner
and table_name = p_view_name
order by column_id)
loop
l_cols_string := l_cols_string||'#'||c.column_id||' '||c.column_name ||'('||c.data_type||'),';
end loop;
print ('view columns: '||rtrim(l_cols_string,','));
print_long (p_query => 'select /*+ xplan_exec_marker */ text from sys.dba_views where owner = :1 and view_name = :2',
p_bind_1_name => ':1', p_bind_1_value => p_owner,
p_bind_2_name => ':2', p_bind_2_value => p_view_name);
exception
when no_data_found then
null;
end print_obj_info_view;
procedure print_obj_info_mview (p_owner varchar2, p_mview_name varchar2)
is
l_cols_string long;
l_table_name varchar2(128 char);
l_object_id number;
begin
for m in (select /*+ xplan_exec_marker */ container_name, compile_state, staleness, last_refresh_date
from sys.dba_mviews
where owner = p_owner
and mview_name = p_mview_name)
loop
l_table_name := m.container_name;
print ('compile_state: '||m.compile_state||' staleness: '||m.staleness
||' last_refresh_date: '||to_char (m.last_refresh_date, 'yyyy-mm-dd/hh24:mi:ss'));
end loop;
for c in (select /*+ xplan_exec_marker */ column_id, column_name, data_type
from sys.dba_tab_cols
where owner = p_owner
and table_name = l_table_name
order by column_id)
loop
l_cols_string := l_cols_string||'#'||c.column_id||' '||c.column_name ||'('||c.data_type||'),';
end loop;
print ('view columns: '||rtrim(l_cols_string,','));
print_long (p_query => 'select /*+ xplan_exec_marker */ query from sys.dba_mviews where owner = :1 and mview_name = :2',
p_bind_1_name => ':1', p_bind_1_value => p_owner,
p_bind_2_name => ':2', p_bind_2_value => p_mview_name);
select /*+ xplan_exec_marker */ object_id
into l_object_id
from sys.dba_objects
where owner = p_owner
and object_name = l_table_name
and object_type = 'TABLE';
print ('table holding MV data:');
print_cache_table_infos (l_object_id);
exception
when no_data_found then
null;
end print_obj_info_mview;
procedure print_obj_info_fixed_view (p_owner varchar2, p_view_name varchar2)
is
l_cols_string long;
l_view_definition sys.v_$fixed_view_definition.view_definition%type;
begin
for c in (select /*+ xplan_exec_marker */ column_id, column_name, data_type
from sys.dba_tab_cols
where owner = p_owner
and table_name = replace (p_view_name, 'V$', 'V_$')
order by column_id)
loop
l_cols_string := l_cols_string||'#'||c.column_id||' '||c.column_name ||'('||c.data_type||'),';
end loop;
print ('fixed view columns: '||rtrim(l_cols_string,','));
select /*+ xplan_exec_marker */ view_definition
into l_view_definition
from sys.v_$fixed_view_definition
where view_name = p_view_name;
print (l_view_definition);
exception
when no_data_found then
null;
end print_obj_info_fixed_view;
procedure print_obj_info_assoc_stats (p_owner varchar2, p_name varchar2, p_type_str varchar2)
is
l_str varchar2(300 char);
l_str_cost varchar2(300 char);
l_str_stat varchar2(300 char);
begin
if p_type_str not in ('FUNCTION', 'PACKAGE', 'TYPE', 'INDEXTYPE') then
return;
end if;
for a in (select /*+ xplan_exec_marker */ def_selectivity, def_cpu_cost, def_io_cost, def_net_cost,
statstype_schema, statstype_name
from sys.dba_associations
where object_owner = p_owner
and object_name = p_name
and object_type = p_type_str)
loop
l_str := 'ASSOCIATED STATISTICS: ';
if a.def_selectivity is not null then l_str := l_str || ' default selectivity ('||a.def_selectivity||')'; end if;
l_str_cost := '';
if a.def_cpu_cost is not null then l_str_cost := l_str_cost || ' cpu='||a.def_cpu_cost; end if;
if a.def_io_cost is not null then l_str_cost := l_str_cost || ' io=' ||a.def_io_cost ; end if;
if a.def_net_cost is not null then l_str_cost := l_str_cost || ' net='||a.def_net_cost; end if;
if l_str_cost is not null then
l_str := l_str || ' default cost ('||trim(l_str_cost)||')';
end if;
if a.statstype_schema is not null then
l_str := l_str || ' using '||a.statstype_schema||'.'||a.statstype_name;
end if;
print (l_str);
end loop;
end print_obj_info_assoc_stats;
procedure print_obj_info_dba_source (p_owner varchar2, p_name varchar2, p_type_str varchar2)
is
begin
for l in (select /*+ xplan_exec_marker */ text
from sys.dba_source
where owner = p_owner
and name = p_name
and type = p_type_str
order by line)
loop
print (l.text);
end loop;
end print_obj_info_dba_source;
procedure print_objinfos
is
l_dot_1 number;
l_dot_2 number;
l_object_str varchar2(100 char);
l_type_str varchar2(100 char);
l_owner varchar2(100 char);
l_name varchar2(100 char);
begin
l_object_str := m_all_non_tab_objects.first;
loop
exit when l_object_str is null;
l_dot_1 := instr (l_object_str, '.', 1);
l_dot_2 := instr (l_object_str, '.', l_dot_1+1);
l_type_str := substr (l_object_str, 1, l_dot_1-1);
l_owner := substr (l_object_str, l_dot_1+1, (l_dot_2-l_dot_1-1) );
l_name := substr (l_object_str, l_dot_2+1 );
--print (l_type_str||' '||l_owner||' '||l_name);
if l_type_str not in ('SYNONYM','SEQUENCE','UNKNOWN_OBJECT_TYPE') then
print ('############################################# '||
case when l_type_str != 'SUMMARY' then lower(l_type_str) else '(summary) materialized view' end
||' '||l_owner||'.'||l_name||' ###');
end if;
if l_type_str in ('SYNONYM','SEQUENCE') then
null;
elsif l_type_str = 'VIEW' then
print_obj_info_view (l_owner, l_name);
elsif l_type_str = 'FIXED VIEW' then
print_obj_info_fixed_view (l_owner, l_name);
-- PACKAGE BODY and TYPE BODY should not be possible for SQL and PL/SQL
elsif l_type_str in ('FUNCTION','PROCEDURE','TYPE','TYPE BODY','PACKAGE','PACKAGE BODY','TRIGGER') then
print_obj_info_assoc_stats (l_owner, l_name, l_type_str);
print_obj_info_dba_source (l_owner, l_name, l_type_str);
elsif l_type_str in ('SUMMARY') then
print_obj_info_mview (l_owner, l_name);
elsif l_type_str in ('INDEX TYPE') then
print_obj_info_assoc_stats (l_owner, l_name, 'INDEXTYPE');
print ('xplan: infos for index types not implemented');
elsif l_type_str in ('OPERATOR') then
print ('xplan: infos for operators not implemented');
end if;
l_object_str := m_all_non_tab_objects.next (l_object_str);
end loop;
end print_objinfos;