Internal toad query takes longer in 11G than 10G.

Toad for Oracle
1

Is anyone familiar with why this query, run by toad when searching for the toad explain plan table, would take 2 minutes in any 11G database, but only seconds in any 10G database? I have both 11.1 and 11.2, and it takes 2 minutes regardless in those databases. I know there’s a problem with ALL_SYNONYMS in 11G, is there a problem in DBA_TABLES that’s known about as well?

SELECT t.owner, t.table_name
FROM sys.DBA_TABLES t
WHERE EXISTS
(SELECT 1
FROM sys.DBA_TAB_COLUMNS tc
WHERE tc.column_id = 1
AND tc.column_name = ‘STATEMENT_ID’
AND tc.data_type = ‘VARCHAR2’
AND tc.OWNER = t.owner
AND tc.table_name = t.table_name)
AND EXISTS
(SELECT 1
FROM sys.DBA_TAB_COLUMNS tc
WHERE tc.column_id = 2
AND tc.column_name = ‘PLAN_ID’
AND tc.data_type = ‘NUMBER’
AND tc.OWNER = t.owner
AND tc.table_name = t.table_name)
AND EXISTS
(SELECT 1
FROM sys.DBA_TAB_COLUMNS tc
WHERE tc.column_id = 3
AND tc.column_name = ‘TIMESTAMP’
AND tc.data_type = ‘DATE’
AND tc.OWNER = t.owner
AND tc.table_name = t.table_name)
ORDER BY 1, 2;

2

I don’t know why it is slow but I see that too and I just updated my
statistics.

Is this faster? It runs in seconds for me.

with columns as

(select /* MATERIALIZE */ column_id , column_name , data_type , owner ,
table_name

from dba_tab_columns

where ( Column_id = 1 and column_name = ‘STATEMENT_ID’ and data_type =
‘VARCHAR2’ )

or ( Column_id = 2 and column_name = ‘PLAN_ID’ and data_type = ‘NUMBER’ )

or ( Column_id = 3 and column_name = ‘TIMESTAMP’ and data_type = ‘DATE’ ))

select t. owner , t. table_name

from sys . dba_tables t

where exists

(SELECT 1

FROM columns tc

WHERE tc . column_id = 1

AND tc . column_name = ‘STATEMENT_ID’

AND tc . data_type = ‘VARCHAR2’

AND tc . OWNER = t. owner

AND tc . table_name = t. table_name )

AND EXISTS

(SELECT 1

FROM columns tc

WHERE tc . column_id = 2

AND tc . column_name = ‘PLAN_ID’

AND tc . data_type = ‘NUMBER’

AND tc . OWNER = t. owner

AND tc . table_name = t. table_name )

AND EXISTS

(SELECT 1

FROM columns tc

WHERE tc . column_id = 3

AND tc . column_name = ‘TIMESTAMP’

AND tc . data_type = ‘DATE’

AND tc . OWNER = t. owner

AND tc . table_name = t. table_name )

ORDER BY 1 , 2 ;

3

Are you running 11.2.0.1 or 11.2.0.2? I ask because 11.2.0.2 is almost more like
a 12G R1 than and 11G R2 patch ……

4

First run: 5 secs

Second run (collect stats): 5 secs

Third run new query: 1 sec J

5

Thanks for the feedback. Next beta will use new query.

6

Here’s another rewrite that runs in half a second and gives same result:

SELECT owner , table_name

FROM dba_tab_columns tc

WHERE column_id in ( 1 , 2 , 3 )

and (

( column_id = 1 and column_name = ‘STATEMENT_ID’ and data_type = ‘VARCHAR2’ ) or

( column_id = 2 and column_name = ‘PLAN_ID’ and data_type = ‘NUMBER’ ) or

( column_id = 3 and column_name = ‘TIMESTAMP’ and data_type = ‘DATE’ )

)

GROUP BY owner , table_name

HAVING count( * ) = 3

ORDER BY 1 , 2 ;

7

Yup, looks, good. Thanks.

8

Or simply this:

SELECT t.owner, t.table_name
FROM sys.DBA_TABLES t
WHERE EXISTS
(SELECT 1
FROM sys.DBA_TAB_COLUMNS tc
WHERE tc.column_id = 1
AND tc.column_name = ‘STATEMENT_ID’
AND tc.data_type = ‘VARCHAR2’
AND tc.OWNER = t.owner
AND tc.table_name = t.table_name
and ROWNUM > 0)
AND EXISTS
(SELECT 1
FROM sys.DBA_TAB_COLUMNS tc
WHERE tc.column_id = 2
AND tc.column_name = ‘PLAN_ID’
AND tc.data_type = ‘NUMBER’
AND tc.OWNER = t.owner
AND tc.table_name = t.table_name
and ROWNUM > 0)
AND EXISTS
(SELECT 1
FROM sys.DBA_TAB_COLUMNS tc
WHERE tc.column_id = 3
AND tc.column_name = ‘TIMESTAMP’
AND tc.data_type = ‘DATE’
AND tc.OWNER = t.owner
AND tc.table_name = t.table_name
and ROWNUM > 0)
ORDER BY 1, 2;

There’s an oracle bug in here, possibly Bug 8800514. If true, they haven’t fixed it, as this is on 11.2.0.2.