Poor perf on dba_tables when filtering on secondary?

  • From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 1 Sep 2006 10:21:10 -0500

Hey all,

Finally getting our new ERP installed on (next server
is 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

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

Thoughts anyone?


Other related posts: