Examples of Oracle SQL Trace problems in practical application

Source: Internet
Author: User

We all know that Oracle SQL Trace and 10046 event are common tools used to diagnose Oracle database performance. However, when the level is over 1, 10046 events are generally called extended SQL trace. It is mainly used to diagnose the performance problems of a single SQL, stored procedure, or session. It has the following advantages:

You can get the actual execution plan for SQL Execution.

You can obtain the specific distribution of the time spent in SQL Execution, the time consumed by the CPU, and the time consumed by multiple reads.

Various performance-related statistics during SQL Execution can be obtained, including logical reads, physical reads, fetch times, and parse times.

It can be used not only for performance testing, but also for diagnosing the performance of SQL statements or stored procedures being executed.

Many tools are used to format the generated trace files, except for the TKPROF, Metalink Note 224270.1 Trace Analyzer, and third-party free tools such as orasrp, TVD $ XTAT developed by the author Troubleshooting Oracle Performance, and even commercialized software Hotsos Profiler.

However, some time ago, when 10046 events were used to diagnose a performance problem, the generated results were misleading. Later, I checked carefully and found that when Oracle SQL trace is enabled in the session, the SQL statement will be re-parsed, which may cause the execution plan to change after enabling SQL trace, as a result, the SQL trace results cannot reflect the execution of SQL statements in a session, which is prone to deviations during analysis.

The following is a test:

The test environment is Oracle 10.2.0.1 for Windows. However, the case mentioned above occurs in Oracle 9i, so 9i and 10g both have this problem, and 11g has not been tested yet, if you are interested, you can perform a test on 11g.

First, create an SQL file with the following content:

 
 
  1. select /*+ testsql */ sum(value) from t1 where flag=:v_flag;  

Create a table with data skew on the column:

 
 
  1. SQL> create table t1 (value number ,flag number,pad varchar2(2000));  

The table has been created.

 
 
  1. SQL> insert into t1 select rownum,mod(rownum,2000),lpad('x',1000,'x') from dba_objects;  

You have created 49796 rows.

 
 
  1. SQL> commit;  

Submitted

 
 
  1. SQL> insert into t1 select rownum,3000,lpad('x',1000,'x') from dba_objects where rownum<=10000; 

10000 rows have been created

 
 
  1. SQL> commit;  

Submitted.

 
 
  1. SQL> create index t1_idx on t1(flag);  

Index created

 
 
  1. SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T1
    ',cascade=>true,method_opt=>'for all indexed columns'); 

Note that the PL/SQL process has been completed successfully.

 
 
  1. SQL> select column_name,num_distinct,num_buckets from user_tab_columns where table_name='T1';   
  2. COLUMN_NAME NUM_DISTINCT NUM_BUCKETS   
  3. VALUE   
  4. FLAG 2030 75   
  5. PAD   
  6. select /*+ testsql */ sum(value) from t1 where flag=:v_flag;  

Create a table with data skew on the column:

 
 
  1. SQL> create table t1 (value number ,flag number,pad varchar2(2000));  

Table created

 
 
  1. SQL> insert into t1 select rownum,mod(rownum,2000),lpad('x',1000,'x') from dba_objects;  

49796 rows have been created

 
 
  1. SQL> commit;  

Submitted.

 
 
  1. SQL> insert into t1 select rownum,3000,lpad('x',1000,'x') from dba_objects where rownum<=10000; 

You have created 10000 rows.

 
 
  1. SQL> commit;  

Submitted. The above content describes the issues that need to be paid attention to when using Oracle SQL trace. I hope it will help you in this regard.

Related Article

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.