11G新特性SQL PLAN BASLINE 执行计划基线

什么是SQL执行计划管理?

 

SQL计划管理(SQL plan management)是一咱预防机制,记录和评估SQL语句的执行计划.SQL plan management的主要功能是sql plan baseline.

sql plan baseline某个SQL的执行计划的集合,这些执行计划经过验证,性能良好.

 

为什么要使用sql baseline?

引入sql baseline的目的是无论数据库是否发生变化,都能保证SQL的性能(解决数据库发生变化时的性能问题).数据库的变化如:

•New optimizer version 新版本的优化器

•Changes to optimizer statistics and optimizer parameters   优化器状态和优化器参数发生变化

•Changes to schema and metadata definitions    模式和元数据的变化 

•Changes to system settings    系统设置的变化

•SQL profile creation    SQL概要文件的变化

ORACLE数据库有SQL调优(SQL tuning ),但是对于突发的情况,SQL tuning 无能为力,只有在这个SQL执行过之后SQL tuning 才能进行优化,这时候就需要sql baseline.

 

应用SQL plan management的情况:

1.当数据库升级后,新版本的优化器可能导致性能问题.

2.数据的变化引起执行执行计划改变,导致性能下降.

3.在测试库上运行良好的SQL放到生产库可能会性能下降.这里把旧的执行计划加入sql plan baseline能稳定执行计划.

sql plan baseline是一个SQL执行计划的集合,能够选择出最优的执行计划,从而最小程度减少性能回归.

 

 

SQL Plan Baselines 的结构(Architecture of SQL Plan Baselines  )

SQL Plan Baselines里的执行计划包含以下内容:

1. Set of hints 一系列的hints
2. Plan hash value 执行计划的hash值
3. Plan-related information 执行计划相关的信息

执行计划历史(plan history)是一个sql的执行计划历史的集合,包括已经被sql plan baseline接受和不被接受的.数据库只将重复出现的sql的执行计划记录到plan history。

将执行计划加入sql plan baseline的过程称为执行计划演化(plan evolution).优化器生成第一个可以被sql plan baseline接受的plan，其余的plan将不加入sql plan baseline，必须通过演化才行。

sql plan hsitory和sql plan baseline的关系:

sql 管理库SQL management base (SMB)是数据字典的一部分,保存了SQL plan baselines 、plan history和sql profile ,存放在sysaux表空间.

数据库只将重复出现的sql的执行计划记录到plan history。数据库会把SQL语句的ID写到一个日志文件，当这个SQL再出现时，数据库就会把它当作是重复的SQL语句。对于每个重复的SQL语句，数据库都会将优化器生成的执行计划记录下来。

 

将已经存在的执行计划加入sql plan baseline

可以人为将plan加入到sql plan baeline中，数据库不会验证人为加入的plan的性能。

1)从sql tuning set和AWR快照中载入sql plan

从sql tuning set加载

1. DECLARE
2.   my_plans PLS_INTEGER;
3. BEGIN
4.   my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
5. END;
6. /

从AWR快照加载plan

先将AWR快照加入sql tuning set,再执行上面的过程

 

2)从sql area加载plan(Loading Plans from the Shared SQL Area)

指定sql id加入sql plan baesline:

1. DECLARE
2.   my_plans PLS_INTEGER;
3. BEGIN
4.   my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');
5. END;
6. /

通过以下方式可以确定一个sql plan:

1. SQL identifier (SQL_ID)  
2. SQL text (SQL_TEXT)
3. One of the following attributes:
   1. PARSING_SCHEMA_NAME
   2. MODULE
   3. ACTION

选择SQL PLAN BASELINE(Selecting SQL Plan Baselines)

当数据库编译一个SQL时,优化器执行以下工作:

1. Uses a cost-based search method to build a best-cost plan  使用基于成本(cost-based)的寻找方法建立plan
2. Tries to find a matching plan in the SQL plan baseline   尝试在sql plan baseline中查找匹配的plan
3. Does either of the following depending on whether a match is found:
   • If found, then the optimizer proceeds using the matched plan  如果找到合适的plan,则使用这个plan
   • If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost      如果没找到匹配的plan,则会评估sql plan baseline中的各个sql plan,使用其中代价最小的plan

如果best-cost的plan跟plan history中的任何计划都不匹配,会生成一个新的plan,并加入plan history中,但是并不加入sql plan baseline中,也不会使用这个plan,而是使用上面步骤3中代价最小的plan.如果这时数据库发生改变,如删除了一个索引,导致所有sql plan baseline的执行计划失效,则会使用这个新计划,并加入sql plan baseline.

启用sql plan baseline:设置参数optimizer_use_sql_plan_baselines为true

 

演化sql plan (Evolving SQL Plan Baselines)

