了解Maclean Liu|向Maclean Liu提问 Oracle ALLSTARS 全明星(群内有多位Oracle高级售后support，N位OCM和ACE) QQ群 # QQ群号:23549328 # 已经升级到 2000人群，空位多多。欢迎有一定基础的Oracle骨友加入，现在入群需要经过Maclean的技术面试，欢迎面试，请加QQ号:47079569 为好友参加面试 2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试

11g新特性SQL执行计划管理(SQL Plan Management) (1)

数据库系统性能受到查询执行的严重影响。然而SQL语句的执行计划可能因统计信息变化，优化参数变化或方案定义变化等原因而意外改变，Oracle Optimizer优化器往往无法在没有人工干预的情况下准确进化执行计划。在无法保证新的执行计划总是趋于变得更好的情况下，用户倾向于通过存储大纲(stored outline)或锁定统计信息来保证执行计划的问题。然而使用这些方式将不可避免地丧失利用到新的优化器特性以改善SQL语句性能的优势。在保证当前可被接受执行计划的前提下，仅允许采用那些更好的，获益更多的执行计划才是终极方案。 Oracle Database 11g是在解决这一SQL执行计划上处于市场领先地位。SQL Plan Management(SPM)提供了一个完全透明且可控的执行计划进化的框架。在SPM的帮助下优化器自动管理执行计划并保证只有已知或已确认的执行计划才被采用。当一个新的计划出现时，Oracle将不会采用它，直到确认其与当前的执行计划有着相当的，或更好的性能。 SQL Plan Management(SPM)保证数据库运行时性能绝不因为执行计划的改变而大幅下降。为了确保这一点，仅仅那些已被接受的(accepted or trusted)的执行计划将被采用；任何计划的进化都将被追踪并仅在其被评价为无损于性能或有益于性能后被采纳。 SPM主要由三个部分组成： 1.执行计划基线捕捉 创建SQL执行计划基线意味着接受(或者说信任)相关SQL语句的执行计划。SQL计划基线存储在历史计划中，历史计划保存在SQL Management BASE(SMB)中，SMB位于SYSAUX表空间上。 
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select occupant_name,space_usage_kbytes  from v$sysaux_occupants where occupant_name like '%SQL%';

OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
SQL_MANAGEMENT_BASE                                                            3776
2.SQL计划基线选择 保证仅采用SQL计划基线中已被信任的执行计划，并追踪计划历史中所有新的执行计划。计划历史中包括了受信任的和不受信任的执行计划。不受信任的执行计划可能是未被检验的(unverified)或被拒绝的(rejected)。 3.SQL计划基线进化 在历史计划中为给定SQL语句评估所有未被检验的执行计划，这些执行计划有的被评估为可接受的(可信任的)，有的则被驳回(rejected)。 图示0  SQL Management Base,由语句记录和历史计划等组成。 
SQL> set autotrace traceonly exp;
SQL> select * from DBA_SQLSET;

Execution Plan
----------------------------------------------------------
Plan hash value: 3125220660

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                         |     2 |   168 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| WRI$_SQLSET_DEFINITIONS |     2 |   168 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

/* sqlset信息来源于基表WRI$基表，即Workload Repository Internal */

SQL> select * from dba_sql_plan_baselines;

Execution Plan
----------------------------------------------------------
Plan hash value: 3413624941

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     1 |  2723 |     1   (0)| 00:00:01 |
|   1 |  FIXED TABLE FULL              | X$MODACT_LENGTH       |     1 |    13 |     0   (0)| 00:00:01 |
|   2 |  FIXED TABLE FULL              | X$MODACT_LENGTH       |     1 |    13 |     0   (0)| 00:00:01 |
|   3 |  NESTED LOOPS                  |                       |       |       |            |          |
|   4 |   NESTED LOOPS                 |                       |     1 |  2723 |     1   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                |                       |     1 |   691 |     1   (0)| 00:00:01 |
|*  6 |     INDEX SKIP SCAN            | SQLOBJ$_PKEY          |     1 |    82 |     1   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| SQLOBJ$AUXDATA        |     1 |   609 |     0   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN          | I_SQLOBJ$AUXDATA_PKEY |     1 |       |     0   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN           | I_SQL$TEXT_PKEY       |     1 |       |     0   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID  | SQL$TEXT              |     1 |  2032 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   6 - access("SO"."OBJ_TYPE"=2)
       filter("SO"."OBJ_TYPE"=2)
   8 - access("SO"."SIGNATURE"="AD"."SIGNATURE" AND "AD"."OBJ_TYPE"=2 AND
              "SO"."PLAN_ID"="AD"."PLAN_ID")
       filter("AD"."OBJ_TYPE"=2 AND "SO"."PLAN_ID"="AD"."PLAN_ID")
   9 - access("AD"."SIGNATURE"="ST"."SIGNATURE")
       filter("SO"."SIGNATURE"="ST"."SIGNATURE")

