Re: data dictionary query with 7million consistent gets ! ? !

mike wrote:
> Why would a query of all_constraints cause 7 million consistent gets
> and take nearly two minutes ?
>
> On a similarly configured instance it only takes about 700 CGs and 0.02
> seconds.
> Oracle 9.2.0.6 on wintel .
>
> CRW32.exe (crystal reports) generates this query when linking tables
> as it looks for FK relationships.
>
> Thanks !
>
>
> SELECT p_constraint_table.CONSTRAINT_NAME
> p_constraint_table.OWNER
> p_constraint_table.TABLE_NAME
> f_constraint_table.CONSTRAINT_NAME
> f_constraint_table.OWNER
> f_constraint_table.TABLE_NAME
> FROM ALL_CONSTRAINTS f_constraint_table
> ALL_CONSTRAINTS p_constraint_table
> WHERE
> f_constraint_table.R_CONSTRAINT_NAME=p_constraint_table.CONSTRAINT_NAME
> AND p_constraint_table.CONSTRAINT_TYPE='P'
> AND f_constraint_table.CONSTRAINT_TYPE='R'
> AND f_constraint_table.OWNER ='GPW'
> AND ( f_constraint_table.TABLE_NAME='FCI_AGREEMENT' or
> f_constraint_table.TABLE_NAME='FCI_CONTRACT' )
> /
>
> 1 row selected.
>
> Elapsed: 00:01:56.08

(the following is easy in OEM and similar tools):

Look at the definition of the all_constraints view.

For each table mentioned there, in both instances, see if there are any statistics, and be sure all indices are there and valid.

I'm guessing the CBO is being fooled by the recursive self joins and funny statistics (or problematic indices) into repetitively full scanning obj$. I'm predicting your faster instance won't have statistics. Make wooeeewoooeee sounds now.

jg

-- 
@home.com is bogus.
http://www.janus-software.com/fb_fyracle.html