Bug 26960528 - SLOW IMPDP ON THE SYS.KUPW$WORKER.MAIN Bug 25786141 - SLOW IMPDP ON THE SYS.KUPW$WORKER.MAIN EXECUTION Bug 22216154 - TTS IMPORT IS VERY SLOW WHEN THERE ARE A LOT OF OBJECTS INVOLVEDSev 1 SR Bug 24423416 - IMPDP FOR SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY TAKES HOURS Bug 16396856 - TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU Use the paramater logtime=all in th export command which could help us find where the export is taking more timeĢ.4.4.1 LOGTIME Parameter for Oracle Data Pump Command The msg can help us to diagnose performance problems. With 12c, a new LOGTIME datapump parameter has been introduced, the msg will be displayed during export and import operations will be timestamped. PL/SQL procedure successfully the paramater logtime to get more diag messages If this number is non-zero, please query the errorĭOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsĭOC> are due to misconfiguration or resource constraints that must beĭOC> fixed before objects can compile successfully.ĭOC> Note: Typical compilation errors (due to coding errors) are notĭOC> logged into this table: they go into DBA_ERRORS instead. Query showing UTL_RECOMP jobs that are runningĭOC> SELECT job_name FROM dba_scheduler_running_jobsĭOC> The following query reports the number of invalid objects.ĭOC> If the number is higher than expected, please examine the errorĭOC> messages reported with each object (using SHOW ERRORS) to see if theyĭOC> point to system misconfiguration or resource constraints that must beĭOC> fixed before attempting to recompile these objects.ĭOC> The following query reports the number of exceptions caught duringĭOC> recompilation. Query showing jobs created by UTL_RECOMPĭOC> SELECT job_name FROM dba_scheduler_jobsĭOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%' ĭOC> 2. Use the following queries to verifyĭOC> whether UTL_RECOMP jobs are being created and run correctly:ĭOC> 1. Jobs are created without instance affinity so that theyĭOC> can migrate across RAC nodes. Streams aq enqueue blocked on low memory serial#This numberĭOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED ĭOC> This script automatically chooses serial or parallel recompilationĭOC> based on the number of CPUs available (parameter cpu_count) multipliedĭOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).ĭOC> On RAC, this number is added across all RAC nodes.ĭOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallelĭOC> recompilation. Query returning the number of objects compiled so far. ThisĭOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6) ĭOC> 2. Query returning the number of invalid objects remaining. Recompilation time is proportional to theĭOC> number of invalid objects in the database, so this command may takeĭOC> a long time to execute on a database with a large number of invalidĭOC> Use the following queries to track recompilation progress:ĭOC> 1. Run the multiple times to validate the invalid altered.ĭOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidĭOC> objects in the database. Run the utlrp.sql multiple times to validate the invalid objects Parallelism improves TTS export and import performance, especially when there are millions of database objects in the data files, including tables, indexes, partitions, and subpartitions. Starting with 20c, Oracle Data Pump improves Transportable Tablespace metadata operations with parallelism. If there are enough objects of the same type to make use of multiple workers, then the objects will be imported by multiple worker processes. For 12.2 import, objects must be created in the correct dependency order. For parallel>1, for 12.2 export, all metadata and data are unloaded in parallel, with the exception of jobs that use transportable tablespace. Starting with 12.2, Data Pump PARALLEL parameter has been extended to include metadata during export and import operations. Set the degree of parallelism to two times the number of CPUs, then tune from there.įor Data Pump Export, the PARALLEL parameter value should be less than or equal to the number of dump files.įor Data Pump Import, the PARALLEL parameter value should not be much larger than the number of files in the dump file set.Ī PARALLEL greater than one is only available in Enterprise Edition Parallel Capabilities of Oracle Data Pump (Doc ID 365459.1) PL/SQL procedure successfully select operation, START_TIME, END_TIME from DBA_OPTSTAT_OPERATIONS Ĩ rows parallel parameter for faster export PL/SQL procedure successfully exec dbms_stats.gather_fixed_objects_stats PL/SQL procedure successfully exec dbms_stats.lock_table_stats (null, 'X$KCCLH' ) Session select operation, START_TIME, END_TIME from DBA_OPTSTAT_OPERATIONS Ģ3-MAR-20 01.81 AM exec dbms_stats.gather_dictionary_stats Alter session set nls_date_format = 'dd-mon-yyyy hh24:mi'
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |