如何稳定SQL语句执行计划 和 并行DML操作的限制

ALLSTARS_ORACLE

如何稳定SQL语句执行计划 和 并行DML操作的限制


稳定执行计划的几点建议
1.        对于10g中GATHER_STATS_JOB定时任务默认是每天22：00自动执行收集统计信息（表中数据量变化超过10%的就会收集）。该定时任务是oracle安装完成后默认自带，不一定适合所有的生产系统。建议关闭该自动任务，改用手工编写脚本方式收集。
2.        建议将数据库中数据量线性增长的表（历史表），数据量易发生突变的表，表中某一列的数值有严重倾斜(skew)等不同情况，在收集统计信息时区别对待。
3.        对数据量变化不大或线性增长的表建议不用每天都收集统计信息，可以根据数据量的变化情况适当锁定一段时期。当数据改变量达到一个数量级后建议更新该表的统计信息。
4.        针对不同的数据表细化收集statistics的运行时间和参数。
5.        针对具体的业务应用细化statistics的收集时间和方法。
6.        建立备份和恢复统计信息的机制。
7.        确定哪些类型的表需要锁定（lock）统计信息。
8.        针对极端的sql语句使用SQL Profile或 stored outline存储该SQL语句性能正常情况下的执行计划。
9.        将BIND-PEEKING 关闭”_optim_peek_user_binds=false”。
10.        调整“OPTIMIZER_INDEX_COST_ADJ”参数，默认值是100，对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间，建议根据测试后调整到一个适当的值。


注意：上述调整建议先在测试环境中严格测试后，再在生产系统中实施和应用。
编写背景
在程序开发中使用嵌入变量减少数据库硬解析对于降低数据库服务器的CPU负荷非常有帮助。自动的统计收集可以减少DBA对数据分析工作的手工干预，并避免数据库表或索引的统计信息过旧以致优化器做出错误判断，以致最终影响业务系统的正常运行。这两种做法对于程序开发和数据库管理而言，都显示了对Oracle数据库有较深的了解，当两者产生复杂的相互作用时，业务应用会出现一些波动－执行计划随机的变化，并在若干个计划中切换。
如何减少执行计划变化，如何保持业务应用的响应在合适的范围呢？
本文试图从嵌入变量与CBO的相互作用出发分析执行计划变化的原因，并提出应用方法。
        原因分析
数据库应用的SQL效率分析
对于数据库应用而言，一个SQL语句传送到数据库引擎，需要经过三个主要的处理才能最终完成，它是解析，执行和获取。获取操作会由于数据逻辑的不同而不同，也没有特别多的优化方法，但对于前两个处理而言，是否可以高效的解析，解析之后是否能得到快速执行就决定了应用程序是否高效的，是否有较强的伸缩性。
解析的优化－减少硬解析－嵌入变量
硬解析在SQL语句第一次被数据库解析时发生，它包含语法，权限，对象属性等进行检查，然后生成最快的执行计划。通常硬解析是大量消耗CPU的操作，应当尽可能的避免。嵌入变量的使用将不同的常量写入嵌入变量缓冲中，在SQL语句的多次执行中保持哈希值不变，避免了重新的解析。
执行的优化--使用CBO基于成本的优化器
对于优化器而言，它在拿到一个SQL的哈希值并结合访问变量，通过对对象属性，索引特性，分区等因素（统计信息）的分析确定各种可能的访问路径，排序方式或关联方式等，并最终形成一个最小执行成本的执行计划。
通常而言，我们从以上两个方面来做，应用的效果会非常的好，然而这两个方面的优化措施相互作用时，却可能产生一些意想不到的情景－多版本的执行计划。在一些高访问负荷，数据分布复杂的业务系统中甚至可能引发突然性的性能下降，这不是我们想看到的，那么原因究竟在哪里呢？接下来我们将会从执行计划决策因素，执行计划评估的触发因素，优化器对用户嵌入变量的处理等方面来阐述多版本执行计划产生的原因，以及应对策略。

