2 Replies Latest reply on Dec 13, 2013 4:57 PM by TSharma-Oracle

    expdp now 10 times slower than previous day?

    z00mantwo

      expdp has suddenly become 10 times slower. I've pasted two logs showing the difference below. As far as I know there were no changes to Oracle or the OS between the two example dates. I also reviewed the alert log for both days and found nothing that would seem to be a problem. The backup went from an elapsed time of 47 secs to over 9 minutes. Note rman backups also ran the same two days and were consistant and within a few seconds of each other. It's only the expdp that is suddenly slow and has stayed that way ever since.

       

      Dec 04:

      Export: Release 12.1.0.1.0 - Production on Wed Dec 4 00:08:03 2013

       

      Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

       

      Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
      Starting "A362634"."BACKUP_BASTIAN": a362634/******** PARALLEL=2 schemas=BASTIAN job_name=backup_BASTIAN DUMPFILE=BASTIAN.dmp LOGFILE=BASTIAN.log
      Estimate in progress using BLOCKS method...
      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
      Total estimation using BLOCKS method: 88.87 MB
      Processing object type SCHEMA_EXPORT/USER
      . . exported "BASTIAN"."TLR_ACTIVITY_PRODUCT" 15.72 MB 102360 rows
      Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
      Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
      Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
      Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
      Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
      Processing object type SCHEMA_EXPORT/TABLE/TABLE
      Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
      Processing object type SCHEMA_EXPORT/TABLE/COMMENT
      Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type SCHEMA_EXPORT/VIEW/VIEW
      Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
      Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
      . . exported "BASTIAN"."RPT_INVENTORYS" 15.67 MB 86869 rows
      . . exported "BASTIAN"."RPT_ACTIVITY" 7.200 MB 38210 rows
      . . exported "BASTIAN"."INVENTORYS" 4.500 MB 190832 rows
      . . exported "BASTIAN"."S2_ACTIVITY_DETAILS" 4.717 MB 29730 rows
      . . exported "BASTIAN"."ACTIVITY" 3.326 MB 95268 rows
      . . exported "BASTIAN"."STAGE_PRODUCT" 2.661 MB 11991 rows
      . . exported "BASTIAN"."PRODUCT" 1.765 MB 12001 rows
      . . exported "BASTIAN"."RPT_PRODUCT" 1.629 MB 8531 rows
      . . exported "BASTIAN"."S2_PRODUCTS" 1.330 MB 8494 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_PROD_LAST12" 1.416 MB 8530 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_STORE_PRODUCT" 993.5 KB 14847 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT_TIRE" 794.3 KB 5287 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_STORE_PRODUCT" 574.9 KB 4375 rows
      . . exported "BASTIAN"."TLR_ACTIVITY_STORE_PROD" 584.0 KB 4048 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_GTS" 526.2 KB 7244 rows
      . . exported "BASTIAN"."RPT_TURNRATES_GTS" 490.7 KB 2400 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT_STORE" 345.2 KB 2472 rows
      . . exported "BASTIAN"."S2_SOLUTION_DETAILS" 389.0 KB 1547 rows
      . . exported "BASTIAN"."TLR_POTENTIAL" 334.4 KB 4545 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_GTS" 300.6 KB 2198 rows
      . . exported "BASTIAN"."S2_STORES_PROD_DATES" 344.3 KB 11938 rows
      . . exported "BASTIAN"."RPT_STORE_DEADSTOCK" 275.1 KB 1596 rows
      . . exported "BASTIAN"."STAGE_ACTIVITY" 267.0 KB 2283 rows
      . . exported "BASTIAN"."STAGE_INVENTORYS" 251.3 KB 4824 rows
      . . exported "BASTIAN"."TLR_ACTIVITY_STORE_GTS" 159.1 KB 980 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDGROUP" 110.1 KB 734 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_STORE_TIRERANK" 111.1 KB 800 rows
      . . exported "BASTIAN"."RPT_TURNRATES_BRANDPARENT" 87.75 KB 432 rows
      . . exported "BASTIAN"."RPT_TURNRATES_STORE" 93.99 KB 336 rows
      . . exported "BASTIAN"."S2_DEMAND_UTS" 120.4 KB 2095 rows
      . . exported "BASTIAN"."S2_SOLUTION_COMPARE" 133.3 KB 1547 rows
      . . exported "BASTIAN"."S2_SOLUTION_GAPS" 118.9 KB 1248 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT" 69.56 KB 390 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRAND_TIRERANK" 57.71 KB 227 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_DATES" 12.05 KB 44 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_GTS_LAST12" 35 KB 221 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_MONTH" 31.51 KB 44 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_MONTH_STORE" 57.93 KB 300 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_UTS_LAST12" 58.02 KB 408 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUST" 81.49 KB 1789 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_BRANDPARENT" 49.62 KB 513 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_MONTH" 17.25 KB 44 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_MONTH_STORE" 31.86 KB 308 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP2" 60.33 KB 776 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP3" 66.74 KB 776 rows
      . . exported "BASTIAN"."RPT_STORES_CLOSEST" 10.13 KB 42 rows
      . . exported "BASTIAN"."RPT_TURNRATES_DATES" 13.11 KB 48 rows
      . . exported "BASTIAN"."RPT_TURNRATES_STORE_STP4" 61.53 KB 308 rows
      . . exported "BASTIAN"."ACTIVITY_FILTER" 5.062 KB 2 rows
      . . exported "BASTIAN"."CORPORATE_SOLUTION" 18.98 KB 262 rows
      . . exported "BASTIAN"."CUSTOMER" 14.32 KB 1 rows
      . . exported "BASTIAN"."LOCATIONS" 21.28 KB 11 rows
      . . exported "BASTIAN"."PRODUCT_APPLICATION" 33.06 KB 850 rows
      . . exported "BASTIAN"."PRODUCT_BRANDGROUPS" 9.640 KB 92 rows
      . . exported "BASTIAN"."QUARTERS" 7.132 KB 12 rows
      . . exported "BASTIAN"."RPT_GOODYEAR_SUMMARY" 17.80 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_LIST" 11.22 KB 50 rows
      . . exported "BASTIAN"."RPT_GTS_LIST_ALL" 20.53 KB 221 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_C" 6.804 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_MODEL" 7.171 KB 1 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_R" 5.945 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP" 5.531 KB 6 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP1" 6.476 KB 6 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP2" 8.312 KB 11 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP3" 8 KB 11 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_W" 5.578 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_YNN" 5.531 KB 7 rows
      . . exported "BASTIAN"."RPT_PRICECLUST" 8.679 KB 48 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_C" 6.804 KB 4 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_MODEL" 7.171 KB 1 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_R" 5.992 KB 4 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP" 15.01 KB 776 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP1" 23.21 KB 798 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_W" 5.570 KB 4 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_YH" 15.01 KB 776 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_YNN" 13.68 KB 776 rows
      . . exported "BASTIAN"."RPT_TURNRATES_MONTH" 29.94 KB 48 rows
      . . exported "BASTIAN"."RPT_TURNRATE_DATE_OFFSETS" 6.078 KB 61 rows
      . . exported "BASTIAN"."S2_ACTIVITY_PRODUCT_INDIRECT" 26.32 KB 211 rows
      . . exported "BASTIAN"."S2_SCREEN" 36.42 KB 221 rows
      . . exported "BASTIAN"."S2_SCREEN_ALL" 44.67 KB 262 rows
      . . exported "BASTIAN"."S2_SETTINGS" 5.523 KB 1 rows
      . . exported "BASTIAN"."S2_STORES" 10.90 KB 7 rows
      . . exported "BASTIAN"."S2_STORES_DATES" 8.546 KB 7 rows
      . . exported "BASTIAN"."S2_STORES_MARKETSHARE" 6.5 KB 7 rows
      . . exported "BASTIAN"."STAGE_CORPORATE_SOLUTION" 31.10 KB 254 rows
      . . exported "BASTIAN"."STAGE_TRADE_AREA" 10.03 KB 97 rows
      . . exported "BASTIAN"."TLR_PRODUCTSCREEN" 9.140 KB 11 rows
      . . exported "BASTIAN"."TRADE_AREA" 7.664 KB 97 rows
      . . exported "BASTIAN"."BRAND_CATVAL" 0 KB 0 rows
      . . exported "BASTIAN"."BRAND_PREFERENCE" 0 KB 0 rows
      . . exported "BASTIAN"."PRODUCT_BRANDLINEOVERRIDES" 0 KB 0 rows
      . . exported "BASTIAN"."PRODUCT_SCREENS" 0 KB 0 rows
      . . exported "BASTIAN"."RPT_COSTOVERRIDE" 0 KB 0 rows
      . . exported "BASTIAN"."RPT_PRODUCT_SCREENS" 0 KB 0 rows
      . . exported "BASTIAN"."S2_SOLUTION_OLD" 0 KB 0 rows
      . . exported "BASTIAN"."SOLUTION" 0 KB 0 rows
      . . exported "BASTIAN"."SOLUTION_DETAILS" 0 KB 0 rows
      . . exported "BASTIAN"."STAGE_BRAND_CATVAL" 0 KB 0 rows
      . . exported "BASTIAN"."STAGE_BRAND_PREFERENCE" 0 KB 0 rows
      . . exported "BASTIAN"."STAGE_LOCATIONS" 0 KB 0 rows
      Master table "A362634"."BACKUP_BASTIAN" successfully loaded/unloaded
      ******************************************************************************
      Dump file set for A362634.BACKUP_BASTIAN is:
      /oracle/app/oracle/admin/mkt1/dpdump/BASTIAN.dmp
      Job "A362634"."BACKUP_BASTIAN" successfully completed at Wed Dec 4 00:08:51 2013 elapsed 0 00:00:47

       


      Dec 05:

      Export: Release 12.1.0.1.0 - Production on Thu Dec 5 00:51:05 2013

       

      Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

       

      Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
      Starting "A362634"."BACKUP_BASTIAN": a362634/******** PARALLEL=2 schemas=BASTIAN job_name=backup_BASTIAN DUMPFILE=BASTIAN.dmp LOGFILE=BASTIAN.log
      Estimate in progress using BLOCKS method...
      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
      Total estimation using BLOCKS method: 88.87 MB
      . . exported "BASTIAN"."TLR_ACTIVITY_PRODUCT" 15.72 MB 102360 rows
      Processing object type SCHEMA_EXPORT/USER
      . . exported "BASTIAN"."RPT_INVENTORYS" 15.67 MB 86869 rows
      Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
      Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
      Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
      Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
      Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
      Processing object type SCHEMA_EXPORT/TABLE/TABLE
      Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
      Processing object type SCHEMA_EXPORT/TABLE/COMMENT
      Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
      Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type SCHEMA_EXPORT/VIEW/VIEW
      Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
      Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
      Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
      . . exported "BASTIAN"."RPT_ACTIVITY" 7.200 MB 38210 rows
      Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
      . . exported "BASTIAN"."INVENTORYS" 4.500 MB 190832 rows
      . . exported "BASTIAN"."S2_ACTIVITY_DETAILS" 4.717 MB 29730 rows
      . . exported "BASTIAN"."ACTIVITY" 3.326 MB 95268 rows
      . . exported "BASTIAN"."STAGE_PRODUCT" 2.661 MB 11991 rows
      . . exported "BASTIAN"."PRODUCT" 1.765 MB 12001 rows
      . . exported "BASTIAN"."RPT_PRODUCT" 1.629 MB 8531 rows
      . . exported "BASTIAN"."S2_PRODUCTS" 1.330 MB 8494 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_PROD_LAST12" 1.416 MB 8530 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_STORE_PRODUCT" 993.5 KB 14847 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT_TIRE" 794.3 KB 5287 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_STORE_PRODUCT" 574.9 KB 4375 rows
      . . exported "BASTIAN"."TLR_ACTIVITY_STORE_PROD" 584.0 KB 4048 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_GTS" 526.2 KB 7244 rows
      . . exported "BASTIAN"."RPT_TURNRATES_GTS" 490.7 KB 2400 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT_STORE" 345.2 KB 2472 rows
      . . exported "BASTIAN"."S2_SOLUTION_DETAILS" 389.0 KB 1547 rows
      . . exported "BASTIAN"."TLR_POTENTIAL" 334.4 KB 4545 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_GTS" 300.6 KB 2198 rows
      . . exported "BASTIAN"."S2_STORES_PROD_DATES" 344.3 KB 11938 rows
      . . exported "BASTIAN"."RPT_STORE_DEADSTOCK" 275.1 KB 1596 rows
      . . exported "BASTIAN"."STAGE_ACTIVITY" 267.0 KB 2283 rows
      . . exported "BASTIAN"."STAGE_INVENTORYS" 251.3 KB 4824 rows
      . . exported "BASTIAN"."TLR_ACTIVITY_STORE_GTS" 159.1 KB 980 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDGROUP" 110.1 KB 734 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_STORE_TIRERANK" 111.1 KB 800 rows
      . . exported "BASTIAN"."RPT_TURNRATES_BRANDPARENT" 87.75 KB 432 rows
      . . exported "BASTIAN"."RPT_TURNRATES_STORE" 93.99 KB 336 rows
      . . exported "BASTIAN"."S2_DEMAND_UTS" 120.4 KB 2095 rows
      . . exported "BASTIAN"."S2_SOLUTION_COMPARE" 133.3 KB 1547 rows
      . . exported "BASTIAN"."S2_SOLUTION_GAPS" 118.9 KB 1248 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT" 69.56 KB 390 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_BRAND_TIRERANK" 57.71 KB 227 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_DATES" 12.05 KB 44 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_GTS_LAST12" 35 KB 221 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_MONTH" 31.51 KB 44 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_MONTH_STORE" 57.93 KB 300 rows
      . . exported "BASTIAN"."RPT_ACTIVITY_UTS_LAST12" 58.02 KB 408 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUST" 81.49 KB 1789 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_BRANDPARENT" 49.62 KB 513 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_MONTH" 17.25 KB 44 rows
      . . exported "BASTIAN"."RPT_INVENTORYS_MONTH_STORE" 31.86 KB 308 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP2" 60.33 KB 776 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP3" 66.74 KB 776 rows
      . . exported "BASTIAN"."RPT_STORES_CLOSEST" 10.13 KB 42 rows
      . . exported "BASTIAN"."RPT_TURNRATES_DATES" 13.11 KB 48 rows
      . . exported "BASTIAN"."RPT_TURNRATES_STORE_STP4" 61.53 KB 308 rows
      . . exported "BASTIAN"."ACTIVITY_FILTER" 5.062 KB 2 rows
      . . exported "BASTIAN"."CORPORATE_SOLUTION" 18.98 KB 262 rows
      . . exported "BASTIAN"."CUSTOMER" 14.32 KB 1 rows
      . . exported "BASTIAN"."LOCATIONS" 21.28 KB 11 rows
      . . exported "BASTIAN"."PRODUCT_APPLICATION" 33.06 KB 850 rows
      . . exported "BASTIAN"."PRODUCT_BRANDGROUPS" 9.640 KB 92 rows
      . . exported "BASTIAN"."QUARTERS" 7.132 KB 12 rows
      . . exported "BASTIAN"."RPT_GOODYEAR_SUMMARY" 17.80 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_LIST" 11.22 KB 50 rows
      . . exported "BASTIAN"."RPT_GTS_LIST_ALL" 20.53 KB 221 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_C" 6.804 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_MODEL" 7.171 KB 1 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_R" 5.945 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP" 5.531 KB 6 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP1" 6.476 KB 6 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP2" 8.312 KB 11 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP3" 8 KB 11 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_W" 5.578 KB 4 rows
      . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_YNN" 5.531 KB 7 rows
      . . exported "BASTIAN"."RPT_PRICECLUST" 8.679 KB 48 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_C" 6.804 KB 4 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_MODEL" 7.171 KB 1 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_R" 5.992 KB 4 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP" 15.01 KB 776 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_TMP1" 23.21 KB 798 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_W" 5.570 KB 4 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_YH" 15.01 KB 776 rows
      . . exported "BASTIAN"."RPT_PRICECLUST_YNN" 13.68 KB 776 rows
      . . exported "BASTIAN"."RPT_TURNRATES_MONTH" 29.94 KB 48 rows
      . . exported "BASTIAN"."RPT_TURNRATE_DATE_OFFSETS" 6.078 KB 61 rows
      . . exported "BASTIAN"."S2_ACTIVITY_PRODUCT_INDIRECT" 26.32 KB 211 rows
      . . exported "BASTIAN"."S2_SCREEN" 36.42 KB 221 rows
      . . exported "BASTIAN"."S2_SCREEN_ALL" 44.67 KB 262 rows
      . . exported "BASTIAN"."S2_SETTINGS" 5.523 KB 1 rows
      . . exported "BASTIAN"."S2_STORES" 10.90 KB 7 rows
      . . exported "BASTIAN"."S2_STORES_DATES" 8.546 KB 7 rows
      . . exported "BASTIAN"."S2_STORES_MARKETSHARE" 6.5 KB 7 rows
      . . exported "BASTIAN"."STAGE_CORPORATE_SOLUTION" 31.10 KB 254 rows
      . . exported "BASTIAN"."STAGE_TRADE_AREA" 10.03 KB 97 rows
      . . exported "BASTIAN"."TLR_PRODUCTSCREEN" 9.140 KB 11 rows
      . . exported "BASTIAN"."TRADE_AREA" 7.664 KB 97 rows
      . . exported "BASTIAN"."BRAND_CATVAL" 0 KB 0 rows
      . . exported "BASTIAN"."BRAND_PREFERENCE" 0 KB 0 rows
      . . exported "BASTIAN"."PRODUCT_BRANDLINEOVERRIDES" 0 KB 0 rows
      . . exported "BASTIAN"."PRODUCT_SCREENS" 0 KB 0 rows
      . . exported "BASTIAN"."RPT_COSTOVERRIDE" 0 KB 0 rows
      . . exported "BASTIAN"."RPT_PRODUCT_SCREENS" 0 KB 0 rows
      . . exported "BASTIAN"."S2_SOLUTION_OLD" 0 KB 0 rows
      . . exported "BASTIAN"."SOLUTION" 0 KB 0 rows
      . . exported "BASTIAN"."SOLUTION_DETAILS" 0 KB 0 rows
      . . exported "BASTIAN"."STAGE_BRAND_CATVAL" 0 KB 0 rows
      . . exported "BASTIAN"."STAGE_BRAND_PREFERENCE" 0 KB 0 rows
      . . exported "BASTIAN"."STAGE_LOCATIONS" 0 KB 0 rows
      Master table "A362634"."BACKUP_BASTIAN" successfully loaded/unloaded
      ******************************************************************************
      Dump file set for A362634.BACKUP_BASTIAN is:
      /oracle/app/oracle/admin/mkt1/dpdump/BASTIAN.dmp
      Job "A362634"."BACKUP_BASTIAN" successfully completed at Thu Dec 5 01:00:23 2013 elapsed 0 00:09:15

        • 1. Re: expdp now 10 times slower than previous day?
          z00mantwo

          I tried the expdp option ACCESS_METHOD=DIRECT_PATH and it helped a little and dropped the time to 7 minutes. Still a big difference from 47 seconds.

           

          Export: Release 12.1.0.1.0 - Production on Fri Dec 13 11:40:18 2013

          Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

          Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
          With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
          Starting "A362634"."BACKUP_BASTIAN":  a362634/******** PARALLEL=2 schemas=BASTIAN job_name=backup_BASTIAN DUMPFILE=BASTIAN.dmp LOGFILE=BASTIAN.log ACCESS_METHOD=DIRECT_PATH
          Estimate in progress using BLOCKS method...
          Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
          Total estimation using BLOCKS method: 88.87 MB
          Processing object type SCHEMA_EXPORT/USER
          Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
          Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
          Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
          Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
          Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
          Processing object type SCHEMA_EXPORT/TABLE/TABLE
          Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
          Processing object type SCHEMA_EXPORT/TABLE/COMMENT
          Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
          Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
          Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
          Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
          Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
          Processing object type SCHEMA_EXPORT/VIEW/VIEW
          Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
          Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
          Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
          Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
          Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
          . . exported "BASTIAN"."TLR_ACTIVITY_PRODUCT"            15.72 MB  102360 rows
          Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
          . . exported "BASTIAN"."RPT_INVENTORYS"                  15.67 MB   86869 rows
          . . exported "BASTIAN"."RPT_ACTIVITY"                    7.200 MB   38210 rows
          . . exported "BASTIAN"."INVENTORYS"                      4.500 MB  190832 rows
          . . exported "BASTIAN"."STAGE_PRODUCT"                   2.661 MB   11991 rows
          . . exported "BASTIAN"."PRODUCT"                         1.765 MB   12001 rows
          . . exported "BASTIAN"."RPT_PRODUCT"                     1.629 MB    8531 rows
          . . exported "BASTIAN"."S2_ACTIVITY_DETAILS"             4.717 MB   29730 rows
          . . exported "BASTIAN"."ACTIVITY"                        3.326 MB   95268 rows
          . . exported "BASTIAN"."TLR_POTENTIAL"                   334.4 KB    4545 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_GTS"                300.6 KB    2198 rows
          . . exported "BASTIAN"."S2_STORES_PROD_DATES"            344.3 KB   11938 rows
          . . exported "BASTIAN"."RPT_STORE_DEADSTOCK"             275.1 KB    1596 rows
          . . exported "BASTIAN"."STAGE_ACTIVITY"                  267.0 KB    2283 rows
          . . exported "BASTIAN"."STAGE_INVENTORYS"                251.3 KB    4824 rows
          . . exported "BASTIAN"."TLR_ACTIVITY_STORE_GTS"          159.1 KB     980 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_BRANDGROUP"         110.1 KB     734 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_STORE_TIRERANK"     111.1 KB     800 rows
          . . exported "BASTIAN"."RPT_TURNRATES_BRANDPARENT"       87.75 KB     432 rows
          . . exported "BASTIAN"."RPT_TURNRATES_STORE"             93.99 KB     336 rows
          . . exported "BASTIAN"."S2_DEMAND_UTS"                   120.4 KB    2095 rows
          . . exported "BASTIAN"."S2_SOLUTION_COMPARE"             133.3 KB    1547 rows
          . . exported "BASTIAN"."S2_SOLUTION_GAPS"                118.9 KB    1248 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT"        69.56 KB     390 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_BRAND_TIRERANK"     57.71 KB     227 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_DATES"              12.05 KB      44 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_GTS_LAST12"            35 KB     221 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_MONTH"              31.51 KB      44 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_MONTH_STORE"        57.93 KB     300 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_UTS_LAST12"         58.02 KB     408 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUST"              81.49 KB    1789 rows
          . . exported "BASTIAN"."RPT_INVENTORYS_BRANDPARENT"      49.62 KB     513 rows
          . . exported "BASTIAN"."RPT_INVENTORYS_MONTH"            17.25 KB      44 rows
          . . exported "BASTIAN"."RPT_INVENTORYS_MONTH_STORE"      31.86 KB     308 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_TMP2"             60.33 KB     776 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_TMP3"             66.74 KB     776 rows
          . . exported "BASTIAN"."RPT_STORES_CLOSEST"              10.13 KB      42 rows
          . . exported "BASTIAN"."RPT_TURNRATES_DATES"             13.11 KB      48 rows
          . . exported "BASTIAN"."RPT_TURNRATES_STORE_STP4"        61.53 KB     308 rows
          . . exported "BASTIAN"."ACTIVITY_FILTER"                 5.062 KB       2 rows
          . . exported "BASTIAN"."CORPORATE_SOLUTION"              18.98 KB     262 rows
          . . exported "BASTIAN"."CUSTOMER"                        14.32 KB       1 rows
          . . exported "BASTIAN"."LOCATIONS"                       21.28 KB      11 rows
          . . exported "BASTIAN"."PRODUCT_APPLICATION"             33.06 KB     850 rows
          . . exported "BASTIAN"."PRODUCT_BRANDGROUPS"             9.640 KB      92 rows
          . . exported "BASTIAN"."QUARTERS"                        7.132 KB      12 rows
          . . exported "BASTIAN"."RPT_GOODYEAR_SUMMARY"            17.80 KB       4 rows
          . . exported "BASTIAN"."RPT_GTS_LIST"                    11.22 KB      50 rows
          . . exported "BASTIAN"."RPT_GTS_LIST_ALL"                20.53 KB     221 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_C"         6.804 KB       4 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_MODEL"     7.171 KB       1 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_R"         5.945 KB       4 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP"       5.531 KB       6 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP1"      6.476 KB       6 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP2"      8.312 KB      11 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_TMP3"          8 KB      11 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_W"         5.578 KB       4 rows
          . . exported "BASTIAN"."RPT_GTS_PRICECLUSTTMP_YNN"       5.531 KB       7 rows
          . . exported "BASTIAN"."RPT_PRICECLUST"                  8.679 KB      48 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_C"                6.804 KB       4 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_MODEL"            7.171 KB       1 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_R"                5.992 KB       4 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_TMP"              15.01 KB     776 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_TMP1"             23.21 KB     798 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_W"                5.570 KB       4 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_YH"               15.01 KB     776 rows
          . . exported "BASTIAN"."RPT_PRICECLUST_YNN"              13.68 KB     776 rows
          . . exported "BASTIAN"."RPT_TURNRATES_MONTH"             29.94 KB      48 rows
          . . exported "BASTIAN"."RPT_TURNRATE_DATE_OFFSETS"       6.078 KB      61 rows
          . . exported "BASTIAN"."S2_ACTIVITY_PRODUCT_INDIRECT"    26.32 KB     211 rows
          . . exported "BASTIAN"."S2_SCREEN"                       36.42 KB     221 rows
          . . exported "BASTIAN"."S2_SCREEN_ALL"                   44.67 KB     262 rows
          . . exported "BASTIAN"."S2_SETTINGS"                     5.523 KB       1 rows
          . . exported "BASTIAN"."S2_STORES"                       10.90 KB       7 rows
          . . exported "BASTIAN"."S2_STORES_DATES"                 8.546 KB       7 rows
          . . exported "BASTIAN"."S2_STORES_MARKETSHARE"             6.5 KB       7 rows
          . . exported "BASTIAN"."STAGE_CORPORATE_SOLUTION"        31.10 KB     254 rows
          . . exported "BASTIAN"."STAGE_TRADE_AREA"                10.03 KB      97 rows
          . . exported "BASTIAN"."TLR_PRODUCTSCREEN"               9.140 KB      11 rows
          . . exported "BASTIAN"."TRADE_AREA"                      7.664 KB      97 rows
          . . exported "BASTIAN"."BRAND_CATVAL"                        0 KB       0 rows
          . . exported "BASTIAN"."BRAND_PREFERENCE"                    0 KB       0 rows
          . . exported "BASTIAN"."PRODUCT_BRANDLINEOVERRIDES"          0 KB       0 rows
          . . exported "BASTIAN"."PRODUCT_SCREENS"                     0 KB       0 rows
          . . exported "BASTIAN"."RPT_COSTOVERRIDE"                    0 KB       0 rows
          . . exported "BASTIAN"."RPT_PRODUCT_SCREENS"                 0 KB       0 rows
          . . exported "BASTIAN"."S2_SOLUTION_OLD"                     0 KB       0 rows
          . . exported "BASTIAN"."SOLUTION"                            0 KB       0 rows
          . . exported "BASTIAN"."SOLUTION_DETAILS"                    0 KB       0 rows
          . . exported "BASTIAN"."STAGE_BRAND_CATVAL"                  0 KB       0 rows
          . . exported "BASTIAN"."STAGE_BRAND_PREFERENCE"              0 KB       0 rows
          . . exported "BASTIAN"."STAGE_LOCATIONS"                     0 KB       0 rows
          . . exported "BASTIAN"."S2_PRODUCTS"                     1.330 MB    8494 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_PROD_LAST12"        1.416 MB    8530 rows
          . . exported "BASTIAN"."RPT_INVENTORYS_STORE_PRODUCT"    993.5 KB   14847 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT_TIRE"   794.3 KB    5287 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_STORE_PRODUCT"      574.9 KB    4375 rows
          . . exported "BASTIAN"."TLR_ACTIVITY_STORE_PROD"         584.0 KB    4048 rows
          . . exported "BASTIAN"."RPT_INVENTORYS_GTS"              526.2 KB    7244 rows
          . . exported "BASTIAN"."RPT_TURNRATES_GTS"               490.7 KB    2400 rows
          . . exported "BASTIAN"."RPT_ACTIVITY_BRANDPARENT_STORE"  345.2 KB    2472 rows
          . . exported "BASTIAN"."S2_SOLUTION_DETAILS"             389.0 KB    1547 rows
          Master table "A362634"."BACKUP_BASTIAN" successfully loaded/unloaded
          ******************************************************************************
          Dump file set for A362634.BACKUP_BASTIAN is:
            /oracle/app/oracle/admin/mkt1/dpdump/BASTIAN.dmp
          Job "A362634"."BACKUP_BASTIAN" successfully completed at Fri Dec 13 11:47:41 2013 elapsed 0 00:07:21

          • 2. Re: expdp now 10 times slower than previous day?
            TSharma-Oracle

            If you think 7 minutes is too long and expdp is slow. Find it yourself by following metalink notes mentioned below:

            Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)


            Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (Doc ID 286496.1)