Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1082880
  • 博文数量: 145
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3510
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(145)

文章存档

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2020-06-11 15:32:01

接PART4:

2.7 两个工具提升疑难SQL优化效率

2.7.1两个工具提升疑难SQL优化效率-10053分析执行计划生成原因


  一条SQL执行12分钟没有结果:其中object_id有索引,从查询结构来看,内层查询完全可以独立执行(最多100行),然后与外层的表进行关联,走NL,这样可以利用到object_id索引,然而,事与愿违,Id=4出现FILTER,这样内层查询会驱动N次,问题出在何处?




下面就使用10053探索优化器行为来研究此问题。

*****************************

Cost-Based Subquery Unnesting

*****************************

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.

Subquery removal for query block SEL$3 (#3)

RSW: Not valid for subquery removal SEL$3 (#3)

Subquery unchanged.

Subquery Unnesting on query block SEL$2 (#2)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block SEL$2 (#2).

SU:   Checking validity of unnesting subquery SEL$3 (#3)

SU:     SU bypassed: Subquery in a view with rowid reference.

SU:   Validity checks failed.
  
10053中可以看出,查询转换失败,因为遇到了rowid,当然把rowid改别名是可以,但是此SQL要求必须用rowid名字.
  
通过改写消除FILTER运算如下:



2.7.2两个工具提升SQL优化效率-SQLT找出正确执行计划需设置的参数

1)SQL能否生成正确执行计划,不光和统计信息、索引等有关,能否正确执行查询转换是至关重要的,由于各种复杂的查询转换机制导致BUG很多,Oracle对这些已知BUG通过fix control参数管理,有的默认打开,有的默认关闭。所以,如果遇到复杂的SQL,特别包含复杂视图的SQL,比如谓词无法推入这种查询转换,收集统计信息无效,这时候可以考虑是否遇到了BUG

2)BUG那么多,我怎么知道是哪个?,SQLT神器来帮你,使用SQLT里面的XPLORE工具,可以把参数打开关闭一遍,并且生成对应执行计划,这样通过生成的报告,可以一眼定位问题。(当然,是已知BUG,比如前面的rowid问题,也是定位不到的)

问题背景:11.2.0.2升级到11.2.0.4出现此问题,性能杀手FILTER操作,SQL跑不出来,FILTER产生原因,无法unnest subquery,其中11g _optimizer_null_aware_antijoin参数为true

执行计划如下所示:

很显然,这两个FILTER有问题,按理说应该走ANTI JOIN

 

下面看看使用SQLTXPLORE来找出问题,先来看下SQLT介绍:



跑一下XPLORE,只需要调用XPLAIN方法即可,提高效率,不实际执行SQL

可以看到和对应的隐含参数_optimizer_squ_bottomup设置有关,这是一个和子查询的查询转换有关的隐

含参数。修正之后的执行计划:

走回ANTI JOIN,正确了。终于从跑不出来到几秒搞定,其实还可以优化,但是那已经不是最重要的事了!

SQLT XPLORE的一些限制:

1)只能单个参数测试是否有效

2)XPLORE使用XPLAIN方法,内部调用explain plan for,不需要执行从而提高效率和避免修改数据

3)只有是已知参数或者BUG fix control才会有用,对于未知BUG无用,当然修改参数需要做足测试,如果非批量问题,建议找出原因,使用SQL PROFILE搞定,批量问题需要做足测试再实施修改!


从一条SQL看基于ORACLE的SQL优化”内容完毕。


阅读(1186) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~