(转)执行计划和直方图
来源:互联网 发布:webshell检测 python 编辑:程序博客网 时间:2024/06/05 10:03
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 信息统计方法介绍
在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 信息统计方法介绍
- (转)执行计划和直方图
- 执行计划和直方图
- oracle直方图和执行计划
- oracle直方图和执行计划
- 直方图统计导致错误的执行计划
- 直方图统计导致错误的执行计划
- Oracle执行计划——直方图的使用
- 一个执行计划异常变更的案例 - 外传之直方图
- 执行计划和执行顺序
- 关于sql和执行计划
- oracle 执行计划和执行顺序
- 第一章:执行计划基本知识--文本执行计划和XML执行计划
- 没有直方图的执行计划预估结果集行数计算公式
- 分析执行计划优化SQLORACLE的执行计划(转)
- 查看oracle执行计划 【转】
- 转:MySQL执行计划解读
- SQL Server 2005执行计划和实际计划命令
- SQL语句的执行计划和暗示
- android中加载assets中的资源文件
- spring 3 mvc中改变XXX-servlet的命名方式
- php中使用curl发送JSON数据
- mysql中的两种锁
- (转)基于fiddler来模拟限速
- (转)执行计划和直方图
- android开发之ClipboardManager
- HTML5 中的websocket已进入W3C的侯选标准行列
- 基调公司免费的一个不错的web页面性能监测工具
- 深入浅出 Cocoa 之多线程 NSThread
- JAVA ReentrantReadWriteLock AND 缓存系统
- 产品经理应该说NO
- REST with Spring series
- 泛型