如何解决不同SQL之间的性能冲突？

ligengocp

我曾经在做数据仓库（EXPRESS）加载的时候，也遇到过比较大，比较多的查询，整套查询执行完毕需要24小时左右，而且还总会报sga不够，cursor不够，或者一些内核错误。
后来也发过贴问过，见：
http://www.itpub.net/showthread.php?s=&threadid=2095

我把当时写的心得如下：
“如果表没有做ANAYISE，然后optimizer_mode = choose，则会选择RULE方式优化SQL，会导致SQL的执行路径需要大量的SHARE_POOL_SIZE(The RULE optimizer has generated a SQL statement that has exhausted the maximum available cursor memory area.  This usually results in an ORA-1037.  )”

后来解决方法关键是：
optimizer_mode由choose改为fire_rows，在ORACLE执行SQL之间自动强制对表进行analysis，并自动使用COST方式执行SQL；
这才得以让SQL顺利执行，但速度却没有做过多大的调整。

顺便问一下，Open_cursor所设置的cursor数，是不是使用share_poor_size的空间？

supersea

最初由 olive 发布
把analyze删掉，就恢复正常。
[/B]

如何删掉analyze呢？是把相应的表清空？谢谢！

biti_rainy

说明你的不同查询中执行计划的要求具有不一致性？

这个可能当初做程序的时候没有考虑周全
所以，对于偶尔执行的大查询
加hints（而不是analyze）是一个比较安全的做法

但我还是有些奇怪其他程序怎么会成那样慢？
应该是写的时候没有考虑数据量和执行计划的缘故

biti_rainy

其实光你这么贴出sql一点意义都没有
为什么：
因为对于oracle来说，它将决定先选择那一个表做内表
从该表开始做表连接
同时也将决定采取3中连接方式之一(merge join/hash join/nested join)
这些决定依赖于你的索引、数据量、数据值分布和估计返回的结果集大小

据说对于超过3个表的连接，据说oracle的优化器已经不能很好的处理了

所以对于这类查询
最重要的是，编程则一定要熟悉sql优化
然后一定要熟悉业务，了解各表的索引状况和数据量及数据值分布
还要了解自己的查询将可能返回怎样的一个结果集
这样才不至于某一日突然出现sql使数据库处于崩溃的边缘

所以，要解决好你的问题，可能不是就这么几句代码和别人的几句话能讲述清楚的

biti_rainy

但是基于cost也要提供了统计信息才可以啊

如果只有部分表有统计信息
这种优化模式说不定结果比单纯的cost或者rule都差

因为cost的含义只是根据统计信息来决定一种oracle认为比较优的方案（不一定是最优，也存在极少机会的差的可能）。提供部分信息……oracle怎么做cost呢？只能根据有限信息来做了

biti_rainy

oracle的cbo的选择
毕竟时间和空间代价不能太高
所以：它大部分时间选择的是比较好的方案
极少数时间选择的是不好的方案

任何人都不能确保它选择的是最优的方案
（当然，别拿简单sql来谈论这个问题，呵呵）

biti_rainy

加0或者用 ||''   就是强制指定不使用索引（字段进行了运算）
跟一些hints具有一样的效果

偶觉得，应该人工分析表和数据，
找出一些一定要首先连接的表，
在人工强制返回一些结果集的基础上再优化

楼上也有朋友说过了
呵呵
不过你的语句能跑完很幸运了
以前我扔一个语句进去
光分析语法就消耗了好几个小时占用了快80M内存
进度条刚走一半不到
cpu占用95以上
我怒了就终止了

后来给sql expert 的开发者提过意见

是个香港人，年纪很大了
介绍他来过这个坛子发言过几次
可惜现在没有踪影了

biti_rainy

我猜是 +0  and   || ''

是不是？hehe

biti_rainy

怎么说曹操曹操就到啊
olive你就请教richto吧
他就是sql expert 的开发者
去LECCOTECH.COM ，他可能能帮你解决问题  ：）

olive

我这两天在忙别的，分不出时间。昨天那个程序又正式运行了，用了29分钟（先加上analyze运行完再删掉），我这两天先这样对付。
不知道我最后调整的结果（不用analyze）能不能达到这个效果。
我星期三开始对这个SELECT做全面测试，有什么结果我一定详细汇报。
关于autotrace我上面说的对吗？我也暂时没有时间去试。
给我点时间，给我点时间...
我这里类似的例子还有很多，如果大家有兴趣，我可以逐一为大家贴上来供大家研究，都是真实的例子，曾经搞得我很头痛，现在我已经学会了一些方法，我要开始重新对付他们，有一种跃跃欲试的感觉，呵呵...但是我一定要先把手头别的事情处理完才行。