使用场景如下:
- 单独的看看执行计划
- ET语句可以查看指定语句的各个操作符的执行时间占比，便于优化
- 单独disql中查看计划
- 一条sql语句没有走期望的执行计划，这时可以从内存中把它的执行计划dump出来 参考第四种方法
- 10053事件根据设置的条件抓取走全表扫描的sql语句
进行性能测试或者sql调优时可以快速的定位出走全表扫描的SQL语句
Explain
使用方法：在执行的SQL语句上，直接加上explain
SQL> explain select 1 from dual;
1 #NSET2: [1, 1, 1]
2 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
3 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
已用时间: 0.495(毫秒). 执行号:0.ET
有时候我们需要查看sql语句具体是哪个步骤慢，可以使用ET命令：
--两个参数均为动态参数，可直接调用系统函数进行修改
CALL SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
CALL SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);
CALL SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
SQL> select 1 from dual;
行号 1
---------- -----------
1 1
已用时间: 0.969(毫秒). 执行号:2200.
SQL> et(2200);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS HASH_CONFLICT
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
1 CSCN2 2 4.26% 3 3 2 0 0
2 PRJT2 7 14.89% 2 2 4 0 0
3 NSET2 38 80.85% 1 1 3 0 0
已用时间: 18.247(毫秒). 执行号:2201.
--关闭 ET
CALL SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
CALL SP_SET_PARA_VALUE(1,'MONITOR_TIME',0);
CALL SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);set autotrace on
在disql工具中，使用set autotrace on
SQL> set autotrace on;
SQL> select * from dba_tables limit 1;
0 | DSCN | V$HUGE_TABLESPACE | SYSINDEXV$HUGE_TABLESPACE
1 | DSCN | V$TABLESPACE | SYSINDEXV$TABLESPACE
2 | ASC | SYSOBJECTS | SYSINDEXSYSOBJECTS
3 | ASC | SYSOBJECTS | SYSINDEXSYSOBJECTS
4 | ASC | SYSOBJECTS | SYSINDEXPIDIDSYSOBJECTS
5 | CSCN2 | SYSOBJECTS | SYSINDEXSYSOBJECTS
6 | ASC | SYSOBJECTS | SYSINDEXIDSYSOBJECTS
7 | UNIQUE | SYSINDEXES | SYSINDEXINDEXES
8 | CSCN2 | SYSINDEXES | SYSINDEXINDEXES
9 | CSCN2 | SYSCOLUMNS | SYSINDEXCOLUMNS
10 | ASC | SYSSTATS | SYSINDEXSTATS
11 | CSCN2 | SYSSTATS | SYSINDEXSTATS
12 | ASC | SYSOBJECTS | SYSINDEXSYSOBJECTS
13 | ASC | SYSOBJECTS | SYSINDEXIDSYSOBJECTS
已用时间: 17.252(毫秒). 执行号:0.Cacheplan
有时候碰到 EXPLAIN 一条SQL计划可以但是实际上计划不理想的情况。
除了通过10053跟踪 该SQL的计划生成过程，另外也可以查询现有的计划缓存中对应该条SQL 的已缓存计划，方法如下：
-- 数据库默认开启了计划缓存，可以通过如下语句查看是否开启：
select PARA_NAME,PARA_VALUE from v$dm_ini where para_name like 'USE_PLN_POOL';
-- 该参数设置的为非0，即开启了计划缓存。
-- 参数值解释：
-- 0：禁止执行计划的重用；
-- 1：启用执行计划的重用功能 ；
-- 2：对不包含显式参数的语句进行常量参数化优化；
-- 3：即使包含显式参数的语句也进行常量参数化优化开启计划缓存功能后，执行的SQL会将对应执行计划存储到缓冲区，下次执行相同的SQL或者常量值不同的SQL会优先从计划缓存池中查找是否存在已缓存计划，这样可以减少语句分析优化过程，提高执行效率。当计划缓存池满了，根据LRU算法进行计划的淘汰。通过如下语句可以查询计划缓存的情况:
- 查询v$cachepln视图
可以直接通过SQL语句匹配：
-- 第一种： 模糊匹配sql语句
select cache_item from v$cachepln where sqlstr = ‘%待优化sql%’
-- 第二种：计算SQL 的HASH值需要通过存储过程，计算前1万个字符ASCII码值的和：
Select cache_item from v$cachepln where hash_value=
-- 第三种：
select a.cache_item,a.sqlstr ,b.*
from v$cachepln a,dba_objects b
where a.sqlstr like '%CTISYS%'
and a.tableid like '%'||b.object_id||'%'
and b.object_name='DM_GOODS' --表名
and b.object_type='TABLE'; -- 类型- 通过trace事件
-- 引号要用双引号 转义
Alter session set events 'immediate trace name plndump level 139944280676448,dump_file ''/opt/dmdbms/a.log'''计划会生成在指定的目录下，可查看执行计划是否存在异常。
- 清空内存中的执行计划
SP_CLEAR_PLAN_CACHE(140485191235648);10053事件
--开启全表扫描跟踪
alter session 0 set events ‘10003 trace name context forever,level 1’
level 支持1到15 含义如下:
LEVEL 1 CSCN
LEVEL 2 HASH JOIN
LEVEL 4 HAGR
LEVR 8 NEST LOOP
支持 这几种操作符号组合，比如TRACE CSCN 和 HASH JOIN 的语句，level 可以设置为3 （1+2）
--开启全表扫描跟踪
alter session 0 set events '10003 trace name context forever, level 1';
--查看trace日志所在的目录
select * from v$dm_ini where para_name = 'TRACE_PATH';
-- 通过10003事件的trace日志发现了一条SQL,如下：
select * from test where id= ？;
--关闭全表扫描跟踪
alter session 0 set events '10003 trace name context off';10053 trace SQL语句的计划生成过程输出到TRACE 文件，TRACE出的是SQL 的实际执行计划；生成的TRACE文件默认在数据目录生成trace文件夹，文件以.trc结尾。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
