Hi,
This is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 -
64bit release.
Has anyone seen performance issues with queries to user_segments?
I have updated dictionary stats for the sys user -- this did not help.
I have created a static copy of user_segments and pointed to it with a
synonym. That helped somewhat but the runtime is still not great.
Would a 10046 trace help find the culprit? Is this a bug?
Below is the query and plan:
SELECT
NVL(SUM(BYTES), -1)
FROM
"USER_SEGMENTS"
WHERE
SEGMENT_NAME = :A0 OR SEGMENT_NAME IN (SELECT SEGMENT_NAME FROM
USER_LOBS WHERE TABLE_NAME = :A1 )
SELECT STATEMENT ( Estimated Costs = 4,157 , Estimated #Rows = 0 )
5 98 SORT AGGREGATE
5 97 FILTER
5 43 VIEW SYS_USER_SEGS
( Estim. Costs = 4,157 , Estim. #Rows = 6,681 )
5 42 UNION-ALL
5 28 NESTED LOOPS
( Estim. Costs = 3,422 , Estim. #Rows = 219 )
5 25 NESTED LOOPS
( Estim. Costs = 3,378 , Estim. #Rows =
219 )
5 22 NESTED LOOPS
( Estim. Costs = 3,200 , Estim. #Rows
= 889 )
1 TABLE ACCESS FULL OBJ$
( Estim. Costs = 309 , Estim.
#Rows = 28,911 )
5 21 VIEW SYS_OBJECTS
5 20 UNION-ALL PARTITION
5 3 TABLE ACCESS CLUSTER TAB$
2 INDEX UNIQUE SCAN
I_OBJ#
Search Columns: 1
5 5 TABLE ACCESS BY INDEX
ROWID TABPART$
4 INDEX UNIQUE SCAN
I_TABPART_OBJ$
Search Columns: 1
5 7 TABLE ACCESS CLUSTER CLU$
6 INDEX UNIQUE SCAN
I_OBJ#
Search Columns: 1
5 9 TABLE ACCESS BY INDEX
ROWID IND$
8 INDEX UNIQUE SCAN
I_IND1
Search Columns: 1
5 11 TABLE ACCESS BY INDEX
ROWID INDPART$
10 INDEX UNIQUE SCAN
I_INDPART_OBJ$
Search Columns: 1
5 13 TABLE ACCESS BY INDEX
ROWID LOB$
12 INDEX UNIQUE SCAN
I_LOB2
Search Columns: 1
5 15 TABLE ACCESS BY INDEX
ROWID TABSUBPART$
14 INDEX UNIQUE SCAN
I_TABSUBPART$_OBJ$
Search Columns: 1
5 17 TABLE ACCESS BY INDEX
ROWID INDSUBPART$
16 INDEX UNIQUE SCAN
I_INDSUBPART_OBJ$
Search Columns: 1
5 19 TABLE ACCESS BY INDEX
ROWID LOBFRAG$
18 INDEX UNIQUE SCAN
I_LOBFRAG$_FRAGOBJ$
Search Columns: 1
5 24 TABLE ACCESS CLUSTER SEG$
23 INDEX UNIQUE SCAN I_FILE#_BLOCK#
Search Columns: 3
5 27 TABLE ACCESS CLUSTER TS$
26 INDEX UNIQUE SCAN I_TS#
Search Columns: 1
5 35 NESTED LOOPS
( Estim. Costs = 24 , Estim. #Rows = 1 )
5 32 NESTED LOOPS
( Estim. Costs = 23 , Estim. #Rows = 1 )
29 TABLE ACCESS FULL UNDO$
( Estim. Costs = 2 , Estim. #Rows =
107 )
5 31 TABLE ACCESS CLUSTER SEG$
30 INDEX UNIQUE SCAN I_FILE#_BLOCK#
Search Columns: 3
5 34 TABLE ACCESS CLUSTER TS$
33 INDEX UNIQUE SCAN I_TS#
Search Columns: 1
5 41 HASH JOIN
( Estim. Costs = 711 , Estim. #Rows = 6,461 )
Memory Used KB: 1,512,448
36 TABLE ACCESS FULL TS$
( Estim. Costs = 22 , Estim. #Rows = 46 )
5 40 NESTED LOOPS
( Estim. Costs = 688 , Estim. #Rows =
6,461 )
37 TABLE ACCESS FULL FILE$
( Estim. Costs = 2 , Estim. #Rows =
343 )
5 39 TABLE ACCESS CLUSTER SEG$
( Estim. Costs = 2 , Estim. #Rows =
19 )
38 INDEX RANGE SCAN I_FILE#_BLOCK#
Search Columns: 2
5 96 VIEW USER_LOBS
( Estim. Costs = 5 , Estim. #Rows = 2 )
5 95 UNION-ALL
5 71 NESTED LOOPS OUTER
( Estim. Costs = 1 , Estim. #Rows = 1 )
5 68 NESTED LOOPS
( Estim. Costs = 1 , Estim. #Rows = 1 )
5 65 NESTED LOOPS
( Estim. Costs = 1 , Estim. #Rows = 1
)
5 63 NESTED LOOPS
( Estim. Costs = 1 , Estim. #Rows
= 1 )
5 60 NESTED LOOPS
( Estim. Costs = 1 , Estim.
#Rows = 1 )
5 57 NESTED LOOPS OUTER
( Estim. Costs = 1 ,
Estim. #Rows = 1 )
5 55 NESTED LOOPS
( Estim. Costs = 1 ,
Estim. #Rows = 1 )
5 52 MERGE JOIN
CARTESIAN
( Estim. Costs =
1 , Estim. #Rows = 1 )
5 48 NESTED LOOPS
5 45 TABLE
ACCESS CLUSTER USER$
44 INDEX
UNIQUE SCAN I_USER#
Search Columns: 1
5 47 TABLE
ACCESS CLUSTER TS$
46 INDEX
UNIQUE SCAN I_TS#
Search Columns: 1
5 51 BUFFER SORT
Memory Used
KB: 8,192
5 50 TABLE
ACCESS BY INDEX ROWID OBJ$
49 INDEX
RANGE SCAN I_OBJ2
Search Columns: 2
5 54 TABLE ACCESS
CLUSTER COL$
53 INDEX UNIQUE
SCAN I_OBJ#
Search
Columns: 1
56 INDEX UNIQUE SCAN
I_ATTRCOL1
Search Columns: 2
5 59 TABLE ACCESS CLUSTER LOB$
58 INDEX UNIQUE SCAN
I_OBJ#
Search Columns: 1
5 62 TABLE ACCESS BY INDEX ROWID
OBJ$
61 INDEX UNIQUE SCAN I_OBJ1
Search Columns: 1
64 INDEX UNIQUE SCAN I_OBJ1
Search Columns: 1
5 67 TABLE ACCESS CLUSTER TAB$
66 INDEX UNIQUE SCAN I_OBJ#
Search Columns: 1
5 70 TABLE ACCESS CLUSTER TS$
69 INDEX UNIQUE SCAN I_TS#
Search Columns: 1
5 94 NESTED LOOPS OUTER
( Estim. Costs = 3 , Estim. #Rows = 1 )
5 91 NESTED LOOPS
( Estim. Costs = 3 , Estim. #Rows = 1 )
5 88 NESTED LOOPS
( Estim. Costs = 2 , Estim. #Rows = 1
)
5 85 NESTED LOOPS
( Estim. Costs = 2 , Estim. #Rows
= 1 )
5 83 NESTED LOOPS
( Estim. Costs = 2 , Estim.
#Rows = 1 )
5 80 NESTED LOOPS OUTER
( Estim. Costs = 2 ,
Estim. #Rows = 1 )
5 78 NESTED LOOPS
( Estim. Costs = 2 ,
Estim. #Rows = 1 )
5 75 NESTED LOOPS
( Estim. Costs = 2
, Estim. #Rows = 1 )
72 TABLE ACCESS
FULL PARTLOB$
( Estim. Costs
= 2 , Estim. #Rows = 1 )
5 74 TABLE ACCESS
BY INDEX ROWID LOB$
73 INDEX
UNIQUE SCAN I_LOB2
Search
Columns: 1
5 77 TABLE ACCESS
CLUSTER COL$
76 INDEX UNIQUE
SCAN I_OBJ#
Search
Columns: 1
79 INDEX UNIQUE SCAN
I_ATTRCOL1
Search Columns: 2
5 82 TABLE ACCESS BY INDEX
ROWID OBJ$
81 INDEX UNIQUE SCAN
I_OBJ1
Search Columns: 1
84 INDEX UNIQUE SCAN I_OBJ1
Search Columns: 1
5 87 TABLE ACCESS BY INDEX ROWID OBJ$
86 INDEX UNIQUE SCAN I_OBJ1
Search Columns: 1
5 90 TABLE ACCESS CLUSTER TAB$
89 INDEX UNIQUE SCAN I_OBJ#
Search Columns: 1
5 93 TABLE ACCESS CLUSTER TS$
92 INDEX UNIQUE SCAN I_TS#
Search Columns: 1
--
//www.freelists.org/webpage/oracle-l
