专栏首页从ORACLE起航,领略精彩的IT技术。Oracle之SQL优化专题02-稳固SQL执行计划的方法

Oracle之SQL优化专题02-稳固SQL执行计划的方法

首先构建一个简单的测试用例来实际演示:

create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
create index idx_emp_empno on emp(empno);
create index idx_dept_deptno on dept(deptno);

测试过程中查看真实执行计划的方法:

set lines 1000 pages 1000
alter session set statistics_level = ALL;
Execute SQL;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

正常的SQL执行,执行计划会走相应的索引:

--good SQL: 39dv3d8jkzyuw
select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;

--good xplan: 1725450077
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  39dv3d8jkzyuw, child number 0
-------------------------------------
select a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where
a.deptno = b.deptno and empno = 7788

Plan hash value: 1725450077

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |       6 |      2 |
|   1 |  NESTED LOOPS                 |                 |      1 |      1 |      1 |00:00:00.01 |       6 |      2 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |      1 |00:00:00.01 |       5 |      2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP             |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
|*  4 |     INDEX RANGE SCAN          | IDX_EMP_EMPNO   |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  5 |    INDEX RANGE SCAN           | IDX_DEPT_DEPTNO |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT            |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMPNO"=7788)
   5 - access("A"."DEPTNO"="B"."DEPTNO")

糟糕的SQL执行,执行计划走全表扫描(这里实验直接利用使用hint强制不走索引来模拟这种情况):

--bad SQL: dqd10y7wqrg7f
select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;

--bad xplan: 1123238657
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqd10y7wqrg7f, child number 1
-------------------------------------
select /*+ no_index(a idx_emp_empno) no_index(b
idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a,
dept b where a.deptno = b.deptno and empno = 7788

Plan hash value: 1123238657

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.01 |       5 |  1214K|  1214K|  377K (0)|
|*  2 |   TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."DEPTNO"="B"."DEPTNO")
   2 - filter("EMPNO"=7788)

假设此时这些糟糕的SQL就是业务实际的SQL,且对应开发人员无法更改SQL文本(这里就是指无法去掉不走索引的hint),那么现在如何能将这些糟糕的SQL绑定成走索引的执行计划呢? 糟糕的SQL清单:

select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;

select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7900;

如何让其走索引?目前Oracle常见的2种稳固执行计划的方式:

1.SQL Profile稳固执行计划

适用于Oracle 10g及以上版本。 利用MOS文档215187.1提供的系列脚本中的coe_xfr_sql_profile.sql来稳固执行计划,只需要输入要调整SQL的SQL_ID和好的执行计划的plan_hash_value即可,脚本内容可参考:

在本次演示实验中,就是将sql_id='dqd10y7wqrg7f'的SQL绑定好的plan_hash_value=1725450077,具体使用过程如下:

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: dqd10y7wqrg7f


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1123238657        .095

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1725450077

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "dqd10y7wqrg7f"
PLAN_HASH_VALUE: "1725450077"
...
Execute coe_xfr_sql_profile_dqd10y7wqrg7f_1725450077.sql
on TARGET system in order to create a custom SQL Profile
with plan 1725450077 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

然后按照提示执行生成的coe_xfr_sql_profile_dqd10y7wqrg7f_1725450077.sql脚本即可; 需要特别注意的是:可以根据实际情况是否需要修改这个脚本中的force_match的值为true。 本次的例子,就是没有使用到绑定变量,而需求是不仅让empno = 7788的条件走索引,还要让其他输入值,比如empno = 7900也同样走索引,那就需要修改这个force_match的值为true。稳固执行计划的效果如下:

SQL> select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;

     EMPNO ENAME      DNAME          JOB              SAL
---------- ---------- -------------- --------- ----------
      7788 SCOTT      RESEARCH       ANALYST         3000

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqd10y7wqrg7f, child number 0
-------------------------------------
select /*+ no_index(a idx_emp_empno) no_index(b
idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a,
dept b where a.deptno = b.deptno and empno = 7788

Plan hash value: 1725450077

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS                 |                 |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |      1 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP             |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | IDX_EMP_EMPNO   |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN           | IDX_DEPT_DEPTNO |      1 |      1 |      1 |00:00:00.01 |       2 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT            |      1 |      1 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMPNO"=7788)
   5 - access("A"."DEPTNO"="B"."DEPTNO")

Note
-----
   - SQL profile coe_dqd10y7wqrg7f_1725450077 used for this statement

常用操作: 1)查询sql_profile 可以通过查询dba_sql_profiles来确认数据库中的sql_profile:

select * from dba_sql_profiles;

2)删除sql_profile 如果有一天不再需要这个sql_profile来稳固执行计划,可以这样删除sql_profile: exec dbms_sqltune.drop_sql_profile('name');

exec dbms_sqltune.drop_sql_profile('coe_dqd10y7wqrg7f_1725450077');

3)清除SQL执行计划 还可以清除共享池中指定SQL的执行计划: exec sys.dbms_shared_pool.purge('address,hash_value','c');

SQL> select sql_id, address, hash_value, plan_hash_value, sql_profile from v$sql where sql_id = 'dqd10y7wqrg7f';

SQL_ID        ADDRESS          HASH_VALUE PLAN_HASH_VALUE SQL_PROFILE
------------- ---------------- ---------- --------------- ----------------------------------------------------------------
dqd10y7wqrg7f 0000000076B909F8 4184587502      1123238657
dqd10y7wqrg7f 0000000076B909F8 4184587502      1123238657
dqd10y7wqrg7f 0000000076B909F8 4184587502      1725450077 coe_dqd10y7wqrg7f_1725450077

SQL> exec sys.dbms_shared_pool.purge('0000000076B909F8,4184587502','c');

PL/SQL procedure successfully completed.

SQL> select sql_id, address, hash_value, plan_hash_value, sql_profile from v$sql where sql_id = 'dqd10y7wqrg7f';

no rows selected

2.SPM稳固执行计划

适用于Oracle 11g及以上版本。 删除掉之前的sql_profile,尝试使用SPM来稳固执行计划,实际上,手工生成sql_plan_baseline的方式要更加灵活,但我实际用的比较少。 查看sql_plan_baselines:

select * from dba_sql_plan_baselines;
select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines;

SPM稳固执行计划方法:

var temp number
--1.bad: sql_id & plan_hash_value
exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '', plan_hash_value => );
--2.good: sql_id & plan_hash_value & sql_handle
exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '', plan_hash_value => , sql_handle => );
--3.drop bad plan_name
exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle => '', plan_name => '');

用上面的例子具体说明:

--1.bad: sql_id & plan_hash_value
SQL> var temp number
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => 'dqd10y7wqrg7f', plan_hash_value => 1123238657);
SQL>  select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SQL_9c3626a309e5e8bd           SQL_PLAN_9sdj6nc4ybu5x96fd8705 MANUAL-LOAD    YES YES NO

--2.good: sql_id & plan_hash_value & sql_handle(上面查到的)
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '39dv3d8jkzyuw', plan_hash_value =>1725450077, sql_handle => 'SQL_9c3626a309e5e8bd');

PL/SQL procedure successfully completed.

SQL>  select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SQL_9c3626a309e5e8bd           SQL_PLAN_9sdj6nc4ybu5x2b78d17a MANUAL-LOAD    YES YES NO
SQL_9c3626a309e5e8bd           SQL_PLAN_9sdj6nc4ybu5x96fd8705 MANUAL-LOAD    YES YES NO

--3.drop bad plan_name
SQL> exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_9c3626a309e5e8bd', plan_name => 'SQL_PLAN_9sdj6nc4ybu5x96fd8705');

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ ------------------------------ -------------- --- --- ---
SQL_9c3626a309e5e8bd           SQL_PLAN_9sdj6nc4ybu5x2b78d17a MANUAL-LOAD    YES YES NO

验证稳固执行计划的效果:

SQL> select /*+ no_index(a idx_emp_empno) no_index(b idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a, dept b where a.deptno = b.deptno and empno = 7788;

     EMPNO ENAME      DNAME          JOB              SAL
---------- ---------- -------------- --------- ----------
      7788 SCOTT      RESEARCH       ANALYST         3000

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dqd10y7wqrg7f, child number 1
-------------------------------------
select /*+ no_index(a idx_emp_empno) no_index(b
idx_dept_deptno)*/a.empno, a.ename, b.dname, a.job, a.sal from emp a,
dept b where a.deptno = b.deptno and empno = 7788

