使用场景如下:
- 单独的看看执行计划
- 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结尾。