Question: I have a slow
data dictionary query using the v$ views and because I
cannot change the view definition, how can I tune a data
dictionary query for faster performance?
Answer: Before
tuning any dictionary query, make sure that you have gathered
fixed statistics on the data dictionary. Collection for
Dictionary Objects is easy, you can gather fixed object
statistics by using the dbms_stats
gather_database_stats procedure and setting the
gather_fixed argument to TRUE (the default is FALSE). You
can also invoke the gather_fixed_object_stats
procedure:
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL')
There are several
ways to tune a data dictionary SQL query:
-
Re-write the SQL to use the
x$ fixed structures:
You can look up the definitions of the v$
views by using grep in your
$ORACLE_HOME/rdbms/admin directory and
see how the x$ structures are mapped to v$
views. Writing the dictionary query with the
native x$ structures will be faster, but there is
no guarantee that Oracle will not change the x$
structures in a future release.
-
Use materialized views:
If your query is against dictionary entities that are
infrequently updated, you can create a materialized view to
make a single table from many v$ views. If you
choose to do this, I recommend re-creating the view nightly,
realizing that the data is current only through the previous
night's data.
Do NOT attempt to use materialized views again any of the
dynamic performance v$ views.
-
Try a /*+ rule */ hint: For testing a
v$ query performance, add a RULE hint to see if the
performance improves. In some cases, the primitive
rules-based optimizer might choose a faster execution plan
against the v$ views.
Queries against the data dictionary views
are especially problematic. Here is a query that accepts
two parameters and displays a range of days from the
all_objects view:
select
TO_DATE ('2009-06-10' /*+:1*/, 'YYYY-MM-DD')-1 + ROWNUM
AS MY_DAY from
all_objects
where
TO_DATE ('2009-05-26'/*+:2*/, 'YYYY-MM-DD')-1 + ROWNUM <=
TO_DATE('2009-06-08'
/*+:3*/, 'YYYY-MM-DD')
MY_DAY
06/10/2009 00:00:00
06/11/2009 00:00:00
06/12/2009 00:00:00
06/13/2009 00:00:00
06/14/2009
00:00:00
06/15/2009 00:00:00
06/16/2009 00:00:00
06/17/2009 00:00:00
06/18/2009 00:00:00
06/19/2009
00:00:00
06/20/2009 00:00:00
06/21/2009 00:00:00
06/22/2009 00:00:00
06/23/2009 00:00:00
Here is the original SQL execution plan, quite convoluted
and expensive:
ID PID Operation Name Rows Bytes Cost CPU
Cost IO Cost
0 SELECT STATEMENT 31576
3361K 255 201M 243
1 0 COUNT
2 1 FILTER
3 2 HASH JOIN 71259 7585K
255 201M 243
4 3 INDEX FULL SCAN
SYS.I_USER2 96 384 1 7321 1
5 3
HASH JOIN 71259 7306K 253 185M 242
6 5 INDEX FULL SCAN SYS.I_USER2 96
2208 1 7321 1
7 5 TABLE ACCESS
FULL SYS.OBJ$ 71259 5706K 251 170M 241
8 2 TABLE ACCESS BY INDEX ROWID SYS.IND$ 1
8 2 15801 2
9 8 INDEX UNIQUE
SCAN SYS.I_IND1 1 1 8171 1
10 2
HASH JOIN 1 24 3 8741280 2
11 10 INDEX RANGE SCAN SYS.I_OBJAUTH1 1
11 2 14730 2
12 10 FIXED TABLE
FULL SYS.X$KZSRO 100 1300 0 350000 0
13 2 FIXED TABLE FULL SYS.X$KZSPR 1 26
0 380297 0
14 2 HASH JOIN 1
24 3 8741280 2
15 14 INDEX RANGE
SCAN SYS.I_OBJAUTH1 1 11 2 14730 2
16 14 FIXED TABLE FULL SYS.X$KZSRO 100
1300 0 350000 0
17 2 FIXED TABLE
FULL SYS.X$KZSPR 1 26 0 380297 0
18 2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380490 0
19 2 NESTED LOOPS 2 48
2 151413 2
20 19 INDEX RANGE
SCAN SYS.I_OBJAUTH1 1 11 2 14913 2
21 19 FIXED TABLE FULL SYS.X$KZSRO 2 26
0 136500 0
22 2 NESTED LOOPS
23 22 NESTED LOOPS
1 77 8 411212 8
24 23
NESTED LOOPS 1 67 5 387358 5
25 24 NESTED LOOPS 1 56 4 378937
4
26 25 MERGE JOIN CARTESIAN
1 52 3 371615 3
27 26
INDEX RANGE SCAN SYS.I_OBJ5 1 39 3 21615 3
28 26 BUFFER SORT 100 1300
0 350000 0
29 28 FIXED
TABLE FULL SYS.X$KZSRO 100 1300 0 350000 0
30 25 INDEX RANGE SCAN SYS.I_USER2
1 4 1 7321 1
31 24 INDEX
RANGE SCAN SYS.I_OBJAUTH1 1 11 1 8421 1
32 23 INDEX RANGE SCAN SYS.I_DEPENDENCY1
3 2 15893 2
33 22 TABLE
ACCESS BY INDEX ROWID SYS.DEPENDENCY$ 1 10 3
23854 3
34 2 FIXED TABLE FULL SYS.X$KZSPR
1 26 0 370497 0
35 2 NESTED
LOOPS 2 76 2 153803 2
36 35
NESTED LOOPS 1 25 2 17303 2
37 36 TABLE ACCESS BY INDEX ROWID SYS.TRIGGER$
1 14 1 8881 1
38 37 INDEX
UNIQUE SCAN SYS.I_TRIGGER2 1 0 1050 0
39 36 INDEX RANGE SCAN SYS.I_OBJAUTH1 1
11 1 8421 1
40 35 FIXED TABLE
FULL SYS.X$KZSRO 2 26 0 136500 0
41 2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
370497 0
42 2 NESTED LOOPS
43 42 NESTED LOOPS 1 77
8 411213 8
44 43 NESTED LOOPS
1 67 5 387359 5
45 44
NESTED LOOPS 1 56 4 378938 4
46 45 MERGE JOIN CARTESIAN 1 52 3
371616 3
47 46 INDEX RANGE
SCAN SYS.I_OBJ5 1 39 3 21616 3
48 46 BUFFER SORT 100 1300 0
350000 0
49 48 FIXED TABLE
FULL SYS.X$KZSRO 100 1300 0 350000 0
50 45 INDEX RANGE SCAN SYS.I_USER2 1
4 1 7321 1
51 44 INDEX
RANGE SCAN SYS.I_OBJAUTH1 1 11 1 8421 1
52 43 INDEX RANGE SCAN SYS.I_DEPENDENCY1
3 2 15893 2
53 42 TABLE
ACCESS BY INDEX ROWID SYS.DEPENDENCY$ 1 10 3
23854 3
54 2 FIXED TABLE FULL SYS.X$KZSPR
1 26 0 370497 0
55 2 FIXED TABLE
FULL SYS.X$KZSPR 1 26 0 360300 0
56 2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380394 0
57 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380490 0
58 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
370497 0
59 2 VIEW 1 13 2
7271 2
60 59 FAST DUAL 1 2
7271 2
61 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 370497 0
62 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380394 0
63 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 370497 0
64 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380490 0
65 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
66 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
370497 0
67 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 360300 0
68 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380394 0
69 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
70 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
360300 0
71 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 370497 0
72 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380297 0
73 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
74 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380585 0
75 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380394 0
76 2
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380297 0
77 2 NESTED LOOPS 2 42
2 151143 2
78 77 INDEX RANGE
SCAN SYS.I_OBJAUTH1 1 8 2 14643 2
79 77 FIXED TABLE FULL SYS.X$KZSRO 2 26
0 136500 0
80 2 FIXED TABLE FULL
SYS.X$KZSPR 1 26 0 380490 0
81 2
NESTED LOOPS 2 42 2 151143 2
82 81 INDEX RANGE SCAN SYS.I_OBJAUTH1 1
8 2 14643 2
83 81 FIXED TABLE
FULL SYS.X$KZSRO 2 26 0 136500 0
84 2 FIXED TABLE FULL SYS.X$KZSPR 1 26 0
380585 0
85 2 VIEW 1 16 1
9621 1
86 85 SORT GROUP BY 1 86
1 9621 1
87 86 NESTED LOOPS
1 86 1 9621 1
88 87
MERGE JOIN CARTESIAN 1 78 0 1450 0
89 88 NESTED LOOPS 1 65 0
1250 0
90 89 INDEX UNIQUE SCAN
SYS.I_OLAP_CUBES$ 1 13 0 1050 0
91 89 TABLE ACCESS BY INDEX ROWID
SYS.OLAP_DIMENSIONALITY$ 1 52 0 200 0
92 91 INDEX RANGE SCAN
SYS.I_OLAP_DIMENSIONALITY$ 1 0 200 0
93 88 BUFFER SORT 1 13 0
1250 0
94 93 INDEX FULL SCAN
SYS.I_OLAP_CUBE_DIMENSIONS$ 1 13 0 200 0
95 87 INDEX RANGE SCAN SYS.I_OBJ1 1
8 1 8171 1
96 2 NESTED LOOPS
1 28 2 20493 2
97 96 INDEX
FULL SCAN SYS.I_USER2 1 20 1 12321 1
98 96 INDEX RANGE SCAN SYS.I_OBJ4 1 8
1 8171 1
Re-writing a dictionary query
Here is how it was re-written, using the connect by operator.
SELECT
TO_DATE ('2009-06-10' /*+:1*/, 'YYYY-MM-DD')
- 1 + ROWNUM AS MEDIA_DAY
FROM
dual
connect by
TO_DATE ('2009-05-26'/*+:2*/, 'YYYY-MM-DD') - 1
+ ROWNUM <= TO_DATE ('2009-06-08' /*+:3*/, 'YYYY-MM-DD')
The connect by in the
statement forces the return of the number of rows that this
calculation returns:
TO_DATE ('2009-05-26', 'YYYY-MM-DD') - 1 + ROWNUM
<= TO_DATE ('2009-06-08' , 'YYYY-MM-DD')
It does a recursive join until the
rownum value is hit. Its a fairly useful way of returning
a known number of rows. This re-written plan
is much faster and simpler:
0 SELECT STATEMENT 1 2 7271 2
1
0 COUNT
2 1 CONNECT BY
WITHOUT FILTERING
3 2 FAST
DUAL 1 2 7271 2
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
and include the URL for the page.