Plan hash value: 1725450077

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS                 |                 |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |      1 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP             |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | IDX_EMP_EMPNO   |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN           | IDX_DEPT_DEPTNO |      1 |      1 |      1 |00:00:00.01 |       2 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT            |      1 |      1 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMPNO"=7788)
   5 - access("A"."DEPTNO"="B"."DEPTNO")

Note
-----
   - SQL plan baseline SQL_PLAN_9sdj6nc4ybu5x2b78d17a used for this statement

可以看到SPM已经起作用了。但如果谓词条件换成7900,就会不起作用。我没有找到SPM中类似像sql_profile中force_match的参数,日常工作中也是使用sql_profile稳固执行计划多一些。 另外注意dba_sql_plan_baselines中记录的执行计划对应的ACCEPTED和ENABLE的值都为YES,才可能会被SQL使用。 常用操作: select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines; 1)将ENABLE的值设为"YES" or "NO"

var temp number
exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_9c3626a309e5e8bd', plan_name => 'SQL_PLAN_9sdj6nc4ybu5x96fd8705', attribute_name => 'ENABLED', attribute_value => 'YES');

var temp number
exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_9c3626a309e5e8bd', plan_name => 'SQL_PLAN_9sdj6nc4ybu5x96fd8705', attribute_name => 'ENABLED', attribute_value => 'NO');

2)将ACCEPTED值设为"YES"

var temp clob
exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_9c3626a309e5e8bd', plan_name => 'SQL_PLAN_9sdj6nc4ybu5x96fd8705', verify => 'NO', commit => 'YES');

注:我这里测试(在11.2.0.4环境下)发现ACCEPTED值设为"YES"后,无法再设置成"NO",而ENABLED的值可以自由设置为"YES" or "NO"。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 相克军_Oracle体系_随堂笔记004-shared pool

    oracle 中只可以整体设置shared pool的大小,不可以对shared pool中的内存区域分别设置大小。

    Alfred Zhao
  • Oracle巡检脚本:ORAWatcher.sh

    Alfred Zhao
  • RAC某节点启动遭遇ORA-01105,ORA-01606

    环境:RHEL6.5 + Oracle11.2.0.4 双节点RAC 故障现象:节点1实例没有启动成功,节点2正常启动。

    Alfred Zhao
  • “大促”也挡不住顾客流失?可能是你的数据在“睡觉”

    互联网、电商的快速发展,造就了“双11”、“618”这样的网购狂欢。消费者的消费行为也慢慢从线下扩展到线上,这带来的一个结果是:以往难以被收集的消费数据开始更多...

    DT数据侠
  • 4-14 还原二叉树 (15 分)

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    韩旭051
  • 怎样学Python之第十九课 高级文件输入和输出

    欢迎回来!如果您还记得以前的几次培训课程,我们介绍了基本的文件I/O。 这是使我们的脚本适用于现实生活场景中的一个非常重要的步骤,今天我们将要深入这些概念。 我...

    用户1631416
  • 人脸检测(一)——基于单文档的应用台程序

    Opencv自带训练好的人脸模型(人脸的人眼、口等器官类似),此文基于vs2013建立应用台单文档程序,具体建立过程不予详细叙述,主要记录利用的Opencv自带...

    小小詹同学
  • 利用 Python ElementTree 生成 xml的实例

    Python 处理 xml 文档的方法有很多,除了经典的 sax 和 dom 之外,还有一个 ElementTree。

    砸漏
  • Integer对象的一些误解

    这里先明确一个概念,对象之间的直等(==)比较的是两个内存的地址。而boolean byte char short int long float double八...

    zhangheng
  • 【HBU数据结构月考】7-2 还原二叉树 (30 分) 输出高度

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY 版权协议,转载请附上原文出处链接和本声明。 ...

    韩旭051

扫码关注云+社区

领取腾讯云代金券