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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.