执行计划的决定因素
对于多版本执行计划产生的原因，我们产生要看执行计划取决于什么，它的依赖因素是什么？
在9i中缺省的优化器模式为chose，在10g中缺省的优化器模式为all_rows，是基于成本的优化器。它使用一些数据作为评估执行计划或路径的依据，这些数据的不同直接影响了优化器的决策结果，也就是执行计划，它们主要包括：
对象统计值：9i R2和10g中CBO已经成为缺省的优化器模式，CBO（cost based optimizer）是基于成本的优化器的简称，这与原有RBO（Rule based optimizer）相对应。CBO的一大特色是可以根据系统的不同配置，包含内存，IO带宽，CPU资源结合对象的统计信息动态的确定一个成本值，并根据不同访问方式，不同访问路径的成本差异选择最快的执行计划。CBO对于不同执行计划的判断精确度很大程度上取决于对象统计信息的准确性，比如有无索引，列的数据分布，表的行数等信息，这些都直接影响CBO对于执行计划的计算和评估。
通常对象的统计值主要指表（分区），索引，列的统计信息，它们包含
统计信息分类        统计项        备注
表(分区)统计        记录数        表中的记录数
        数据块数        表所占的数据块数
        平均行长        所有记录的平均长度
列统计        不同值数(NDV distinct value)        不同值数
        空值数        值为NULL的数
        最高值／最低值        最高值／最低值
        分布桶数        分布桶数
索引统计        叶节点的块数       
        索引层数        叶节点到根节点的层数
        簇因子       
系统统计        I/O性能与利用率       
        CPU性能与利用率       

对象统计值的有效性—是否有陈旧的统计信息：数据库在使用对象的统计值之前会对统计值的有效性进行检查，如果从上次统计收集到现在数据修改占总数据量的10%左右（通过dba_tab_modifications来判断），则该统计值被视为stale，即无效，CBO则使用动态取样（Dynamic Sampling）的方式通过快速扫描来估计统计值。因此虽然对象有统计信息，但CBO也可能弃之不用。

优化器参数设置（会话／数据库级）：对于优化器而言，一些参数的设定会影响到它对执行计划的选择，这些参数众多，其中包含160个隐含参数和33个可由用户修改的参数，这些参数可以在会话级或数据级设定。具体参数请见参考部分。

系统基准值变化－硬件配置变化：从优化器的参数来看，CPU数，活动实例数，以及以下特性值也可能影响到优化器对执行计划的选择：
特性        含义        缺省值
CPUCSPEED        CPU Speed        无
IOTFRSPEED        I/O transfer speed        4096
IOSEEKTIM        I/O seek time        10
SREADTIM        average single block read time        无
MREADTIM        average multiblock read time        无
MBRC        average multiblock read count        无
MAXTHR        maximum I/O system throughput        无
SLAVETHR        average slave I/O throughput        无

不难看出，如果上述数据发生变化，优化器将可能做出完全不同的执行计划选择，那么当一个语句的执行计划变化时，我们首先确定上述的因素是否不同，比如对象的统计信息是否存在，若存在，统计信息是否准确；同时优化器的设置在数据库一级或会话一级是否不同（后者较隐避，比较难发现），若这些因素变化了，极有可能就是导致执行计划变化的原因。
然而，优化器除了使用数据库自身的决策数据以外，也会参考用户会话中的信息，如嵌入变量的值，这时问题就稍显复杂了。这也是为什么有时以上的决策数据均没有发生变化，但执行计划却仍然在变化的另一个原因 (BIND PEEKING)。

