Plsql_ Performance Optimization effectiveness tracking tool SQL Trace Analysis (case)

2014-06-25 Baoxinjian

I. Summary

SQL Trace is an Oracle-provided means for SQL tracing and is a powerful auxiliary diagnostic tool. SQL Trace is a very common method in the diagnosis and resolution of daily database problems.
In general, a single trace can be divided into the following steps:
1. Define the range of targets that need to be tracked and enable the required traces using the appropriate commands.
2, after a period of time, stop tracking. A trace result file should be produced at this time.
3. Locate the trace file and format it, then read or analyze it.

Another article has introduced other tracking tools Dbms_profiler, Form Trace, Request trace, etc.

Second, detailed analysis

Case: Tracking SQL operations in your session

Step1. Query the SID and serial# of your session

Step2. Start the SQL trace node

Begin
Dbms_system.set_sql_trace_in_session (497,13413,true);
End

Step3. After the SQL Trace node is started, the SQL operation is automatically recorded for all SQL operations in the session until it is closed

Step4. Close the SQL trace node

Begin
Dbms_system.set_sql_trace_in_session (497,13413,false);
End

Step5. To view the resulting trace file directory

Step6. Download the trace

Step7. In the trace file, you can find SQL Trace to start and close the SQL between nodes, such as

Step8. You can then convert the trace file into a readable file by using the Format tool tkprof

This step is described in the article, skip

Abalone New ********************

Reference: Xzc http://www.blogjava.net/xzclog/archive/2010/11/13/337985.html