1. 示例说明直方图的作用。

初始化数据

dexter@STARTREK> select count(*) fromall_objects ;

COUNT(*)

----------

72642

dexter@STARTREK> create table tuning4_tabnologging as select * from all_objects ;

Table created.

dexter@STARTREK> select count(*) fromall_objects ;

COUNT(*)

----------

72643

dexter@STARTREK> create indexidx_tuning4_tab_owner on tuning4_tab (owner) ;

Index created.

dexter@STARTREK> @gather_tab

Enter value for tbname: tuning4_tab

PL/SQL procedure successfully completed.

在这里碰到了一个小问题，因为数据的倾斜比较严重，而且oracle数据库在执行gather_table_stats的时候没有收集owner列的统计信息，这里优化器选择了错误的执行计划。下面记录了完整的处理过程。

出现错误的执行计划

dexter@STARTREK> select* from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;

59253 rows selected.

Execution Plan

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

Plan hash value: 989038285

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

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

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

| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |

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

Predicate Information(identified by operation id):

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

3 - access("OWNER"='PUBLIC' OR"OWNER"='SYS')

Statistics

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

1 recursive calls

0 db block gets

9581 consistent gets

0 physical reads

0 redo size

6805858 bytes sent via SQL*Net to client

43970 bytes received via SQL*Net from client

3952 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

59253 rows processed

下面的输出可以看到，其实使用全表扫描的效率要高于indexrange scan。

dexter@STARTREK> select /*+full(tuning4_tab)*/ *from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;

59253 rows selected.

Execution Plan

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

Plan hash value: 1641193091

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

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

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

| 0 |SELECT STATEMENT | | 4687 | 443K| 290 (1)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 4687 | 443K| 290 (1)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')

Statistics

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

1 recursive calls

0 db block gets

4927 consistent gets

0 physical reads

0 redo size

3035580 bytes sent via SQL*Net toclient

43970 bytes received via SQL*Netfrom client

3952 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

59253 rows processed

dexter@STARTREK>

trace 一下

dexter@STARTREK> alter session settracefile_identifier=histogram ;

Session altered.