BIND PEEKING
优化器对用户嵌入变量的处理(Peeking/no-peeking)
优化器的设计目标是为每个SQL提供尽可能精确的，成本最低的执行计划，除了在上面所列出的决策信息以外，当优化器遇到用户定义的嵌入变量时，它会做进一步的处理尝试获取更精确的执行计划，这个特性称为BIND-PEEKING。
什么是BIND-PEEKING
当该特性打开时，对于包含嵌入变量（或通过cursor_sharing系统自动添加的嵌入变量）的SQL语句，优化器进行执行计划的评估和计算时会使用嵌入变量的值与相应的统计信息进行对比，这样的优势是可以用嵌入变量的值获得与其更精确匹配的执行计划。相反，如果该特性是关闭的，优化器就只能根据可能性的概率来选择不同的访问路径或方法。
简单的说，在使用嵌入变量带来的解析减少的收益之后，优化器希望能够为用户获得近似使用常量（与嵌入变量相对）时所能产生的精确的执行计划，优化器尽可能产生与此嵌入变量组合最为匹配的执行计划，当嵌入变量的组合较为极端时，优化器可能产生极端的执行计划（非常规的执行计划），虽然极有可能并不适用于其它嵌入变量组合。
在9iR2和10g中该特性由一个隐含参数_optim_peek_user_binds来控制，它缺省为True，即优化器的BIND-PEEKING特性为打开状态。
当BIND-PEEKING关闭时如何影响优化器
该参数控制了优化器在硬件解析时是否在使用统计信息的同时使用用户的嵌入变量来完成执行计划的评估，它可以在会话及系统级设定。
当该参数设定为false时，嵌入变量的值不用在执行计划的评估当中，优化器不使用嵌入变量的值来评估执行计划，优化器只根据可能性的概率(density,num_distinct),来选择不同的访问路径或方法,而非low_value/high_value，histogram等，所产生的执行计划不如该特性打开时精确，与不同的嵌入变量组合相对照，其执行计划也较为平衡（适合于大多数的可能性）。
使用BIND PEEKING技术引起查询速度下降的案例
问题现象
Bind peeking技术自ORACLE 9i(R2)被加入ORACLE数据库的SQL PARSE过程中，其作用在于根据数据表中的数据样本的群集特征选择更为精确的SQL 执行路径来提高数据访问效率。但是在有的数据环境中，Bind Peeking技术却可能导致数据查询性能的下降，其主要原因就在于数据的群集特征极不均衡。在以下的案例中我将用SET SQL_TRACE=TRUE命令来触发SQL hard parse来解释bind peeking的运作结果以便于大家理解bind peeking技术。
问题分析
当设定SQL_TRACE=TRUE时系统重新设定了一个"parse environment" ，所以会引发sql hard parse。在数据库的运行中，很多方法也可以引发hard parse，如：
        restart instance
        analyze table (Delete or change the cursor underlying objects statistics)
        flush shared pool
        Grant and/or revoke privileges on any of the cursor underlying objects
        Alter cursor underlying objects
等等。
“We have changed the session in such a way that it will not share any existing SQL that was not parsed with SQL_TRACE enabled. So it is highly likely that we will either hard-parse a new version of the query or use some existing child cursor that is different from the one you would use with SQL_TRACE disabled. ”

Bind变量值会在hard parse阶段被加入parse过程，并最终影响执行计划的选择。
通过如下步骤重现该现象：
我们先建立一个数据样本

SQL>create table demo as
select case when rownum = 1
then 1 else 99 end id, a.*
from all_objects a;
SQL>create index demo_idx on demo(id);
SQL>execute dbms_stats.gather_table_stats( 'hr','demo',method_opt=>'for all indexed columns size 254' );

以上命令建立一张表，其中id=1的记录只有1条，而id=99的纪录超过10000
条。很明显，针对于demo表的基于id字段的访问，其数据样本的群集特征差异极大。
接下来，我们来看SQL optimizer的解析结果，我们将看到随着参数的不同而导致2种不同执行计划。

解析1：
SQL> set autotrace traceonly explain
SQL> select * from demo where id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1165830477
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DEMO_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


