执行计划绑定

http://www.mamicode.com/info-detail-1943333.html


需要绑定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)
语法:(手动创建outline)

CREATE [ OR REPLACE ]
   [ PUBLIC | PRIVATE ] OUTLINE [ outline ]
   [ FROM [ PUBLIC | PRIVATE ] source_outline ]
   [ FOR CATEGORY category ]
   [ ON statement ] ;

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

2、使用大纲(outline)固定执行计划
--环境构建,建立测试表
SQL> create table zw as select * from dba_objects where object_id is not null;

Table created.

SQL> explain plan for select count(*) from zw;

Explained.

SQL> set lines 200
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 249608387

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   339   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ZW   |   100K|   339   (1)| 00:00:05 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

13 rows selected.

--查询数据字典dba_outlines:
SQL> select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;

no rows selected



--创建大纲(全表扫描),默认是私有outline

SQL> create or replace outlinezwoutlinefor category mycate onselect count(*) from zw;

Outline created.

--再次查询dba_outlines;
 col NAME for a10
 col OWNER for a10
 col CATEGORY for a10
 col SQL_TEXT for a30
 select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;

NAME       OWNER      CATEGORY   USED   SQL_TEXT                       ENABLED  TIMESTAMP
---------- ---------- ---------- ------ ------------------------------ -------- -------------------
ZWOUTLINE SYS       MYCATE    UNUSEDselect count(*) from zw        ENABLED 2017-09-02 15:36:33



--创建object_id列索引,将该列属性设置为非空
--索引不存储null值  
SQL> alter table zw modify object_id not null;         

Table altered.

SQL> create index idx_zw_obj_id on zw(object_id);

Index created.

SQL> analyze table zw compute statistics;

Table analyzed.


SQL> explain plan for select count(*) from zw;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1836624960

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    54   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID |   100K|    54   (0)| 00:00:01 |
-------------------------------------------------------------------------------


--使用大纲固定执行计划:(alter system/session set use_stored_outlines=mycate;)系统级或会话级别;

SQL> alter systemset use_stored_outlines=mycate;

System altered.


--查询dba_ouitlines(sql还未应用)

SQL>  select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;

NAME       OWNER      CATEGORY   USED   SQL_TEXT                       ENABLED  TIMESTAMP
---------- ---------- ---------- ------ ------------------------------ -------- -------------------
ZWOUTLINE  SYS        MYCATE     UNUSED select count(*) from zw        ENABLED  2017-09-02 15:36:33


--固定执行计划之后,就会按照创大纲时的执行计划去执行。
实际执行验证:(实际执行后就会应用outline)
SQL> select count(*) from zw;

  COUNT(*)
----------
     87036

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

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      |               |       |    54 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID | 87036 |    54   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / ZW@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "ZW"@"SEL$1" ("ZW"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]


39 rows selected.

--查询dba_outlines
select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;

NAME       OWNER      CATEGORY   USED   SQL_TEXT                       ENABLED  TIMESTAMP
---------- ---------- ---------- ------ ------------------------------ -------- -------------------
ZWOUTLINE  SYS        MYCATE     UNUSED select count(*) from zw        ENABLED  2017-09-02 15:54:31



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

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

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

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

关于outline具体参考如下链接:
http://blog.csdn.net/whiteoldbig/article/details/17210079

从10g起,可以通过引用共享池中已经存在的SQL语句来创建outline
exec DBMS_OUTLN.create_outline(hash_value=>1752921103,child_number => 0,category=>‘test‘);
注意
  • 这种方法不能指定outline的具体名字,由系统自动生成,可以通过alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2修改
  • 不指定类别的话默认为default,而且此处创建时,不能指定为default类别(会报错)。
  • 我们使用outline固定执行计划时,一般都是选用此种方法

参考:
http://www.bubuko.com/infodetail-216529.html

实验如下:
SQL>  create table dh_stat as select rownum id ,object_name name ,object_type type from dba_objects;

Table created.

SQL> create index ind_1 on dh_stat(id) compute statistics;                                          

Index created.

SQL> alter system flush shared_pool;

System altered.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'sys',TABNAME=>'dh_stat',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);

PL/SQL procedure successfully completed.

