SQL优化【基础10】 - 10046&10053

version:11.2

--10046事件

作用：统计SQL执行过程中资源开销统计信息
使用方法：

一.  采用非oradebug方法 

1.针对本地会话
alter session set events '10046 trace name context forever,level n';  --开启,其中n为1，2，4，8
alter session set events '10046 trace name context off';  --结束

level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件


举例：
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select status from t1 where object_id=1;

no rows selected

SQL> alter session set events '10046 trace name context off';

Session altered.


--获取spid,便于关联trace文件
SQL> @getspid

CURRENT_SPID
------------------------
3444

SQL> !more getspid.sql
select spid  current_spid from v$process where addr in (select paddr from v$session where sid in (select sid from 
v$mystat where rownum=1));

--在udump目录下根据spid获取
[oracle@11g3 trace]$ ls -tl |grep 3444
-rw-r----- 1 oracle oinstall     97671 Oct 11 10:58 oracle11gr3_ora_3444.trc

2.  针对其它会话:
EXEC DBMS_SYSTEM.set_ev(si=>182, se=>785, ev=>10046, le=>12, nm=>'');    --最后一个参数nm不要有空格
EXEC DBMS_SYSTEM.set_ev(si=>182, se=>785, ev=>10046, le=>0, nm=>'');

si:  v$session.sid
se: v$session.serial#
ev:  事件号码，如10046,10053
le:  level级别(1,2,4,8)
nm:为空''

 
二.  采用oradebug方法 
oradebug setospid 12345;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12;
oradebug event 10046 trace name context off;
oradebug tracefile_name

---------------------------------------------------------------------------------------------
--10053事件

=>与10046的获取方法类似
--获取单前会话
alter session set events '10053 trace name context forever,level 1';
alter session set events '10053 trace name context off';

--获取其它会话
EXEC DBMS_SYSTEM.set_ev(si=>182, se=>787, ev=>10053, le=>1, nm=>'');
EXEC DBMS_SYSTEM.set_ev(si=>182, se=>787, ev=>10053, le=>0, nm=>'');

--采用oradebug方法 
oradebug setospid spidxx;
--oradebug unlimit;
oradebug event 10053 trace name context forever,level 1;
oradebug event 10053 trace name context off;
oradebug tracefile_name

三.文件查看
1.--10046文件查看

(1).直接查看,能看到较为详细说明，但比较难看
(2).tkprof工具,后跟参数
--按逻辑读的降序排序，最耗逻辑读的排在最前面,并且执行用户为非sys用户
tkprof /u01/app/oracle/diag/rdbms/oracle11gr3/oracle11gr3/trace/oracle11gr3_ora_3486.trc  02.txt  sort=exeqry  sys=no
(3).这个跟通常的explan,set autot 的统计信息有何异同?
明显10046包含SQL执行的parse,exec,bind,fetch阶段的统计信息，而不是像explan是全部的时间，10046更详细更易于定位问题,

--下图看到解析花费在CPU上的时间为121982微秒，elapsed=245360微秒（全程)

2.--10053文件查看
=>必须是硬解析才能捕获
作用：10053是解释生成执行计划的过程，解释选择访问方式，连接方式原因，有几种连接方式。。。

--通过grep匹配可以看到SQL的连接顺序的组合有6种
*********************************
[oracle@11g3 trace]$ grep "Number of join permutations tried"  /u01/app/oracle/diag/rdbms/oracle11gr3/oracle11gr3/trace/oracle11gr3_ora_4532.trc
Number of join permutations tried: 6

四.  使用场景
10046:比如当一条SQL当成执行计划来看实际的执行时间为0.1秒，但全程的执行时间为4秒，那么剩下的3.9秒跑哪去了呢？，也就是10046关注于资源的消耗分布;
10053:接上面，发现硬解析的3.9秒大部份时间都耗在硬解上，那么就可以在10053上关注下是否连接方式过多造成，默认最大为2000，也就是10053关注于访问方式，连接方式的选择上
NAME                                     VALUE                     ISDEFAULT ISMOD      ISADJ
---------------------------------------- ------------------------- --------- ---------- -----
_optimizer_max_permutations              2000                      TRUE      FALSE      FALSE