最近发现某条sql一执行时系统read IO很高，而且db buffer hit%很低

在该sql执行前 db buffer hit%都是99%以上，该sql 一执行就变为80%

sql如下：

SELECT sum(qty) FROM smp.WIP_RUNCARD
WHERE Org_ID =51 and Enabled='Y' and Turn_in_ord_no='37355303';

Execution Plan
----------------------------------------------------------
Plan hash value: 2195165714

--------------------------------------------------------------------------------------------------
| Id | Operation                    | Name              | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    15 | 5868   (1)| 00:01:11 |
|   1 | SORT AGGREGATE              |                   |     1 |    15 |            |          |
|* 2 |   TABLE ACCESS BY INDEX ROWID| WIP_RUNCARD       | 13554 |   198K| 5868   (1)| 00:01:11 |
|* 3 |    INDEX RANGE SCAN          | WIP_RUNCARD_INX06 | 13555 |       |    46   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

2 - filter("ENABLED"='Y')
3 - access("TURN_IN_ORD_NO"='37355303' AND "ORG_ID"=51)

利用oradebug event 10046 trace name context forever,level 8

发现该sql执行时需要读取很多数据块

db file parallel read

db file parallel read 很多

猜想是通过index 去读取data 中的qty时，消耗的时间太多而且数据分散的很厉害

修改方式
create index wip_runcard_ind_test on wip_runcard(org_id,enabled,TURN_IN_ORD_NO,qty) parallel 2;

删除原本的index，重新创建，使得数据仅需从index中即可获取，不需要再去读取table data的数据

SQL> SELECT sum(qty) FROM smp.WIP_RUNCARD
WHERE Org_ID =51 and Enabled='Y' and Turn_in_ord_no='37355303'; 2

SUM(QTY)
----------
23408

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 883025211

------------------------------------------------------------------------------------------
| Id | Operation         | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     1 |    16 |    55   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |                      |     1 |    16 |            |          |
|* 2 |   INDEX RANGE SCAN| WIP_RUNCARD_IND_TEST | 13883 |   216K|    55   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

2 - access("ORG_ID"=51 AND "ENABLED"='Y' AND "TURN_IN_ORD_NO"='37355303')

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
         95 consistent gets
         93 physical reads
          0 redo size
        517 bytes sent via SQL*Net to client
        492 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

删除index wip_runcard_inx99
create index wip_runcard_indx_99 on wip_runcard(org_id,enabled,TURN_IN_ORD_NO,qty) parallel 2;

ITPub博客

创建组合index 减少table data读取

最新文章