This may or may not be relevant, but I ran into performance issues with DBA_EXTENTS in oracle 10.2.0.2 and Oracle recommended another syntax for creating that view. You may be experiencing the same issue but with a different view. Oracle support may be able to recommend an alternative syntax for user_segments as well. thank you Gene Gurevich "Khemmanivanh, Somckit" <somckit.khemmani To vanh@weyerhaeuser "oracle-l" <oracle-l@xxxxxxxxxxxxx> .com> cc Sent by: oracle-l-bounce@f Subject reelists.org Slow USER_SEGMENTS query 12/11/2006 01:47 PM Please respond to somckit.khemmaniv anh@weyerhaeuser. com 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 -- //www.freelists.org/webpage/oracle-l