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

From: mike <sjmnet_at_gmail.com>
Date: 28 Apr 2006 14:03:34 -0700
Message-ID: <1146258214.849190.11360@u72g2000cwu.googlegroups.com>

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 Fri Apr 28 2006 - 16:03:34 CDT