sys.all_cons_columns query slow on oracle 11

[Ruudbern]

Hi

on our development environment the query on sys.all_cons_columns is realy slow on our oracle11 server. 10 to 12 seconds! Yes, seconds not milliseconds.

i already tried (without effect):
dbms_stats.gather_fixed_objects_stats;
dbms_stats.gather_dictionary_stats;
dbms_stats.gather_schema_stats('SYS');

I know i can turn this off on the dataset.
Do you know a way to optimize this?

thanx in advance,
kind regards

Ruud

 

[Marco Kalter]

Can you let me know the exact query that is slow?

 

[Ruudbern]

Hi Marco

its this one

select constraint_name, column_name from sys.all_cons_columns
where owner = wner and table_name = :table_name
and constraint_name not like 'SYS_C%'
order by constraint_name, position

it fires once per table. (as you know)

thanks in advance

Ruud

 

[Ruudbern]

Hi Marco

any news on this?

kind regards Ruud