总有一种SQL执行计划绑定方式合适你-白红宇

总有一种SQL执行计划绑定方式合适你

阅读量：6685 次

发布时间：2019-06-25

本文共 14259 字，大约阅读时间需要 47 分钟。

需要绑定SQL执行计划常见的几种情况：

SQL执行计划突变，导致数据库性能下降，从历史执行计划找一个合理的，进行绑定。

SQL无法使用更优的执行计划，且无历史执行计划，可通过hint手工构造的方式，进行绑定。

某些Bug引起优化器生成较差的执行计划。在bug修复前，进行绑定。

ORACLE固定执行计划的3种方式：

Oracle 9i使用outline （可跨版本10，11g均可使用）

Oracle 10g使用sql profile （11g也可使用）

Oracle 11g使用sql plan manage

接下来简述如何使用这3种方式进行执行计划的固定，并举例说明3种固定执行计划的优缺点，通过对比选择合适的固定执行计划来应对不同的业务场景。也就是什么场景下使用何种执行计划固定比较合适。

一、大纲（Stored Outline）

1、当SQL执行计划因新版本变更，统计信息不准确，新建索引，参数改变等发生改变时，存储大纲可以使SQL语句的执行计划保持不变。在创建某条语句的大纲时，ORACLE会将SQL语句的文本，执行计划和语句使用的hints存储在一个系统默认用户OUTLN的3个表OL$，OL$HINTS，OL$NODES上。

2、使用大纲（outline）固定执行计划

--环境构建，建立测试表create table zw as select * fromdba_objects where object_id is not null;explain plan for select count(*) from zw;select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 249608387 -------------------------------------------------------------------| Id | Operation       | Name | Rows  | Cost (%CPU)| Time       |-------------------------------------------------------------------|   0| SELECT STATEMENT   |      |   1 |  312   (1)| 00:00:04 ||   1|  SORT AGGREGATE    |            |   1 |          |            ||   2|   TABLE ACCESS FULL| ZW   | 83926 |  312   (1)| 00:00:04 |------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   -dynamic sampling used for this statement (level=2) --创建大纲(全表扫描)create or replace outline zwoutline forcategory mycate on select count(*) from zw;--创建object_id列索引，将该列属性设置为非空alter table zw modify object_id not null;  --索引不存储null值create index idx_zw_obj_id onzw(object_id);analyze table zw compute statistics;explain plan for select count(*) from zw;select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1836624960 -------------------------------------------------------------------------------| Id | Operation          | Name               | Rows | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0| SELECT STATEMENT      |           |    1 |    50         (0)| 00:00:01 ||   1|  SORT AGGREGATE       |                 |    1 |      |                ||   2|   INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID |79741 |    50        (0)| 00:00:01 |------------------------------------------------------------------------------- --使用大纲固定执行计划alter system/session setuse_stored_outlines=mycate;--固定执行计划之后，就会按照创大纲时的执行计划去执行。

上述的建立的大纲为公有大纲，为了不影响其它用户的使用，可以建立私有大纲如下：

create or replace private outlinezwoutline2 for category mycate2 on select count(*) from zw;

思考：为什么我构建测试时，固定的是全表扫描，而不是比较优化的索引扫描？

其实这里我想说明的是outline的缺点是比较死板的，当创建新的索引，或者数据量大幅度变化时是无法做出相应改变的，也就是说它是固定死的。

二、SQL_PROFILE

1、DBMS_SQLTUNE是10g引入的一个新特性，它可以通过自动优化性能较差SQL，并给出合理的优化建议，其中优化建议中的sql_profile文件它是一个存储在数据字典中的信息集合。sql_profile不包含单独的执行计划，提供数据库配置、绑定变量、优化统计信息、数据集等信息供优化器选择执行计划。这里不对SQL优化建议工具SQL Tuning Advisor STA）进行介绍，有兴趣的童鞋研究一下DBMS_SQLTUNE包。

2、使用coe_xfr_sql_profile.sql固定执行计划

