Laurenz Albe wrote:
> J Huntley Palmer <jh_at_dontspam.spam> wrote:
>> I am getting this request from an oracle ODBC driver attached to some PC
>> based development tools. MS Access is one of them. Regardless, when
>> there is catalog information needed this statement gets called and takes
>> 10 seconds to resolve on a database that otherwise is quick and
>> efficient for regular user data selects and updates.
>>
>> I am using Release 10.2.0.1.0 on Solaris9 SPARC.
>>
>> Any ODBC configs to make?
>
> What does the explain plan of this statement say?
>
Operation Object Object Type Order Rows Size (KB) Cost Time (sec) CPU
Cost I/O Cost
SELECT STATEMENT
142 0 0.000 0 0 0 0
SORT UNIQUE
141 0 0.000 0 0 0 0
UNION-ALL
140 0 0.000 0 0 0 0
VIEW
SYS.ALL_TAB_COLUMNS VIEW 26 0 0.000 0 0 0 0
FILTER
25 0 0.000 0 0 0 0
NESTED LOOPS OUTER
18 0 0.000 0 0 0 0
NESTED LOOPS OUTER
15 0 0.000 0 0 0 0
NESTED LOOPS OUTER
12 0 0.000 0 0 0 0
NESTED LOOPS OUTER
10 0 0.000 0 0 0 0
NESTED LOOPS
8 0 0.000 0 0 0 0
NESTED LOOPS
5 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.USER$ CLUSTER 2 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_USER1 INDEX (UNIQUE) 1 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.OBJ$ TABLE 4 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_OBJ2 INDEX (UNIQUE) 3 0 0.000 0 0 0 0
TABLE ACCESS CLUSTER
SYS.COL$ CLUSTER 7 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_OBJ# INDEX (CLUSTER) 6 0 0.000 0 0 0 0
TABLE ACCESS CLUSTER
SYS.COLTYPE$ CLUSTER 9 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_HH_OBJ#_INTCOL# INDEX 11 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.OBJ$ TABLE 14 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_OBJ3 INDEX 13 0 0.000 0 0 0 0
TABLE ACCESS CLUSTER
SYS.USER$ CLUSTER 17 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_USER# INDEX (CLUSTER) 16 0 0.000 0 0 0 0
TABLE ACCESS CLUSTER
SYS.TAB$ CLUSTER 20 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_OBJ# INDEX (CLUSTER) 19 0 0.000 0 0 0 0
NESTED LOOPS
23 0 0.000 0 0 0 0
FIXED TABLE FULL
SYS.X$KZSRO TABLE (FIXED) 21 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_OBJAUTH2 INDEX 22 0 0.000 0 0 0 0
FIXED TABLE FULL
SYS.X$KZSPR TABLE (FIXED) 24 0 0.000 0 0 0 0
FILTER
139 0 0.000 0 0 0 0
NESTED LOOPS OUTER
132 0 0.000 0 0 0 0
NESTED LOOPS OUTER
129 0 0.000 0 0 0 0
NESTED LOOPS OUTER
126 0 0.000 0 0 0 0
NESTED LOOPS OUTER
124 0 0.000 0 0 0 0
NESTED LOOPS
122 0 0.000 0 0 0 0
NESTED LOOPS
119 0 0.000 0 0 0 0
NESTED LOOPS
116 0 0.000 0 0 0 0
VIEW
SYS.ALL_SYNONYMS VIEW 113 0 0.000 0 0 0 0
SORT UNIQUE
112 0 0.000 0 0 0 0
UNION-ALL
111 0 0.000 0 0 0 0
FILTER
46 0 0.000 0 0 0 0
NESTED LOOPS
34 0 0.000 0 0 0 0
NESTED LOOPS
31 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.USER$ CLUSTER 28 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_USER1 INDEX (UNIQUE) 27 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.OBJ$ TABLE 30 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_OBJ2 INDEX (UNIQUE) 29 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.SYN$ TABLE 33 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_SYN1 INDEX (UNIQUE) 32 0 0.000 0 0 0 0
FILTER
44 0 0.000 0 0 0 0
FILTER
42 0 0.000 0 0 0 0
NESTED LOOPS
41 0 0.000 0 0 0 0
NESTED LOOPS
39 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.USER$ CLUSTER 36 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_USER1 INDEX (UNIQUE) 35 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.OBJ$ TABLE 38 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_OBJ2 INDEX (UNIQUE) 37 0 0.000 0 0 0 0
INDEX RANGE SCAN
SYS.I_OBJAUTH1 INDEX (UNIQUE) 40 0 0.000 0 0 0 0
FIXED TABLE FULL
SYS.X$KZSRO TABLE (FIXED) 43 0 0.000 0 0 0 0
FIXED TABLE FULL
SYS.X$KZSPR TABLE (FIXED) 45 0 0.000 0 0 0 0
NESTED LOOPS
110 0 0.000 0 0 0 0
NESTED LOOPS
107 0 0.000 0 0 0 0
NESTED LOOPS
104 0 0.000 0 0 0 0
TABLE ACCESS BY INDEX ROWID
SYS.USER$ CLUSTER 48 0 0.000 0 0 0 0
INDEX UNIQUE SCAN
SYS.I_USER1 INDEX (UNIQUE) 47 0 0.000 0 0 0 0
VIEW
SYS._ALL_SYNONYMS_TREE VIEW 103 0 0.000 0 0 0 0
CONNECT BY WITHOUT FILTERING
102 0 0.000 0 0 0 0
FILTER
85 0 0.000 0 0 0 0
COUNT
62 0 0.000 0 0 0 0
NESTED LOOPS
Received on Mon Apr 03 2006 - 11:19:18 CDT
