我们在优化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执行效率相对较高
选择的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执行效率相对较高