Note
-----
   - dynamic sampling used for this statement (level=3)
   - SQL plan baseline "SQL_PLAN_9s3btm2sx51n074830d3a" used for this statement

/* 这里启用了一个基线中的计划 */
/* 可以看到SQL计划基线的数据SQL$TEXT等基表中，不同于WRI */

SQL> desc sql$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SIGNATURE                                 NOT NULL NUMBER
 INUSE_FEATURES                            NOT NULL NUMBER
 FLAGS                                     NOT NULL NUMBER
 SPARE1                                             NUMBER
 SPARE2                                             CLOB

SQL> desc sqllog$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SIGNATURE                                 NOT NULL NUMBER
 BATCH#                                    NOT NULL NUMBER

SQL计划基线捕获

为了支持SPM和新建立的执行计划的性能验证，我们必须首次以基于成本的执行计划填充SQL Management Base，这些执行计划将成为首批的计划基线。我们有2种方式来填充SQL Management Base:
  • 自动SQL计划捕捉
  • 成批导入执行计划

自动SQL计划基线捕捉

自动计划捕捉可以通过初始化参数optimizer_use_sql_plan_baselines来开关；当打开了自动计划捕捉时，Oracle会自动将任意重复的SQL语句加载到SPM仓库中；为了识别重复SQL语句，优化器将在语句首次编译时记录每一条SQL语句的id(SQL Signature)到语句日志中 (前面指出的SQL$,SQL$TEXT等基表中)。若这些SQL语句再次被处理(被执行或被编译)时发现其已经存在于语句日志中，则该语句被标示为重复语句(repeatable statement)。相应的Oracle将为该语句创建SQL历史计划，历史计划中包含了足以让优化器再生原执行计划的信息，如SQL text,大纲，绑定变量，编译环境信息等。当前的基于成本的执行计划将被加入到首批的SQL计划基线中，并且这些计划被标记为可接受的(可信任的)。仅有那些可接受的计划会被采用；若未来该SQL语句有了一个新的执行计划，则该执行计划被加入到历史计划中并被标示为已确认的。一个新的SQL计划仅在与SQL计划基线性能进行比较，检验它的性能后，才被认为是可接受的计划。 
SQL> conn maclean/maclean
Connected.

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.2
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL> alter session set events '10046 trace name context forever,level 1';
Session altered.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.

SQL>  select count(object_id) from youyus where object_id between 10 and 1000;
COUNT(OBJECT_ID)
----------------
               0

SQL> select count(object_id) from youyus where object_id between 10 and 1000;
COUNT(OBJECT_ID)
----------------
               0

SQL>  alter session set events '10046 trace name context off';
Session altered.

SQL> set autotrace on;
SQL> select count(object_id) from youyus where object_id between 10 and 1000;

COUNT(OBJECT_ID)
----------------
               0
........
Note
-----
   - SQL plan baseline "SQL_PLAN_4km5h6vp5rprm4267f89c" used for this statement

/* trace文件显示创建历史计划和SQL计划基线包括以下递归操作 */

SELECT signature FROM sqllog$ WHERE signature = :1
INSERT INTO sqllog$ SELECT :1, 1+TRUNC(sqllog$_seq.nextval/8192) FROM dual
MERGE INTO sqlobj$    ......
INSERT INTO sql$ (signature, inuse_features, flags, spare1, spare2)           VALUES (:1, :2, :3, null, null)
INSERT INTO sql$text (signature, sql_handle, sql_text)                        VALUES (:1, :2, :3)

SQL> exec dbms_stats.gather_table_stats(ownname => 'MACLEAN',tabname => 'YOUYUS',cascade =>true,method_opt => 'for all columns size 254');
PL/SQL procedure successfully completed.

SQL> select * from YOUYUS where object_id between 10 and 1000;
no rows selected
..............................

SQL> alter system flush shared_pool;
System altered.

SQL> alter session set events '10053 trace name context forever,level 1';

SQL> select * from YOUYUS where object_id between 10 and 1000;
no rows selected

Note
-----
   - SQL plan baseline "SQL_PLAN_95dr7p7y4ucm3bd23881d" used for this statement

alter session set events '10053 trace name context off';

/* 10053事件的trace内容如下 */