很明显，第2步的索引选择是基于("ID"=1) ，因为该数据在数据表中占极小部分，所以索引引入会迅速找到纪录，同时数据读取量很少。

解析2
SQL> select * from demo where id = 99;
Execution Plan
----------------------------------------------------------
Plan hash value: 4000794843
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9329 | 792K| 39 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEMO | 9329 | 792K| 39 (3)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

解析的第1步的选择基于参数("ID"=99) ，由于该数据集群在表中占绝大多数，所以全表扫描更加有利。
上面即表现了执行计划的选择，下面我们在看下SQL的执行效率。这里我使用BIND变量赋值的方式来执行。


SQL> set autotrace traceonly statistics
SQL> variable id number
SQL> exec :id := 99
      PL/SQL procedure successfully completed.
SQL> select * from demo where id = :id;
      9329 rows selected.
      Statistics
      ----------------------------------------------------------
      744 consistent gets
      0 physical reads
      9329 rows processed

SQL> set autotrace off
SQL> select sql_id from v$sql where sql_text ='select * from demo where id = :id';

       SQL_ID
       -------------
       19bbx9swu48yj

SQL> select * from table( dbms_xplan.display_cursor( '19bbx9swu48yj', 0 ) );
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      SQL_ID 19bbx9swu48yj, child number 0
      -------------------------------------
      select * from demo where id = :id
      Plan hash value: 4000794843
      --------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      --------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 39 (100)| |
      |* 1 | TABLE ACCESS FULL| DEMO | 9329 | 792K| 39 (3)| 00:00:01 |
      --------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      1 - filter("ID"=:ID)

上面标明第一次执行时，带入参数ID=99，执行计划是FULL TABLE SCAN。



SQL> set autotrace traceonly statistics
SQL> exec :id := 1
      PL/SQL procedure successfully completed.
SQL> select * from demo where id = :id;
      Statistics
      ----------------------------------------------------------
      126 consistent gets
      0 physical reads
      1 rows processed

SQL> set autotrace off
SQL> select sql_id from v$sql where sql_text ='select * from demo where id = :id';
      SQL_ID
      -------------
      19bbx9swu48yj


在第二次执行时，我是用了参数ID=1,但是由于HARD PARSE后，CURSOR 被共享，所以该语句的执行仍然使用了FULL TABLE SCAN执行计划。接着我们重新HARD PARSE



SQL> alter session set sql_trace=true;
Session altered.
SQL> set autotrace traceonly statistics
SQL> select * from demo where id = :id;
Statistics
----------------------------------------------------------
4 consistent gets
0 physical reads
1 rows processed

在这里我们看到执行计划的成本极低，效率比上一步明显提高很多。

SQL> set autotrace off
SQL> select sql_id from v$sql where sql_text ='select * from demo where id = :id';
      SQL_ID
      -------------
      19bbx9swu48yj
      19bbx9swu48yj < ====


在此我们看见同一语句的2个parse版本（child cursor），当前使用第二个。


