Poor perf on dba_tables when filtering on secondary?

From: Jesse, Rich <Rich.Jesse_at_qg.com>
Date: Fri, 1 Sep 2006 10:21:10 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE6697A5212D@QTEX1.qg.com>

Hey all,

Finally getting our new ERP installed on 10.2.0.1.0/AIX5.3 (next server is 10.2.0.2.0 w/July2006 CPU) and I wanted to see how many of the 3300+ tables in the vendor's schema don't have PKs. So I ran this:

select dt.table_name, dt.num_rows
from dba_tables dt
where dt.owner = 'ERPOWNER'
and dt.temporary = 'N'
and dt.secondary = 'N'
and not exists
(

	select 1
	from dba_constraints dc
	where dt.owner = dc.owner
	and dt.table_name = dc.table_name
	and 'P' = dc.constraint_type

)
order by 1

And it took almost 8 minutes, most of that time spent spinning the 2.7Ghz MPV'd CPU(s). So, I attempted some tuning:

select dt.table_name, dt.num_rows
from dba_tables dt, dba_constraints dc
where dt.owner = 'ERPOWNER'

and dt.temporary = 'N'
and dt.secondary = 'N'
and dt.owner = dc.owner(+)
and dt.table_name = dc.table_name(+)

and 'P' = dc.constraint_type(+)
and dc.table_name is null
order by 1

I stopped this after maybe half a minute since this should really return within 10 seconds. After several incantations, I see that the latter query seems to suffer when the "secondary" column is used in the filter. Since it's necessary in order to get the correct output, I reversed it, changing that line to "and dt.secondary != 'Y'". With that change, the query comes back subsecond. I also see it comes back subsecond if I use the dreaded RULE hint, which I could have sworn I read that it was gone as of R2.

Given this, my hypo is that we have a stats issue. The STATISTICS_LEVEL is the default TYPICAL and I see that the default GATHER_STATS_JOB is running, which I've seen posts here complaining about but no specific solutions with respect to the data dictionary stats. I can't find what parameters are used for gathering the stats, but judging by the content of DBA_TAB_HISTOGRAMS, it looks like AUTO is one of them.

If this were application data, I'd either adjust the stats collection parameters over time or perhaps implement outlines. But since this is from the data dictionary, I'm not exactly sure where the SQL is coming from, so I'm not as comfortable with a "Method C" approach to tuning this.

Metalink was no help at all and I can't seem to find much more on the list archives, although it's nice to be getting back into some DB diving again...

Thoughts anyone?

Rich

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 01 2006 - 10:21:10 CDT