SPM: planId's of plan baseline are: 3173222429
SPM: using qksan to reproduce, cost and select accepted plan, sig = 10571891892567224931
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3173222429
Registered qb: SEL$1 0x90a81b18 (PARSER)
...........

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX_RS_ASC ("YOUYUS" "OBJD_YOUYUS") */ "YOUYUS"."OWNER" "OWNER","YOUYUS"."OBJECT_NAME" "OBJECT_NAME","YOUYUS"."SUBOBJECT_NAME" "SUBOBJECT_NAME","YOUYUS"."OBJECT_ID" "OBJECT_ID","YOUYUS
"."DATA_OBJECT_ID" "DATA_OBJECT_ID","YOUYUS"."OBJECT_TYPE" "OBJECT_TYPE","YOUYUS"."CREATED" "CREATED","YOUYUS"."LAST_DDL_TIME" "LAST_DDL_TIME","YOUYUS"."TIMESTAMP" "TIMESTAMP","YOUYUS"."STATUS" "ST
ATUS","YOUYUS"."TEMPORARY" "TEMPORARY","YOUYUS"."GENERATED" "GENERATED","YOUYUS"."SECONDARY" "SECONDARY","YOUYUS"."NAMESPACE" "NAMESPACE","YOUYUS"."EDITION_NAME" "EDITION_NAME" FROM "MACLEAN"."YOUY
US" "YOUYUS" WHERE "YOUYUS"."OBJECT_ID">=10 AND "YOUYUS"."OBJECT_ID"<=1000 AND 10<=1000
kkoqbc: optimizing query block SEL$1 (#0)
..................
/* 可以看到使用SQL计划基线后，通过plan outline + session OFE信息，优化器reproduce了执行计划 */
Figure 1描绘了使用SQL计划基线情况下的SQL编译流程

批量导入

批量导入在数据库升级到11g或部署新的应用时显得特别有用。批量导入(bulk load)可以替代自动捕捉或与自动捕捉协同工作。批量导入的执行计划将被自动接收(automatically accepted)以创建新的SQL计划基线或加入到已有的中。SQL Management Base可以以三种方式批量导入:
  1. 由给定SQL调优集合(SQL Tuning SET STS)生成执行计划
  2. 使用游标缓存中的执行计划
  3. 从staging表中将SQL计划基线解压出来
来源于STS 我们可以从SQL Tuning Set(STS)中捕获那些高负载SQL语句的执行计划，接着将这些计划导入到SMB中作为SQL计划基线，这可以通过 PL/SQL 存储过程DBMS_SPM.LOAD_PLANS_FROM_SQLSET或EM界面来完成。下一次这些语句被执行时SQL计划基线将被采用。 批量从STS导入执行计划是一种完美的可以保证数据库升级后计划不变的方式。该方式一般存在4个步骤:
  1. 在10gr2上创建包含执行计划的STS
  2. 将STS导入到某张中转表中，并将表上的数据导出到平面文件上
  3. 将平面文件中的STS数据导入到11g中,并还原为STS
  4. 使用EM或DBMS_SPM.LOAD_PLANS_FROM_SQLSET存储过程将执行计划导入到SQL Management Base中
一旦SQL计划基线正式创建将被采用，保证在10gR2与11g间执行计划不发生变化。若11g中优化器实际生成了不同的执行计划，那么这些计划将被加入到计划历史中且被标记为已验证的。仅在确认其性能优于当前SQL计划基线(来源于10gR2中的计划)的情况下才被标记为可接受的(accpeted)。 从游标缓存中 我们可以直接将游标缓存(cursor cache)中的语句导入到SQL Management Base中。通过基于模块名(module name),方案(schema)，或SQL_ID属性的过滤，我们可以分离出那些希望捕捉的SQL语句。这需要用到PL/SQL 存储过程DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE或者OEM界面。这些语句下次运行时就将采用SQL计划基线。 从中转表中解压出SQL计划基线 新的应用模块部署意味着引入新的SQL语句到数据库中。在Oracle Database 11g中，任何第三方的软件供应商可以将他们认为合适的SQL计划基线导入到数据库中。这保证了所有的 SQL语句都将包含到SQL计划基线中，并且在首次运行时将可以获取如在测试环境下一样的良好性能。当应用程序在开发或测试阶段，我们就可以通过以下步骤将正确的执行计划从测试系统导入到生产系统：
  1. 在源系统中使用DBMS_SPM.CREATE_STGTAB_BASELINE创建中转表(stageing table)
  2. 使用DBMS_SPM.PACK_STGTAB_BASELINE函数，将SQL计划基线从SMB中导入到中转表上
  3. 导出中转表数据到平面文件，这一步可以选择使用exp或expdp
  4. 将平面文件的数据导入到目标系统中，可采用imp或impdp
  5. 使用DBMS_SPM.UNPACK_STGTAB_BASELINE函数，将SQL计划基线从中转表还原到目标系统的SQL Management Base中
SQL计划基线选择 每次SQL语句被编译时，优化器都会首先使用传统的基于成本的搜索方式以便构造最佳执行计划。若初始化参数OPTIMIZER_USE_PLAN_BASELINES被设置为 TRUE(即默认值)，那么在该基于成本的"最优"执行计划被采用前，优化器将尝试在该SQL语句的SQL计划基线中寻找一个匹配的执行计划，该操作在内存内完成，因此不会引发明显的资源花费。若优化器找到了匹配的执行计划则直接使用之。相反如果没有匹配的计划，那么优化器新产生的计划将被加入到历史计划中，在它成为可接受的计划前首先需要受到验证(verified)。取而代之，优化器将在SQL基线计划中寻找一个成本最低的可接受的计划(注意SQL计划基线可能包括一个SQL语句的多个verified/accepted的执行计划)来执行。但是如果系统中发生了变化(例如drop了索引)，导致所有可接受的执行计划都变成不可重复的，那么优化器还是会采用新产生的基于成本的计划。 我们也可以限定优化器从SQL计划基线中选取执行计划的行为。SQL计划基线可以被标示为固定的。固定的SQL计划基线意味着更受优化器的青睐。若优化器在比较SQL计划基线中各计划的成本，且此时存在执行计划是固定的(fixed)，则优化器将只计算那些已被固定的计划的成本，前提是该计划在当前场景中是可重复的。若固定的计划都是不可重复的(reproducible)，优化器将回归到计算剩余基线中计划的老路上，并找出一个成本最低的。注意计算一个计划的成本远不像硬解析那样铺张浪费。优化器仅仅是去查看指定的路劲，而非遍历所有可能的执行路径。 SQL计划基线进化 当优化器为一条SQL语句找到一个新的计划，该计划将被以未被接受的(non-accpeted)计划的身份加入到计划历史中，这意味着在其转正为可接受计划前首先需要受到验证。可以通过OEM界面或者使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函数在命令行中完成进化。在进化基线时有几种选择：
  1. 仅接受那些性能优于已有计划基线的计划
  2. 不进行性能验证就接受计划
  3. 进行性能对比且生成报告，但暂时仍不接受新的计划
若我们选择第一种方式，则Oracle会尝试评估新的计划以了解其是否具有比即选计划更佳的性能。若评估通过，则新的计划将被加入到SQL计划基线中成为一个新的可接受的计，若未通过，则该新计划将仍以未接受的计划身份保留在计划历史中，但其LAST_VERIFIED属性将会被以当前时间戳更新。通过函数会返回一份已格式化的文本报告，该报告描述了函数所完成的所有动作，同时也会显示新老执行计划的性能统计信息。 若我们选择第二种方式，则Oracle将无视性能因素，直接将新的计划加入到SQL计划基线中成为一个可接受的执行计划。当然同样会产生文本报告。 若我们选择第三种方式，Oracle将完成第一种方式中的评估工作，但不会自动接收新的计划为可接受的(accepted)执行计划。在评估后同样会产生文本报告。

使用并管理SQL MANAGEMENT BASE

初始化参数 存在2个控制SPM的初始化参数 optimizer_capture_sql_plan_baselines 控制是否自动为重复的SQL语句捕捉SQL基线计划。在11gR1/R2中默认为false optimizer_use_sql_plan_baselines 控制是否使用SQL基线计划。当被启用时，优化器将会为正在被编译的SQL语句在SQL基线计划中查找合适的可接受计划。若找到，优化器将为每一个SQL基线计划中的执行计划评估成本并采用成本最低的一个。在11gR1/R2中默认为TRUE 管理SQL Management Base的空间使用 SQL语句日志，计划历史以及SQL基线计划都存储在SQL Management Base中。SQL Management Base作为数据库数据字典的一部分，被存放在SYSAUX表空间上。默认情况下SQL Managment Base的空间使用限制是不超过SYSAUX表空间大小的10%。但我们可以有选择地使用DBMS_SPM.CONFIGURE 存储过程在1%-50%之间修改该限制参数。Oracle会在后台每隔一个礼拜检查SMB的空间使用情况，若超出限制则在告警日志中发出警告。同时也会有周度的维护任务会定期清除任何在53周内未被使用的计划。

posted on 2010-09-18 18:56  Oracle和MySQL  阅读(512)  评论(0)  编辑  收藏  举报

导航