Slow Rename A Table In Oracle (Doc ID 1537295.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 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.



Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms