ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 执行计划错误导致系统负载高

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

原创 Linux操作系统 作者:yantaicuiwei 时间:2011-11-28 22:00:26 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-15

  • 博文量
    64
  • 访问量
    237234