全部博文（80）
分类： Oracle
2010-12-28 10:02:06
大数据表，按某字段T（Varchar型的时间字段）分区，一个月一个分区。有常用业务字段A和B（其它字段并不重要），字段A值的重复率不高，B的重复率稍高。建有索引1：A，组合索引2：T,B。
对该表的常用查询有两种：通过条件T、A查询，通过条件T、B查询。
原来应用一直正常，近期由于其它分区表查询索引选择策略不优化的问题，对所有大数据表的分区表进行数据分析（1％抽样，对数据和索引进行分析），促使Oracle选用正确的更合适的索引。分析后，其它表的问题解决，但这个表分析后查询速度反面大幅下降。
查看数据库查询的计划，对条件T、A进行查询时（select * from talbe where t between someday and anotherday and a=something order by T desc），当T的范围在一个月分区时，选用索引1，速度在一秒以内，在T范围跨月时，系统选择索引2，速度很慢，要10几秒甚至更多，通过SQL语句强制使用索引1，速度很快，在一秒以内。
问题处理及疑问：
1、以为索引1没带时间信息，造成跨分区查询时选择了不够优化的索引，于是增加一索引3（T, A)，重新进行数据分析，但数据库仍没有选择更优的索引，强制使用索引3，发现还没有原索引1快（4秒多）（这是个疑问1，理论上更快的索引反而速度慢，而且慢好几秒）。
2、把索引1修改为全局索引（原为分区索引），问题仍未解决。
3、经过一番折腾，发现有索引3时，把Where条件中的order by T desc的DESC去掉，数据库就能选择索引3的索引。（这是疑问2，DESC对索引选择策略有什么影响，在这里的影响是偶然还是必然？）
4、一个类似的表，有类似的T/A/B字段以及类似的索引2、索引3，当时速度慢，选择的是不优化的索引，就是通过对表进行数据分析，让数据库选择了较优化的索引，但同样的方法使用到这个表反而引起了问题，情况正相反（这是疑问3，数据分析的后果不确定嘛？）
5、这个表数据分析后发生了慢的问题，于是采用Delete分析结果的方法进行回退，但回退后数据库仍选择“错误”的索引（这是疑问4，为什么回退失败？数据库的查询计划和分析结果到底有什么关系？）
6、应用的SQL语句是个组合查询，条件语句是拼出来的，如果在SQL中直接指定索引，一来应用实现起来很麻烦（要根据不同的组合条件拼前面的指定索引语句），二来应用的数据库依赖性增强。除了改应用，到底可不可以通过对分区数据表进行定时分析的方法确保数据库选择更优的索引？数据库对分区表的索引选择策略到底有没有固定可循的原则？（这是疑问5）
为什么Oracle不自动做Analyze?
如果Analyze的利大于弊的话，为什么Oracle不自己做？它完全可以做到实时的/定时的（调度表）/策略的（系统非繁忙时/数据量变化到一定程度时）。我认为Oracle不自动去做是与设计思想有关的，它追求尽量的稳妥，同时也让用户自己去承担Analyze的风险（有点不负责任吧，它在文档中也几乎没有提及作Analyze的风险）。
Analyze 的风险：
先来看两个极端的例子：
例1，假设现有系统已经是最优系统，那么在作了Analyze后，最好的情况是系统效率没有变化，即收益为0。正常的情况是某些原本最佳化的SQL, 在Analyze后由于Cost的变化，Oracle内部优化器选择了一个不同原先最佳的执行计划的新执行计划，性能反而下降了，或者说收益<0。
结论：对于最佳系统，作Analyze后的总体收益<=0。
例2，假设现有系统为最差系统，那么在作了Analyze后，最坏的情况是系统效率没有变化，即收益为0。正常的情况是某些原本最差的SQL,在Analyze后由于Cost的变化，Oracle内部优化器选择了一个不同原先最差的执行计划的新执行计划，性能上升了，或者说收益>0。
结论：对于最差系统，作Analyze后的总体收益>=0。
那么对于正常的系统--介于最佳和最差系统之间的系统，作Analyze后的情况会是什么样呢？收益：a.原先性能很差的SQL性能提高了。b.一部分性能不错的SQL,性能又再一步提高了（少数）。损失：a.一部分性能很差的SQL,性能更差了（少数）。b.一部分性能不错的SQL,性能下降了--------注意这一点。
结论：对于正常系统，作Analyze后的收益不可知！！！！！！！！！！！！！
正是这个不可知产生了风险。对于一个生产系统，在作Analyze前，你可以明确的知道瓶颈在那里（虽然系统性能在逐渐下降中），但在Analyze后，你只能祈求上帝了，因为你根本不知道什么地方性能会下降，什么地方性能会上升（系统性能产生了不可预知的急剧变化）。假设只有一条SQL的性能下降了，999条性能提高了，那么你的系统的性能提高了吗？未必，下降的那条可能每天需要运行几千万次：），整体性能可能还是下降了。想看性能下降的例子？请看 http://www.itpub.net/showthread.php?s=&threadid=27994
总结：作Analyze的风险在于你不知道Analyze后，那一部分的性能会提高，那一部分的性能会下降，也不知道整体的性能会下降还是提高。或者说作Analyze会把系统性能推入一个不可预知的状态。
现在你知道为什么oracle不自动作Analyze了吧：）
请尊重原创作品。转载请保持文章完整性，并以超链接形式注明原始作者“”和主站点地址，方便其他朋友提问和指正。
