Have you ever got stucked up in a long running job ? Do you want to know the behaviour of each sql you are writing ? how the execution path of a sql is determined ? Do you want to know the most time consuming sql statement in your long running procedure or job ? Do you want to know how much time is being spent for cpu cyles, disk acces ? where is the root problem of an query ? on What wait event oracle is waiting ?
For all your question on simple answer is Oracle 10046 Trace. Oracle trace Sql event traces the sql that a user session executes giving diagnostics information, which is useful for general troubleshooting and performance tuning. Oracle 10046 trace is a powerful platform to identify the most problematic sql and how it is behaving with in the oracle environment. With Oracle 10046 trace oracle is capable of providing detailed information that can be used to locate an error or to identify and resolve Performance problems. Oracle 10046 Trace itself consumes memory as well effects the performance of jobs you want to test it. Never run a oracle trace in a production system.To avoid this Oracle has introduced different trace levels based on the information needed. The other trace levels available with oracle 10046 trace are level 1, level 4, level 8, level 12.A 10046 deep trace (level 12) shows exactly how the database is processing a query, the events the system is waiting on and how much time it spends waiting for a specific event.You can enable the tracing or disable it at different phases of a complex job and it is clearly possible to identify a performance issue.