SQL> select * from table( dbms_xplan.display_cursor( '19bbx9swu48yj',1) );
      PLAN_TABLE_OUTPUT
     ---------------------------------------------------------------------------------------------
      SQL_ID 19bbx9swu48yj, child number 1
     -------------------------------------
      select * from demo where id = :id
      Plan hash value: 1165830477
      ----------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ----------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 2 (100)| |
      | 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 87 | 2 (0)| 00:00:01 |
      |* 2 | INDEX RANGE SCAN | DEMO_IDX | 1 | | 1 (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      2 - access("ID"=:ID)

当前的执行计划是使用的索引DEMO_IDX。

现象总结
Bind peeking技术将参数带入hard parse过程中，有利于找到更加精确的执行计划。但是数据样本的差异很大时，可能在第一时间选择的执行计划对于其他参数的访问不利，如果第一时间使用的参数在整个应用运行周期中是个特殊的个例则可能导致应用的执行性能明显下降。

解决方法
如果应用发生不可避免的会遇到hard parse，而数据样本可能导致Bind Peeking技术选择不利的执行计划，我们只能采取“先声夺人”或禁止bind peeking的做法来解决问题。
1）        在每次analyze table（类似可能引发hard parse的操作）后立即执行一个准备好的sql，来引导bind peeking选择一个有利于我们的执行计划，并共享于以后的sql执行。
2）        关闭bind peeking.
SQL>alter system set "_optim_peek_user_binds"=false;

多版本执行计划产生的原因—硬解析
如我们在SQL效率分析中所说的，由于硬解析消耗大量的资源，也产生许多内部锁定，因此数据库引擎将尽可能的减少硬解析，只有当某些因素触发后，数据库才再次进行硬解析。而硬解析中又包含了对执行计划的生成，如果一个SQL在第一次解析后再没有硬解析发生，那么可以确定，它的执行计划一定也是不变的，所以减少触发硬解析的操作也可以大大减少不同执行计划产生的可能性。
下表是数据库中触发SQL语句硬解析的因素，我们在右侧也列出了与之对应的应对策略：
硬解析触发因素        避免策略
首次SQL访问        第一解析无法避免
与SQL相关对象的DDL操作        在生产库中屏蔽DDL语句
与SQL相关对象的权限变化        在业务高峰期屏蔽权限修改
与SQL相关对象的统计信息变化        仅当需要时收集统计，必须时固定统计信息
会话或数据库级的优化器参数改变        统一规则，不在应用中修改优化器相关参数
游标过期或共享池刷新        指定适当大小的共享池，避免不必要的共享池刷新


        案例分析
某客户近期相关问题的原因分析在12月底至1月这段时间内，某客户曾经出现过由于执行计划变化而导致的业务运行异常，表现为关键业务过程的执行速度显著变慢，数据库选择了较差的执行计划。
通过对Oracle数据库的执行计划历史分析后发现，此关键SQL的执行计划在近期频繁变化，共有6种不同的执行方式与之对应，我们把各自的执行次数／逻辑读／io等待／CPU时间／执行时间对比之后发现：
PLAN_HASH_VALUE        执行次数
EXEC_SUM        逻辑读
GETS_AVG        io等待
IOWAIT_AVG        CPU时间
CPU_AVG        执行时间
ELAP_AVG
267901271        116        4129936        104518        185493469        191939077
2667863071        24        353        277406        39489        310641
2893608670        1170        888583        363355        57591747        90999644
3231199102        1402        508418        171859        22097784        29853920
3318325031        538        46        29749        3678        34645
3928782998        2        2161        703458        184358        864604

其中执行计划267901271是引起整体性能下降的原因，它的平均逻辑读／平均CPU时间／平均等待时间均8倍于其它的执行计划。
而执行计划3231199102整体而言，各方面的特征都较为平衡，并且经过了1402次的运行检验，可以认为是适合于绝大多数数据的最优的执行计划。相比之下对于其它执行计划虽然其逻辑读／CPU等较低，但执行次数极少，或平均值过低，在打开自动统计收集的情况下，这此执行计划可能仅适用于某些数据分布，所以可能不是最优执行计划。
对于优化器的BIND-PEEKING而言，这6种执行计划分别与不同的嵌入变量相对应，在这个SQL语句中它对应于不同的ZONENO：


业务模块
（SQL_ID）        执行计划        嵌入变量组合        执行计划的适应性
gz3vqqgggar54        267901271        B1:’3010’        较差
        2667863071        B1:’2604’        偏小（运行次数少）
        2893608670        B1:’0809’        较好
        3231199102        B1:’0912’        好
        3318325031        B1:’3500’        偏小（运行次数少）
        3928782998        B1:’3400’        偏小（运行次数少）

由于这些执行计划分别在不同的时间段，并且该业务会定时清除相关表的数据，与这些执行计划相关的统计信息无法获得，但从3010与其它的ZONENO的业务特性分析，3010的业务与其它ZONENO的区域差异较大，优化器为此选择了较为极端的执行计划，而这个执行计划在当时的确适用于3010这个地区号，是最快的。
那么为什么优化器不能产生最优的执行计划呢？
对于优化器而言，在一次硬解析中分析所有的嵌入变量组合，并得到适合大多数的执行计划是非常困难的，在逻辑上也是不可能的，即使能够做到，这个代价对于硬解析而言是过于高了。优化器可以做到的是通过BIND-PEEKING技术产生与其更精确匹配的执行计划（最快的执行计划），但这个执行计划却未必适合后来访问的嵌入变量组合，那么有一种可能性：：当某次硬解析被触发后，优化器要根据第一次给出的嵌入变量组合来确定一个执行计划，当执行计划确定后，可以它对于这个变量而言是‘极端好’，而此后，该实例将一直使用这个执行计划直到下一次硬解析被触发。但对于随后更多的嵌入变量组合而言它却可能成为‘极端差’的执行计划，这就是近期生产系统中发现的突发性能问题的根本原因。
通常而言，BIND-PEEKING对于在开发或测试中的系统是适合的，但对于需要稳定运行的系统而言，关键业务过程是否稳定与是否高效同样重要，此时将BIND-PEEKING关闭更为合适。



应对策略
通过以上的分析，我们可以对优化器如何评估和使用执行计划有一个初步的了解，那么在业务运行当中如何避免这种执行计划突然改变而引发的性能呢？
针对这个问题有几方面的建议：
最快与最优
通常开发人员的关注点在于如何为业务过程确定一个最快的执行计划，关注这一点在许多情况下是正确也是合乎逻辑的，然而这个执行计划是否适合所有可能的变量组合也同样重要，因为在上线后它要接受所有可能组合的检验，如果它仅仅适合某个值是不够的，它应当对于大多数值都是最快或接近于最快的－最优执行计划。

如何在设计阶段确定最优执行计划
优化器无法得到最优的执行计划的原因在于它无法得知所有的变量组合，但这一数据我们可以在应用的设计与开发阶段知道，因此可以从以下两个方面着手：
1.        根据数据分布特性得到最优执行计划：在设计阶段各个数据表的数据特点都是可预知的，从这些特征出发确定一个执行计划就最为准确，同时也避免了频繁统计收集。，这个执行计划需要与具体的数据无关，不求最快，但求最优，即适合大多数情况。从技术上而言，可以使用嵌入变量的均值（而非理想或极端值）来创建执行计划。如果结合优化器指令（HINT）可以更精确的固定一个最优的执行计划，或者缩小BIND-PEEKING所带来的执行计划变化的范围，减少BIND-PEEKING带来的副作用。如果很好的应用HINT和嵌入变量，BIND-PEEKING就不会影响到应用的正常运行。
2.        适当使用常量与变量的组合：对于数据中的极端值（最大，最小等）可以使用常量，由数据库自行选定最快执行计划，对于其它值则使用HINT来固定一个折中的执行计划。

如何在应用发布后强制数据库使用最优的执行计划
生产与测试环境在许多时候存在较大的差异，通常就可以包括CPU/内存/IO带宽等因素，加上业务的运行带来了数据量的变化，并且这种变化可能是线性的也可能是频繁波动的，如何在应用发布后强制数据库使用经过验证的最优执行计划呢？这有几个策略：
1.        使用HINT完全固定执行计划：屏蔽统计信息变化等因素对执行计划的影响，这种方法对HINT的使用要求较高。
2.        不使用HINT，固定统计信息并关闭BIND-PEEKING特性：这样数据库会根据数据概率分布自动获取一个折中的执行计划，避免了某些极端情况发生。这种方法不需要修改应用代码，适用于已经发布无法修改的应用。
3.        使用SQL Profile固定执行计划：10g可以使用SQL Tuning Advisory来优化SQL语句，在生成一个较优的执行计划后，可以使用SQL Profile固定它。
关于统计信息的收集和备份
备份和恢复统计信息
1.在<OWNER> schema 下创建创建一个statistics table

SQL> connect user/password
SQL> exec dbms_stats.create_stat_table('SYS',stattab=>'bkstat01',tblspace=>'statspack');

2. 将统计信息备份到statistics table

--按表备份
SQL> exec dbms_stats.export_table_stats(user,'<TABLE_NAME>',NULL,'STAT_TIMESTAMP');

--按schema备份
SQL> exec dbms_stats.export_schema_stats(user,'STAT_TIMESTAMP');
Example：
SQL> exec dbms_stats.export_schema_stats('ecm_dctm_acct',statown=>'SYS',stattab=>'bkstat01');



3.同时也可以备份SYSTEM statistics（该系统统计信息不要轻易恢复，一般在系统迁移时使用）


SQL> select * from sys.aux_stats$;
   SQL> exec dbms_stats.export_system_stats('STAT_TIMESTAMP');

4. 在某条语句的执行计划发生改变后，可以尝试恢复早期备份的统计信息

--查看该表的统计信息是否在STAT_TIMESTAMP这个备份表中
select distinct c1 from STAT_TIMESTAMP where type ='T';

-- 按表恢复
SQL> exec dbms_stats.import_table_stats(NULL,'<TABLE_NAME>', NULL,'STAT_TIMESTAMP');
Example:
exec dbms_stats.import_schema_stats('ecm_dctm_acct',statown=>'SYS',
stattab=>'bkstat01');

--如果是10g的数据库可以通过如下方式恢复table statistics到之前某个时间点
1)10g默认将早期的统计信息保留31天，可通过如下方式修改：
  execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx) --xx为保留天数

