Re: extremely high "consistent gets" count on query of all_constraints view

Mike,

Is any of the database has statistics on dictionary? Its a good idea to enable 10046 with level 12 and find out what is going on.

Jaffar

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
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 7738081 consistent gets
> 0 physical reads
> 0 redo size
> 761 bytes sent via SQL*Net to client
> 498 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
Received on Sun Apr 30 2006 - 01:44:28 CDT