1. 首页
  2. IT资讯

柱状图与执行计划

简单探讨一下9i下柱状图和执行计划

不绑定变量的情况:

考虑以下数据

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

OWNER COUNT(1)—————————— ———-SUK 1SYS 36216SYSTEM 1

其中,在表的OWENR上建立有一个索引

做普通分析后,执行查询SQL> analyze table th compute statistics;

Table analyzed

suk@ORACLE9I> select * from th where owner=’SYS’;

已选择36216行。

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=’SUK’;

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=’SYS’;

已选择36216行。

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=’SUK’;

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=:o;

已选择32192行。

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=:o;

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=: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 owner=:o;

已选择32192行。

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=:o;

Execution Plan———————————————————- 0 SELECT STATEMENT Optimizer=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 owner=:o 当:o:=’SYS’时

call count cpu elapsed disk query current rows——- —— ——– ———- ———- ———- ———- ———-Parse 1 0.00 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 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: 1Optimizer goal: CHOOSEParsing user id: 23

Rows Row Source Operation——- ————————————————— 32192 TABLE ACCESS FULL TH

第一次执行select * from th where owner=:o 当:o:=’SUK’时

call count cpu elapsed disk query current rows——- —— ——– ———- ———- ———- ———- ———-Parse 1 0.01 0.01 0 31 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 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: 1Optimizer goal: CHOOSEParsing 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查看需要的信息

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195637.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code