2) 如何知道有多少天的统计信息可以用
  --查询当前系统统计信息的保留期限
  select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
  
  --查询最早可以用于恢复的统计信息
  select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

3)如果找到某个表的统计信息历史记录
  select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;

4)10g如何恢复统计信息到之前某一时间点
     execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
   
--Example：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');


详细信息请参考metalink Note:464939.1，Note: 452011.1       

锁定（lock）统计信息 (Only for 10g)
对于表进行了统计分析后，相关 SQL 语句的执行计划会发生变化，oracle建议将这个语句所涉及到的table的统计信息进行锁定。其作用在于下再对库中的表进行大规模统计分析时，该表的统计信息将保持不变。所涉及的统计信息包括table statistics, column statistics, histograms, and dependent index statistics。如果在统计分析中使用FORCE选项，则会忽略lock锁定。

具体操作如下：


EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');
EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS ('owner name');

通过下面查询哪些表的统计信息被锁定：
select OWNER,TABLE_NAME,PARTITION_NAME,OBJECT_TYPE,NUM_ROWS,BLOCKS,
stattype_locked FROM DBA_TAB_STATISTICS
where owner = 'HBCRM'
OWNER      TABLE_NAME          PARTITION_NAME OBJECT_TYPE    NUM_ROWS     BLOCKS      STATT
---------- ------------------- -------------- ------------ ----------    ---------- -----
HBCRM      T_LIST_FS_ACC_NBR   P310           PARTITION
HBCRM      T_LIST_FS_ACC_NBR   P335           PARTITION
HBCRM      BILL_CONSUME_SCORE  P1301          PARTITION      15779880     187748
HBCRM      BILL_CONSUME_SCORE  P1302          PARTITION      13223276     157356
HBCRM      BILL_CONSUME_SCORE  P1303          PARTITION       4895725      69954
HBCRM      BILL_CONSUME_SCORE  P1304          PARTITION      13215004     155566
HBCRM      BILL_CONSUME_SCORE  P1305          PARTITION       6682400      74307
HBCRM      BILL_CONSUME_SCORE  P1306          PARTITION      13928880     166625
HBCRM      BILL_CONSUME_SCORE  P1307          PARTITION       4848364      54186
HBCRM      BILL_CONSUME_SCORE  P1308          PARTITION       5003840      55606
HBCRM      BILL_CONSUME_SCORE  P1309          PARTITION       7910521      88014


