【干货攻略】DM8获取SQL历史执行计划

达梦E学 2024-10-14

820

点击上方蓝色字体关注我们

为了更好获取和展示SQL的历史执行计划，达梦提供了 DBMS_XPLAN 系统包。DBMS_XPLAN 包用于历史执行计划的获取展示和清理。本期干货为大家介绍如何使用 DBMS_XPLAN包获取SQL历史执行计划。

环境说明

数据库：达梦数据库管理系统DM8。

正文

一、开启参数

要查看历史执行计划，需要开启ENABLE_MONITOR_PLNHIST参数,开启该参数后历史执行计划保存在SYSPLANHIST表中。该参数是会话级别参数，默认为0。

执行如下SQL查询该参数：

SQL> select name, type, value, sys_value, file_value from v$parameter where name ='ENABLE_MONITOR_PLNHIST';
NAME                   TYPE    VALUE SYS_VALUE FILE_VALUE
---------------------- ------- ----- --------- ----------
ENABLE_MONITOR_PLNHIST SESSION 0     0         0

使用如下SQL在测试会话下修改该参数（注意这里修改的会话级别，后面的测试都需要在该会话下测试，不影响其他会话）：

SQL> alter session set 'ENABLE_MONITOR_PLNHIST'=1;

查询修改后的参数：

SQL> select name, type, value, sys_value, file_value from v$parameter where name ='ENABLE_MONITOR_PLNHIST';
NAME                   TYPE    VALUE SYS_VALUE FILE_VALUE
---------------------- ------- ----- --------- ----------
ENABLE_MONITOR_PLNHIST SESSION 1     0         0

二、执行测试SQL查看历史执行计划

以查询DMHR下人力资源示例库表为例，依次执行如下SQL查询员工表数据（注意，在上面参数修改的会话下执行）：

1、查询员工表中姓马的员工信息：

SQL> select employee_name, employee_id from dmhr.employee t where employee_name like '马%';

2、在员工表姓名列创建索引：

SQL> create index dmhr.ix_emp_name on dmhr.employee(employee_name);

3、再次执行刚才SQL查询姓马的员工数据：

SQL> select employee_name, employee_id from dmhr.employee t where employee_name like '马%';

4、执行如下存储过程将历史执行计划信息刷盘到系统表 SYSPLANHIST 中：

SQL> sp_flush_hist_plan;

5、在SYSPLANHIST表中查询该SQL的PLAN_HASH_VALUE值。

select t.PLAN_HASH_VALUE, T.SQL_STR from SYS.SYSPLANHIST t
where t.SQL_STR like 'select employee_name, employee_id from dmhr.employee%';

6、查看历史执行计划

使用DBMS_XPLAN.DISPLAY_PLANHIST方法获取该SQL的历史执行计划（注意这里的参数是传入步骤5中查询出来的PLAN_HASH_VALUE）：

SQL> set lineshow off
SQL> DBMS_XPLAN.DISPLAY_PLANHIST(1990214725);

7、删除创建的索引，再次执行SQL并查看历史执行计划：

SQL> drop index dmhr.ix_emp_name;
SQL> select employee_name, employee_id from dmhr.employee t where employee_name like '马%';
SQL> sp_flush_hist_plan;
SQL> DBMS_XPLAN.DISPLAY_PLANHIST(1990214725);

三、查询SYSPLANHIST获取历史执行计划

上述测试结果，也可以直接查询SYSPLANHIST表来手工获取历史执行计划：

select t.OPERATION,t.TABLE_NAME,t.INDEX_NAME,t.DEPTH,t.CARDINALITY, t.ACCESS_PREDICATES, t.FILTER_PREDICATES, t.TIMECREATE, t.PLN_STR
 from SYSPLANHIST t
where t.PLAN_HASH_VALUE = 1990214725;

查询结果如下。可以看到，没有创建索引时，FILTER_PREDICATES中是过滤条件；有索引时，ACCESS_PREDICATES是访问条件。

四、测试多表关联历史执行计划

多表关联与单表查询SQL一样，按照步骤二中方法操作（执行SQL、创建索引或者使用其他方式优化SQL、再次执行SQL、执行计划刷盘、查看比对历史执行计划）即可。

1、执行多表关联SQL

select a.DEPARTMENT_ID,
         a.department_name,
         count(*)
    from dmhr.department a
    join dmhr.employee b
      on a.department_id=b.department_id
   where a.department_name = '开发部'
group by a.DEPARTMENT_ID,
         a.department_name;

2、在查询条件创建索引

create index ix_dept_name on dmhr.department(department_name);

3、再次执行步骤1的SQL

4、执行计划信息刷盘

sp_flush_hist_plan;

5、查询SQL的PLAN_HASH_VALUE值

select t.PLAN_HASH_VALUE, T.SQL_STR from SYS.SYSPLANHIST t
where t.SQL_STR like '%开发部%';

6、查看历史执行计划：

DBMS_XPLAN.DISPLAY_PLANHIST(-976726205);

五、删除历史执行计划：

DBMS_XPLAN提供清理历史执行计划记录的方法。如下所示，执行该过程，即可删除1-10天内的系统表 SYSPLANHIST 记录：

（注意：如生产系统执行，请联系达梦技术服务团队获取支持。）

DBMS_XPLAN.CLEAR_PLANHIST(sysdate-10, trunc(sysdate)-1);

总结

1、DBMS_XPLAN用于显示特定 SQL在不同执行时间点的多个执行计划历史记录。该功能用于性能调优，它可以帮助我们了解同一个 SQL 语句在不同时期是如何被优化器处理的，以及它的性能和执行计划的变化。

2、使用时需要先开启ENABLE_MONITOR_PLNHIST参数，该参数是会话级别，可以对单个会话开启，也可以对整个数据库，如果是数据库级别使用完毕后建议及时关闭。

3、测试中注意执行计划的刷盘，以方便迅速的获取历史执行计划。

以上为本期干货。想要了解更多往期干货，可访问页面最下方#达梦技术干货攻略#合集或下方相关分享。在此邀请更多学员参与“达梦技术干货投稿活动”，稿件获选后将在达梦“干货分享”专栏进行发布，欢迎来稿！

作者：青城

审核：培训部

一周热文

达梦E学

微信号：DM-Elearning

扫码关注查看更多内容

点击下方在看，分享本文

文章转载自达梦E学，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。