目录
一、测试环境
1、创建测试表和索引，加载数据
2、显示测试表的数据分布
3、查询统计信息
二、生成执行计划
1、执行前用explain命令显示计划
2、disql执行同时显示真实计划
3、用et命令统计执行过程
三、执行计划的重用
1、收集统计信息
2、查询统计信息
3、用explain命令显示执行计划
4、disql执行语句并显示真实计划
四、修正SQL执行计划
1、查询内存中执行计划的CACHE_ITEM
2、清除缓存的执行计划
3、生成新的执行计划
五、执行计划使用指南
六、SQL优化思路总结
1、定位最慢语句
1）最近一个小时已经执行过的最慢语句TOP20
2）当前正在执行的最慢语句TOP20
2、EXPLAN推荐与当前执行计划对比
3、用ET工具协助分析
执行计划是对SQL语句在数据库中的执行过程或访问路径的描述。对于一个SQL查询任务，执行计划是数据库给出的完成此任务的详细方案。为学习并加深认识，我们搭建一个简单实验环境。
一、测试环境
1、创建测试表和索引，加载数据
循环加载，100000行的name字段为“选择性差”，只有一行数据是“选择性好”。并创建索引。
2、显示测试表的数据分布
SQL> select name,count(*) from test 2 group by name; 行号 NAME COUNT(*) ---------- -------- -------------------- 1 选择性差 100000 2 选择性好 1
3、查询统计信息
SQL> dbms_stats.table_stats_show('SYSDBA','TEST');
行号 NUM_ROWS LEAF_BLOCKS LEAF_USED_BLOCKS
---------- -------------------- -------------------- --------------------
1 NULL NULL NULL
已用时间: 0.810(毫秒). 执行号:1202.
SQL> dbms_stats.column_stats_show('SYSDBA','TEST','NAME');
行号 NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
---------- -------------------- --------- ---------- -------------------- ----------- -------------------- ---------
1 NULL NULL NULL NULL NULL NULL NULL
已用时间: 3.096(毫秒). 执行号:1203.
SQL> dbms_stats.index_stats_show('SYSDBA','IND_TEST');
行号 BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
---------- ----------- -------------------- -------------------- ----------------- --------------------
SAMPLE_SIZE
--------------------
1 NULL NULL NULL NULL NULL
NULL
已用时间: 3.611(毫秒). 执行号:1204.
可以看出表、字段和索引上都没有统计信息。
二、生成执行计划
1、执行前用explain命令显示计划
SQL> explain insert into test2 select * from test where name='选择性差'; 1 #INSERT : [0, 0, 0]; table(TEST2), type(select), hp_opt(0), mpp_opt(0) 2 #PRJT2: [2, 2500, 52]; exp_num(2), is_atom(FALSE) 3 #BLKUP2: [2, 2500, 52]; IND_TEST(TEST) 4 #SSEK2: [2, 2500, 52]; scan_type(ASC), IND_TEST(TEST), scan_range['选择性差','选择性差']
由上可见，达梦数据库查询优化器默认优先使用索引。
执行计划的数据流从下向上传递。其中【2，2500，52】三个数字分别表示估算的操作符代价（毫秒）、处理的记录行数和每行记录的字节数。
该表行数100001行，通过查询条件实际筛选出100000行。但由于没有统计信息。优化器不了解真实情况，只能根据"常识"推断该字段选择性为1/40，通过该条件估算可以筛选出2500行数据。因为数据库预估选择性有1/40，所以选择了索引路径。
该计划的大致执行流程如下：
（1）SSEK2：搜索IND_TEST二级索引定位查找条件值，返回ROWID给上级节点。
（2）BLKUP2：用SSEK2节点搜索到的ROWID回表查询，将结果数据传递给父节点PRJT2。
（3）PRJT2：用于表达式项的计算。本例中该节点什么都不做。
（4）INSERT：将查询子句返回的数据写入test2表。
（5）重复过程（1）—（4）直至SSEK2数据全部取完。
2、disql执行同时显示真实计划
SQL> set autotrace trace SQL> insert into test2 select * from test where name='选择性差'; 影响行数 100000 1 #INSERT : [0, 0, 0]; table(TEST2), type(select) mpp_opt(0) 2 #PRJT2: [2, 2500->100000, 52]; exp_num(2), is_atom(FALSE) 3 #BLKUP2: [2, 2500->100000, 52]; IND_TEST(TEST) 4 #SSEK2: [2, 2500->100000, 52]; scan_type(ASC), IND_TEST(TEST) 已用时间: 190.300(毫秒). 执行号:1211.
由上可见，执行语句时使用了explain命令推荐的执行计划。显示结果与explain唯一不同的是“[2, 2500, 52]”变成了“[2, 2500->100000, 52]”，表示处理的记录数估算值是2500，实际是100000。
3、用et命令统计执行过程
SQL> sf_set_session_para_value('MONITOR_SQL_EXEC',1);
DMSQL 过程已成功完成
已用时间: 0.830(毫秒). 执行号:1212.
SQL> et(1211);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB)
---------- ------- -------------------- ------- -------------------- ----------- ----------- --------------------
DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT
-------------------- -------------------- -------------------- ----------------- ---------------
1 DLCK 3 0% 5 0 2 0
0 0 0 NULL NULL
2 PRJT2 122 0.06% 4 2 260 0
0 0 0 NULL NULL
3 SSEK2 14245 7.50% 3 4 130 0
0 0 0 NULL NULL
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB)
---------- ------- -------------------- ------- -------------------- ----------- ----------- --------------------
DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT
-------------------- -------------------- -------------------- ----------------- ---------------
4 INSERT2 28238 14.87% 2 1 131 0
0 0 0 NULL NULL
5 BLKUP2 147269 77.56% 1 3 260 0
0 0 0 NULL NULL
很显然这是一个糟糕的执行计划。绝大部分时间都用在了BLKUP2（回表）操作上。
三、执行计划的重用
为了生成更优的执行计划，为表、字段和索引收集统计信息。
1、收集统计信息
SQL> dbms_stats.gather_TABLE_stats('SYSDBA','TEST',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
DMSQL 过程已成功完成
2、查询统计信息
SQL> dbms_stats.table_stats_show('SYSDBA','TEST');
行号 NUM_ROWS LEAF_BLOCKS LEAF_USED_BLOCKS
---------- -------------------- -------------------- --------------------
1 100001 464 459
SQL> dbms_stats.column_stats_show('SYSDBA','TEST','NAME');
行号 NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
---------- -------------------- --------- ---------- -------------------- ----------- -------------------- ---------
1 2 选择性差 选择性好 0 2 100001 FREQUENCY
SQL> dbms_stats.index_stats_show('SYSDBA','IND_TEST');
行号 BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
---------- ----------- -------------------- -------------------- ----------------- --------------------
SAMPLE_SIZE
--------------------
1 2 848 2 0 100001
100001
可以看出数据字典内已经登记该表的数据分布情况。
3、用explain命令显示执行计划
SQL> explain insert into test2 select * from test where name='选择性差'; 1 #INSERT : [0, 0, 0]; table(TEST2), type(select), hp_opt(0), mpp_opt(0) 2 #PRJT2: [12, 100000, 52]; exp_num(2), is_atom(FALSE) 3 #SLCT2: [12, 100000, 52]; TEST.NAME = '选择性差' 4 #CSCN2: [12, 100001, 52]; INDEX33558522(TEST)
由上可见，查询优化器根据统计信息推荐了新的执行计划。不再使用二级索引IND_TEST了。
该计划的大致执行流程如下：
（1）CSCN2：扫描test表的聚集索引，数据传递上级节点。
（2）SLCT2：根据查询条件筛选出符合的数据传递给上级节点。
（3）PRJT2：用于表达式项的计算。本例中该节点什么都不做。
（4）INSERT：将查询子句返回的数据写入test2表。
重复过程（1）—（4）直至CSCN2数据全部取完。
说明：达梦默认创建索引组织表，每个表都有一个唯一聚簇索引（clustered index ），除此之外的非聚簇索引叫二级索引（secondary indexes）。因此达梦数据库执行计划中不再有ORACLE中常见的“全表扫描”，代之为CSCN2，扫描聚簇索引。
4、disql执行语句并显示真实计划
SQL> insert into test2 select * from test where name='选择性差'; 影响行数 100000 1 #INSERT : [0, 0, 0]; table(TEST2), type(select) mpp_opt(0) 2 #PRJT2: [2, 2500->100000, 52]; exp_num(2), is_atom(FALSE) 3 #BLKUP2: [2, 2500->100000, 52]; IND_TEST(TEST) 4 #SSEK2: [2, 2500->100000, 52]; scan_type(ASC), IND_TEST(TEST)
您可能惊讶的是执行计划还是收集统计信息之前的那个，并没有采用EXPLAIN命令推荐的。究其原因，数据库为了缩短总体执行时间，第一次执行生成的计划会被缓存到内存中。下次同样的SQL不再进行硬解析，直接重用缓存中的计划。
怎么办？
方法是清除旧的执行计划，创建新的执行计划。
四、修正SQL执行计划
1、查询内存中执行计划的CACHE_ITEM
根据sql语句文本在v$cachepln视图内搜索对应的cache_item
SQL> select cache_item,sqlstr from v$cachepln 2 where sqlstr like '%insert into test2 select%' and sqlstr not like '%cachepln%'; 行号 CACHE_ITEM SQLSTR ---------- -------------------- ----------------------------------------------------------- 1 2011994272 insert into test2 select * from test where name='选择性差';
2、清除缓存的执行计划
SQL> sp_clear_plan_cache(2011994272); DMSQL 过程已成功完成 SQL> select cache_item,sqlstr from v$cachepln 2 where sqlstr like '%insert into test2 select%' and sqlstr not like '%cachepln%'; 未选定行
清除执行计划除了采用sp_clear_plan_cache过程以外。还可以在表上执行一个DDL语句。DDL语句后涉及该表的SQL的执行计划自动被清除。
3、生成新的执行计划
清除完缓存的某SQL执行计划后，下一次执行时会根据当前的统计信息生成系统认为最优的计划。本测试在disql中执行，同时显示执行计划。
SQL> insert into test2 select * from test where name='选择性差'; 影响行数 100000 1 #INSERT : [0, 0, 0]; table(TEST2), type(select) mpp_opt(0) 2 #PRJT2: [12, 100000->100000, 52]; exp_num(2), is_atom(FALSE) 3 #SLCT2: [12, 100000->100000, 52]; 4 #CSCN2: [12, 100001->100001, 52]; INDEX33558522(TEST) 已用时间: 44.678(毫秒). 执行号:1224.
由上可见，执行计划已经与前面explain推荐相同了。对比上一次执行时间从193毫秒大幅缩短到44.678毫秒。
五、执行计划使用指南
执行计划顺序：
各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。
缩进最深的，最先执行；缩进深度相同的，先上后下。
口诀：最右最上先执行
常见操作符：
BLKUP2 二次扫描（回表）
CSCN2 聚簇索引扫描（全表扫描）
HASH2 INNER JOIN hash 内连接
NEST LOOP FULL JOIN2 嵌套连接
MERGE INNER JOIN3 归并连接
NSET2 结果集
PRJT2 投影
SSCN 二级索引扫描
SSEK2 二级索引数据定位
CSEK2 聚簇索引扫描
SLCT:选择，用于查询条件的过滤
AAGR:简单聚集，用于没有GROUP BY的COUNT、SUM等聚集函数的计算；
六、SQL优化思路总结
1、定位最慢语句
1）最近一个小时已经执行过的最慢语句TOP20
with SQL_HISTORY as ( select a.* from v$sql_history a,v$session_history b where a.START_TIME > sysdate - 1.0/24 and a.sess_id=b.sess_id and a.sess_seq=b.sess_seq and b.user_name='用户名' ) select sql_id,top_sql_text,sec_to_time(round((sum(time_used)+0.0)/1000000,3)) sql_time_used, count(*) sql_execs,sec_to_time(round((sum(time_used)+0.0)/count(*)/1000000,5)) sql_time_per_exec, round((sum(time_used)+0.0) * 100/(select sum(time_used) from SQL_HISTORY ),2) ratio from SQL_HISTORY group by sql_id,top_sql_text order by 6 desc limit 20 ；
2）当前正在执行的最慢语句TOP20
select clnt_ip,sec_to_time(datediff(ss,last_send_time,sysdate)) elapsed,appname,user_name,RUN_STATUS,sql_id,sql_text,last_send_time
from v$sessions where state in ('ACTIVE','WAIT')
order by elapsed desc
limit 20
2、EXPLAN推荐与当前执行计划对比
当前执行计划可以在disql中通过先设置set autotrace trace而后执行语句获得。
如果是DML命令，或者查询语句返回数据太多无法手动执行的话，可以通过alter session set events 'immediate trace name plndump level 6591105184,dump_file ''d:/1.log'''命令把缓存中的执行计划保存到本地文件中查看。“6591105184”是v$cachepln视图的cache_item字段，根据实际情况修改。“'d:/1.log”根据实际目录修改。
如果EXPLAIN显示的执行计划推荐和缓存中的执行计划不同，则考虑是否需要清除缓存。
3、用ET工具协助分析
用et（执行号，每次执行完会显示；历史语句查找v$sql_history视图的exec_id字段）执行计划中哪部分时间占比最多。通过建索引或修改SQL语法或加HINT消除这个性能瓶颈。
使用et之前需要先通过sf_set_session_para_value('MONITOR_SQL_EXEC',1);命令修改会话级参数。
