My Oracle Support Banner

Slow Rename A Table In Oracle (Doc ID 1537295.1)

Last updated on FEBRUARY 27, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2017***

Symptoms

Renaming a table takes more time than expected (10 seconds when it normally finishes in less than a second):

This happens with any table even if the table has only 1 column.

 

From the 10046 trace, the following statement was found to be taking all the delay time:
select count(*) from sys.dba_refs r where r.scope_table_owner=&1 and r.scope_table_name=&2;


The DBA_REFS is made up of the following syntax:
select distinct u.name, o.name,
      decode(bitand(c.property, 1), 1, ac.name, c.name),
      decode(bitand(rc.reftyp, 2), 2, 'YES', 'NO'),
      decode(bitand(rc.reftyp, 1), 1, 'YES', 'NO'),
      su.name, so.name,
      case
        when bitand(reftyp,4) = 4 then 'USER-DEFINED'
        when bitand(reftyp, 8) = 8 then 'SYSTEM GENERATED AND USER-DEFINED'
        else 'SYSTEM GENERATED'
      end
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.refcon$ rc, sys.obj$ so,
    sys.user$ su, sys.attrcol$ ac
where o.owner# = u.user#
 and o.obj# = c.obj#
 and c.obj# = rc.obj#
 and c.col# = rc.col#
 and c.intcol# = rc.intcol#
 and rc.stabid = so.oid$(+)
 and so.owner# = su.user#(+)
 and c.obj# = ac.obj#(+)
 and c.intcol# = ac.intcol#(+)
 and bitand(c.property,32768) != 32768           /* not unused column */

STABID is part of the query and does not have an index on it.

Gathering dictionary stats does not affect the execution time.



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.