记一次oracle sql调优过程

 

    这里两天都在对一条sql进行调优。该sql并不复杂,类似于

    select ... from some_view

    union all

    select ... from some_table where datetime >= d1 and datetime< d2 and .... 

底层使用ibatis2.1.6 + oracle 10g。

    接到任务就像通常那样查看CBO生成的执行计划；一看就知道问题的所在--"Full table scan"（面对那样的多分区大表，这个选择实在太差），稍微修改一下查询条件选择了一个有global index的时间字段来替代；再在sql developer上试运行了一下，结果很满意--从原来的12s下降到了0.5s左右。ok，提交代码。本想着这就完事儿了，接下来出现了戏剧性的一幕：

引用一下我在twitter上的原话
“@JoardSpike
这该死的Oracle CBO，无论你使用sqlplus, Toad还是sql developer在targe DB上调试出多么完美的执行计划，一旦上了应用，一样让你蛋疼。”

出现了这样的话，想必就知道为啥了？无论怎么样通过app访问测试数据库，该sql和以前一样慢。God！新的执行计划还是全表扫描，但是在其他client里却能正确执行。

    一招不行来第二招，这种不一致的情况以前也有过，ok，把视图拿下换上原表，然后加上index hint。结果还是不行，这次虽然走索引了，但是用的却不是我“提示”的索引。通常招数不行了，来更狠的。
        重新收集表的统计信息。不行... :(
        重建索引。还是不行... wo kao
        备份数据，truncate源表，重建表，为了节省时间，随便还再次重建索引。依然不行....晕
        清空shared_pool，强制让CBO为该sql生成新的执行计划。仍然不行...
        劫持DBA，一同调试，也没有找到原因。
时间就这么过去了！！！

    当我对CBO一筹莫展之际，逐步把怀疑的目光移向了应用程序。首先，在ibatis里使用的map作为parameterClass，而且使用的绑定变量，难道会是这个原因？问题会出现在这种基本处理上？首先将变量中的时间改成格式化为字符串，并在sql中在显示进行类型转换“datetime >= to_date(d1, 'yyyy-MM-dd hh24:mi:ss')”。执行计划回归正常。这个过程总只花了很少的时间。
    从上述情况来看，很有可能ibatis在处理Date时未能正确经行转换，导致oracle对datetime字段进行了隐式类型转换，最终CBO未能使用该列的全局索引。
    当然，也有可能是oracle driver有问题？也许... ...

下班了咯！现在暂时就不深究了！
不过各位同学有没有类似经验？ibatis2里会出现这种基础问题？还请告之 嘿嘿  :)

------------顽强的分隔符------------
    其实这个绑定变量的潜在问题，Tom在《Oracle高效设》就提到过，需要同学们多注意，我这次就是忘记了这点，浪费了不少时间。但最主要的原因，也是我本次写blog要记录的问题--思维定势。就像以前总结的那样，CBO大部分情况下都是正确的，不要老是怀疑CBO有问题，先找找自己的问题，最后再来怀疑那些成熟的（开源）产品。

------------顽强的分隔符------------
    在啰嗦两句。对那些才接触oracle的同学，看看这两类写法在oracle上会有多大的性能差异：
    select * from (
        select * from table_gmail
        union all
        select * from table_gdoc
    ) t where name = 'google' order by ...
和
    select * from (
        select * from table_gmail where name = 'google'
        union all
        select * from table_gdoc where name = 'google'
    ) order by ...

 

------------顽强的分隔符------------

(updated at 2010-1-18)

今天花了些时间继续研究这个问题，导致该问题的原因的确是“导致oracle对datetime字段进行了隐式类型转换，最终CBO未能使用该列的全局索引”，不过问题不是出在ibatis上而是oracle driver。

 

我会再写一blog记录具体原因。

评论

14 楼 Spike 2010-02-04  

yongdi2 写道

Oracle调优类问题发在itpub上，几分钟内会有大牛来解决。
我发现很多开发和DBA，一有性能问题，很喜欢质疑数据库本身的能力，好像这样才显得有本事，假设Oracle，MS等设计的优化器都靠不住似的，实际以我的经验来看，它们出错的几率低到完全不必考虑。就好像本楼这个问题，date被隐形转换导致全表扫描，其实这在优化器看来是正常的不是么。和电脑不同，人总是想当然的以为什么什么就该这样，而电脑始终按实际情况办事。
80%的性能问题是因为糟糕的SQL，所以有问题应该更着眼于应用程序设计和SQL，PROC这些是否合理，也能更快速的解决问题

同意楼上的观点
CBO“正确地”输出了执行计划，而我缺“错误地”理解。
我之前也写过blog阐述过和你类似的观点
但是即便是记录过问题，却仍然出现了定势思维，这就是我记录这次调优过程的原因，提醒自己。

13 楼 yongdi2 2010-02-04  

