From: steve.mckee@ericsson.com (Steve Mckee)
Newsgroups: comp.databases.oracle.server
Subject: Joining user_cons_columns and user_constraints SLOWWWW!
Date: 12 Dec 2001 12:08:22 -0800
Organization: http://groups.google.com/
Lines: 26
Message-ID: <7926e63f.0112121208.154674b5@posting.google.com>
NNTP-Posting-Host: 192.75.88.232
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1008187703 2271 127.0.0.1 (12 Dec 2001 20:08:23 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 12 Dec 2001 20:08:23 GMT


Hi,

We use Tool for Oracle Application Development (TOAD)from 
Quest software as a database browser. The browser in this
GUI executes the following query that absolutely hammers 
the Oracle Server:

 select  a1.constraint_name, 
         c1.column_name, 
         c1.position  
 from    user_cons_columns  C1,  
         user_constraints A1  
 where   A1.table_name = C1.Table_name  And  
         A1.constraint_name = C1.Constraint_Name  and 
         A1.constraint_type = 'P'  and 
         A1.table_name ='PP_CALLS'  
  ORDER BY 3

It takes several minutes to run, 100% CPU utilization.

Seeing as we don't have control over the query, is there 
something we can do at the Server level?

Thanks.

Steve