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

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

时时处处皆修行

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

阅读量2.8k

点赞数

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

版权

Oracle 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

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

本文包括如下主题：

1.SQL Plan Baseline概述

2.管理SQL Plan Baseline

1.SQL Plan Baseline概述

SQL Plan Management是一个预防机制。这种机制可以通过一段时间的记录和评估sql语句的执行计划，来建立一个SQL语句的accepted plan的集合，即SQL Plan Baseline.

1.1 SQL Plan Baseline的目的

SQL Plan Baseline的目标是保持相应SQL语句的性能，而无视数据库的变化。变化的例子包括：

新的优化器版本
优化器统计信息和优化器参数变化
schema和元数据定义变化
系统设置的变化
SQL Profile的创建

SQL Plan Baseline在一个事件已造成不可逆的执行计划改变时无能为力，如删除索引情况时，会将SQL Plan baseline的相应SQL的执行计划删除，此时将不能帮助避免性能下降。个人认为这是Oracle综合考虑的选择，并不是SQL Plan Management的限制。

Oracle数据库的SQL Tuning 功能可以生成SQL profile，以帮助优化器产生调整良好的计划。然而，这种机制是被动的，不能保证当发生剧烈数据库更改时的性能稳定。SQL Tuning只能解决已经出现并被识别出来的性能问题，例如，可能会因为计划变化而变得高负荷的SQL语句。SQL调优只有到了执行计划改变后，才能解决这个问题。

SQL Plan Management可以改善或维持SQL性能的常见场景包括：

数据库升级后安装了一个新的优化器版本，这通常会导致一小部分的SQL语句的计划变更。大多数这些计划变更导致没有性能变化或改进。然而，一些计划的更改可能会导致性能下降。通过导入SQL Performance Analyzer的SQL Tuning Sets，SQL Plan Baseline 可以帮助显著的减少升级中引起的潜在的性能下降。
正运行的系统和数据的变化可能会影响一些SQL语句的计划，从而可能导致性能下降。SQL Plan Baseline 有助于最大限度地减少性能下降并稳定SQL性能。
新的应用程序模块的部署意味着引入新的SQL语句到数据库中。应用软件可能已经在标准测试配置环境中中为新的SQL语句使用了恰当的SQL执行计划。如果系统配置与测试配置差异较大，那么数据库先load测试环境已经准备好的执行计划。

1.2 SQL Plan Baseline的体系架构

一个SQL Plan baseline包含一个或多个accepted plan，每个accepted plan又包含以下信息：

hints
计划的哈希值
计划相关的信息

Plan History是在一段时间内优化器产生的包含了accepted与not accepted计划的一组计划。因为只有accepted plan在SQL Plan Baseline中，因此SQL Plan Baseline中的计划是plan history的子集。例如，在优化器生成第一个可接受的计划后，后续的计划是plan history的一部分，但不是plan baseline的一部分。

加入SQL Plan baseline过程叫plan evolution。要能够被evolved，计划必须被enabled 供优化器使用。

SQL Management Base(SMB)是数据字典的一部分，在SYSAUX表空间中存放sql plan baseline和plan history。SMB也包括SQL profile。SMB使用自动空间管理。

2.管理SQL Plan Baseline

管理SQL Plan Baseline 包括以下几个阶段：

capture SQL Plan Baseline

select SQL Plan Baseline

evolve SQL Plan Baseline

2.1 capturing sql plan baseline

在SQL Plan Baseline 捕获阶段，数据库检测计划的变化，并记录新的计划，以便它可以由数据库管理员evlove(verified)。为此，数据库为每个SQL语句维护一个plan history。因为临时SQL语句不重复，因此不会有性能的下降，数据库仅维护可重复的SQL语句的plan history。

为了识别重复的SQL语句，数据库维护一个statment log，它包含的各种已被优化器评估的SQL语句的SQL ID。在一个SQL语句被数据库记录后，又被再次解析或再次执行后，数据库将该SQL语句识别为可重复的。

对于每一个可重复的SQL语句，数据库维护一个plan history，该plan history包含由优化器生成的所有计划。plan history中的所有可接受计划即SQL Plan Baseline.

您可以配置SQL Plan Baseline 捕获阶段为自动获取plan history和SQL Plan baseline，也可以配置为手动load计划为SQL Plan baseline.

2.1.1 自动获取计划

当自动计划捕获启用时，数据库会使用由优化器提供过的信息为sql语句自动创建和维护plan history。该plan history包括该优化器使用的相关信息来产生执行计划，例如sql text，outline，bind变量，编译环境。

优化器将一个SQL语句所产生的初始计划作为accepted计划使用，此时这个初始计划既是sql plan baseline也是plan history(即当前plan history=sql plan baseline)。Plan history包括所有后续计划，在SQL Plan Baseline evolution阶段，数据库将被证实不会导致性能下降的计划加入到基线中。

要启用自动获取计划，设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数为TRUE。默认情况下，此参数为FALSE。

