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

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

.

Relevant Pages