ORACLE统计信息收集不及时优化案例一则

                           ORACLE统计信息收集不及时优化案例一则

      某业务系统有表T1,id是主键,用户id和下单日期等三个主要栏位.分别在userid和order_date上存在索引.

create table t1(id number,userid number,order_date date, primary key (id));

create index ix_order_date on t1(order_date);

create index ix_userid on t1(userid);

 前端的主查询方式为根据用户id查当前时间的24小时以内的数据.

select *
 from t1
 where userid=:1
 and order_date >=trunc(sysdate)
 and order_date <=trunc(sysdate)+1

数据的特征是，单个用户平均在1000条记录左右，而每天产生的数据量大概在2-3万条,每个用户24小时内产生数据大概1-10条
 .统计信息使用提oracle默认的收集策略,每天凌晨4点钟收集；

优化器的主要参数如下;

DONGDONGTANG> show parameter optimizer;
 
 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 optimizer_capture_sql_plan_baselines boolean     FALSE
 optimizer_dynamic_sampling           integer     2
 optimizer_features_enable            string      11.1.0.7
 optimizer_index_caching              integer     0
 optimizer_index_cost_adj             integer     100
 optimizer_mode                       string      ALL_ROWS
 optimizer_secure_view_merging        boolean     TRUE
 optimizer_use_invisible_indexes      boolean     FALSE
 optimizer_use_pending_statistics     boolean     FALSE
 optimizer_use_sql_plan_baselines     boolean     TRUE

 从数据特征描术来看，业务高峰时大部分可能走userid的索引性能更加稳定一些.

但是当系统第一次收集完成统计信息以后，走的是ix_order_date的索引

DONGDONGTANG>select count(id)
 from t1
 where 1=1
 and userid=6775
 and order_date >=trunc(sysdate)
 and order_date <=trunc(sysdate)+1  2    3    4    5    6  ;

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 2961821412

-----------------------------------------------------------------------------------------------
 | Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT       |        |     1 |    12 |    18 (0)| 00:00:01 |
 |   1 |  SORT AGGREGATE        |        |     1 |    12 |     |       |
 |*  2 |   FILTER        |        |       |       |     |       |
 |*  3 |    TABLE ACCESS BY INDEX ROWID| T1       |     1 |    12 |    18 (0)| 00:00:01 |
 |*  4 |     INDEX RANGE SCAN       | IX_ORDER_DATE |    57 |       |     3 (0)| 00:00:01 |
 -----------------------------------------------------------------------------------------------

由于在06:00收集后，这一段时间产生的数据比较少，对比order_date评估是57行，而userid为6775的值有2418行数据
 所以优化器选择了orader_date列的索引．

随着到业务高峰期的到来，在06:00-12:00这段时间内新增了1万条数据左右.而且因为绑定变量(没有启用ACS))的原因，
 这个时候再走order_date上索引变得很不合适了.　

 DONGDONGTANG>select /*+ GATHER_PLAN_STATISTICS */ count(id)
 from t1
 where 1=1
 and userid=6775
 and order_date >=trunc(sysdate)
 and order_date <=trunc(sysdate)+1

 COUNT(ID)
 ----------
   1

DONGDONGTANG>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

 ---------------------------------------------------------------------------------------------------------
 | Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
 ---------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

|   0 | SELECT STATEMENT       |        |      1 | |      1 |00:00:00.02 |     105 |
 |   1 |  SORT AGGREGATE        |        |      1 |      1 |      1 |00:00:00.02 |     105 |
 |*  2 |   FILTER        |        |      1 | |      1 |00:00:00.02 |     105 |
 |*  3 |    TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |      1 |00:00:00.02 |     105 |
 |*  4 |     INDEX RANGE SCAN       | IX_ORDER_DATE |      1 |     57 |  10300 |00:00:00.01 |      59 |
 ---------------------------------------------------------------------------------------------------------

