sql优化 consistent gets与cost

Question

执行计划
----------------------------------------------------------
Plan hash value: 2241963146-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |     1 |   142 |    22   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                  |                          |     1 |   142 |            |       |
|   2 |   NESTED LOOPS                   |                          |     1 |   142 |    22   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                          |     1 |   111 |    20   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                          |     1 |    88 |    19   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                          |     3 |   168 |    11   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| WEB_APP_APPLICANT        |     3 |    99 |     5   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | PK_APP_APPLICANT_APPNME  |     3 |       |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN           | IDX_APP_INSURED_APPNO    |     1 |    23 |     2   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS BY INDEX ROWID | WEB_PAY_CONFIRM_INFO     |     1 |    32 |     3   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN           | IDX_PAYCONFIRMINFO_APPNO |     1 |       |     2   (0)| 00:00:01 |
|* 11 |     INDEX UNIQUE SCAN            | PK_WEB_APP_VHL           |     1 |    23 |     1   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID   | WEB_APP_BASE             |     1 |    31 |     2   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN            | PK_WEB_APP_BASE          |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   7 - access("APPAPPLICA2_"."C_APP_NME" LIKE '江苏万帮汽车有限公司%')
       filter("APPAPPLICA2_"."C_APP_NME" LIKE '江苏万帮汽车有限公司%')
   8 - access("APPINSURED1_"."C_APP_NO"="APPAPPLICA2_"."C_APP_NO")
   9 - filter("PAYCONFIRM0_"."C_REG_DPT_CDE"='0401' AND "PAYCONFIRM0_"."C_CHECK_STS"='00' AND
              ("PAYCONFIRM0_"."C_PROD_NO"='0320' OR "PAYCONFIRM0_"."C_PROD_NO"='0325' OR
              "PAYCONFIRM0_"."C_PROD_NO"='0326' OR "PAYCONFIRM0_"."C_PROD_NO"='0327' OR
              "PAYCONFIRM0_"."C_PROD_NO"='0329') AND "PAYCONFIRM0_"."C_CHECK_STS"<>'8')
  10 - access("PAYCONFIRM0_"."C_APP_NO"="APPINSURED1_"."C_APP_NO")
  11 - access("PAYCONFIRM0_"."C_APP_NO"="APPVHLVO4_"."C_APP_NO")
  12 - filter("APPBASEVO3_"."C_OPR_CDE"='910428')
  13 - access("APPAPPLICA2_"."C_APP_NO"="APPBASEVO3_"."C_APP_NO")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5267  consistent gets
        319  physical reads
        188  redo size
        335  bytes sent via SQL*Net to client
       1226  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed在进行sql解释计划分析的时候发现，有的sql consistent gets 很大，但是cost很小，就像上面的sql，这是什么原因呢？性能优化SQL

Answer 1

consistent gets 很大没有什么问题.如果physical reads 很大,才会有很大问题.consistent gets 你可以理解为从内存(SGA中的相关内存存储)取出来的数据,由于内存的存取速度非常快,因此这个大一点没有什么关系.physical reads 你可以理解为从内存中读取数据(物理读),这个因为反复读取硬盘信息,而现在的硬盘IO速度一直比较慢,因此经常会出现physical reads比较大的时候,硬盘灯是不断闪烁的.影响COST的一个比较重要的指标就是physical reads.