Hi,
on my 10.2.0.3 rac (1 000 000 of partitioned tables) I've got performance problems with simple queries on dictionary views :
select /*+ gather_plan_statistics */ * from dba_segments where owner = 'USER1' and segment_name = 'T'
CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED CPU_MS ELA_MS LIOS PIOS SORTS USERS_EXECUTING
--- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
0 000000018A3E9200 00000002397AD010 2527334192 1 1 1 2 1 9625.744 85621.017 92908 87360 0 0
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b3rh2vgxq92g3, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from dba_segments where owner = 'USER1' and segment_name = 'T'
Plan hash value: 2527334192
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | VIEW | SYS_DBA_SEGS | 1 | 1346 | 1 |00:00:03.74 | 92902 | 87360 | | | |
| 2 | UNION-ALL | | 1 | | 1 |00:00:03.74 | 92902 | 87360 | | | |
|* 3 | FILTER | | 1 | | 1 |00:00:03.74 | 89220 | 84809 | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 1 | 316 | 9 |00:00:03.74 | 89220 | 84809 | 925K| 925K| 1136K (0)|
| 5 | TABLE ACCESS FULL | USER$ | 1 | 1017 | 1115 |00:00:00.01 | 30 | 0 | | | |
|* 6 | HASH JOIN | | 1 | 316 | 9 |00:00:03.73 | 89190 | 84809 | 972K| 972K| 1126K (0)|
| 7 | TABLE ACCESS FULL | FILE$ | 1 | 215 | 255 |00:00:00.01 | 3 | 0 | | | |
|* 8 | HASH JOIN | | 1 | 316 | 9 |00:00:03.73 | 89187 | 84809 | 833K| 833K| 1166K (0)|
| 9 | TABLE ACCESS FULL | TS$ | 1 | 52 | 60 |00:00:00.01 | 67 | 0 | | | |
| 10 | NESTED LOOPS | | 1 | 316 | 9 |00:00:03.73 | 89120 | 84809 | | | |
|* 11 | HASH JOIN | | 1 | 1181 | 9 |00:00:18.47 | 89090 | 84806 | 841K| 841K| 1168K (0)|
| 12 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 1303 | 19 |00:00:00.01 | 406 | 3 | | | |
|* 13 | INDEX SKIP SCAN | I_OBJ2 | 1 | 1303 | 19 |00:00:00.01 | 381 | 3 | | | |
| 14 | VIEW | SYS_OBJECTS | 1 | 664K| 773K|00:00:34.80 | 88684 | 84803 | | | |
| 15 | UNION-ALL | | 1 | | 773K|00:00:33.25 | 88684 | 84803 | | | |
|* 16 | TABLE ACCESS FULL | TAB$ | 1 | 121K| 104K|00:00:17.08 | 20294 | 19637 | | | |
| 17 | TABLE ACCESS FULL | TABPART$ | 1 | 407K| 495K|00:00:05.48 | 5457 | 4734 | | | |
| 18 | TABLE ACCESS FULL | CLU$ | 1 | 10 | 10 |00:00:00.01 | 20293 | 19466 | | | |
|* 19 | TABLE ACCESS FULL | IND$ | 1 | 9522 | 10534 |00:00:17.14 | 20294 | 19473 | | | |
| 20 | TABLE ACCESS FULL | INDPART$ | 1 | 110K| 151K|00:00:00.62 | 1853 | 1840 | | | |
|* 21 | TABLE ACCESS FULL | LOB$ | 1 | 725 | 735 |00:00:17.41 | 20296 | 19463 | | | |
| 22 | TABLE ACCESS FULL | TABSUBPART$ | 1 | 8256 | 3735 |00:00:00.02 | 122 | 119 | | | |
| 23 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 6644 | 6644 |00:00:00.02 | 72 | 69 | | | |
| 24 | TABLE ACCESS FULL | LOBFRAG$ | 1 | 10 | 67 |00:00:00.01 | 3 | 2 | | | |
|* 25 | TABLE ACCESS CLUSTER | SEG$ | 9 | 1 | 9 |00:00:00.03 | 30 | 3 | | | |
|* 26 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 9 | 1 | 9 |00:00:00.01 | 20 | 0 | | | |
| 27 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 1 | 1 | | | |
| 28 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 1 | 1 | | | |
|* 29 | FILTER | | 1 | | 0 |00:00:00.01 | 1 | 1 | | | |
| 30 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 1 | 1 | | | |
| 31 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 1 | 1 | | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | UNDO$ | 1 | 1 | 0 |00:00:00.01 | 1 | 1 | | | |
|* 33 | INDEX RANGE SCAN | I_UNDO2 | 1 | 1 | 0 |00:00:00.01 | 1 | 1 | | | |
|* 34 | TABLE ACCESS CLUSTER | SEG$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 35 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 36 | TABLE ACCESS CLUSTER | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 37 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 38 | TABLE ACCESS BY INDEX ROWID | FILE$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 39 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 40 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 41 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 42 | FILTER | | 1 | | 0 |00:00:14.74 | 3681 | 2550 | | | |
|* 43 | HASH JOIN RIGHT OUTER | | 1 | 1029 | 0 |00:00:14.74 | 3681 | 2550 | 925K| 925K| 1138K (0)|
| 44 | TABLE ACCESS FULL | USER$ | 1 | 1017 | 1115 |00:00:00.01 | 30 | 0 | | | |
|* 45 | HASH JOIN | | 1 | 1029 | 0 |00:00:14.74 | 3651 | 2550 | 833K| 833K| 1126K (0)|
| 46 | TABLE ACCESS FULL | TS$ | 1 | 52 | 60 |00:00:00.01 | 67 | 0 | | | |
| 47 | NESTED LOOPS | | 1 | 1029 | 0 |00:00:14.74 | 3584 | 2550 | | | |
| 48 | TABLE ACCESS FULL | FILE$ | 1 | 215 | 255 |00:00:00.01 | 3 | 0 | | | |
|* 49 | TABLE ACCESS CLUSTER | SEG$ | 255 | 5 | 0 |00:00:14.74 | 3581 | 2550 | | | |
|* 50 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 255 | 1 | 0 |00:00:14.74 | 3581 | 2550 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL("U"."NAME",'SYS')='USER1')
4 - access("O"."OWNER#"="U"."USER#")
6 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
8 - access("S"."TS#"="TS"."TS#")
11 - access("O"."OBJ#"="SO"."OBJECT_ID" AND "O"."TYPE#"="SO"."OBJECT_TYPE_ID")
13 - access("O"."NAME"='T')
filter("O"."NAME"='T')
16 - filter(BITAND("T"."PROPERTY",1024)=0)
19 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR
"I"."TYPE#"=9))
21 - filter((BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128))
25 - filter("S"."TYPE#"="SO"."SEGMENT_TYPE_ID")
26 - access("S"."TS#"="SO"."TS_NUMBER" AND "S"."FILE#"="SO"."HEADER_FILE" AND "S"."BLOCK#"="SO"."HEADER_BLOCK")
29 - filter(NVL("U"."NAME",'SYS')='USER1')
32 - filter("UN"."STATUS$"<>1)
33 - access("UN"."NAME"='T')
34 - filter(("S"."TYPE#"=1 OR "S"."TYPE#"=10))
35 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#")
37 - access("S"."USER#"="U"."USER#")
39 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#")
41 - access("S"."TS#"="TS"."TS#")
42 - filter(NVL("U"."NAME",'SYS')='USER1')
43 - access("S"."USER#"="U"."USER#")
45 - access("S"."TS#"="TS"."TS#")
49 - filter(("S"."TYPE#"<>5 AND "S"."TYPE#"<>6 AND "S"."TYPE#"<>10 AND "S"."TYPE#"<>8 AND "S"."TYPE#"<>1))
50 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
filter(TO_CHAR("F"."FILE#")||'.'||TO_CHAR("S"."BLOCK#")='T')
92 rows selected.
If You could share with me Your plans maybe I'll be able to find a solution .
select /*+ gather_plan_statistics */ * from dba_segments where owner = 'USER1' and segment_name = 'T'
Regards
GregG