2.1.2 从现有的plan创建baseline

可以通过手动加载一组SQL语句的现有计划到plan baseline中来创建SQL Plan baseline。数据库不验证手动加载计划的性能，将他们作为accepted plan 加入到现有或新的SQL Plan baseline。可以与自动plan capture一起使用手动加载计划，或者将手动加载计划作为自动plan capture的替代方法。

可以通过执行手动加载的计划：

A.从SQL Tuning Sets和AWR快照中装载计划

从SQL Tuning Set中加载计划，使用DBMS_SPM包的LOAD_PLANS_FROM_SQLSET的功能。下面的例子加载存储在名为SQL tuning set中的计划TSET1：

DECLARE 
  my_plans PLS_INTEGER; 
BEGIN 
  my_plans：= DBMS_SPM.LOAD_PLANS_FROM_SQLSET（sqlset_name =>'TSET1'）; 
END; 
/

要从AWR中加载计划，需要先将AWR snapshot中存储的计划load到SQL Tuning Set中，然后再使用上述功能。

B.从Shared SQL Area装载计划

要加载的Shared SQL区的计划，使用DBMS_SPM包的LOAD_PLANS_FROM_CURSOR_CACHE功能。在下面的示例中，Oracle数据库加载位于Shared SQL区的由SQL_ID确定的SQL计划：

DECLARE 
  my_plans PLS_INTEGER; 
BEGIN 
  my_plans：= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE（SQL_ID => '99twu5t2dn5xd'）; 
END; 
/

2.2 选择SQL Plan Baselines

在SQL Plan Baseline 选择阶段，Oracle数据库检测基于存储plan history的变化，选择计划以避免潜在的性能下降。

每次数据库编译SQL语句，优化器将执行以下操作：

使用基于成本的搜索方法(Cost-based search method)来构建一个最佳成本方案
试图在SQL Plan Baseline中找到一个匹配的计划
如果找到了，那么优化器进行使用匹配计划；如果没有找到，那么优化器将评估所有的SQL Plan baseline中的accepted SQL Plan，选择最低成本的SQL计划。

优化器找到的best-cost plan，如果不匹配该sql语句的plan history中的计划，那么该best-cost plan就是一个新的计划，数据库将添加这一计划作为nonaccepted计划到plan history。数据库不使用这个新的计划，直到它被证实不会导致性能下降。但是，如果一个系统变化（例如drop掉索引）会导致所有的accepted plan都不可用，这时优化器会选择该best-cost plan.因此，SQL Plan baseline会让优化器使用保守的计划选择策略。

若要使用SQL Plan Baseline，设置OPTIMIZER_USE_SQL_PLAN_BASELINES初始化参数为TRUE（默认值）。

2.3 evloving SQL PLAN baselines

在SQL Plan Baseline进化阶段，数据库评估新计划的性能，并将更好性能的Plan 集成到SQL Plan baseline中。
当优化程序发现一个SQL语句的新计划，数据库将添加该计划到plan history中，作为一个nonaccepted计划。数据库可以验证该计划相对于SQL Plan Baseline的性能表现。一个成功的验证过程包括将其与SQL Plan baseline比对，以及保证其可以deliver更好的性能。当数据库验证一个nonaccepted计划不会导致性能下降，数据库将其改为一个accepted plan，并将其集成到baseline中。

A.使用手动load Plan来进化plan

如2.1.2中所示，如果手工的从Shared SQL Area或者从SQL tuning set中load plan到SQL Plan baseline，则数据库会将这些计划作为accepted plan加入到SQLP Plan baseline中。

B.使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE进化计划

PL/SQL函数DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE会试图已经被优化器假如到plan history的新计划。如果函数可以验证新的计划比相应的SQL Plan baseline中的计划效果更好，那么数据库添加该新计划为一个accepted计划。

在下面的例子中，DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 函数为一个由SQL句柄标示的SQL语句进化一个新的计划（SQL句柄是一个SQL唯一标示，以字符串形式标示）。您可以通过DBA_SQL_PLAN_BASELINES.SQL_HANDLE查询找到SQL句柄.
SET SERVEROUTPUT ON
SET LONG 10000

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
    report clob;
BEGIN
    report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                  sql_handle => 'SYS_SQL_593bc74fca8e6738');
    DBMS_OUTPUT.PUT_LINE(report);
END;
/

备注：可以使用该函数来指定一个特殊的计划的名字、一组计划或者no value。如果是no value，则数据库会进化所有的nonaccepted plans.

时时处处皆修行

关注

0
点赞
踩
1

收藏

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

本文描述如何使用SQL Plan Management管理SQL执行计划。SQL Plan management通过提供capturing，selecting和evolving SQL Plan信息的组件，来防止由于SQL语句的执行计划突然变化导致的性能下降。本文包括如下主题：1.SQL Plan Baseline概述2.管理SQL Plan Baseline
复制链接

扫一扫