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

本文描述如何使用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

Oracle11g性能调优--使用SQLPlanManagement(1)(一)