老熊的三分地-Oracle及数据恢复

关于全局索引和本地索引的优缺点，分别应该在什么情况下使用，这方面的资料很多，本文不作讨论。本文讨论一种特殊情况，即建立在分区键之上的本地索引。9i也算是很老的Oracle版本了，只是很多系统包括很多大型的核心的系统都在用，因此本文介绍建立在分区键列上的本地索引存在的问题。下面是一些测试：

SQL> create table t1 ( a int, b varchar2(300)) partition by range(a)
  2  (
  3   partition p01 values less than (1000),
  4   partition p02 values less than (2000),
  5   partition p03 values less than (3000),
  6   partition p04 values less than (4000),
  7   partition p05 values less than (5000),
  8   partition p06 values less than (6000),
  9   partition p07 values less than (7000),
 10   partition p08 values less than (8000),
 11   partition p09 values less than (9000),
 12   partition p10 values less than (10000),
 13   partition p11 values less than (11000),
 14   partition p12 values less than (12000),
 15   partition p13 values less than (13000),
 16   partition p14 values less than (14000),
 17   partition p15 values less than (15000),
 18   partition p16 values less than (16000),
 19   partition p17 values less than (17000),
 20   partition p18 values less than (18000),
 21   partition p19 values less than (19000),
 22   partition p20 values less than (20000)
 23  )
 24  /

表已创建。

SQL> insert into t1 select rownum,lpad('x',200,'x') from dual connect by rownum<20000;

已创建19999行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建19999行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建39998行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建79996行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建159992行。

SQL> commit;

提交完成。

SQL> insert /*+ append */ into t1 select * from t1;

已创建319984行。

SQL> commit;

提交完成。

首先建立一个测试范围分区表，分区键列是"a"，共20个分区，在这个测试表中生成约64万行数据。下面在列a上建本地索引并收集统计信息：

SQL> create index t1_idx on t1(a) local;

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'T1',
    method_opt=>'for all columns size 1',cascade=>true);

PL/SQL 过程已成功完成。
SQL> @sosi

Please enter Name of Table Owner (Null = TEST):
Please enter Table Name to show Statistics for: t1

***********
Table Level
***********


Table                   Number                 Empty
Name                   of Rows   Blocks       Blocks
--------------- -------------- -------- ------------
T1                     639,968   18,880            0

Column                    Column                       Distinct            Number       Number
Name                      Details                        Values   Density Buckets        Nulls
------------------------- ------------------------ ------------ --------- ------- ------------
A                         NUMBER(22)                     19,999   .000050       1            0
B                         VARCHAR2(300)                       1  1.000000       1            0

                              B
Index                      Tree     Leaf       Distinct         Number      Cluster
Name            Unique    Level     Blks           Keys        of Rows       Factor
--------------- --------- ----- -------- -------------- -------------- ------------
T1_IDX          NONUNIQUE     1    1,390         19,999        639,968      639,968

Index           Column                     Col Column
Name            Name                       Pos Details
--------------- ------------------------- ---- ------------------------
T1_IDX          A                            1 NUMBER(22)

***************
Partition Level
***************

  Part Partition               Number                 Empty
   Pos Name                   of Rows   Blocks       Blocks
------ --------------- -------------- -------- ------------
     1 P01                     31,968      944            0
     2 P02                     32,000      944            0
     3 P03                     32,000      944            0
     4 P04                     32,000      944            0
     5 P05                     32,000      944            0
     6 P06                     32,000      944            0
     7 P07                     32,000      944            0
     8 P08                     32,000      944            0
     9 P09                     32,000      944            0
    10 P10                     32,000      944            0
    11 P11                     32,000      944            0
    12 P12                     32,000      944            0
    13 P13                     32,000      944            0
    14 P14                     32,000      944            0
    15 P15                     32,000      944            0
    16 P16                     32,000      944            0
    17 P17                     32,000      944            0
    18 P18                     32,000      944            0
    19 P19                     32,000      944            0
    20 P20                     32,000      944            0
                                    B
