2014-07-15 8:09分rac4 断连分析

8:09分rac4 断连

--1、根据问题时间前后,取TOP SQL和WAIT EVENT
select t.inst_id,
       to_char(t.sample_time, 'yyyy-mm-dd hh24:mi:ss'),
       t.sql_id,
       t.event,
       count(*)
  from gv$active_session_history t
 where t.sample_time between
       To_Date('2014-07-15 08:00:00', 'yyyy-mm-dd hh24:mi:ss') and
       To_Date('2014-07-15 08:10:00', 'yyyy-mm-dd hh24:mi:ss') having
 count(*) > 20
 group by t.inst_id,
          to_char(t.sample_time, 'yyyy-mm-dd hh24:mi:ss'),
          t.sql_id,
          t.event
 order by to_char(t.sample_time, 'yyyy-mm-dd hh24:mi:ss'), t.inst_id

--确定问题SQL
g4wfg4j39cayy

select t.msisdn, t.stolenmsisdn, t.bookid, t.stealtime, t.distance
  from us_stealbook_record t
 where t.msisdn = :1
   and t.bookid = :2
   and t.stealtime >= :3 + :"SYS_B_0"
   and t.stealtime <= :4 + :"SYS_B_1"

latch: cache buffers chains

--2、根据等待事件和基本判断该sql出现了热块争夺，主要原因是瞬时访问表/数据块过多。
--检查一下执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g4wfg4j39cayy, child number 1
-------------------------------------
select t.msisdn,t.stolenmsisdn,t.bookid,t.stealtime,t.distance
        from us_stealbook_record t                  where t.msisdn = :1
                  and t.bookid = :2                   and t.stealtime
>= :3  + :"SYS_B_0"                   and t.stealtime <= :4 + :"SYS_B_1"

Plan hash value: 1487731263

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                     |       |       |     2 (100)|          |       |       |
|*  1 |  FILTER                   |                     |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|                     |     1 |    47 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL      | US_STEALBOOK_RECORD |     1 |    47 |     2   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:3+:SYS_B_0<=:4+:SYS_B_1)
   3 - filter(("T"."MSISDN"=:1 AND "T"."BOOKID"=:2 AND "T"."STEALTIME">=:3+:SYS_B_0 AND
              "T"."STEALTIME"<=:4+:SYS_B_1))

Note
-----
   - dynamic sampling used for this statement


29 rows selected.

--3、检查该表，发现在MSISDN、BOOKID字段上有一个复合索引，因此可能原因是表没有统计信息。检查USER_TABLE_STATICS确认该表是一个按天分区的表，由于过期的分区被清理掉了，因此该表目前缺少统计信息

--4、按分区逐个收集统计信息
BEGIN
    dbms_stats.gather_table_stats(
    ownname     => 'MREAD',
    tabname     => 'US_STEALBOOK_RECORD',
    partname    =>'P_20140723',
    estimate_percent => 10,
    method_opt        =>'FOR ALL COLUMNS SIZE 1',
    granularity        =>'ALL',
    cascade                =>TRUE);
END;
/

--5、更新完统计信息后，还需要清理shared_pool，刷新执行计划
--查看SQL信息
SELECT INST_ID,ADDRESS,HASH_VALUE,SQL_TEXT FROM GV$SQLAREA WHERE SQL_ID='g4wfg4j39cayy';

--批量获取刷新语句
SELECT INST_ID,'exec dbms_shared_pool.purge('''||ADDRESS||','||HASH_VALUE||''''||',''C'');' FROM GV$SQLAREA WHERE SQL_id='g4wfg4j39cayy';

1    exec dbms_shared_pool.purge('C0000018637B88C0,1184246750','C');
2    exec dbms_shared_pool.purge('C0000018FE0F8B18,1184246750','C');
3    exec dbms_shared_pool.purge('C0000018DF042F48,1184246750','C');
4    exec dbms_shared_pool.purge('C0000018EF0B6E70,1184246750','C');
5    exec dbms_shared_pool.purge('C00000189EFDEBD0,1184246750','C');

--在5个RAC节点上分别刷新后，再次检查执行计划

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g4wfg4j39cayy, child number 1
-------------------------------------
select t.msisdn,t.stolenmsisdn,t.bookid,t.stealtime,t.distance
        from us_stealbook_record t                  where t.msisdn = :1
                  and t.bookid = :2                   and t.stealtime
>= :3  + :"SYS_B_0"                   and t.stealtime <= :4 + :"SYS_B_1"

Plan hash value: 1929408122

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |       |       |     1 (100)|          |       |       |
|*  1 |  FILTER                             |                          |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR          |                          |     1 |    47 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| US_STEALBOOK_RECORD      |     1 |    47 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN                | IDX_RECORD_MSISDN_BOOKID |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:3+:SYS_B_0<=:4+:SYS_B_1)
   3 - filter(("T"."STEALTIME">=:3+:SYS_B_0 AND "T"."STEALTIME"<=:4+:SYS_B_1))
   4 - access("T"."MSISDN"=:1 AND "T"."BOOKID"=:2)


26 rows selected.

结论：对于按日期进行分区，由于分区有生命周期，可能会出现统计信息丢失而导致执行计划变化，需要考虑定期收集统计信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/692830/viewspace-1218844/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/692830/viewspace-1218844/