IMP Takes More Time To Import The Constraints (Doc ID 166887.1)

rongshiyuan發表於2014-02-18
ImportAI
IMP Takes More Time To Import The Constraints (Doc ID 166887.1)

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.6.0 to 11.2.0.4 [Release 8.1.6 to 11.2]
Information in this document applies to any platform.

Symptoms

Exporting from Oracle database (older versions 8i/9i) and importing into Oracle 8i/9i/10g/11g. You observed a slow perfomance when running import job. The TKPROF output shows the time is spent on view SYS.IMP8CDT. Your application has many constraints.

Cause

The view IMP8CDT was investigated in bugs:

Bug 1779169 IMPORT IS VERY SLOW TO WRAP UP WHEN THERE ARE LARGE NUMBERS OF CONSTRAINTS
Bug 1936535 IMPORT TAKES MORE TIME AFTER UPGRADE
Bug 12341094 IMPORT INTO ORACLE 11.2 IS TOO SLOW USING ORACLE 9.2 DUMP FILE

which are closed (not a bug, not reproducible)

Solution

1. Import with CONSTRAINTS=N. Or:

2. Change the following export data dictionary views and restart the import:

CREATE OR REPLACE view imp8cdt (bad) AS
SELECT decode(bitand(c$.defer,16),16,1,0)
FROM   sys.cdef$ c$
WHERE  c$.defer is NOT NULL
  AND bitand(c$.defer,16) = 16
/

CREATE OR REPLACE view imp8cdt2 (ownerid, bad) AS
SELECT co$.owner#, decode(bitand(c$.defer,16),16,1,0)
FROM   sys.cdef$ c$, sys.con$ co$
WHERE  c$.defer is NOT NULL
   AND bitand(c$.defer,16) = 16
   AND c$.con# = co$.con#
/

grant select on imp8cdt to select_catalog_role;
grant select on imp8cdt2 to select_catalog_role;
CREATE OR REPLACE view imp8cdtu  AS
SELECT * from imp8cdt2
     WHERE ownerid = UID
/

References

BUG:1936535 - IMPORT TAKES MORE TIME AFTER UPGRADE
BUG:12341094 - IMPORT INTO ORACLE 11.2 IS TOO SLOW USING ORACLE 9.2 DUMP FILE.
BUG:1779169 - IMPORT IS VERY SLOW TO WRAP UP WHEN THERE ARE LARGE NUMBERS OF CONSTRAINTS.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1083336/，如需轉載，請註明出處，否則將追究法律責任。

相關文章