问题：
   2012年12月10日生产库出现了大量“cache buffer chain lath”等待事件，在定位问题后，通过收集对公查询结果缓存表的统计信息，问题得到解决。为了避免类似问题，在2012年12月10日晚8时，重新收集缓存表的统计信息、并锁定该统计信息，有望避免类似情况。

通过进一步分析验证，可以得到如下结论：
（1）针对挥发性表（数据量变化剧烈的表），在插入数据较少时收集统计数据，执行计划会选择全表扫描；如果在大量插入收据后，没有及时收集最新的统计数据，全表扫描可能导致整个系统性能下降，参考测试1。
（2）创建索引时会自动收集索引统计信息，而表上的统计信息并没有更新，在表数据量变化剧烈的情况下，表上的统计信息与索引的统计信息不一致也可能导致性能问题，参考测试3。

下面对相关概念验证，并给出统计信息收集策略建议。

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1,挥发性表的统计信息问题
挥发性表的统计信息很难及时更新，以反映数据的真实情况，可能导致错误的执行计划。
下面的测试，分以下三种情况收集统计信息，测试执行计划的选择：
（1）空表
（2）很少记录的表
（3）插入较多记录的表
测试结果表明,在插入数据较少时，执行计划会选择全表扫描；如果在大量插入收据后，没有及时收集最新的统计数据，全表扫描可能导致整个系统性能下降。

（1）空表
SQL>
SQL> drop table test;
 
Table dropped
SQL> create table test(x int,y char(100));
 
Table created
SQL> create index idx_test_x on test(x);
 
Index created
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
 
PL/SQL procedure successfully completed
SQL> select leaf_blocks,distinct_keys,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_indexes where index_name='IDX_TEST_X';
 
