对一条基于分区的简单SQL的优化

今天遇到一个很奇怪的问题，在同一个数据库上，一条简单SQL访问同样的表，一个用常量，一个用变量，但执行计划看起来一样，返回相同的数据，性能差别很大，经过一番研究，终于发现原因。
下面通过实验模拟这个问题。

[@more@]由于我遇到问题的表是分区表，故在实验中也用分区表模拟。

drop table test purge;
create table test(id int, big_col char(2000))
partition by range(id)
(
partition p0 values less than(1),
partition p1 values less than(2),
partition p2 values less than(3),
partition p3 values less than(4),
partition p4 values less than(5),
partition p5 values less than(6),
partition p6 values less than(7),
partition p7 values less than(8),
partition p8 values less than(9),
partition p9 values less than(10)
)
;
insert into test select mod(rownum,10),'1' from dual connect by level <=10000;
commit;
analyze table test compute statistics for table;

以上SQL模拟10个分区的表，下面我们重现问题：
suk@ORA10G> alter system flush shared_pool;

系统已更改。

suk@ORA10G> var v number
suk@ORA10G> exec :v:=9

suk@ORA10G> select count(1) from test where id>1 and id>:v;


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 732 (1)| 00:00:09 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 25 | 325 | 732 (1)| 00:00:09 | 2 | 10 |
|* 3 | TABLE ACCESS FULL | TEST | 25 | 325 | 732 (1)| 00:00:09 | 2 | 10 |
--------------------------------------------------------------------------------------------------

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

3 - filter("ID">1 AND "ID">TO_NUMBER(:V))


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

suk@ORA10G> select count(1) from test where id>:v;


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 813 (1)| 00:00:10 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 500 | 6500 | 813 (1)| 00:00:10 | KEY | 10 |
|* 3 | TABLE ACCESS FULL | TEST | 500 | 6500 | 813 (1)| 00:00:10 | KEY | 10 |
--------------------------------------------------------------------------------------------------

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

3 - filter("ID">TO_NUMBER(:V))


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

suk@ORA10G> select count(1) from test where id>:v and id>1 ;


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 813 (1)| 00:00:10 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 25 | 325 | 813 (1)| 00:00:10 | KEY | 10 |
|* 3 | TABLE ACCESS FULL | TEST | 25 | 325 | 813 (1)| 00:00:10 | KEY | 10 |
--------------------------------------------------------------------------------------------------

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

3 - filter("ID">1 AND "ID">TO_NUMBER(:V))


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

这个SQL非常简单，甚至连表关联都没有，但是它却引发了很严重的性能问题。它之所以加上条件id>1是因为业务逻辑要求这个sql的id必须大于1，虽然绝大部分情况下输入的变量值也会大于1，但开发人员担心用户输入小于1的值，导致数据错误。
我刚拿到SQL的时候，直接在PL/SQL DEV中查看执行计划，计划看起来一模一样，想不通为什么性能会这么差，后来在SQLPLUS中查看执行计划，才发现其中的端倪。

首先我们看select count(1) from test where id>1 and id>:v，它的执行计划是范围分区扫描，但是我们看到pstart=2，pstop=10，那意味着虽然我们输入的变量是9，但这个SQL仍然要扫描9个分区。
也就是在生成执行计划的时候，用的是id>1这个条件而忽略id>:v这个更好的过滤条件。扫描太多不必要的分区从而导致性能很差。
从第一个SQL看，绑定变量窥视并不起作用。

再看第二个SQL：select count(1) from test where id>:v，这个SQL对应的pstart=key，pstop=10，这说明扫描的分区个数不确定，依输入的变量值而定。

最后看第三个SQL：select count(1) from test where id>:v and id>1，神奇发生了，它和第二个SQL只是条件的顺序发生了变化，但是它的性能要好很多，主要原因是pstart=key，pstop=10，我们这里输入的变量值是9，意味着它只需要扫描2个分区，效率和第二种情况一样。

现象貌似很奇怪，但实际上是可以解释的。首先这个表很简单，没有索引，所以优化器在选择执行计划时可选计划并不多。优化器认为where中的两个条件是平等的，这种情况下，它生成执行计划时并不考虑变量值(没有使用绑定变量窥视)，在RBO下，两个平等的条件会优先考虑靠后的条件，而在CBO下更好相反，会优先考虑更靠近where关键字的条件。
这些SQL都是基于CBO的，
第一个SQL，id>1靠近where关键字，因此优先考虑的是id>1，所以pstart=1
第三个SQL，id>:v靠近where关键字，因此优先考虑的是id>:v，所以pstart=key。

从这个结论可以推测，如果变量:v<=1，则第一个SQL比第三个SQL更高效，因为这种情况下它扫描更少分区，大家有兴趣的可以去验证一下。


这个文章重点是为了说明如何去分析这些问题，当你对一些优化的基本知识都有所了解时，看似很诡异的问题也就豁然开朗了。

最后，这个SQL的解决办法是把把条件id>:v放在靠近where的位置，而把id>1放在更后面的位置。

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

转载于:http://blog.itpub.net/231499/viewspace-1035616/