Oracle11g性能调优--使用SQL Plan Management(2)

当使用SQL Tuning Advisor来调优SQL语句时，如果advisor发现一个调优的计划，并验证其性能比从相应的SQL Plan baseline中的计划更好，那么它可以建议接受一个SQL Profile。当SQL Profile被接受后，数据库会将该计划加入到相应的SQL Plan baseline。但是，SQL Tuning Advisor不会验证在plan history中的unaccepted plan。

在Oracle11g中，一个自动配置任务在维护窗口中运行SQL Tuning Advisor。这个任务通过在AWR快照中搜集到的执行性能数据来标示出高负载的SQL语句。这个自动配置任务会实施SQL Tuning Advisor的SQL profile建议。因此，数据库会自动将高负载的SQL语句的调优计划加入到SQL Plan baselines中。

4.使用Fixed SQL Plan Baseline

要查看存储在SQL plan baseline的给定语句的计划，使用DBMS_XPLAN包的DISPLAY_SQL_PLAN_BASELINE功能。下面的示例显示指定SQL语句的一个活多个执行计划:

SELECT * FROM TABLE( 
    DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( 
        sql_handle=>‘SYS_SQL_209d10fabbedc741‘, 
        format=>‘basic‘));

本例子通过sql_handle，也可以使用plan name来显示单个计划。

此函数使用存储在SQL Management Base的计划信息来解释和显示计划。在这个例子中，DISPLAY_SQL_PLAN_BASELINE功能显示由句柄SYS_SQL_209d10fabbedc741指定的SQL语句的执行计划：

SQL handle: SYS_SQL_209d10fabbedc741
SQL text: select cust_last_name, amount_sold from customers c,
          sales s where c.cust_id=s.cust_id and cust_year_of_birth=:yob
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_bbedc741a57b5fc2
Enabled: YES      Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
----------------------------------------------------------------------------------
Plan hash value: 2776326082

----------------------------------------------------------------------------------
| Id  | Operation                                | Name                          |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                               |
|   1 | HASH JOIN                                |                               |
|   2 |   TABLE ACCESS BY INDEX ROWID            | CUSTOMERS                     |
|   3 |     BITMAP CONVERSION TO ROWIDS          |                               |
|   4 |     BITMAP INDEX SINGLE VALUE            | CUSTOMERS_YOB_BIX             |
|   5 |    PARTITION RANGE ALL                   |                               |
|   6 |    TABLE ACCESS FULL                     | SALES                         |
----------------------------------------------------------------------------------

----------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_bbedc741f554c408
Enabled: YES     Fixed: NO      Accepted: YES       Origin: MANUAL-LOAD
----------------------------------------------------------------------------------
Plan hash value: 4115973128

----------------------------------------------------------------------------------
| Id  | Operation                                | Name                          |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                               |
|   1 |   NESTED LOOPS                           |                               |
|   2 |     NESTED LOOPS                         |                               |
|   3 |       TABLE ACCESS BY INDEX ROWID        | CUSTOMERS                     |
|   4 |         BITMAP CONVERSION TO ROWIDS      |                               |
|   5 |           BITMAP INDEX SINGLE VALUE      | CUSTOMERS_YOB_BIX             |
|   6 |       PARTITION RANGE                    |                               |
|   7 |        BITMAP CONVERSION TO ROWIDS       |                               |
|   8 |          BITMAP INDEX SINGLE VALUE       | SALES_CUST_BIX                |
|   9 |     TABLE ACCESS BY LOCAL INDEX ROWID    | SALES                         |
----------------------------------------------------------------------------------

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED 
FROM   DBA_SQL_PLAN_BASELINES;
 
SQL_HANDLE                PLAN_NAME                      ENA  ACC    FIX
------------------------------------------------------------------------
SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741a57b5fc2  YES  NO     NO
SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741f554c408  YES  YES    NO

6.SQL Management Base

SQL Management Base（SMB）是驻留SYSAUX表空间的数据字典中的一部分。它存储statement log，plan history，SQL plan baselines和SQL  profile。为了每周清洗未使用的计划和日志，SMB使用自动空间管理(ASSM)。

也可以手动添加一组SQL语句的计划到SMB。从Oracle数据库11之前版本的数据库升级到oracle11g时，此功能特别有用，因为它有助于减少因采用了新的优化器版本而导致的性能下降。

因为SMB是完全位于SYSAUX，如果该表空间不可用，那么数据库不使用SQL Plan Management和SQL tuning的相关功能。

数据库定期核对用于SMB的磁盘空间。默认情况下，SMB不超过SYSAUX大小的10％。允许的范围为1％和50％之间。

每周后台进程测量由SMB所占用的总空间。当超出定义的限制，会想alert log写入一个警告。这个警告会一直写入，直到使用如下方法：

• 增加SMB的limit

• 增大SYSAUX表空间的尺寸

• 通过清理SQL Management 对象(SQL plan baseline或者Sql profile)，降低了SMB使用的磁盘空间

要改变limit的比例，使用DBMS_SPM包的CONFIGURE。下面的例子改变了空间限制为30％：

BEGIN
  DBMS_SPM.CONFIGURE(‘space_budget_percent‘,30);
END;
/

每周的定时purge任务管理SQL plan management使用的磁盘空间，会将53周前的计划purge掉。53周保证了计划信息在在一年内是有效的，这个rentiton period可以在5周到523周（10年多一点）之间。

使用DBMS_SPM包的CONFIGURE更改rentiong period为105周：

BEGIN
  DBMS_SPM.CONFIGURE( ‘plan_retention_weeks‘,105);
END;
/

6.3 SQL Management Base Configuration Paramters

这两个参数可以通过DBA_SQL_MANAGEMENT_CONFIG 查看：

SELECT PARAMETER_NAME, PARAMETER_VALUE 
FROM   DBA_SQL_MANAGEMENT_CONFIG;
 
PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

SQl Plan baseline的导入导出是使用oracle data pump来实现的。

1.在原始数据库，使用dbms_spm.create_stgtab_baseline创建一个stage table：

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name => ‘stage1‘);
END;
/

DECLARE
  my_plans number;
BEGIN
  my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => ‘stage1‘,
    enabled => ‘yes‘,
    creator => ‘dba1‘);
END;
/

3.使用data pump导出该staging table到一个flat 文件，transfer到目标系统，然后使用data pump import导入到目标数据库。

4.使用UNPACK_STGTAB_BASELINE 函数将staging 表中的数据解包到sQL Plan baseline中：

DECLARE
  my_plans number;
BEGIN
  my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
    table_name => ‘stage1‘,
    fixed => ‘yes‘);
END;
/