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

【魄爺】

--一条带有绑定变量的SQL语句，但数据分布不均，严重倾斜时，最好的执行计划会根据绑定变量的值而不同。执行时，根据不同的变量值，SPM会花费很少的运算从中选择一条最合适的。　　
SQL> select id,count(*) from test groupby id order by 2;
　　

　　
         ID   COUNT(*)
　　
---------- ----------
　　
         10      1100
　　
         88      10100
　　
999  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 NO
　　
SQL_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.