RE: Poor perf on dba_tables when filtering on secondary?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 1 Sep 2006 13:23:48 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410508554332@usahm236.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 01 2006 - 12:23:48 CDT