Oracle SQL_TRACE和10046事件的优化SQL实例

一数据库版本

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                  切换为管理员