sql执行计划错误之cache buffers chain

分享个小案例：
今天某个库出现了cache buffers chain，最近应用没啥变更，怎么会突然出现呢，当然latch:cache buffers chain的作用是db cache中Find data很重要的latch，不管逻辑读，物理读(也要经历逻辑读),如果link或者unlink一个buffer到不同的Hash Bucket，再或者pin，unpin一个buffer，都要获得相关bucket上相关的cache buffers chain latch。所以，关联到sql上，正如我们通常说的，调sql的一个目标就是减少资源的消耗，包括降低逻辑读，如果某个sql的读的块很多，那么和其它在访问相同数据的session就会争夺cache buffers chain latch(因为决定buffer被连接到那个bucket里面是由block的信息决定的,一个cache buffers chain latch会保护多个bucket，如果很多访问一个bucket里面的buffer，此时就会导致次latch的争用，也就是我们说的热块)所以，应用最近没啥变更，可以肯定是某些sql走错了执行计划。
我们收集统计信息是按照segment_size大于150M，并且每天的变化量超过20%的对象才会收集统计信息。所以对于有些对象没达到这个
收集的条件，统计信息可能是很久以前的或者是缺失,数据变化较大的时候，可能导致执行计划错误。

查看下当时ASH信息：

select sql_id,count(*)
from dba_hist_active_sess_history
where event='latch: cache buffers chains' and sample_time between
to_date('2012-08-02:16:00:00',‘YYYY-MM-DD:HH24:MI:SS') and to_date('2012-08-02:16:10:00',‘YYYY-MM-DD:HH24:MI:SS')
group by sql_id
order by 2 desc;

  SQL_ID	COUNT(*)
0a3zj3m5h72rb	6098
3xyq2hqdd3akj	24
9rt5b6s9ry50q	16

很明显，sql_id:0a3zj3m5h72rb嫌疑比较大,看看执行计划：
当前的执行计划：

SQL_ID 0a3zj3m5h72rb
--------------------
select M.LOG_ID,M.STATE,M.SESSION_ID,M.IP,M.LOGOUT_DATE,M.LOGIN_DATE,M.STAFF_COD
MAC ,M.ROWID as MROWID___  from SEC_LOGIN_LOG_201208 M where M.SESSION_ID = :1
order by  M.LOG_ID DESC
Plan hash value: 177413507
--------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| SEC_LOGIN_LOG_201208    |     1 |   146 |
|   2 |   INDEX FULL SCAN DESCENDING| PK_SEC_LOGIN_LOG_201208 |     1 |       |
--------------------------------------------------------------------------------

当然这个sql很简单，执行计划也很简单，我们先看下这个表上索引的信息：

INDEX_NAME                      BLEVEL COLUMN_NAME     COL_POS DISTINCT_KEYS   NUM_ROWS     FACTOR PAR LAST_ANALYZE
-------------------------   ---------- ----------------------- ------------- ---------- ---------- --- ------------
IDX_SEC_LOGIN_LOG2_201208       2      LOGOUT_DATE           1        106719     393830     376028 NO  01-AUG-12
IDX_SEC_LOGIN_LOG_201208_3      2      LOGIN_DATE            1        123557     496287     419906 NO  01-AUG-12
IDX_SEC_LOGIN_LOG1_201208       2      SESSION_ID            1        384049     384049     383848 NO  01-AUG-12
PK_SEC_LOGIN_LOG_201208         2      LOG_ID                1        496292     496292     451386 NO  01-AUG-12

看看表信息:

NAME                   ROW#       BLK#        AVG_ROW_LEN G_S USE SAMPLE_SIZE LAST_A
--------------         ---------- ----------  ----------- --- --- ----------- ----------
SEC_LOGIN_LOG_201208     496712   7804         111        YES  NO  56591      08-01-2012

看看列信息：

COL_NAME      DATA_TYPE       N  DISTINCT#      NULL#    DENSITY    BUCKETS G_S HISTOGRAM       SAMPLE_SIZE 
------------- --------------- - ---------- ---------- ---------- ---------- --- --------------- ----------- 
STATE         NUMBER(1,0)     Y          3          0 .333333333          1 YES NONE                   5705 
LOGOUT_DATE   DATE            Y      62654     101280 .000015961          1 YES NONE                  45052 
LOGIN_DATE    DATE            Y      94539          0 .000010578          1 YES NONE                  56591 
MAC           VARCHAR2(25)    Y          6     365396 .166666667          1 YES NONE                   1542 
IP            VARCHAR2(20)    Y      33242      83919 .000128916        254 YES HEIGHT BALANCED       47030 
STAFF_CODE    VARCHAR2(20)    Y      16950          0 .000199561        254 YES HEIGHT BALANCED        5705 
SESSION_ID    VARCHAR2(100)   Y     405087      91292 2.4686E-06          1 YES NONE                  46190 
LOG_ID        NUMBER(12,0)    N     496712          0 2.0132E-06          1 YES NONE                   5705 

都是这个order by desc使CBO倾向于走INDEX FULL SCAN DESCENDING(如果你有相关的知识，应该知道，index_fs会读取所有的索引块，当然这个读取时有序的,因为我们有order by desc，所以是从索引叶块的最右端，降序的来读)然后再回表。
很明显，走错了索引，正常的应该走IDX_SEC_LOGIN_LOG1_201208,1号的数据变化比较大，走错了索引.
看正确的执行计划：

SQL_ID 0a3zj3m5h72rb
--------------------
select M.LOG_ID,M.STATE,M.SESSION_ID,M.IP,M.LOGOUT_DATE,M.LOGIN_DATE,M.STAFF_COD
as MROWID___  from SEC_LOGIN_LOG_201208 M where M.SESSION_ID = :1  order by  M.L
Plan hash value: 1455286942
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |       |
|   1 |  SORT ORDER BY               |                           |     1 |   108
|   2 |   TABLE ACCESS BY INDEX ROWID| SEC_LOGIN_LOG_201208      |     1 |   108
|   3 |    INDEX RANGE SCAN          | IDX_SEC_LOGIN_LOG1_201208 |     1 |
--------------------------------------------------------------------------------

小小sql,其实平时即使遇到大的sql，复杂的，多表连接的，执行计划几百行的，都一样，收集相关的对象的信息是必不可少的。