拜年+散分贴《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(*)
----------------