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);

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

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

| 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

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

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

| 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

執行計劃正常。

執行計劃錯誤導致系統負載高

相關文章