Index           Partition        Tree     Leaf       Distinct         Number
Name            Name            Level     Blks           Keys        of Rows
--------------- --------------- ----- -------- -------------- --------------
T1_IDX          P01                 1       67            999         31,968
T1_IDX          P02                 1       67          1,000         32,000
T1_IDX          P03                 1       67          1,000         32,000
T1_IDX          P04                 1       67          1,000         32,000
T1_IDX          P05                 1       67          1,000         32,000
T1_IDX          P06                 1       67          1,000         32,000
T1_IDX          P07                 1       67          1,000         32,000
T1_IDX          P08                 1       67          1,000         32,000
T1_IDX          P09                 1       67          1,000         32,000
T1_IDX          P10                 1       67          1,000         32,000
T1_IDX          P11                 1       72          1,000         32,000
T1_IDX          P12                 1       72          1,000         32,000
T1_IDX          P13                 1       72          1,000         32,000
T1_IDX          P14                 1       72          1,000         32,000
T1_IDX          P15                 1       72          1,000         32,000
T1_IDX          P16                 1       72          1,000         32,000
T1_IDX          P17                 1       72          1,000         32,000
T1_IDX          P18                 1       72          1,000         32,000
T1_IDX          P19                 1       72          1,000         32,000
T1_IDX          P20                 1       72          1,000         32,000

下面执行查询：

Read the rest of this entry

有客户遇到SQL性能不稳定，突然变差导致系统性能出现严重问题的情况。对于大型的系统来说，SQL性能不稳定，有时突然变差，这是常常遇到的问题。这也是一些DBA的挑战。

对于使用Oracle数据库的应用系统，有时会出现运行得好好的SQL，性能突然变差。特别是对于OLTP类型系统执行频繁的核心SQL，如果出现性能问题，通常会影响整个数据库的性能，进而影响整个系统的正常运行。对于个别的SQL，比如较少使用的查询报表之类的SQL，如果出现问题，通常只影响少部分功能模块，而不会影响整个系统。

那么应该怎么样保持SQL性能的稳定性？

SQL的性能变差，通常是在SQL语句重新进行了解析，解析时使用了错误的执行计划出现的。下列情况是SQL会重新解析的原因：

• 1. SQL语句没有使用绑定变量，这样SQL每次执行都要解析。
• 2. SQL长时间没有执行，被刷出SHARED POOL，再次执行时需要重新解析。
• 3. 在SQL引用的对象（表、视图等）上执行了DDL操作，甚至是结构发生了变化，比如建了一个索引。
• 4. 对SQL引用的对象进行了权限更改。
• 5. 重新分析（收集统计信息）了SQL引用的表和索引，或者表和索引统计信息被删除。
• 6. 修改了与性能相关的部分参数。
• 7. 刷新了共享池。
• 8. 当然重启数据库也会使所有SQL全部重新解析。

SQL重新解析后，跟以前相比，性能突然变差，通常是下列原因：

