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 9.2.0.6 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
OWNER1 WO_ORDERS_TO_RELEASE WO_ORDERS_TO_RELEASE_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 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
