Oracle SQL trace的正确使用以及需注意的问题

今天主要向大家介绍的是如何正确的使用Oracle Sql Trace与10046 event来对Oracle数据库的性能进行相关的诊断，我们大家都知道level超过1的10046事件时通常被称为extended sql trace，通常用于诊断确定的单个SQL、存储过程或会话的性能问题，具有如下的几个优点：

可以得到SQL执行时实际的执行计划。

可以得到SQL执行时所花时间的具体分布，CPU消耗了多长时间，多块读消耗了多长时间等等。

可以得到SQL执行时的各种与性能相关的统计数据，逻辑读、物理读、fetch次数、parse次数等等。

不仅能够用于性能测试，同时能够用于诊断正在执行的SQL或存储过程的性能。

有很多的工具用于格式化生成的trace文件，除了Oracle自带的TKPROF、Metalink Note 224270.1 Trace Analyzer，以及第三方的免费工具如orasrp，《Troubleshooting Oracle Performance》作者开发的TVD$XTAT，甚至还有商业化的软件Hotsos Profiler等。

不过前段时间在用10046事件诊断一个性能问题的时候，却让生成的结果误导了。后来仔细检查发现，在会话开启Oracle sql trace的情况下，SQL语句会重新解析，导致开启sql trace之后与开启之前相比，执行计划可能发生了变化，导致sql trace的结果不能真实地反映会话执行SQL的情况，在分析时容易发生偏差。

下面是一个测试：

测试的环境是Oracle 10.2.0.1 for Windows，不过前面提到的案例，是发生在Oracle 9i下的，所以9i和10g都有这个问题，而11g目前还没有测试过，有兴趣的朋友可以在11g上进行测试。

首先创建一个sql文件，内容为：

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

创建一个列上数据有倾斜的表

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

表已创建。

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

已创建49796行。

SQL> commit;  

提交完成。

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

已创建10000行。

SQL> commit;  

提交完成。

SQL> create index t1_idx on t1(flag);  

索引已创建。

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

PL/SQL 过程已成功完成。

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;  

创建一个列上数据有倾斜的表：

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

表已创建。

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

已创建49796行。

SQL> commit;  

提交完成。

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

已创建10000行。

SQL> commit;  

提交完成。上述的相关内容就是对使用Oracle SQL trace时需要注意的问题的描述，希望会给你带来一些帮助在此方面。

【编辑推荐】

1. 实现Oracle传输表空间的实际功能解析
2. Oracle rownum用法的归纳
3. Oracle存储过程定时执行2种方法
4. Oracle 10g中的hints调整机制详解
5. 提高Oracle SQL的执行效率的3个方案