也可以通过方式如下解锁：
EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('owner name', 'table name');
EXECUTE DBMS_STATS.UNLOCK_SCHEMA_STATS ('owner name');

并行DML操作的限制
如果想让DML操作使用并行执行，必须显式的在会话里执行如下命令
SQL> alter session enable parallel dml;
只有执行了上述操作oracle才会对之后符合条件的DML操作并行执行，如果没有这个设定，即使SQL中指定了并行oracle也会忽略它。
--Delete,Update,Merge
对于Delete,Update,Merge操作只有当操作的对象是分区表时oracle才会启用并行。对于分区表oracle会对每个分区启用一个并行服务进程进行数据处理
alter session enable parallel dml;
explain plan for delete /*+ parallel(t 2) */ from t;
select * from table(dbms_xplan.display);
explain plan for delete /*+ parallel(t1 2) * from t1;
select * from table(dbms_xplan.display);

--INSERT 并行执行
对于insert into ... select ...并行才有意义
对于insert into ....values ...并行没有意义，因为sql本身只插入一条记录
      
explain plan for insert /*+ parallel(t 6) */ into t select /*+ parallel(t1 6) id,name from t1;
select * from table(dbms_xplan.display);
explain plan for insert /*+ parallel(t 6) */ into t select id,name from t1;
select * from table(dbms_xplan.display);
其它限制也可参考MOS文档：


