SCRIPT: To Enable and Disable 10046 Tracing for a Specific Data Pump Job after The Start Of The Job
(Doc ID 2005120.1)
Last updated on JULY 02, 2023
Applies to:
Oracle Database Cloud Service - Version N/A and laterOracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Goal
The purpose of this note is to have scripts that can be run after a Data Pump job has started that will either enable or disable 10046 tracing of the dm## and dw## processes.
The expected scenario where this pair of scripts would be used is when there is a need to find out what SQL statements issued by dm## and dw## processes are using the most time to be executed and/or to be parsed.
With the trace dm## and dw## files generated while event 10046 was in effect, the tkprof utility which comes with the Oracle RDBMS server software can then be used to process each of the trace files to find out which SQL statements consumed the most time sorted by various criteria.
Typically, 10046 tracing at level 12 would be used when troubleshooting a performance issue with Data Pump based operations that is thought to be likely due to the slow execution of one or more SQL queries performed by the Data Pump job.
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |
| Requirements |
| Configuring |
| Instructions |
| Sample Code |
| Sample Output |