SQL> col name format a15
SQL> col name format a30
SQL> col sql_text for a55
SQL>  select /* outlinetest1 */ * from dh_stat where id=771;

        ID NAME                           TYPE
---------- ------------------------------ -------------------
       771 SYS_IL0000000772C00002$$       INDEX
       
SQL> set lines 200
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%outlinetest1%' and sql_text not like '%v$sql%';

SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
 select /* outlinetest1 */ * from dh_stat where id=771  9a69w18a8uuhk  344812050            0
 
SQL>  select * from table(dbms_xplan.display_cursor('9a69w18a8uuhk',null));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  9a69w18a8uuhk, child number 0
-------------------------------------
 select /* outlinetest1 */ * from dh_stat where id=771

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    39 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("ID"=771)


19 rows selected.

---根据sql已经有的执行计划生成outline:

SQL> exec DBMS_OUTLN.create_outline(hash_value=>344812050,child_number => 0,category=>'TEST');        

PL/SQL procedure successfully completed.

--查询outline情况:
SQL> select name,category,used,sql_text from dba_outlines;

NAME                           CATEGORY                       USED   SQL_TEXT
------------------------------ ------------------------------ ------ -------------------------------------------------------
SYS_OUTLINE_17090216454529101  TEST                           UNUSED  select /* outlinetest1 */ * from dh_stat where id=771

此处outline的USED状态没有改变,因为我们没有激活TEST类别的outline,再次申明,outline必须通过use_stored_outlines参数激活后,优化器才会使用outline

---下面这一步激活TEST类别的OUTLINE,也可以在系统级激活OUTLINE
SQL> alter session set use_stored_outlines=TEST;

验证省略。。。。。。。。。。。

创新互联专注于中大型企业的网站设计制作、网站设计和网站改版、网站营销服务,追求商业策划与数据分析、创意艺术与技术开发的融合,累计客户上千,服务满意度达97%。帮助广大客户顺利对接上互联网浪潮,准确优选出符合自己需要的互联网运用,我们将一直专注高端网站设计和互联网程序开发,在前进的路上,与客户一起成长!


二、SQL_PROFILE

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


语法:
SQL text format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  user_name        IN VARCHAR2  := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;  

SQL ID format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

Workload Repository format:

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  begin_snap       IN NUMBER,
  end_snap         IN NUMBER,
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;

实验1:sql_id format
1. sys用户创建oracle优化任务(v_sqlid,task_name根据实际情况改变)
 set long 10000000 longchunksize 1000000 linesize 150 pagesize 0 serveroutput on size 1000000   verify off
 declare
  my_task_name varchar2(30);
  v_sqlid      varchar2(50);
 begin
  v_sqlid:='11wrxmug9y4a7';
  my_task_name := dbms_sqltune.create_tuning_task
                  (sql_id=> v_sqlid,
                   scope => 'comprehensive',
                   time_limit=>160,
                   task_name=>'task_00000',
                   description => 'tuning task');
  dbms_sqltune.execute_tuning_task('task_00000');              
  end;
 /
 
注:dbms_sqltune.execute_tuning_task('task_00000'),是执行优化任务
 
2. 打印优化任务,里面有一些具体的改进措施
 select dbms_sqltune.report_tuning_task('task_00000') from dual;
 
3.根据优化建议,绑定profile,理论上可提高语句执行效率 
execute dbms_sqltune.accept_sql_profile(task_name => 'task_00000',task_owner => 'sys', replace => true); 

实验2:sql_text format
SQL> alter session set statistics_level=all;

Session altered.

SQL> set serveroutput off
SQL> select * from scott.emp where ename='SCOTT' and DEPTNO=20;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4dsqbp572auuu, child number 0
-------------------------------------
select * from scott.emp where ename='SCOTT' and DEPTNO=20

Plan hash value: 3956160932

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       7 |

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

   1 - filter(("ENAME"='SCOTT' AND "DEPTNO"=20))


18 rows selected.

SQL> DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := 'select * from scott.emp where ename= :name and DEPTNO= :deptno';
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
bind_list   => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
         user_name   => 'SYS',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'test_sql_tuning',
         description => 'Task to tune a query on emp');
END;
/

PL/SQL procedure successfully completed.

