Oracle11g性能调优--使用SQLPlanManagement(2) - 数据库编程

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语句查询DBA_SQL_PLAN_BASELINE视图：

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的相关功能。

6.1 磁盘空间使用情况

数据库定期核对用于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;
/

6.2 Purging policy
每周的定时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
```
7.导入导出SQL Plan baseline
SQl Plan baseline的导入导出是使用oracle data pump来实现的。

1.在原始数据库，使用dbms_spm.create_stgtab_baseline创建一个stage table：
```
BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name => 'stage1');
END;
/
```
2.使用PACK_STGTAB_BASELINE
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;
/
```

Oracle11g性能调优--使用SQLPlanManagement(2)(二)