Oracle调优类问题发在itpub上，几分钟内会有大牛来解决。
我发现很多开发和DBA，一有性能问题，很喜欢质疑数据库本身的能力，好像这样才显得有本事，假设Oracle，MS等设计的优化器都靠不住似的，实际以我的经验来看，它们出错的几率低到完全不必考虑。就好像本楼这个问题，date被隐形转换导致全表扫描，其实这在优化器看来是正常的不是么。和电脑不同，人总是想当然的以为什么什么就该这样，而电脑始终按实际情况办事。
80%的性能问题是因为糟糕的SQL，所以有问题应该更着眼于应用程序设计和SQL，PROC这些是否合理，也能更快速的解决问题

12 楼 强强爱妍妍 2010-01-30  

Spike 写道

强强爱妍妍 写道

可否把v$sql_text的最终执行的sql语句的条件部分发出来?

select SQL_TEXT , SQL_ID from v$sqltext
where SQL_TEXT  like '%KEY_WORD%';

:KEY_WORD是你想要查询的sql的关键字，例如特定的表名

我想要的是你这句sql的结果集,谢谢.

11 楼 Spike 2010-01-29  

强强爱妍妍 写道

可否把v$sql_text的最终执行的sql语句的条件部分发出来?

select SQL_TEXT , SQL_ID from v$sqltext
where SQL_TEXT  like '%KEY_WORD%';

:KEY_WORD是你想要查询的sql的关键字，例如特定的表名

10 楼 强强爱妍妍 2010-01-28  

Spike 写道

LucasLee 写道

LZ似乎有一个大问题：
你不该对ibatis的逻辑sql调试，而应该以ibatis生成、oracle最终收到的(物理)SQL来调。
这个还是能得到的，ibatis应该提供这样的debug信息，或者用oracle相应的功能，监控收到的sql日志。

可能是我没描述清楚

我并没有用“逻辑sql”调试，
我这个人比较懒，向来是使用v$sql_text查看最终的执行地sql语句

可否把v$sql_text的最终执行的sql语句的条件部分发出来?

9 楼 cwfmaker 2010-01-21  

Spike 写道

icefishc 写道

数据库没那么傻
在很多数据库中这两个是一样的。　都是先过滤在union

oracle在解析sql的时候就会“重写”这类sql，使之达到最好的情况

呵呵，貌似一般的数据库都有自动优化

8 楼 Spike 2010-01-21  

icefishc 写道

数据库没那么傻
在很多数据库中这两个是一样的。　都是先过滤在union

oracle在解析sql的时候就会“重写”这类sql，使之达到最好的情况

7 楼 Spike 2010-01-21  

<div class="quote_title">anranran 写道</div>
<div class="quote_div">Hibernate也有这个问题,参考我的文:http://www.cnblogs.com/anran_guojianjun/archive/2009/10/30/1593038.html</div>
<p><br><br>呵呵 我和兄台的情况类似<br>具体导致整个问题的根本原因我在<a href="http://joard.iteye.com/blog/573534">这个blog</a>记录一下</p>
<p> </p>

6 楼 Spike 2010-01-21  

LucasLee 写道

LZ似乎有一个大问题：
你不该对ibatis的逻辑sql调试，而应该以ibatis生成、oracle最终收到的(物理)SQL来调。
这个还是能得到的，ibatis应该提供这样的debug信息，或者用oracle相应的功能，监控收到的sql日志。

可能是我没描述清楚

我并没有用“逻辑sql”调试，
我这个人比较懒，向来是使用v$sql_text查看最终的执行地sql语句

5 楼 icefishc 2010-01-20  

JimmyWen 写道

   select * from (
        select * from table_gmail
        union all
        select * from table_gdoc
    ) t where name = 'google' order by ...
和
    select * from (
        select * from table_gmail where name = 'google'
        union all
        select * from table_gdoc where name = 'google'
    ) order by


这个肯定有差异的 ，不过差异多少得要看数据量了
第二个效率明显高，因为他是先把查询后的结果集再union
第一个是先union再过滤

数据库没那么傻
在很多数据库中这两个是一样的。　都是先过滤在union

4 楼 anranran 2010-01-20  

Hibernate也有这个问题,参考我的文:http://www.cnblogs.com/anran_guojianjun/archive/2009/10/30/1593038.html

3 楼 JimmyWen 2010-01-20  

   select * from (
        select * from table_gmail
        union all
        select * from table_gdoc
    ) t where name = 'google' order by ...
和
    select * from (
        select * from table_gmail where name = 'google'
        union all
        select * from table_gdoc where name = 'google'
    ) order by


这个肯定有差异的 ，不过差异多少得要看数据量了
第二个效率明显高，因为他是先把查询后的结果集再union
第一个是先union再过滤

2 楼 LucasLee 2010-01-20  

LZ似乎有一个大问题：
你不该对ibatis的逻辑sql调试，而应该以ibatis生成、oracle最终收到的(物理)SQL来调。
这个还是能得到的，ibatis应该提供这样的debug信息，或者用oracle相应的功能，监控收到的sql日志。

1 楼 rentianchou 2010-01-20  

完啦？