• 1. 表和索引的优化统计信息被删除，或者重新收集后统计信息不准确。重新收集统计信息通常是由于收集策略（方法）不正确引起。比如对分区表使用analyze命令而不是用dbms_stats包、收集统计信息时采样比例过小等等。Oracle优化器严重依赖于统计信息，如果统计信息有问题，则很容易导致SQL不能使用正确的执行计划。
• 2. SQL绑定变量窥探(bind peeking)，同时绑定变量对应的列上有直方图；或者绑定变量的值变化范围过大、分区数据分布极不均匀：
  • 1） 绑定变量的列上有直方图：
    假如表orders存储所有的订单，state列有3种不同的值：0表示未处理，1表示处理成功完成，2表示处理失败。State列上有一个索引，表中绝大部分数据的state列为1，0和2占少数。有下面的SQL：

    select * from orders where state=:b1
    

    这里:b1是变量，在大多数情况下这个值为0，则应该使用索引，但是如果SQL被重新解析，而第一次执行时应用传给变量b1值为1，则不会使用索引，采用全表扫描的方式来访问表。对于绑定变量的SQL，只在第一次执行时才会进行绑定变量窥探，并以此确定执行计划，该SQL后续执行时全部按这个执行计划。这样在后续执行时，b1变量传入的值为0的时候，仍然是第一次执行时产生的执行计划，即使用的是全表扫描，这样会导致性能很差。

  • 2） 绑定变量的值变化范围过大：
    同样假如orders表有一列created_date表示一笔订单的下单时间，orders表里面存储了最近1年的数据，有如下的SQL：

    Select * from orders where created_date >=:b1;
    

    假如大多数情况下，应用传入的b1变量值为最近几天内的日期值，那么SQL使用的是created_date列上的索引，而如果b1变量值为5个月之前的一个值，那么就会使用全表扫描。与上面描述的直方图引起的问题一样，如果SQL第1次执行时传入的变量值引起的是全表扫描，那么将该SQL后续执行时都使用了全表扫描，从而影响了性能。

  • 3） 分区数据量不均匀：
    对于范围和列表分区，可能存在各个分区之间数据量极不均匀的情况下。比如分区表orders按地区area进行了分区，P1分区只有几千行，而P2分区有200万行数据。同时假如有一列product_id，其上有一个本地分区索引，有如下的SQL：

    select * from orders where area=:b1 and product_id =:b2
    

    这条SQL由于有area条件，因此会使用分区排除。如果第1 次执行时应用传给b1变量的值正好落在P1分区上，很可能导致SQL采用全表扫描访问，如前面所描述的，导致SQL后续执行时全部使用了全表扫描。

• 3. 其他原因，比如表做了类似于MOVE操作之后，索引不可用，对索引进行了更改。当然这种情况是属于维护不当引起的问题，不在本文讨论的范围。

综上所述，SQL语句性能突然变差，主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况，而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。
为保持SQL性能或者说是执行计划的稳定性，需要从以下几个方面着手：

• 1. 规划好优化统计信息的收集策略。对于Oracle 10g来说，默认的策略能够满足大部分需求，但是默认的收集策略会过多地收集列上的直方图。由于绑定变量与直方图固有的矛盾，为保持性能稳定，对使用绑定变量的列，不收集列上的直方图；对的确需要收集直方图的列，在SQL中该列上的条件就不要用绑定变量。统计信息收集策略，可以考虑对大部分表，使用系统默认的收集策略，而对于有问题的，可以用DBMS_STATS.LOCK_STATS锁定表的统计信息，避免系统自动收集该表的统计信息，然后编写脚本来定制地收集表的统计信息。脚本中类似如下：

  exec dbms_stats.unlock_table_stats…
  exec dbms_stats.gather_table_stats…
  exec dbms_stats.lock_table_stats…
  

• 2. 修改SQL语句，使用HINT，使SQL语句按HINT指定的执行计划进行执行。这需要修改应用，同时需要逐条SQL语句进行，加上测试和发布，时间较长，成本较高，风险也较大。
• 3. 修改隐含参数” _optim_peek_user_binds”为FALSE，修改这个参数可能会引起性能问题（这里讨论的是稳定性问题）。
• 4. 使用OUTLINE。对于曾经出现过执行计划突然变差的SQL语句，可以使用OUTLINE来加固其执行计划。在10g中DBMS_OUTLN.CREATE_OUTLINE可以根据已有的执行正常的SQL游标来创建OUTLINE。如果事先对所有频繁执行的核心SQL使用OUTLINE加固执行计划，将最大可能地避免SQL语句性能突然变差。
  注：DBMS_OUTLN可以通过$ORACLE_HOME/rdbms/admin/dbmsol.sql脚本来安装。
• 5. 使用SQL Profile。SQL Profile是Oracle 10g之后的新功能，此处不再介绍，请参考相应的文档。

除此之外，可以调整一些参数避免潜在的问题，比如将"_btree_bitmap_plans"参数设置为FALSE（这个参数请参考互联网上的文章或Oracle文档）。

而在实际工作中，通过使用定制的统计信息收集策略，以及在部分系统上使用OUTLINE，系统基本上不会出现已有的SQL性能突然变差的情况。当然也有维护人员操作不当引起的SQL性能突然变差，比如建了某个索引而没有收集统计信息，导致SQL使用了新建的索引，而该索引并不适合于那条SQL；维护人员意外删除了表个索引的统计信息。