4 Replies Latest reply: Dec 2, 2012 3:22 PM by 887980 RSS

    Import running slow since last days.

    887980
      I have a issue where Oracle 9i database import is running very slow since last couple of days. The process used to be completed in 5 hours every day. But since last two days it is taking 10 to 15 hours to complete. I have checked the data-file size, and export dump-file sizes are consistent with what it used to be. There are no any significant changes in the database size no errors reported in the import and alert log files. Please help me Identify and resolve the real cause of this performance degrade.

      Thanks,
      Farhan

      Edited by: 884977 on Dec 2, 2012 11:25 AM
        • 1. Re: Import running slow since last days.
          Srini Chavali-Oracle
          Pl post exact OS and database versions, along with the complete export and import commands used, and the first 10 lines of the export and import logs.

          Pl see if these MOS Docs can help

          Tuning Considerations When Import Is Slow [ID 93763.1]
          Getting 10046 Trace for Export and Import [ID 258418.1]

          HTH
          Srini
          • 2. Re: Import running slow since last days.
            887980
            --Exact OS version
            SunOS dbw01 5.10 Generic_141444-09 sun4v sparc SUNW,SPARC-Enterprise-T2000

            --Oracle database version
            Oracle9i Release 9.2.0.6.0 - 64bit Production
            PL/SQL Release 9.2.0.6.0 - Production
            CORE 9.2.0.6.0 Production
            TNS for Solaris: Version 9.2.0.6.0 - Production
            NLSRTL Version 9.2.0.6.0 - Production


            --Lines from import logfile
            Connected to: Oracle9i Release 9.2.0.6.0 - 64bit Production
            JServer Release 9.2.0.6.0 - Production

            Export file created by EXPORT:V09.02.00 via direct path
            import done in US7ASCII character set and AL16UTF16 NCHAR character set
            import server uses WE8ISO8859P1 character set (possible charset conversion)
            export client uses AL32UTF8 character set (possible charset conversion)
            . importing CFD_USER's objects into CFD_USER
            . . importing table "CFD_ACCOUNT_POLICY" 613 rows imported
            . . importing table "CFD_ACTIVE_EMPLOYEE_COUNT" 0 rows imported
            . . importing table "CFD_ADDRESS" 1435608 rows imported
            . . importing table "CFD_APPEAL" 1688 rows imported
            . . importing table "CFD_APPEALS_DOCUMENTS_LNK" 5155 rows imported
            . . importing table "CFD_APPEAL_NOTES_LNK" 2564 rows imported
            . . importing table "CFD_CALC_METHOD_CHANGE_HELPER" 38323 rows imported
            . . importing table "CFD_CODE" 379 rows imported
            . . importing table "CFD_CONSTRAINT_CONTEXT" 2823 rows imported
            . . importing table "CFD_CONSTRAINT_CRITERIA" 34070 rows imported
            . . importing table "CFD_CONTACT_INFORMATION_MV" 32018 rows imported



            --Command used
            imp CFDa_user/xxxxxxxx parfile=/export/home/oracle/shells/cfdtables.par file=/oradata/dump/cfdtables.dmp log=/oradata/dump/CFD_tablesimp.log ignore=y recordlength=65535 trace=y

            Thanks,
            • 3. Re: Import running slow since last days.
              887980
              Please note that I have added trace=y in order to trace session. Otherwise, the original command did not have the option trace=y.
              The original command is as follows.
              imp CFD_user/xxxxxxxx parfile=/export/home/oracle/shells/cfdtables.par file=/oradata/dump/cfdtables.dmp log=/oradata/dump/CFD_tablesimp.log ignore=y recordlength=65535
              • 4. Re: Import running slow since last days.
                Srini Chavali-Oracle
                >
                Export file created by EXPORT:V09.02.00 via direct path
                import done in US7ASCII character set and AL16UTF16 NCHAR character set
                import server uses WE8ISO8859P1 character set (possible charset conversion)
                ...
                >

                NLS_LANG needs to be set WE8ISO8859P1 before the import is invoked, else there is the possibility of data corruption. If there is a similar warning on the export log file (which is the reason for asking for the export log), that will need to be corrected too.

                Pl post the contents of the par file /export/home/oracle/shells/cfdtables.par

                >
                Please note that I have added trace=y in order to trace session. Otherwise, the original command did not have the option trace=y.
                >

                TRACE is not a valid option for imp, AFAIK - http://docs.oracle.com/cd/B10501_01/server.920/a96652/ch02.htm#1014036

                Pl see the MOS Doc I posted above on how to trace import session

                HTH
                Srini