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

最新推荐文章于 2023-10-01 20:57:50 发布

时时处处皆修行

最新推荐文章于 2023-10-01 20:57:50 发布

阅读量1.4k

点赞数

本文链接：https://blog.csdn.net/davidwang9527/article/details/18654083

版权

Oracle 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

本文描述如何使用SQL Plan Management管理SQL执行计划。SQL Plan management通过提供capturing，selecting和evolving SQL Plan信息的组件，来防止由于SQL语句的执行计划突然变化导致的性能下降。

第一部分见http://blog.csdn.net/davidwang9527/article/details/18620953

本文包括如下主题：

3.与SQL Tuning Advisor一起使用SQL Plan Baseline
4.使用Fixed SQL Plan Baseline

5.Displaying SQL Plan Baseline
6.SQL Management Base
7.导出和导入SQL Plan Baseline

3.与SQL Tuning Advisor一起使用SQL Plan Baseline

当使用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包含至少一个属性Fixed设置为true且已经被enabled的plan是，该SQL Plan baseline就是fixed。可以使用fixed SQL来 Plan baseline为一个SQL语句来固定一组可能的计划（通常是一个计划）。

如果一个fixed的SQL Plan baseline中也包含non-fixed plans，那么优化器会优先选择fixed plan而不选择non-fixed plan。因此，优化器采用fixed plan中的最低成本的计划，即使一个non-fixed plan有更低的成本。

优化器不会添加新的计划到一个fixed SQL plan baseline。因为优化器不会自动添加新的计划，当你执行DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE时，数据库不会进化一个fixed SQL Plan baseline. 但是，您可以通过手动从shared SQL area或者sqL tuning set中加载新计划。

当使用SQL Tuning Advisor来调整一个使用fixed SQL plan baseline的SQL语句时，该SQL profile推荐具有特殊的意义。当SQL profile被接受后，数据库会将调优的计划以non-fixed plan加入到fixed SQL plan baseline。然而，如上所述，优化器当时可重复fixed plan存在时，不会使用tuned plan。因此，无法实现 SQL tuning带来的好处。若要使用该tuned plan，需要手动更改该计划为一个fixed plan（fixed属性改为为YES）。

5.Displaying SQL Plan Baselines

要查看存储在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语句查询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;
/

时时处处皆修行

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle11g性能调优--使用SQL Plan Management(2)

本文描述如何使用SQL Plan Management管理SQL执行计划。SQL Plan management通过提供capturing，selecting和evolving SQL Plan信息的组件，来防止由于SQL语句的执行计划突然变化导致的性能下降。第一部分见http://blog.csdn.net/davidwang9527/article/details/18620953本文包括如下主题：3.与SQL Tuning Advisor一起使用SQL Plan Baseline4.使用Fix
复制链接

扫一扫