Oracle SQL优化之buffer read

我们在优化Oracle SQL时，CBO方面COST固然重要，但有时候也须参考buffer read（consistent gets或者db block gets），尤其是在面对复杂的执行计划时，这个值直接决定着SQL执行效率的高低。参见下面例子：
选择的mc$ma_action_result表在status字段选择性比较低。
[quote]SQL> select count(*) from MC$MA_ACTION_RESULT;

COUNT(*)
----------
240029

SQL> select count(distinct status) from MC$MA_ACTION_RESULT;

COUNT(DISTINCTSTATUS)
---------------------
7[/quote]
当status字段有索引时，执行计划为如下，可以看到consistent gets为895
[quote]SQL> set autot traceonly exp stat
SQL> select id,target_id,target_type,source_file_path from mc$ma_action_result
2 where status='DELETED' order by updated_at asc;

10707 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3391357726

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

| Id | Operation | Name | Rows | Bytes |
TempSpc| Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 10095 | 798K|
| 673 (1)| 00:00:09 |

| 1 | SORT ORDER BY | | 10095 | 798K|
2008K| 673 (1)| 00:00:09 |

| 2 | TABLE ACCESS BY INDEX ROWID| MC$MA_ACTION_RESULT | 10095 | 798K|
| 479 (1)| 00:00:06 |

|* 3 | INDEX RANGE SCAN | MC$MA_ACTION_RESULT_IDX | 10251 | |
| 29 (0)| 00:00:01 |

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


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

3 - access("STATUS"='DELETED')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
[color=red]895 consistent gets[/color]
0 physical reads
0 redo size
732057 bytes sent via SQL*Net to client
8335 bytes received via SQL*Net from client
715 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10707 rows processed[/quote]

当字段status无索引时，其consistent gets为5923

[quote]SQL> drop index mc$ma_action_result_idx;

Index dropped.

SQL> alter system flush buffer_cache;

System altered.

SQL> select id,target_id,target_type,source_file_path from mc$ma_action_result
2 where status='DELETED' order by updated_at asc;

10707 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 276506348

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

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 10095 | 798K| | 1478
(2)| 00:00:18 |

| 1 | SORT ORDER BY | | 10095 | 798K| 2008K| 1478
(2)| 00:00:18 |

|* 2 | TABLE ACCESS FULL| MC$MA_ACTION_RESULT | 10095 | 798K| | 1284
(2)| 00:00:16 |

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


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

2 - filter("STATUS"='DELETED')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
[color=red]5923 consistent gets[/color]
0 physical reads
0 redo size
732047 bytes sent via SQL*Net to client
8335 bytes received via SQL*Net from client
715 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10707 rows processed）[/quote]

从以上执行计划，我们可以判断如下：
在表格MC$MA_ACTION_RESULT DML操作比较少的前提下，在status字段上建立索引，SQL执行效率相对较高