SQL的IOWAIT问题

fengbao

各位好！
   我查看dba_hist_sqlstat，采集了10天的9点-11点的SQL信息，发现其中一个节点SQL的iowait非常高。

SQL_ID        INSTANCE_NUMBER SUM(T.IOWAIT_DELTA) SUM(T.EXECUTIONS_DELTA)
------------- --------------- ------------------- -----------------------
1gt3t5gv3tmq2               2        154903504824                 4091039
2juvqp2bqu1pr               2        123185828197                 3673843
6073a1q0da6pf               2        104479762705                 3736366
4tdmpur34ymhk               2        100716087596                 2058651
7fwssyxa4uvpj               1         81836772882                       4
da67qrduc7gaw               2         80013877198                 1913559
cbvv8xrxu39tf               2         66428363722                 6104037
7fdmmuyu5grcq               2         62765294858                 2443320
0sfxjcchvgkyw               2         58341448937                 2223589

发现IOWAIT比较高的SQL语句为：
SELECT "A1"."PRD_INST_ID",
       "A1"."PRD_INST_ID",
       "A1"."ACCT_ID",
       "A1"."OWN_CUST_ID",
       "A1"."OFR_ID",
       "A1"."IF_PREPAY",
       "A1"."ACCT_ID",
       "A1"."ACCT_ID",
       "A2"."PAY_METHOD",
       "A2"."ACCT_ID",
       "A2"."EFF_STATE",
       "A3"."ACCT_ID",
       "A3"."PRD_INST_ID",
       "A4"."OFR_ID",
       "A4"."OFR_ID",
       "A4"."OFR_DETAIL_INST_REF_ID",
       "A4"."OFR_INST_ID",
       "A4"."OFR_DETAIL_TYPE_ID",
       "A5"."OFR_INST_ID",
       "A5"."OFR_INST_STAS_ID"
  FROM "CRM"."TB_PRD_PRD_INST_551"  "A1",
       "CRM"."TB_BIL_ACCT_551"      "A2",
       "CRM"."TB_PRD_PRD_INST_551"  "A3",
       "TB_PRD_OFR_DETAIL_INST_551" "A4",
       "CRM"."TB_PRD_OFR_INST_551"  "A5"
WHERE "A5"."OFR_INST_STAS_ID" = 1001
   AND "A4"."OFR_INST_ID" = "A5"."OFR_INST_ID"
   AND "A4"."OFR_DETAIL_TYPE_ID" = 'A1'
   AND "A3"."PRD_INST_ID" = "A4"."OFR_DETAIL_INST_REF_ID"
   AND "A1"."ACCT_ID" = "A3"."ACCT_ID"
   AND "A2"."EFF_STATE" = '10A'
   AND "A1"."ACCT_ID" = "A2"."ACCT_ID"
   AND "A1"."PRD_INST_ID" = :1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 78921940
--------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |     1
|   1 |  NESTED LOOPS                   |                               |     1
|   2 |   NESTED LOOPS                  |                               |     1
|   3 |    NESTED LOOPS                 |                               |     2
|   4 |     NESTED LOOPS                |                               |     1
|*  5 |      TABLE ACCESS BY INDEX ROWID| PROD_INST_551                 |     1
|*  6 |       INDEX UNIQUE SCAN         | UIDX_PROD_INST_ID_551         |     1
|*  7 |      TABLE ACCESS BY INDEX ROWID| ACCOUNT_551                   | 52503
|*  8 |       INDEX UNIQUE SCAN         | UIDX_ACCOUNT_551              |     1
|*  9 |     TABLE ACCESS BY INDEX ROWID | PROD_INST_551                 |     2
|* 10 |      INDEX RANGE SCAN           | IDX_PROD_INST_ACCT_ID_551     |     2
|* 11 |    TABLE ACCESS BY INDEX ROWID  | OFFER_PROD_INST_REL_551       |     1
|* 12 |     INDEX RANGE SCAN            | IDX_OFFER_PROD_REL_OBJ_ID_551 |     2
|* 13 |   TABLE ACCESS BY INDEX ROWID   | PROD_OFFER_INST_551           |     1
|* 14 |    INDEX UNIQUE SCAN            | UIDX_PROD_OFFER_INST_ID_551   |     1

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
        305  recursive calls
          0  db block gets
        291  consistent gets
          9  physical reads
          0  redo size
       2730  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         16  rows processed

SQL的执行效率挺好，但是iowait却非常高。

SQL> select
  2      idx.index_name,
  3      tab.table_name,
  4      tab.num_rows,
  5      tab.blocks,
  6      idx.clustering_factor
  7  from
  8      dba_indexes idx , dba_tables tab
  9  where idx.table_name = tab.table_name
10  and tab.table_name = 'OFFER_PROD_INST_REL_551'
11  and tab.owner = 'CRM20_INS'
12  and index_name like 'IDX_OFFER_PROD_INST_REL%'
13  order by table_name;

INDEX_NAME                     TABLE_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
IDX_OFFER_PROD_INST_REL551     OFFER_PROD_INST_REL_551          34675773     495088          30792733

查看其中一个索引的 clustering_factor 比较高，然后查看其它iowait比较高的SQL语句，发现均使用了此索引。

问题：
  我这个SQL的iowait高是否就是索引的clustering_factor导致的呢，如果是的话，如果证明是这个导致的呢？谢谢啦！

fengbao

其中绝大多数的db file sequential read等待的object，都是该表和该表的索引。
IDX_OFFER_PROD_INST_REL553 和 OFFER_PROD_INST_REL_553

fengbao

trace下面就3个SQL，都是查询的，其中等待事件如下：
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message from client                105635      123.64       1642.01
  db file sequential read                     56822        0.76        622.85
  SQL*Net message to client                  105635        0.00          0.13
  gc current block 2-way                      10969        0.05          9.19
  gc cr grant 2-way                           28683        0.07         18.45
  read by other session                        2424        0.10         20.90
  SQL*Net more data to client                  6287        0.00          0.34
  latch: cache buffers chains                   351        0.00          0.06
  gc current grant busy                           5        0.00          0.00
  gc cr block 2-way                              52        0.00          0.04
  gc buffer busy                               2700        0.09         17.09
  latch: KCL gc element parent latch            129        0.01          0.03
  latch free                                     86        0.02          0.14
  gc cr grant congested                           9        0.02          0.04
  gc current block congested                      6        0.00          0.01
  gc current grant 2-way                          7        0.00          0.00
  latch: shared pool                              7        0.02          0.10
  cursor: pin S                                 178        0.00          0.00
  latch: session allocation                       2        0.00          0.00
  latch: cache buffer handles                     2        0.00          0.00

Maclean Liu(刘相兵

单次执行只看到 9 单位的物理读，具体为什么IO wait高 建议你整合SQL与等待事件对应到具体的 object来确定