两种方式:手工演化和sql tuning advisor

手工演化:将plan从sql tuning set、AWR快照、sql area加载到sql plan baseline（上面已经介绍）或者用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE：

1. SET SERVEROUTPUT ON
2. SET LONG 10000
3. DECLARE
4. report clob;
5. BEGIN
6. report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
7. sql_handle => 'SYS_SQL_593bc74fca8e6738');
8. DBMS_OUTPUT.PUT_LINE(report);
9. END;
10. /

输出：

1. REPORT
2.  
3. --------------------------------------------------------------------------------
4.  
5.  
6.  
7. --------------------------------------------------------------------------------
8.  
9. Evolve SQL Plan Baseline Report
10.  
11. --------------------------------------------------------------------------------
12.  
13.  
14.  
15. Inputs:
16.  
17. -------
18.  
19. SQL_HANDLE = SYS_SQL_593bc74fca8e6738
20.  
21. PLAN_NAME =
22.  
23. TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
24.  
25. VERIFY = YES
26.  
27. COMMIT = YES
28.  
29.  
30.  
31. Plan: SYS_SQL_PLAN_ca8e6738a57b5fc2
32.  
33. -----------------------------------
34.  
35. Plan was verified: Time used .07 seconds.
36.  
37. Passed performance criterion: Compound improvement ratio >= 7.32.
38.  
39. Plan was changed to an accepted plan.
40.  
41.  
42.  
43. Baseline Plan Test Plan Improv. Ratio
44.  
45. ------------- --------- -------------
46.  
47. Execution Status: COMPLETE COMPLETE
48.  
49. Rows Processed: 40 40
50.  
51. Elapsed Time(ms): 23 8 2.88
52.  
53. CPU Time(ms): 23 8 2.88
54.  
55. Buffer Gets: 450 61 7.38
56.  
57. Disk Reads: 0 0
58.  
59. Direct Writes: 0 0
60.  
61. Fetches: 0 0
62.  
63. Executions: 1 1
64.  
65.  
66.  
67. -------------------------------------------------------------------------------
68.  
69. Report Summary
70.  
71. -------------------------------------------------------------------------------
72.  
73. Number of SQL plan baselines verified: 1.
74.  
75. Number of SQL plan baselines evolved: 1.
76.  
77. Hide Navigation
78. Search
79.  
80. Advanced Search
81. Reference
82. Home · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Acronyms · Initialization Parameters · Error Messages
83.  
84. Categories
85. Installation
86. Getting Started
87. Administration
88. Application Development
89. Grid Computing
90. High Availability
91. Data Warehousing
92. Content Management and Unstructured Data
93. Information Integration
94. Security
95. Videos
96. New and changed documents

用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE可：

A 指定一个plan 进行演化

B 选择一个plan列表进行演化

C 不指定任何值,则演化SMB中的任何执行计划

 

用sql tuning advisor进行演化:

在用sql tuning advsor进行sql调试时,会生成一个sql profile文件.如果这个sql profile的性能比sql plan baseline里的好时,sql tuning advisor会建议将这个plan加入sql plan baseline中.

在11G中,自动维护窗口(maintenance window)有一个自动维护任务,这个任务是目的是从AWR中找到负载最高的SQL,然后找到该SQL已经被sql tuning advisor验证过性能的plan加入到sql plan baseline.

 

 

固定执行计划基线(Using Fixed SQL Plan Baselines)

当一个基线里有一个plan被设置为fixed时,这个基线是固定的.一个基线里可以有固定和非固定的计划,但是优化器只会使用固定的计划.对于某些情况比较有用,如从10G升级到11G后,性能下降,即使使用了基线也不行,这时将10G的执行计划导入到基线里,并设置为固定的,可以防止使用错误的执行计划,类似于10G的outline.当fixed的plan变的不可用时(如删除了索引),才会考虑非fixed的plan

优化器不会向fixed的基线中加入新的plan(优化器仅仅会在sql第一次运行时向基线加入一个plan).

 

 

展示基线(Displaying SQL Plan Baselines)

用sql handle展示一个或多个基线计划

1. SELECT * FROM TABLE(
2.  
3. DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
4.  
5. sql_handle=>'SYS_SQL_209d10fabbedc741',
6.  
7. format=>'basic'));
8.  
9. Hide Navigation
10. Search
11.  
12. Advanced Search
13. Reference
14. Home · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Acronyms · Initialization Parameters · Error Messages
15.  
16. Categories
17. Installation
18. Getting Started
19. Administration
20. Application Development
21. Grid Computing
22. High Availability
23. Data Warehousing
24. Content Management and Unstructured Data
25. Information Integration
26. Security
27. Videos
28. New and changed documents

或者用name参数只展示一个计划

