ITPub博客

首页 > 应用开发 > IT综合 > 简单探讨一下10G下柱状图和执行计划

简单探讨一下10G下柱状图和执行计划

原创 IT综合 作者:jolly10 时间:2007-12-04 17:59:04 0 删除 编辑
1、对数据分布不均的情况下柱状图是很有效的
2、对数据分布不均匀的情况下,使用绑定变量可能会造成恶果,就算对表做了柱状图也一样
3、使用绑定变量,sql第一次执行决定了以后同样的sql执行的执行计划
4、AUTOTRACE的信息不一定准确,必要时要用10046查看需要的信息
[@more@]

简单探讨一下10G下柱状图和执行计划


不绑定变量的情况:

SQL> conn / as sysdba
已连接。
SQL> create table th as
2 select * From dba_objects;

表已创建。

SQL> update th set owner='SYS' where owner<>'SCOTT';

已更新49870行。

SQL> create index idx_th on th(owner);

索引已创建。

--在OWNER上建立一个索引

SQL> select owner,count(1) from th group by owner;

OWNER COUNT(1)
------------------------------ ----------
SCOTT 9
SYS 49870

--可以看到分布很不均称

SQL> analyze table th compute statistics;

表已分析。

--做普通分析后,执行查询

SQL> set autot traceonly;
SQL> select * from th where owner='SYS';

已选择49870行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3971 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed


SQL> select * from th where owner='SCOTT';

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SCOTT')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

只需返回9条数据,但做了全表扫描,这是不合理的执行计划。因为,它只是知道owner列有二个不同的值,但oracle不知道每个

不同的owner分别有多少记录,oracle默认为这些数据的分布是完全均匀的,所以,当用owner作条件时,oracle会认为会返回总

记录的二分之一(从执行计划中的rows=24940(49879/2)可以看出来)

对表TH生成柱状图后在做同样的查询

SQL> analyze table th compute statistics for table for all indexes for all indexed columns;

表已分析。

SQL> select * from th where owner='SYS';

已选择49870行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49870 | 4188K| 157 (4)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 49870 | 4188K| 157 (4)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3971 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> select * from th where owner='SCOTT';

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 807472438

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 774 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TH | 9 | 774 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TH | 9 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SCOTT')


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

可见,生成了柱状图后,oracle会根据数据的实际分布情况选择合适的执行计划,提高性能。


----------------------------------------------------------------------
下面看看在绑定变量的情况下,执行同样的操作,会发生什么事情

SQL> analyze table th compute statistics;

表已分析。

SQL> var o varchar2(20);
SQL> exec :o:='SYS';

PL/SQL 过程已成功完成。
SQL> select * from th where owner=:o;

已选择49870行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=:O)


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3971 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> exec :o:='SCOTT';

PL/SQL 过程已成功完成。

SQL> select * from th where owner=:o;

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2094K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2094K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=:O)


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

从以上测试可以看到,在绑定变量的情况下,如果没有分析柱状图,两个查询都使用了相同的执行计划--全表扫描。这也很容易

理解,在第一次解析SQL的时候,会根据:o的绑定的值去窥视表数据,因为oracle不知道连接列的数据的具体分布,所以它会以

为会返回二分之一的数据,所以选择了全表扫描。在以后的执行同样的SQL时会重用该SQL,都会使用第一次解析生成的执行计划

了。在本例中,由于没有做柱状图,索引第一次执行select * from th where owner=:o时,无论:0是'SYS'还是'SCOTT',都会

使用全表扫描。那么,我们是否可以得出这样的一个结论:如果对表做了柱状图,那么如果第一次硬解析SQL时:o的值是'SCOTT'

时,这个sql将会使用索引扫描;如果第一次硬解析时:o的值是'SYS'时,SQL将会使用全表扫描呢?看如下的测试例子:

SQL> alter system flush shared_pool;

系统已更改。

SQL> analyze table th delete statistics;

表已分析。

SQL> analyze table th compute statistics for table for all indexes for all indexed columns;

表已分析。

SQL> exec :o:='SYS'

PL/SQL 过程已成功完成。

SQL> select * from th where owner=:o;

已选择49870行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=:O)


统计信息
----------------------------------------------------------
244 recursive calls
0 db block gets
3995 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> alter system flush shared_pool;

系统已更改。

SQL> exec :o:='SCOTT';

PL/SQL 过程已成功完成。

SQL> select * from th where owner=:o;

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=:O)


统计信息
----------------------------------------------------------
446 recursive calls
0 db block gets
59 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
9 rows processed

从这个结果可以看到,分析了柱状图后,无论:o的值是'SYS'还是'SCOTT',第一次执行该sql时,使用的都是全表扫描,这与刚

才我们的推论不一致了,如果真是这样的话,使用绑定变量对表做柱状图还有什么意义呢?其实这应该算是ORACLE的一个BUG,

在这里AUTOTRACE的结果是不对的,我们可以用10046看


SQL> alter session set events '10046 trace name context forever, level 12';

会话已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> exec :o:='SYS'

PL/SQL 过程已成功完成。

SQL> select * from th where owner=:o;

已选择49870行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=:O)


统计信息
----------------------------------------------------------
462 recursive calls
0 db block gets
4024 consistent gets
0 physical reads
0 redo size
2237152 bytes sent via SQL*Net to client
36964 bytes received via SQL*Net from client
3326 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
49870 rows processed

SQL> exec :o:='SCOTT';

PL/SQL 过程已成功完成。

SQL> select * from th where owner=:o;

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 625254064

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24940 | 2313K| 156 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TH | 24940 | 2313K| 156 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=:O)


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1634 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> alter session set events '10046 trace name context off';

会话已更改。

下面看看TKPROF后的内容:

select *
from
th where owner=:o

当:o:='SYS'时


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3326 0.25 0.14 0 3971 0 49870
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3328 0.26 0.15 0 3971 0 49870

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
49870 TABLE ACCESS FULL TH (cr=3971 pr=0 pw=0 time=49941 us)


select *
from
th where owner=:o

当:o:='SCOTT'时

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 6 0 9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
9 TABLE ACCESS BY INDEX ROWID TH (cr=6 pr=0 pw=0 time=43 us)
9 INDEX RANGE SCAN IDX_TH (cr=3 pr=0 pw=0 time=119 us)(object id 51508)


从9i开始,oracle在对sql第一次硬解析时,会对绑定的变量值进行窥视,从而根据变量值和数据的分布决定sql的执行计划。从

以上的例子可以证明这点。


到此为止可以可以得出如下结论:
1、对数据分布不均的情况下柱状图是很有效的
2、对数据分布不均匀的情况下,使用绑定变量可能会造成恶果,就算对表做了柱状图也一样
3、使用绑定变量,sql第一次执行决定了以后同样的sql执行的执行计划
4、AUTOTRACE的信息不一定准确,必要时要用10046查看需要的信息

参考(http://space6212.itpub.net/post/12157/207731

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/271283/viewspace-988527/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-20

  • 博文量
    263
  • 访问量
    772991