2

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.

  • 4
    Under no circumstances create indexes on internal Oracle views/tables! – Philᵀᴹ Dec 10 '13 at 22:33
  • Exactly what version of Oracle are you using? I know you said 10.2 but what patchlevel? Have you gathered statistics on dictionary objects? Are those statistics accurate? – Justin Cave Dec 10 '13 at 22:35
  • @JustinCave This is 10.2.0.1.0 on linux. I just found a note in oracle support about the same problem when the database is an upgrade from 9i, but this is not my case. The solution suggested there is to remove statistics on user$, con$ and cdef$. – eppesuig Dec 10 '13 at 22:38
  • 1
    @eppesuig - Is there a reason that you are running a base release rather than applying the most recent patchset? Most folks are well down the path of upgrading 10.2 databases to 11.2 if they're not already there-- running the terminal 10.2 patchset would be a good idea for a variety of reasons. – Justin Cave Dec 10 '13 at 22:46
  • 1
    Statistics are gathered by oracle default job. I just removed these statistics from the tables in this query and now it is really faster. – eppesuig Dec 10 '13 at 22:55
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.