--环境构建，建立测试表，与outline测试一样SQL> create table zw as select * fromdba_objects where object_id is not null;SQL> alter table zw modify object_id notnull;  --索引不存储null值SQL> create index idx_zw_obj_id onzw(object_id);SQL> analyze table zw computestatistics;SQL> select count(*) from zw;  -- INDEX FAST FULLSCAN  COUNT(*)----------    79741 SQL> select * fromtable(dbms_xplan.display_cursor(null,0)); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------SQL_ID     1f5n0rapts695, child number 0-------------------------------------select count(*) from zw Plan hash value: 1836624960 -------------------------------------------------------------------------------| Id | Operation          | Name               | Rows | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0| SELECT STATEMENT      |           |      |    50 (100)|         ||   1|  SORT AGGREGATE       |                 |    1 |      |                ||   2|   INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID |79741 |    50        (0)| 00:00:01 |------------------------------------------------------------------------------- SQL> select /*+ full(zw) */ count(*)from zw; --使用hint提示，强制走全表，生成一个执行计划  COUNT(*)----------    79741 SQL> select * fromtable(dbms_xplan.display_cursor(null,0)); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------SQL_ID     g5yd6rabqtdbp,child number 0-------------------------------------select /*+ full(zw) */ count(*) from zw Plan hash value: 249608387 -------------------------------------------------------------------| Id | Operation       | Name | Rows  | Cost (%CPU)| Time       |-------------------------------------------------------------------|   0| SELECT STATEMENT   |      |    |   312 (100)|      ||   1|  SORT AGGREGATE    |            |   1 |          |            ||   2|   TABLE ACCESS FULL| ZW   | 79741 |  312   (1)| 00:00:04 |-------------------------------------------------------------------SQL> @coe_xfr_sql_profile.sql Parameter 1:SQL_ID (required) Enter value for 1: 1f5n0rapts695  PLAN_HASH_VALUE AVG_ET_SECS--------------- -----------    1836624960          .02 Parameter 2:PLAN_HASH_VALUE (required) Enter value for 2: 249608387 Values passed to coe_xfr_sql_profile:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL_ID            : "1f5n0rapts695"PLAN_HASH_VALUE: "249608387" Executecoe_xfr_sql_profile_1f5n0rapts695_249608387.sqlon TARGET system in order to create acustom SQL Profilewith plan 249608387 linked to adjustedsql_text.  COE_XFR_SQL_PROFILE completed. SQL> explain plan for select count(*)from zw; Explained. SQL> select * fromtable(dbms_xplan.display()); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 249608387 -------------------------------------------------------------------| Id | Operation       | Name | Rows  | Cost (%CPU)| Time       |-------------------------------------------------------------------|   0| SELECT STATEMENT   |      |   1 |  312   (1)| 00:00:04 ||   1|  SORT AGGREGATE    |            |   1 |          |            ||   2|   TABLE ACCESS FULL| ZW   | 79741 |  312   (1)| 00:00:04 |------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------   -SQL profile "coe_1f5n0rapts695_249608387"used for this statement SQL> selectname,category,status,sql_text from dba_sql_profiles; NAME                                CATEGORY     STATUS  SQL_TEXT------------------------------      ------------      -------------------------------------------coe_1f5n0rapts695_249608387    DEFAULE   ENABLED  select count(*) from zw

使用coe_xfr_sql_profile.sql固定计划是不是很好用呢？是的，这一切都归功于oracle mos上的功劳，需要的童鞋可以到matelink上查找和下载。还有其它两个有关的脚本：coe_load_sql_baseline.sql，coe_load_sql_profile.sql，有兴趣的童鞋可以一起下载研究。

思考：当在使用SQL_PROFILE绑定之前，使用了OUTLINE进行固定的话，谁的优先级高呢？

根据网上的一些资料说是OUTLINE的优先级最高，但都是简短的一句话，没有证明。可是经过我无数次的测试，发现都是SQL_PROFILE的优先级较高，具体相关测试结果我就不粘贴出来了。（或许是我测试语句的特殊性，需再进一步验证）

值得一提的是，sql_profile并不会以outline方式存储冻结执行计划，当表中数据增长或索引被删除或重建时，在sql_profile不变的情况下执行计划也可以发生变化，信息的存储和与数据的分布或者访问路径有关。

三、SQL PLANMANAGE

1、从11g开始，oracle引入了SQL执行计划管理（SQLPlan Management）这个新特性，与Oracle 9i 的outline和10g 的profile比，Oracle 11g的SPM相对更加的灵活，允许你同时接受多个执行计划。

2、使用SQL PlanManagement固定执行计划

