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