Print Thread
sys.all_cons_columns query slow on oracle 11
#49676 10/16/14 10:35 AM
Joined: Jul 2002
Posts: 71
R
Member
OP Offline
Member
R
Joined: Jul 2002
Posts: 71
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




Last edited by Ruudbern; 10/16/14 03:15 PM.
Re: sys.all_cons_columns query slow on oracle 11
Ruudbern #49682 10/17/14 09:28 AM
Joined: Aug 1999
Posts: 22,177
Member
Offline
Member
Joined: Aug 1999
Posts: 22,177
Can you let me know the exact query that is slow?


Marco Kalter
Allround Automations
Re: sys.all_cons_columns query slow on oracle 11
Marco Kalter #49690 10/20/14 06:10 AM
Joined: Jul 2002
Posts: 71
R
Member
OP Offline
Member
R
Joined: Jul 2002
Posts: 71
Hi Marco

its this one

select constraint_name, column_name from sys.all_cons_columns
where owner = :owner 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

Last edited by Ruudbern; 10/21/14 06:48 AM.
Re: sys.all_cons_columns query slow on oracle 11
Ruudbern #49827 11/27/14 08:18 AM
Joined: Jul 2002
Posts: 71
R
Member
OP Offline
Member
R
Joined: Jul 2002
Posts: 71
Hi Marco

any news on this?

kind regards Ruud


Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.023s Queries: 15 (0.006s) Memory: 2.5055 MB (Peak: 3.0392 MB) Data Comp: Off Server Time: 2024-03-29 15:24:16 UTC
Valid HTML 5 and Valid CSS