--一条带有绑定变量的SQL语句，但数据分布不均，严重倾斜时，最好的执行计划会根据绑定变量的值而不同。执行时，根据不同的变量值，SPM会花费很少的运算从中选择一条最合适的。SQL> select id,count(*) from test groupby id order by 2;          ID   COUNT(*)---------- ----------         10      1100         88      10100999  1000000--接下来定义一个变量a,分别赋值999和10，看它的执行计划是如何的SQL>alter system flush shared_pool;SQL>var a1 number;SQL>exec :a1:=999;SQL>select t.* from test t wheret.id=:a1; 1000000 rows selected. Elapsed: 00:00:25.30 SQL> select * fromtable(dbms_xplan.display_cursor(null,0)); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID     cpsdn05zdq02p,child number 0-------------------------------------select t.* from test t where t.id=:a1 Plan hash value: 1357081020 --------------------------------------------------------------------------| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------|   0| SELECT STATEMENT  |       |     |      |  424 (100)|      ||*  1|  TABLE ACCESS FULL| TEST |   337K| 1316K|   424   (2)| 00:00:06 | PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- Predicate Information (identified byoperation id):---------------------------------------------------    1- filter("T"."ID"=:A1) -##########################ID列上有个索引IDX_ID ################################SQL>alter system flush shared_pool;SQL>var a1 number;SQL>exec :a1:=10;SQL>select t.* from test t wheret.id=:a1;1100 rows selected. Elapsed: 00:00:00.04 SQL> select * fromtable(dbms_xplan.display_cursor(null,0)); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID     cpsdn05zdq02p,child number 0-------------------------------------select t.* from test t where t.id=:a1 Plan hash value: 1357081020 --------------------------------------------------------------------------| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------|   0| SELECT STATEMENT  |       |     |      |  424 (100)|      ||*  1|  TABLE ACCESS FULL| TEST |   337K| 1316K|   424   (2)| 00:00:06 | PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- Predicate Information (identified byoperation id):---------------------------------------------------    1- filter("T"."ID"=:A1) --这里可以看到，无论赋值是999还是10，其执行计划都是一样的，但根据理论来说，我们都知道，id=10时走索引效率是最好的。假设数据是均匀分布的那么基数评估cardinality=density*num_rows。Density可通过user_tab_col_statistics查询。select column_name,num_distinct,densityfrom user_tab_col_statistics where table_name='TEST'; COLUMN_NAME                      NUM_DISTINCT    DENSITY------------------------------    -------------   ---------ID                                 3                .333333333--我们看到的Rows列预估的337k就是cardinality=density*num_rows=0.3333*1011200约等于337k行，--但是我们都知道ID=10只有1100行，而ID=999有1000000行，所以当ID=10的时候走索引全扫描，ID=999--的时候走全表扫描是最合理的执行计划。那么面对这种情况，我们该如何让这种情况下的执行计划达--到最优呢？方法有如下几个：--1、去除绑定变量，直接硬解析的方式（非理想的，如果涉及要该程序代码这是很不可取的）--2、启用11g的新特性ACS（这个BUG不是一般的多，不建议启用）--3、收集直方图信息（如果在生产高峰期，收集直方图信息所占资源无法评估）--4、使用SPM把不同的执行计划加入到SQLPlan Baseline中。--使用手工捕获的方式alter system flush shared_pool;var a1 number;exec :a1:=999;select t.* from test t where t.id=:a1;select * fromtable(dbms_xplan.display_cursor(null,0));var temp varchar2(1000);exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'cpsdn05zdq02p');exec :temp :=dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_d230ce970caa0077',plan_name=>'SQL_PLAN_d4c6fkw6an03r97bbe3d0',attribute_name=>'ENABLED',attribute_value=>'NO');  --先修改全表扫描的sql planbaselines的enabled属性为NO，不然捕获不了索引的。exec :a1:=10;select t.* from test t where t.id=:a1;select * fromtable(dbms_xplan.display_cursor(null,0));exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'cpsdn05zdq02p');dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_d230ce970caa0077',plan_name=>'SQL_PLAN_d4c6fkw6an03r97bbe3d0',attribute_name=>'ENABLED',attribute_value=>'YES'); SQL> select sql_handle,plan_name,origin,enabled,accepted,fixed from dba_sql_plan_baselines; SQL_HANDLE        PLAN_NAME                        ORIGIN       ENA ACC FIX-------------------------------------------------- -------------- --- --- ---SQL_d230ce970caa0077SQL_PLAN_d4c6fkw6an03r97bbe3d0 MANUAL-LOAD   YES YES NOSQL_d230ce970caa0077SQL_PLAN_d4c6fkw6an03rf98b55bb MANUAL-LOAD   YES YES NO--验证结果：SQL> var a1 number;SQL> exec :a1:=10;SQL> select t.* from test t wheret.id=:a1;SQL> select * fromtable(dbms_xplan.display_cursor(null,0)); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID     cpsdn05zdq02p,child number 0-------------------------------------select t.* from test t where t.id=:a1 Plan hash value: 578627003 ---------------------------------------------------------------------------| Id | Operation     | Name   | Rows | Bytes | Cost (%CPU)| Time         |---------------------------------------------------------------------------|   0| SELECT STATEMENT |            |     |     |   5(100)|     ||*  1|  INDEX RANGE SCAN| IDX_ID |  1280 | 5120 |  5   (0)| 00:00:01 | PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------    1- access("T"."ID"=:A1) Note-----   -SQL plan baseline SQL_PLAN_d4c6fkw6an03rf98b55bbused for this statement 22 rows selected. SQL> var a1 number;SQL> exec :a1:=999;SQL> select t.* from test t wheret.id=:a1;SQL> select * fromtable(dbms_xplan.display_cursor(null,0)); PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID     cpsdn05zdq02p,child number 0-------------------------------------select t.* from test t where t.id=:a1 Plan hash value: 1357081020 --------------------------------------------------------------------------| Id | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------|   0| SELECT STATEMENT  |       |     |      |  424 (100)|      ||*  1|  TABLE ACCESS FULL| TEST |  1001K| 3912K|   424   (2)| 00:00:06 | PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified byoperation id):---------------------------------------------------    1- filter("T"."ID"=:A1) Note-----   -SQL plan baseline SQL_PLAN_d4c6fkw6an03r97bbe3d0used for this statement 22 rows selected.

SPM的灵活之处在于，可以动态管理，不像存储大纲（stored outline）和SQL Profile需要DBA手工创建，当然SPM也可以，因为我在以上演示中也没让它自动捕获。

思考：1、何种情况下使用什么固定执行计划的方法更加有效？2、在各种固定执行计划都使用的情况下，那种优先级更高？

转载于:https://blog.51cto.com/wyzwl/1954012

你可能感兴趣的文章