Examples of Oracle SQL Trace problems in practical application

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:

 
 

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

Create a table with data skew on the column:

 
 

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

The table has been created.

 
 

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

You have created 49796 rows.

 
 

  
  
SQL> commit;  
 
 

Submitted

 
 

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

10000 rows have been created

 
 

  
  
SQL> commit;  
 
 

Submitted.

 
 

  
  
SQL> create index t1_idx on t1(flag);  
 
 

Index created

 
 

  
  
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.

 
 

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

Create a table with data skew on the column:

 
 

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

Table created

 
 

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

49796 rows have been created

 
 

  
  
SQL> commit;  
 
 

Submitted.

 
 

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

You have created 10000 rows.

 
 

  
  
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.