1. SQL handle: SYS_SQL_209d10fabbedc741
2. SQL text: select cust_last_name, amount_sold from customers c,
3. sales s where c.cust_id=s.cust_id and cust_year_of_birth=:yob
4. ----------------------------------------------------------------------------------
5. ----------------------------------------------------------------------------------
6. Plan name: SYS_SQL_PLAN_bbedc741a57b5fc2
7. Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
8. ----------------------------------------------------------------------------------
9. Plan hash value: 2776326082
10.  
11. ----------------------------------------------------------------------------------
12. | Id | Operation | Name |
13. ----------------------------------------------------------------------------------
14. | 0 | SELECT STATEMENT | |
15. | 1 | HASH JOIN | |
16. | 2 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS |
17. | 3 | BITMAP CONVERSION TO ROWIDS | |
18. | 4 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |
19. | 5 | PARTITION RANGE ALL | |
20. | 6 | TABLE ACCESS FULL | SALES |
21. ----------------------------------------------------------------------------------
22.  
23. ----------------------------------------------------------------------------------
24. Plan name: SYS_SQL_PLAN_bbedc741f554c408
25. Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
26. ----------------------------------------------------------------------------------
27. Plan hash value: 4115973128
28.  
29. ----------------------------------------------------------------------------------
30. | Id | Operation | Name |
31. ----------------------------------------------------------------------------------
32. | 0 | SELECT STATEMENT | |
33. | 1 | NESTED LOOPS | |
34. | 2 | NESTED LOOPS | |
35. | 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS |
36. | 4 | BITMAP CONVERSION TO ROWIDS | |
37. | 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX |
38. | 6 | PARTITION RANGE | |
39. | 7 | BITMAP CONVERSION TO ROWIDS | |
40. | 8 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX |
41. | 9 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES |
42. ----------------------------------------------------------------------------------

DBA_SQL_PLAN_BASELINES可以查看基线的信息

1. SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
2. FROM DBA_SQL_PLAN_BASELINES;
3.  
4. SQL_HANDLE PLAN_NAME ENA ACC FIX
5. ------------------------------------------------------------------------
6. SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741a57b5fc2 YES NO NO
7. SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741f554c408 YES YES NO

 

 

SQL管理库SQL Management Base

SMB是数据字典的一部分,放在SYSAUX表空间,保存了sql语句的日志、执行计划历史、基线和sql profile。SMB用自动空间管理（automatic space management）来删除不再使用的计划和日志。SMB在SYSAUX表空间上默认使用的空间不超过10%，最大可以调到50%.当超过可以使用的空间时，会在警告日志中写一条警告，直到表空间增大、SBM在SYSAUX上的配额增大、或者SBM减少到正常水平后才消失。设置SBM大小：

1. BEGIN
2. DBMS_SPM.CONFIGURE('space_budget_percent',30);
3. END;
4. /

自动删除执行计划：

当一个计划超过53周没有使用时，自动维护窗口会删除这个计划。这个期限可以是5－523周。设置期限：

1. BEGIN
2. DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
3. END;
4. /

 

导出导入基线：

首先用下面的创建一个中转表：

1. BEGIN
2. DBMS_SPM.CREATE_STGTAB_BASELINE(
3. table_name => 'stage1');
4. END;
5. /

把基线打包放到表里：

1. DECLARE
2. my_plans number;
3. BEGIN
4. my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
5. table_name => 'stage1',
6. enabled => 'yes',
7. creator => 'dba1');
8. END;
9. /

然后用EXPDP和IMPDP工具迁移到目标数据库上。

然后解压包：

1. DECLARE
2. my_plans number;
3. BEGIN
4. my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
5. table_name => 'stage1',
6. fixed => 'yes');
7. END;
8. /

这样就可以了

 

 

迁移OUTLINE

outline是10G里用于稳定执行计划用的。

首先了解outline的一些缺点：

1.OUTLINE不会随着时间自动演化，当环境变化时性能可能变差。

2.OUTLINE里的hints可能会失效如索引删除等。此种情况下，数据库依然会使用这个错误的hints导致性能低下。

3.优化器仅仅会使用某一类别的OUTLINE，即使有更好的也不使用。

基线的优点：

1.随着环境变化演化执行计划，保证执行计划是可靠的。、

2.不会使用错误的hints

3.可以使用多个执行计划而非不像OUTLINE只能使用一个。

迁移OUTLINE到baseline的步骤：

                                       、

1.用户执行函数选定要迁移的outline

2.数据库从outline复制信息到baseline

3.数据库重新为获取hints的信息

4.在sql第一次运行时重新获取其它丢失的信息

 

 

未完。。。

参考:http://docs.oracle.com/cd/E11882_01/server.112/e41573/optplanmgmt.htm#PFGRF95105