执行计划错误导致系统负载高

oracle: 11gr2

os : linux 5

接到监控人员报警，数据库服务器负载高，登陆机器查询

select session_id,sample_id,sample_time,event,sql_id,BLOCKING_SESSION from v$active_session_history 

where event is not null

and sample_time >=to_timestamp('2011-11-28 19:20:00.1','yyyy-mm-dd hh24:mi:ss.ff')

and sample_time <=to_timestamp('2011-11-28 19:50:00.1','yyyy-mm-dd hh24:mi:ss.ff') ;

SESSION_ID  SAMPLE_ID SAMPLE_TIME                    EVENT                          SQL_ID        BLOCKING_SESSION

---------- ---------- ------------------------------ ------------------------------ ------------- ----------------

         8    2687143 28-NOV-11 07.39.31.598 PM      latch: cache buffers chains    55skfxm2ufdbg

        17    2687143 28-NOV-11 07.39.31.598 PM      latch: cache buffers chains    55skfxm2ufdbg

        18    2687143 28-NOV-11 07.39.31.598 PM      latch: cache buffers chains    55skfxm2ufdbg

        19    2687143 28-NOV-11 07.39.31.598 PM      latch: cache buffers chains    55skfxm2ufdbg

。。。。。。。。。。。。。。。。

大量 latch: cache buffers chains 等待事件

找到对应的SQL

select sql_text from v$sqltext where sql_id='55skfxm2ufdbg' order by piece;

SQL_TEXT

----------------------------------------------------------------

SELECT DCNT, ENCNT FROM 

(SELECT COUNT(*) AS DCNT FROM T_ENTERPRISE_DESTMSISDN D WHERE D.EID = :B1 AND D.DESTMSISDN = :B2 ) D, 

(SELECT COUNT(*) AS ENCNT FROM T_ENTERPRISE_AMOUNT E WHERE E.EID = :B1 ) E

col column_name for a20

set lines 180

select index_name,table_name,column_name,column_position from dba_ind_columns where table_name='T_ENTERPRISE_DESTMSISDN';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME          COLUMN_POSITION

------------------------------ ------------------------------ -------------------- ---------------

IND_T_ENTERPRISE_DESTMSISDN    T_ENTERPRISE_DESTMSISDN        EDATE                              1

PK_T_ENTERPRISE_DESTMSISDN     T_ENTERPRISE_DESTMSISDN        EID                                1

PK_T_ENTERPRISE_DESTMSISDN     T_ENTERPRISE_DESTMSISDN        DESTMSISDN                         2

select index_name,table_name,column_name,column_position from dba_ind_columns where table_name='T_ENTERPRISE_AMOUNT';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME          COLUMN_POSITION

------------------------------ ------------------------------ -------------------- ---------------

PK_T_ENTERPRISE_AMOUNT         T_ENTERPRISE_AMOUNT            EID                                1

explain plan for

SELECT DCNT, ENCNT FROM 

(SELECT COUNT(*) AS DCNT FROM T_ENTERPRISE_DESTMSISDN D WHERE D.EID = :B1 AND D.DESTMSISDN = :B2 ) D, 

(SELECT COUNT(*) AS ENCNT FROM T_ENTERPRISE_AMOUNT E WHERE E.EID = :B1 ) E;

select * from table(dbms_xplan.display);

------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |                            |     1 |    26 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS           |                            |     1 |    26 |     3   (0)| 00:00:01 |

|   2 |   VIEW                  |                            |     1 |    13 |     2   (0)| 00:00:01 |

|   3 |    SORT AGGREGATE       |                            |     1 |    16 |            |          |

|*  4 |     INDEX FAST FULL SCAN| PK_T_ENTERPRISE_DESTMSISDN |     1 |    16 |     2   (0)| 00:00:01 |

|   5 |   VIEW                  |                            |     1 |    13 |     1   (0)| 00:00:01 |

|   6 |    SORT AGGREGATE       |                            |     1 |     4 |            |          |

|*  7 |     INDEX UNIQUE SCAN   | PK_T_ENTERPRISE_AMOUNT     |     1 |     4 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter("D"."EID"=1234 AND "D"."DESTMSISDN"='23434')

   7 - access("E"."EID"=1234)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      98876  consistent gets

          0  physical reads

          0  redo size

        590  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

T_ENTERPRISE_DESTMSISDN 表索引全扫描， EID  和 DESTMSISDN 为主键，应该是 INDEX UNIQUE SCAN才对，肯定是执行计划出了问题，导致逻辑读高

采用如下 解决方法：

1.  exec dbms_statS.GATHER_TABLE_STATS('USER_NESHUOKE','T_ENTERPRISE_DESTMSISDN');

2. 添加暗示 /*+ no_index_ffs */

SELECT DCNT, ENCNT FROM 

(SELECT /*+ no_index_ffs */ COUNT(*) AS DCNT FROM user_neshuoke.T_ENTERPRISE_DESTMSISDN D WHERE D.EID = :B1 AND D.DESTMSISDN = :B2 ) D, 

(SELECT COUNT(*) AS ENCNT FROM user_neshuoke.T_ENTERPRISE_AMOUNT E WHERE E.EID = :B1 ) E;

3. 表使用空间浪费严重，必要的时候进行move 表和rebuild 索引操作

优化后：

Execution Plan

----------------------------------------------------------

Plan hash value: 3996183545

---------------------------------------------------------------------------------------------------

| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                            |     1 |    26 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS        |                            |     1 |    26 |     3   (0)| 00:00:01 |

|   2 |   VIEW               |                            |     1 |    13 |     2   (0)| 00:00:01 |

|   3 |    SORT AGGREGATE    |                            |     1 |    16 |            |          |

|*  4 |     INDEX UNIQUE SCAN| PK_T_ENTERPRISE_DESTMSISDN |     1 |    16 |     2   (0)| 00:00:01 |

|   5 |   VIEW               |                            |     1 |    13 |     1   (0)| 00:00:01 |

|   6 |    SORT AGGREGATE    |                            |     1 |     4 |            |          |

|*  7 |     INDEX UNIQUE SCAN| PK_T_ENTERPRISE_AMOUNT     |     1 |     4 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("D"."EID"=1234 AND "D"."DESTMSISDN"='23434')

   7 - access("E"."EID"=1234)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

        590  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

执行计划正常。

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

转载于:http://blog.itpub.net/21601207/viewspace-712268/