现在的位置: 首页 > 数据库 > 正文

柱状图_SQL绑定_执行计__10046_autotrace

2019年08月03日 ⁄ 数据库 ⁄ 共 12171字 ⁄ 字号 小 中 大 ⁄ 评论关闭

可以得出如下结论：
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％的记录。
通常情况下在以下场合中建议使用直方图：
 （1）、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）

（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。

四、如何使用直方图：

（1）、创建直方图：

   通过使用早先的analyze命令和最新的dbms_stats工具包都可以创建直方图。Oracle推荐使用后者来创建直方图，而且直方图的创建不受任何条件限制，可以在一张表上的任何你想创建直方图的列上创建直方图。我们这里主要介绍如何通过dbms_stats包来创建直方图。
   Oracle 通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在 method_opt 子句中有三个相关选项，即 skewonly、repeat 和 auto。
  “skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL 优化器决定是使用索引还是全表扫描访问。示例如下：
        begin
      dbms_stats. gather_table_stats (
                         wnname => '',
                         tabname=>''，
                         estimate_percent =>dbms_stats.auto_sample_size,
                         method_opt       => 'for all columns size skewonly',
                         cascade=>true,
                          degree=> 7);
end;

     其中degree指定了并行度视主机的CPU个数而定，estimate_percent指定了采样比率，此处使用了auto目的是让oracle来决定采样收集的比率，绘制直方图时会根据采样的数据分析结果来绘制，当然也可以人为指定采样比率。如：estimate_percent=>20指定采样比率为20%，cascade=>true指定收集相关表的索引的统计信息，该参数默认为false，因此使用dbms_stats收集统计信息时抹人事部收集表的索引信息的。

     在对表实施监视 (alter table xxx monitoring;) 时使用 auto 选项，它基于数据的分布以及应用程序访问列的方式（例如由监视所确定的列上的负载）来创建直方图。示例如下：

begin
             dbms_stats.gather_ table _stats(
              ownname          => '',
              tabname          =>''，
              estimate_percent => dbms_stats.auto_sample_size,
              method_opt       => 'for all columns size auto',
              cascade          =>true，
              degree           => 7
           );
end;

     重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再生成新的直方图。示例如下：
             dbms_stats.gather_ table _stats(
              ownname           => '',
              tabname           =>''，
              estimate_percent  => dbms_stats.auto_sample_size,
              method_opt        => 'for all columns size repeat',
              cascade           =>true，
              degree            => 7
            );
end;

（2）、创建直方图的考虑因素：
 如果想为某一列创建直方图，示例如下：
           dbms_stats.gather_ table _stats(
            ownname            => '',
            tabname            =>''，
            estimate_percent   => dbms_stats.auto_sample_size,
            method_opt         => 'for columns size 10 列名',
            cascade            =>true，
            degree             => 7

);

end;

           其中size 10指定的是直方图所需的存储桶(bucket)数，所谓存储桶可以理解为存储数据的容器，这个容器会按照数据的分布将数据尽量平均到各个桶里，如一张表如果有6000条记录，那么每个桶中平均就会有600条记录，但这只是一个平均数，每个桶中的记录数并不会相等，它会将高频出现记录集中在某一些桶中，低频记录会存放在少量桶中，因此如果存储桶(bucket)数合适的增加就会减少高频记录使用的桶数，统计结果也会更加准确（可以避免被迫将低频记录存入高频桶中，影响优化器生成准确的执行计划）。所以我们最后得到的直方图信息的准确性就由两个数值决定，一个是BUCTET的个数，一个NUM_DISTINCT的个数。所以创建直方图时首先要正确地估计存储桶(bucket)数。默认情况时，Oracle的直方图会产生75个存储桶。可以把SIZE的值指定在1~254之间。
（3）、删除直方图信息：
     在oracle中要删除直方图信息就是设置bucket的数据为1，可以使用如下两个命令来实现：
   Analyze table compute statistics for table for columns id size 1；
   exec dbms_stats.gather_table_stats('user', 'table',cascade=>false, method_opt=>'for columns size 1');
五,Oracle直方图的种类：
        Oracle利用直方图来提高非均匀数据分布的选择率和技术的计算精度。但是实际上Oracle会采用另种不同的策略来生成直方图：其中一种是针对包含很少不同值的数据集；另一种是针对包含很多不同的数据集。Oracle会针对第一种情况生成频率直方图，针对第二种情况生成高度均衡直方图。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET = 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。由于满足BUCTET
= 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是HEIGHT BALANCED（高度平衡）直方图。在Oracle 10GR2之前如果使用dbms_stats包来创建直方图，那么如果指定需要创建的直方图的桶的数目与表的NUM_DISTINCT值相等，那么几乎无法创建出一个FREQUENCY（频率）直方图，此时为了得到频率直方图只能使用analyze命令的“for all columns size 表的NUM_DISTINCT值”，这在某种程度上来说是一个退步，但这个问题在Oracle
10GR2后被修正。但是如果列中有180 - 200个不同值时，还是无法创建FREQUENCY（频率）直方图.此时需要手工建立直方图,并写入数据字典方能完成FREQUENCY（频率）直方图的创建。

       对于含有较少的不同值而且数据分布又非常不均匀的数据集来说，创建FREQUENCY（频率）直方图将会更加合适，因为它不存在会将低频出现的记录存入高频桶中的情况，而HEIGHT BALANCED（高度平衡）直方图在存储桶(bucket)数分配不合理时就可能会出现这种情况。因此一定要在创建直方图前确定使用何种直方图，并且要合理估计存储桶(bucket)数。

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