LEAF_BLOCKS DISTINCT_KEYS TO_CHAR(LAST_ANALYZED,'YYYYMMD
----------- ------------- ------------------------------
          0             0 20200810143337
SQL> select num_rows,blocks,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_tables where table_name='TEST';
 
  NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'YYYYMMD
---------- ---------- ------------------------------
         0          0 20200810143337
 
Commit complete
SQL> explain plan for select * from test where x=10;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 604183338
--------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   115 |     1   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     1 |   115 |     1   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_X |     1 |       |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"=10)
 
14 rows selected
 
SQL>

（2）很少记录的表
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bnt@DEV95
 
SQL>
SQL> drop table test;
 
Table dropped
SQL> create table test(x int,y char(100));
 
Table created
SQL> create index idx_test_x on test(x);
 
Index created
SQL> insert into test
  2  select mod(rownum,4),'a' from dba_objects where rownum<70;
 
69 rows inserted
SQL> commit;
 
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
 
PL/SQL procedure successfully completed
SQL> select leaf_blocks,distinct_keys,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_indexes where index_name='IDX_TEST_X';
 
LEAF_BLOCKS DISTINCT_KEYS TO_CHAR(LAST_ANALYZED,'YYYYMMD
----------- ------------- ------------------------------
          1             4 20200810143359
SQL> select num_rows,blocks,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_tables where table_name='TEST';
 
  NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'YYYYMMD
---------- ---------- ------------------------------
        69          5 20200810143359
Commit complete
SQL> explain plan for select y,sum(x) from test where x=2 group by y;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   103 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     1 |   103 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST |    17 |  1751 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("X"=2)
 
14 rows selected
 
（3）插入较多记录的表
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bnt@DEV95
 
SQL>
SQL> drop table test;
 
Table dropped
SQL> create table test(x int,y char(100));
 
Table created
SQL> create index idx_test_x on test(x);
 
Index created
SQL> insert into test
  2  select mod(rownum,4),'a' from dba_objects;
 
80139 rows inserted
SQL> commit;
 
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
 
PL/SQL procedure successfully completed
SQL> select leaf_blocks,distinct_keys,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_indexes where index_name='IDX_TEST_X';
 
LEAF_BLOCKS DISTINCT_KEYS TO_CHAR(LAST_ANALYZED,'YYYYMMD
----------- ------------- ------------------------------
        234             4 20200810143433
SQL> select num_rows,blocks,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_tables where table_name='TEST';
 
  NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'YYYYMMD
---------- ---------- ------------------------------
     80077       1252 20200810143433
 
Commit complete
SQL> explain plan for select * from test where x=10;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 604183338
--------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   103 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |     1 |   103 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_X |     1 |       |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"=10)
 
14 rows selected
 
SQL>

2，挥发性表的统计信息管理策略
对于挥发性表，可以采用收集典型数据量、锁定统计信息的方式解决。
注意事项：
（1）锁定统计信息会导致列的最大、最小值，列的数据分布锁定；而绑定变量可能随时间变化，如交易流水、日期等。需要确保相关sql不依赖于此类统计信息,因此在收集统计信息时不收集直方图。
（2）在后续针对该表的结构变更、sql查询，需要考虑统计信息锁定情况;必要时，需要重新收集统计信息并锁定。

具体操作:

begin
   dbms_stats.gather_table_stats(user,'TEST',method_opt=>'FOR ALL COLUMNS SIZE 1');
end;
/

begin
   dbms_stats.lock_table_stats(user,'TEST');
end;
/

验证：
SQL> select table_name,stattype_locked from user_tab_statistics where table_name='TEST';
 
TABLE_NAME                     STATTYPE_LOCKED
------------------------------ ---------------
TEST                           ALL

锁定后：
（1）针对该表的统计数据更新将失败；
（2）但针对多表的（GATHER_SCHEMA_STATS等）统计信息收集会跳过对该表的统计信息收集；
（3）可以lock当前或空的统计信息（以使用动态采样）；
（4）该表上的DDL操作不会该表统计信息。

3,表统计信息与索引统计信息不一致问题
创建索引时会自动收集索引统计信息，并不会更新表上的统计信息。这会导致表上的统计信息与索引的统计信息不一致，在表数据量变化剧烈的情况下，会导致严重的性能问题。

测试：
（1）创建测试表
SQL> drop table test;
 
Table dropped
SQL> create table test(x int);
 
Table created
SQL> insert into test
  2  select rownum from dba_objects where rownum<10;
 
9 rows inserted
SQL> commit;
 
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
 
PL/SQL procedure successfully completed
SQL> select num_rows,blocks,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_tables where table_name='TEST';
 
  NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'YYYYMMD
---------- ---------- ------------------------------
         9          5 20200810110053

（2）大量插入数据后创建索引
SQL>
SQL> insert into test
  2  select rownum from dba_objects;
 
80114 rows inserted
SQL> commit;
 
Commit complete
SQL> create index idx_test_x on test(x);
 
Index created

（3）表和索引统计不一致
SQL> select leaf_blocks,distinct_keys,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_indexes where index_name='IDX_TEST_X';
 
LEAF_BLOCKS DISTINCT_KEYS TO_CHAR(LAST_ANALYZED,'YYYYMMD
----------- ------------- ------------------------------
        178         80114 20200810111902
SQL> select num_rows,blocks,to_char(last_analyzed,'YYYYMMDDHH24MISS') from user_tables where table_name='TEST';
 
  NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZED,'YYYYMMD
---------- ---------- ------------------------------
         9          5 20200810110053
 

（4）执行计划选择了全表扫描，没有选择索引
SQL> explain plan for select * from test where x=10;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     3 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("X"=10)
 
13 rows selected

（5）重新收集表统计信息后，执行计划选择了索引

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'TEST');
 
PL/SQL procedure successfully completed
SQL> explain plan for select * from test where x=10;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 852839443
-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_TEST_X |     1 |     4 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("X"=10)
 
13 rows selected

结论：
对于变化剧烈的表，创建/重建索引时，应该重新收集表的统计信息。

4，有多少sql会被awr采样？
在故障分析中，已开始怀疑应用程序中新增SQL导致系统性能下降，理由是该sql_id没有在awr历史采样中出现过。
其实AWR数据只是采样明显消耗系统资源的sql，并且在存储到awr中时只采样部分数据。

比如，在以下系统中，当前sql area中有512个sql，而历史采样中仅372个。在繁忙系统中，仅有部分sql是大量消耗资源的，awr历史采样中保留的sql比例会更低。
SQL>
SQL> select count(distinct sql_id) from v$sql;
 
COUNT(DISTINCTSQL_ID)
---------------------
                  512
SQL> select count(distinct sql_id) from dba_hist_sqlstat;
 
COUNT(DISTINCTSQL_ID)
---------------------
                  372

5,缺省统计信息收集周期
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Connected as bnt@U4_22.188.31.102
 
SQL>
SQL> SELECT program_name,
  2         schedule_name,
  3         schedule_type,
  4         stop_on_window_close,
  5         comments
  6    FROM dba_scheduler_jobs
  7   WHERE job_name = 'GATHER_STATS_JOB';
 
PROGRAM_NAME           SCHEDULE_NAME             SCHEDULE_TYPE STOP_ON_WINDOW_CLOSE COMMENTS
---------------------- ------------------------- ------------- -------------------- --------------------------------------------------------------------------------
GATHER_STATS_PROG      MAINTENANCE_WINDOW_GROUP  WINDOW_GROUP  TRUE                 Oracle defined automatic optimizer statistics collection job
 
SQL>
SQL> select window_group_name,enabled,number_of_windows from dba_scheduler_window_groups;
 
WINDOW_GROUP_NAME              ENABLED NUMBER_OF_WINDOWS
------------------------------ ------- -----------------
MAINTENANCE_WINDOW_GROUP       TRUE                    2
 
SQL>
SQL> select * from dba_scheduler_wingroup_members;
 
WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ------------------------------
MAINTENANCE_WINDOW_GROUP       WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP       WEEKEND_WINDOW
 
SQL>
SQL> select window_name,repeat_interval,duration from dba_scheduler_windows;
 
WINDOW_NAME       REPEAT_INTERVAL                                                         DURATION
----------------- ----------------------------------------------------------------------- --------------
WEEKNIGHT_WINDOW  freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0   +000 08:00:00
WEEKEND_WINDOW    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                     +002 00:00:00

统计数据收集时间分布如下:
    +-------06----------------------22--+  统计数据收集时间(小时)
MON:+wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwcc+         2
TUE:+ccccccccwwwwwwwwwwwwwwwwwwwwwwwwwcc+         8
WED:+ccccccccwwwwwwwwwwwwwwwwwwwwwwwwwcc+         8
THU:+ccccccccwwwwwwwwwwwwwwwwwwwwwwwwwcc+         8
FRI:+ccccccccwwwwwwwwwwwwwwwwwwwwwwwwwcc+         8
SAT:+ccccccccccccccccccccccccccccccccccc+        24
SUN:+ccccccccccccccccccccccccccccccccccc+        24
上图中:
WORKING TIME:w
STATS COLLECT TIME:c

说明：
（1）缺省的收集周期长达：2+4*8+2*24=84小时，而每周的非收集时间为24*7-84=84小时
（2）收集统计信息会在窗口结束时停止（STOP_ON_WINDOW_CLOSE=true）
（3）周六早上00:00 AM开始时间与周五窗口结束时间重叠，STOP_ON_WINDOW_CLOSE=true是否有效尚不确定
（4）周五晚22:00开始的窗口长达50小时，可用于部分大表的数据收集