拜年+散分贴《Oracle SQL_TRACE和10046事件优化SQL实例》

一数据库版本LEO1@LEO1select*fromv$version;BANNER--------------------------------------------------------------------------------OracleDatabase11gEnter

一 数据库版本

LEO1@LEO1>select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux:Version 11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 - Production

 

二 演示使用SQL_TRACE和10046事件对其它会话进行跟踪，并给出trace结果

SQL_TRACE：Oracle这个功能主要是为了追踪SQL的执行过程，分析SQL的性能，资源消耗情况。

1.查看SQL是如何操作处理数据

2.查看SQL在执行过程中产生了的等待事件

3.查看SQL的执行过程资源消耗

4.查看SQL的实际执行计划

5.查看SQL的递归语句

6.如果要探索SQL如何执行的可以详细看看

10046：用于分析SQL执行过程中性能消耗情况，可以查看绑定变量信息，可以查看等待事件信息，它比SQL_TRACE输入输出更多参数。

 

上述工具使用场合：1.优化SQL语句

                  2.查看SQL语句执行计划

                  3.跟踪SQL语句执行过程

                  4.把会话中SQL的信息重定向到一个文件里

 

SET AUTO TRACE：1.输出SQL语句估算的执行计划（猜出来的）

                2.SQL语句并没有真正执行，只关注这条SQL的执行计划对不对

                3.只是用来估算执行计划

 

实验

使用SQL_TRACE对其它会话进行跟踪

如果对当前会话进行跟踪只需alter session set sql_trace=true;即可，如果对其它会话进行跟踪还需要设置另外一些参数。

我们现在做一下，从144会话跟踪12会话的SQL

144会话我们使用leo1用户操作

12会话我们使用leo2用户操作

144会话

LEO1@LEO1> selectdistinct sid from v$mystat;    可以查询当前会话ID

       SID

----------------

       144

我们用会话ID和串号来唯一定位一个会话，现在我们把2个会话信息都显示出来了

LEO1@LEO1>select sid,serial# from v$session where sid in (144,12);

       SID   SERIAL#

---------------------------------

      12      4472

     144       979

这时我有了一个疑问，定位一个会话一般来说看sid就可以了，那么为什么后面还跟着个serial呢，这个serial是干什么用的呢，咨询了一下Alantany 查了一下官方文档

SID NUMBER：Sessionidentifier   就是会话标识

SERIAL#  NUMBER  ：是用来标识唯一一个会话操作对象的，保证这个会话发出的命令可以正确的应用到对应的会话对象上。

场合 一个会话的结束和另一个会话开始都使用了同一个SID，区分这是2个不同的会话

例子

第一次leonarding登陆sid=12，操作了leo1表，退出

  SID   SERIAL#

---------------------------------

  12       4472

第二次Alan登陆sid=12，又操作了leo2表，退出

  SID   SERIAL#

---------------------------- ----

  12      4777

如果只是看SID我们不能分辨出是谁登录了会话操作了leo1表和leo2表，而serial可以分辨出不同会话的命令正确应用到对应的对象上，区分这是2个不同的人登录的会话。

 

LEO1@LEO1> droptable leo1;                  清理环境

Table dropped.

LEO1@LEO1>create table leo1 as select * from dba_objects;    用leo1用户创建leo1表

Table created.

LEO1@LEO1>select count(*) from leo1;                    看看有多少条记录

  COUNT(*)

----------------

     72007

LEO1@LEO1>execute dbms_stats.gather_table_stats('LEO1','LEO1',method_opt=>'for allcolumns size 254');

PL/SQL proceduresuccessfully completed.

随便做个表分析和直方图

LEO1@LEO1> conn/ as sysdba                  切换为管理员

Connected.

SYS@LEO1> grantexecute on dbms_system to leo1; 授予执行“系统包”的用户权限给leo1，必须授予否则报错

ERROR atline 1:

ORA-06550:line 1, column 7:

PLS-00201:identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared

ORA-06550:line 1, column 7:

PL/SQL:Statement ignored

SYS@LEO1> connleo1/leo1                     我们在切换回来

LEO1@LEO1>execute sys.dbms_system.set_sql_trace_in_session(12,4472,true);

PL/SQL proceduresuccessfully completed.

启动跟踪会话ID=12，SERIAL=4472的SQL

声明：这个存储过程是SYS用户特有的，网站空间，所以在引用时必须带schema，不带就会报错如下

ERROR atline 1:

ORA-06550:line 1, column 7:

PLS-00201:identifier 'SYS.DBMS_SYSTEM' must be declared

ORA-06550:line 1, column 7:

PL/SQL:Statement ignored

12会话

LEO2@LEO1> select /*+ trace_by_leo1_session*/ count(*) from leo1.leo1;    leo2用户查询leo1表

  COUNT(*)

----------------