可以得出如下结论:
1、无论是否绑定变量,对数据分布不均的情况下柱状图都是很有效的
2、对数据分布不均匀的情况下,使用绑定变量可能会造成恶果,就算对表做了柱状图也一样
3、使用绑定变量,sql第一次执行决定了以后同样的sql执行的执行计划
4、AUTOTRACE的信息不一定准确,必要时要用10046查看需要的信息
转自:http://database.e800.com.cn/articles/2008/312/1205262289188364594_1.html
本文从不绑定变量与绑定变量两种情况讨论柱状图的作用。
一、不绑定变量的情况:
考虑以下数据
SQL> select owner,count(1) from th group by owner;
OWNER COUNT(1)
------------------------------ ----------
SUK 1
SYS 36216
SYSTEM 1
其中,在表的OWENR上建立有一个索引
做普通分析后,执行查询
SQL> analyze table th compute statistics;
Table analyzed
suk@ORACLE9I> select * from th where wner='SYS';
已选择36216行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=50 Card=12073 Bytes=
881329)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=12073 Bytes=8813
29)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2894 consistent gets
0 physical reads
0 redo size
2045535 bytes sent via SQL*Net to client
27057 bytes received via SQL*Net from client
2416 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36216 rows processed
suk@ORACLE9I> select * from th where wner='SUK';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=50 Card=12073 Bytes=
881329)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=12073 Bytes=8813
29)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
513 consistent gets
0 physical reads
0 redo size
1133 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
只需返回一条数据,但做了全表扫描,这是不合理的执行计划。因为,它只是知道owner列有三个不同的值,但oracle不知道每个不同的owner分别有多少记录,oracle默认为这些数据的分布是完全均匀的,所以,当用owner作条件时,oracle会认为会返回总记录的三分之一(从执行计划中的Card=12073可以看出来)
对表TH生成柱状图后在做同样的查询
SQL> analyze table th compute statistics for table for all indexes for all indexed columns;
Table analyzed
suk@ORACLE9I> select * from th where wner='SYS';
已选择36216行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=50 Card=36216 Bytes=
2643768)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=50 Card=36216 Bytes=2643
768)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2894 consistent gets
0 physical reads
0 redo size
2045535 bytes sent via SQL*Net to client
27057 bytes received via SQL*Net from client
2416 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36216 rows processed
suk@ORACLE9I> select * from th where wner='SUK';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=73)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TH' (Cost=2 Card=1 Bytes
=73)
2 1 INDEX (RANGE SCAN) OF 'IDX_TH' (NON-UNIQUE) (Cost=1 Card
=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1133 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可见,生成了柱状图后,oracle会根据数据的实际分布情况选择合适的执行计划,提高性能。
----------------------------------------------------------------------
二、绑定变量的情况下
下面看看在绑定变量的情况下,执行同样的操作,会发生什么事情
suk@ORACLE9I> analyze table th compute statistics;
表已分析。
suk@ORACLE9I> var o varchar2(20)suk@ORACLE9I> exec :o:='SYS'
PL/SQL 过程已成功完成。
suk@ORACLE9I> select * from th where wner=:o;
已选择32192行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=75 Card=10731 Bytes=
783363)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=7833
63)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2886 consistent gets
0 physical reads
0 redo size
1818406 bytes sent via SQL*Net to client
24109 bytes received via SQL*Net from client
2148 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32192 rows processed
suk@ORACLE9I> exec :o:='SUK'
PL/SQL 过程已成功完成。
suk@ORACLE9I> select * from th where wner=:o;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=75 Card=10731 Bytes=
783363)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=7833
63)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
770 consistent gets
0 physical reads
0 redo size
1151 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从以上测试可以看到,在绑定变量的情况下,如果没有分析柱状图,两个查询都使用了相同的执行计划--全表扫描。这也很容易理解,在第一次解析SQL的时候,会根据:o的绑定的值去窥视表数据,因为oracle不知道连接列的数据的具体分布,所以它会以为会返回三分之一的数据,所以选择了全表扫描。在以后的执行同样的SQL时会重用该SQL,都会使用第一次解析生成的执行计划了。在本例中,由于没有做柱状图,索引第一次执行select * from th where wner=:o时,无论:0是'SYS'还是'SUK',都会使用全表扫描。那么,我们是否可以得出这样的一个结论:如果对表做了柱状图,那么如果第一次硬解析SQL时:o的值是'SUK'时,这个sql将会使用索引扫描;如果第一次硬解析时:o的值是'SYS'时,SQL将会使用全表扫描呢?看如下的测试例子:
suk@ORACLE9I> alter system flush shared_pool;
系统已更改。
suk@ORACLE9I> analyze table th delete statistics;
表已分析。
suk@ORACLE9I> analyze table th compute statistics for table for all indexes for all indexed columns;
表已分析。
suk@ORACLE9I> exec :o:='SYS'
PL/SQL 过程已成功完成。
suk@ORACLE9I> select * from th where wner=:o;
已选择32192行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=75 Card=10731 Bytes=
890673)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=8906
73)
Statistics
----------------------------------------------------------
271 recursive calls
0 db block gets
2900 consistent gets
0 physical reads
0 redo size
1818406 bytes sent via SQL*Net to client
24109 bytes received via SQL*Net from client
2148 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
32192 rows processed
suk@ORACLE9I> alter system flush shared_pool;
系统已更改。
suk@ORACLE9I> exec :o:='SUK'
PL/SQL 过程已成功完成。
suk@ORACLE9I> select * from th where wner=:o;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=75 Card=10731 Bytes=
890673)
1 0 TABLE ACCESS (FULL) OF 'TH' (Cost=75 Card=10731 Bytes=8906
73)
Statistics
----------------------------------------------------------
529 recursive calls
0 db block gets
51 consistent gets
0 physical reads
0 redo size
1151 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
从这个结果可以看到,分析了柱状图后,无论:o的值是'SYS'还是'SUK',第一次执行该sql时,使用的都是全表扫描,这与刚才我们的推论不一致了,如果真是这样的话,使用绑定变量对表做柱状图还有什么意义呢?其实这应该算是ORACLE的一个BUG,在这里AUTOTRACE的结果是不对的,我们可以用10046看
第一次执行
select *
from
th where wner=:o
当:o:='SYS'时
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2148 0.09 0.17 0 2886 0 32192
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2150 0.09 0.18 0 2886 0 32192
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Rows Row Source Operation
------- ---------------------------------------------------
32192 TABLE ACCESS FULL TH
第一次执行
select *
from
th where wner=:o
当:o:='SUK'时
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 31 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 35 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TH
1 INDEX RANGE SCAN IDX_TH (object id 7248)
从9i开始,oracle在对sql第一次硬解析时,会对绑定的变量值进行窥视,从而根据变量值和数据的分布决定sql的执行计划。从以上的例子可以证明这点。
到此为止可以可以得出如下结论:
1、无论是否绑定变量,对数据分布不均的情况下柱状图都是很有效的
2、对数据分布不均匀的情况下,使用绑定变量可能会造成恶果,就算对表做了柱状图也一样
3、使用绑定变量,sql第一次执行决定了以后同样的sql执行的执行计划
4、AUTOTRACE的信息不一定准确,必要时要用10046查看需要的信息
-------引用 http://space.itpub.net/22664653/viewspace-671681
Oracle直方图解析
一.何谓直方图:
直方图是一种统计学上的工具,并非Oracle专有。通常用于对被管理对象的某个方面的质量情况进行管理,通常情况下它会表现为一种几何图形表,这个图形表是根据从实际环境中所收集来的被管理对象某个方面的质量分布情况的数据所绘制成的,通常会画成以数量为底边,以频度为高度的一系列连接起来的矩形图,因此直方图在统计学上也称为质量分布图。比如下图所示,是一个以关学生化学考试成绩分数分布情况绘制的直方图:
二.Oracle中直方图的作用:
既然直方图是一种对被管理对象某一方面质量进行管理的描述工具,那么在Oracle中自然它也是对Oracle中某个对象质量的描述工具,这个对象就是Oracle中最重要的东西——“数据”。
在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少,以及出现的频率高低来绘制数据的分布情况,以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下,表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时,将出现这种情况,使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图,将会对优化器做出正确的选择发挥巨大的作用,使得SQL语句执行成本最低从而提升性能。
三.Oracle中使用直方图的场合:
在分析表或索引时,直方图用于记录数据的分布。通过获得该信息,基于成本的优
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划:例如,如果一到两个值构成了表中的大部分数据(数据偏斜),相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适,或何时应该根据WHERE子句中的值返回表中80%的记录。
通常情况下在以下场合中建议使用直方图:
(2)、当列值导致不正确的判断时:这种情况通常会发生在多表连接时,例如,假设我们有一个五项的表联接,其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集(集合基数)尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载,查询将会运行得更快。为了使中间结果最小化,优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断,它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。
四、如何使用直方图:
(1)、创建直方图:
end;
begin
end;
end;
(2)、创建直方图的考虑因素:
);
end;
(3)、删除直方图信息:
五,Oracle直方图的种类: