RE: Poor perf on dba_tables when filtering on secondary?

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 1 Sep 2006 13:23:48 -0400

What version of Oracle are you on?  Oracle added the PK index name to
dba_constraints a while back so here is a quick stab at you problem.
The query probably needs another join condition related to the table
owner but I have to get back to work.  This query took only a few
seconds to find over 1800 hits.  You could add a filter condition to
only show the no PK results: (Ran on AIX 5.2)

ut1 > l
  1  select a.owner, a.table_name, nvl(c.index_name,'NO PK')
  2  from dba_tables a,
  3       (select c1.table_name, c1.index_name
  4        from dba_constraints c1
  5        where c1.constraint_type = 'P'
  6       ) c
  7  where a.table_name = c.table_name(+)
  8* and a.owner in ('OWNER1','OWNER2')

OWNER        TABLE_NAME                     NVL(C.INDEX_NAME,'NOPK')
------------ ------------------------------
OWNER1       WO_MASTER_LOG                  NO PK
OWNER1       WO_NC_INDICATOR                NO PK
OWNER2       WO_PARAMETERS                  NO PK
OWNER1       WO_PRINT                       WO_PRINT_PK

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jesse, Rich
Sent: Friday, September 01, 2006 11:21 AM
To: oracle-l
Subject: Poor perf on dba_tables when filtering on secondary?

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: