ORACLE柱状图与执行计划

布满荆棘的人生 2022-08-27 10:42 34阅读 0赞

本文从不绑定变量与绑定变量两种情况讨论柱状图的作用。

一、不绑定变量的情况：

考虑以下数据

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][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][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][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)

[suk@ORACLE9I][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][suk_ORACLE9I]> analyze table th compute statistics;

表已分析。

[suk@ORACLE9I][suk_ORACLE9I]> var o varchar2(20)

[suk@ORACLE9I][suk_ORACLE9I]> exec :o:='SYS'

PL/SQL 过程已成功完成。

[suk@ORACLE9I][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][suk_ORACLE9I]> exec :o:='SUK'

PL/SQL 过程已成功完成。

[suk@ORACLE9I][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][suk_ORACLE9I]> alter system flush shared\_pool;

系统已更改。

[suk@ORACLE9I][suk_ORACLE9I]> analyze table th delete statistics;

表已分析。

[suk@ORACLE9I][suk_ORACLE9I]> analyze table th compute statistics for table for all indexes for all indexed columns;

表已分析。

[suk@ORACLE9I][suk_ORACLE9I]> exec :o:='SYS'

PL/SQL 过程已成功完成。

[suk@ORACLE9I][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][suk_ORACLE9I]> alter system flush shared\_pool;

系统已更改。

[suk@ORACLE9I][suk_ORACLE9I]> exec :o:='SUK'

PL/SQL 过程已成功完成。

[suk@ORACLE9I][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 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 owner=: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查看需要的信息

[suk_ORACLE9I]: mailto:suk@ORACLE9I