通过对比E_ROWS和A_ROWS的栏位可以发现，优化器的评估信息已经和现实的中数据已经差别非常大了.

强制优化器使用ix_userid索引
 select /*+ GATHER_PLAN_STATISTICS INDEX(T1 IX_USERID) */ count(id)
 from t1
 where 1=1
 and userid=6775
 and order_date >=trunc(sysdate)
 and order_date <=trunc(sysdate)+1

-----------------------------------------------------------------------------------------------------
 | Id  | Operation        | Name   | Starts | E-Rows | A-Rows |  A-Time   | Buffers |
 -----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT       |    |  2 |     |    2 |00:00:00.01 |  36 |
 |   1 |  SORT AGGREGATE        |    |  2 |   1 |    2 |00:00:00.01 |  36 |
 |*  2 |   FILTER        |    |  2 |     |    2 |00:00:00.01 |  36 |
 |*  3 |    TABLE ACCESS BY INDEX ROWID| T1   |  2 |   1 |    2 |00:00:00.01 |  36 |
 |*  4 |     INDEX RANGE SCAN       | IX_USERID |  2 | 377 | 2840 |00:00:00.01 |  22 |
 -----------------------------------------------------------------------------------------------------

而使用userid索引栏位，评估相对接近,所以优化器在后续的过程中继续使用ix_order_date索引不合适
 (即使order_date的cluster factor相比userid已经比较低)

 由于对userid的order_date两个栏位比较少更新，我们决定使用复合索引的方式(userid+order_date)或是(order_date+userid).
 创建复合索引根据选择性最高的栏位排在最前的原则，我们认为userid的选择性相对高，所以先把userid放在前面

DONGDONGTANG>create index ix_u_o on t1(userid,order_date);

Index created.

Plan hash value: 1669135283

-------------------------------------------------------------------------------------------------
 | Id  | Operation       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
 -------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT      |       |      1 | |      1 |00:00:00.01 |       5 |
 |*  1 |  FILTER        |       |      1 | |      1 |00:00:00.01 |       5 |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |      1 |      1 |00:00:00.01 |       5 |
 |*  3 |    INDEX RANGE SCAN      | IX_U_O |      1 |      1 |      1 |00:00:00.01 |       4 |
 -------------------------------------------------------------------------------------------------

这个时候优化器对评估的行接近于等实际的行，所以这个计划比较合理.

如果我们选择把order_date放在前面呢，来看看执行计划是怎么样的

DONGDONGTANG>drop index ix_u_o;

Index dropped.

DONGDONGTANG>create index ix_o_u on t1(order_date,userid);

Index created.

Plan hash value: 445006054

-------------------------------------------------------------------------------------------------
 | Id  | Operation       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
 -------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT      |       |      1 | |      1 |00:00:00.01 |      38 |
 |*  1 |  FILTER        |       |      1 | |      1 |00:00:00.01 |      38 |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |      1 |      1 |00:00:00.01 |      38 |
 |*  3 |    INDEX RANGE SCAN      | IX_O_U |      1 |      1 |      1 |00:00:00.01 |      37 |
 -------------------------------------------------------------------------------------------------

 但是在这里我们发现使用order_date+userid的复合索引的buffers读取数要高，达到了36,相比(userid,order_date)
 只有5,高出了将近7倍.所以采用userid+order_date的复合索引相对来说资源开销要少.

        最后我们认为在这其中有以下的总结要点:1,如果频繁手动收集该表的统计信息可能会有好处，但是考虑到该表是
 主要业务表之一，重新收集再重新对SQL进行硬解析，可能会带来较大的稳定性风险;2,在原来的userid索引后面
 增加一列order_date列变成复合索引，仅会带来很小的空间开销，原有的order_date索引继续保持不变;3,使用多个
 栏位可以让oracle评估出更加精确的行信息;4,不同时间段好的执行计划是不同的(ACS根本解决?)