(转)执行计划和直方图

http://aliyundba.aliapp.com/?p=292#more-292
在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。
根据dba_objects创建一个倾斜列的表。并在严重倾斜的列上创建索引
YANG@yangdb-rac3> create table bind as select * from dba_objects;
Table created.
YANG@yangdb-rac3> update bind set status='INVALID' WHERE WNER='SCOTT';
6 rows updated.
YANG@yangdb-rac3> create index bind_idx on bind(status);
Index created.
收集表和索引的信息。
YANG@yangdb-rac3> exec dbms_stats.gather_table_stats(user,'BIND',cascade=>true);
PL/SQL procedure successfully completed.
YANG@yangdb-rac3> set autot trace exp
查看其执行计划，发现执行计划并没有走索引而是全表扫描
YANG@yangdb-rac3> select owner from bind where status='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3586145581
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36374 | 461K| 291 (1)| 00:00:04 |
|* 1 |TABLE ACCESS FULL| BIND | 36374 | 461K| 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='INVALID')
为索引列的两个值创建直方图。
YANG@yangdb-rac3>EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'BIND',method_opt => 'FOR ALL INDEXED COLUMNS SIZE 2');
PL/SQL procedure successfully completed.
YANG@yangdb-rac3> select owner from bind where status='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 4106465825
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 169 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIND | 13 | 169 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIND_IDX | 13 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='INVALID')

但是当对于收集直方图的列在sql 语句where 中使用绑定变量的时候：执行计划改变了！没有选择索引而是全表扫描。
YANG@yangdb-rac3>variable val varchar2(10);
YANG@yangdb-rac3>exec :val :='VALID';
PL/SQL procedure successfully completed.
YANG@yangdb-rac3>select owner from bind where status= :val;
Execution Plan
----------------------------------------------------------
Plan hash value: 3586145581
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36374 | 461K| 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| BIND | 36374 | 461K| 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=:VAL)
YANG@yangdb-rac3>exec :val :='INVALID';
PL/SQL procedure successfully completed.
YANG@yangdb-rac3>
YANG@yangdb-rac3>select owner from bind where status= :val;
Execution Plan
----------------------------------------------------------
Plan hash value: 3586145581
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36374 | 461K| 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| BIND | 36374 | 461K| 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter("STATUS"=:VAL)
YANG@yangdb-rac3>alter system flush shared_pool;
System altered.
避免bind 变量，第一次执行时使用 INVALID
YANG@yangdb-rac3> variable val varchar2(10);
YANG@yangdb-rac3> exec :val :='INVALID';
PL/SQL procedure successfully completed.
YANG@yangdb-rac3> select owner from bind where status= :val;
Execution Plan
----------------------------------------------------------
Plan hash value: 3586145581
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36374 | 461K| 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| BIND | 36374 | 461K| 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"=:VAL)
YANG@yangdb-rac3> select owner from bind where status= 'INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 4106465825
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 169 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIND | 13 | 169 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | BIND_IDX | 13 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='INVALID')
其实直方图具有一下几个使用限制
1 all predicates on the column use bind variables
2 the column data is uniformly distributed
3 the column is not used in WHERE clauses of queries
4 the column is unique and is used only with equality predicates
当sql 语句遇到上述情况，收集直方图信息是无效的。
参考文章：
oracle直方图解析
oracle 信息统计方法介绍