从一条SQL看基于ORACLE的SQL优化_PART5

接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。

 

下面看看使用SQLT的XPLORE来找出问题，先来看下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优化”内容完毕。