参数说明:
bind_list:多个绑定变量以','逗号分隔。参数值一定要根据绑定变量对应的列的类型书写.如:emp.ename类型是VARCHAR2(10),那么就要写成 bind_list =>sql_binds(anydata.convertvarchar2(10)),
time_limit:执行的最长时间,默认是60。
scope:LIMITED,用大概1秒时间去优化SQL语句,但是并不进行SQL Profiling分析;COMPREHENSIVE,进行全面分析,包含SQL Profiling分析;比LIMITED用时更长。

--查看任务名 
SQL> select task_name from dba_advisor_log where task_name='test_sql_tuning';

TASK_NAME
------------------------------
test_sql_tuning

--执行sql tuning任务
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' );

PL/SQL procedure successfully completed.

--查看sql tunning任务状态
SQL> select task_name,status from dba_advisor_log where task_name='test_sql_tuning';

TASK_NAME                      STATUS
------------------------------ -----------
test_sql_tuning                COMPLETED

----展示sql tunning结果
set long 10000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;

...........省略

--根据建议accept_sql_profile
execute dbms_sqltune.accept_sql_profile(.....)

--完成后删除sql tunning任务
exec dbms_sqltune.drop_tuning_task('test_sql_tuning');

--查看SQL Tuning Advisor的进展(task执行很久)
set lines 200
col opname for a20
col ADVISOR_NAME for a20
select sid,serial#,username,opname,advisor_name,target_desc,start_time sofar,totalwork from v$advisor_progress where username = 'SYS'; 

三、coe_xfr_sql_profile.sql固定执行计划(也是生成sql_profile)


--环境构建,建立测试表,与outline测试一样

 create table zw as select * from dba_objects where object_id is not null;
 alter table zw modify object_id not null;          --索引不存储null值
 create index idx_zw_obj_id on zw(object_id);
 analyze table zw compute statistics;
 
select count(*) from zw;                      

  COUNT(*)
----------
    87038
 
SQL> select * from table(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      |               |       |    54 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID | 87038 |    54   (0)| 00:00:01 |
-------------------------------------------------------------------------------

--使用hint提示,强制走全表,生成一个执行计划:

SQL> select /*+ full(zw) */ count(*)from zw;
 
 COUNT(*)
----------
    87038
 
SQL> select * from table(dbms_xplan.display_cursor(null,0));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID  fp5ksbhww4594, child number 0
-------------------------------------
select /*+ full(zw) */ count(*)from zw

Plan hash value: 249608387

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |   339 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ZW   | 87038 |   339   (1)| 00:00:05 |
-------------------------------------------------------------------


14 rows selected.


--运行coe_xfr_sql_profile脚本:(固定执行计划)

SQL> @coe_xfr_sql_profile.sql
 
Parameter 1:
SQL_ID (required)
 
Enter value for 1: 1f5n0rapts695                   --原sql的sql_id
 
 
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
    1836624960          .02
 
Parameter 2:
PLAN_HASH_VALUE (required)
 
Enter value for 2: 249608387                      --想要选择的执行计划的PLAN_HASH_VALUE值
 
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "1f5n0rapts695"
PLAN_HASH_VALUE: "249608387"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_1f5n0rapts695_249608387.sql
on TARGET system in order to create a custom SQL Profile
with plan 249608387 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

SQL> @coe_xfr_sql_profile_1f5n0rapts695_249608387.sql

省略.................
coe_xfr_sql_profile_1f5n0rapts695_249608387 completed

SQL> explain plan for select count(*) from zw;
 
Explained.
 
SQL> 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   | 87038 |  312   (1)| 00:00:04 |
-------------------------------------------------------------------
 
Note
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
   -SQL profile "coe_1f5n0rapts695_249608387"used for this statement

--查询:
SQL> select name,category,status,sql_text fromdba_sql_profiles;
 
NAME                                CATEGORY     STATUS  SQL_TEXT
------------------------------      ------------      -------------------------------------------
coe_1f5n0rapts695_249608387    DEFAULE   ENABLED  select count(*) from zw

当存在只有一个执行计划时,而又不想让应用不改sql代码的情况下改变执行计划,请参照自动/手动类型sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)


使用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 PLAN MANAGE(baseline)

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 group by 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 * from table(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,density from 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.


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

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







名称栏目:执行计划绑定
本文路径:http://ybzwz.com/article/jsejii.html