BIND PEEKING测试案例
-- Set Optimizer mode to choose
Alter Session Set Optimizer_Mode='ALL_ROWS'
/

-- Enabling bind peeking feature
Alter Session Set "_optim_peek_user_binds" = True
/

-- Testing with CPU costing off
Alter Session Set "_Optimizer_Cost_Model"='io'
/

-- Optimizer Features must be set to 9.2.x or higher
Alter Session Set Optimizer_Features_Enable = '10.2.0.5
/

Begin
For i in 1..74998 Loop
Insert Into PreferredMeals Values (i, 'Mansaf');
End Loop;
Insert Into PreferredMeals Values (74999, 'Kabab');
Insert Into PreferredMeals Values(75000, 'Pasta');
End;
.
/

Drop Index PrefMl_Indx
/

Create Index PrefMl_Indx
On
PreferredMeals(Meal)
/

Gather statistics and let Oracle decide which columns requires histograms and how many buckets are required.
Exec DBMS_Stats.Gather_Table_Stats(OwnName => 'BP', TabName => 'PreferredMeals', Method_Opt => 'For All Indexed Columns Size Auto', Cascade => True)
/

Select Count(*)
From PreferredMeals
Where Meal = 'Mansaf'
/
Select Count(*)
From PreferredMeals
Where Meal = 'Kabab'
/

Select * From Table(DBMS_XPlan.Display_Cursor);

Var BP Varchar2(10)
Exec :BP := 'Mansaf'

Select Count(*)
From PreferredMeals
Where Meal = :BP
/

Select * From Table(DBMS_XPlan.Display_Cursor);
Assign another value for the bind variable:
Exec :BP := 'Pasta'

Select Count(*)
From PreferredMeals
Where Meal = :BP
/

To invalidate any cursor,  one of the followings  can be done:
1. Issue the command Alter System Flush Shared_Pool;
2. Delete or change the cursor underlying objects statistics
3. Grant and/or revoke privileges on any of the cursor underlying objects
4. Alter cursor underlying objects
5. Bounce the instance

   详细文档可参考：Bind Peeking By Example (Doc ID 430208.1)