Internal query on CDEF$ and CON$ very CPU bound I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the most time consuming query is one internal to oracle. (I assume it is an internal one since the application does not execute that query.) Here it is:

select c.name, u.name 
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and 
  c.owner# = u.user#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     5901      0.09       0.09          0          0          0           0
Execute   5901      0.26       0.24          0          0          0           0
Fetch     5901    978.68     969.36          0   65955477          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    17703    979.04     969.71          0   65955477          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  HASH JOIN  (cr=11177 pr=0 pw=0 time=161966 us)
     86   TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=897 us)
      0   HASH JOIN  (cr=11170 pr=0 pw=0 time=161253 us)
      0    TABLE ACCESS FULL CDEF$ (cr=11170 pr=0 pw=0 time=159825 us)
      0    TABLE ACCESS FULL CON$ (cr=0 pr=0 pw=0 time=0 us)


From what I see this seems related to cdef$ table that have 915386 records. The same number or records is in con$.

So, is this a normal behaviour? May I add an index on cdef$(con#,enabled)? Would that make any change?

Thank you very much.
Browse My Oracle Support for the possible causes. For example quick search revealed a bug about your Oracle internal SQL. Workaround for it is:


Set optimizer_mode=rule for the affected DMLs


The bug is registered against version 11g but of course it is possible that it exists also in 10g.