Windchill性能调整之'_always_semi_join'

整理这篇文件的起因源于一次Windchill系统的性能调整。记得当时系统响应很慢，I/O较高。用一些性能监测的脚本找到这些耗用大量资源的SQL，发现它们有个共同的特征：用到in()类型的语句，如A0.idA2A2 IN(:1,:2,:3,:4,:5,...:N)。要命的是这些SQL语句非客制化的，所以改写的可能性很小。当时PTC给出的调整建议是：在数据库初始化参数设置_always_semi_join=off。理由如下：

there is currently a oracle bug to execute SQL with large IN() clause,the bug can not use the index efficiently.this parameter is workaround of the oracle bug.

 

我们来看看_always_semi_join到底为何物？

 

ALWAYS_SEMI_JOIN

This parameter specifies the join method for semi-joins. These types of joins are carried out by Optimizer after transforming a query. In such joins, duplicate values from the inner table are removed and then the type of join specified in the parameter is used to perform a semi-join. It can be set to NESTED_LOOPS, MERGE or HASH. In Oracle 8i, it is defaulted to STANDARD and in Oracle 9i it is defaulted to CHOOSE, to pick up an appropriate join. This parameter is renamed to _ALWAYS_SEMI_JOIN as of Oracle 9i.

原来这个参数是在9i的时候引进来的，之前叫always_semi_join。再来寻寻它的历史。

Parameter: always_semi_join Description

always use this method for semi-join when possible

Supported Versions

This supported parameter was introduced in Oracle 8.0.4

This supported parameter was removed in Oracle 9.0.1

Version	Parameter Name	Data Type	Session Modifiable	System Modifiable
8.0.4	always_semi_join	String	FALSE	FALSE
8.0.5	always_semi_join	String	FALSE	FALSE
8.0.6	always_semi_join	String	FALSE	FALSE
8.1.5	always_semi_join	String	FALSE	FALSE
8.1.6	always_semi_join	String	FALSE	FALSE
8.1.7	always_semi_join	String	TRUE	FALSE

Unsupported Versions

This unsupported parameter was introduced in Oracle 9.0.1

Version	Parameter Name	Data Type	Session Modifiable	System Modifiable
9.0.1	_always_semi_join	String	TRUE	FALSE
9.2.0	_always_semi_join	String	TRUE	FALSE
10.1.0	_always_semi_join	String	TRUE	IMMEDIATE
10.2.0	_always_semi_join	String	TRUE	IMMEDIATE
11.1.0	_always_semi_join	String	TRUE	IMMEDIATE

Syntax

_ALWAYS_SEMI_JOIN = OFF | CHOOSE | NESTED_LOOPS | MERGE | HASH

In Oracle 9.2 the default value is CHOOSE

Example

This example was developed on an Oracle 9.2 database using a 2048 byte block size.

The example requires the following table and index definitions

CREATE TABLE t1 (c1 NUMBER NOT NULL, c2 NUMBER); CREATE TABLE t2 (c1 NUMBER NOT NULL, c2 NUMBER); ANALYZE TABLE t1 COMPUTE STATISTICS; ANALYZE TABLE t2 COMPUTE STATISTICS;

With the default value for _ALWAYS_ANTI_JOIN

ALTER SESSION SET "_always_semi_join" = CHOOSE;

the statement SELECT c2 FROM t1 WHERE EXISTS ( SELECT NULL FROM t2 WHERE t2.c1 = t1.c1 );

generates the execution plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH JOIN (SEMI) 2 1 TABLE ACCESS (FULL) OF 'T1' 3 1 TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_SEMI_JOIN set to HASH

ALTER SESSION SET "_always_semi_join" = HASH;

the same statement generates the same execution plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH JOIN (SEMI) 2 1 TABLE ACCESS (FULL) OF 'T1' 3 1 TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to NESTED_LOOPS

ALTER SESSION SET "_always_semi_join" = NESTED_LOOPS;

the same statement generates the execution plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS (SEMI) 2 1 TABLE ACCESS (FULL) OF 'T1' 3 1 TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to MERGE

ALTER SESSION SET "_always_semi_join" = MERGE;

the same statement generates the execution plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN (SEMI) 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'T1' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to OFF

ALTER SESSION SET "_always_semi_join" = OFF;

the same statement generates the execution plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH JOIN (SEMI) 2 1 TABLE ACCESS (FULL) OF 'T1' 3 1 TABLE ACCESS (FULL) OF 'T2'

Note that in Oracle 9.2 (at least) _ALWAYS_SEMI_JOIN = OFF appears to have no effect

既然这样，修改初始化参数设置_always_semi_join=off的用意何在呢？我们的数据库版本是9.2.0.7.0。看来真是个bug了。在metalink上查了一下没有找到_always_semi_join=off跟execute SQL with large IN() clause无法有效的使用索引有什么联系。不过在查询的过程中又有了新的收获。这是关于从8.1.X 升级到 9.X关于子查询问题的诊断和解决。

oracle9i默认subquery unnesting 是enabled的。你可以通过下列方法来disable subquery unnesting ：

• Set (underscore) = FALSE
  
  alter session set "_UNNEST_SUBQUERY" = false;
  
  If _unnest_subquery = false does not help then you may also need to set _always_semi_join = off (if the subquery is an IN or EXISTS) or _always_anti_join = off (if the subquery is a NOT IN or NOT EXISTS)
  
  alter session set "_ALWAYS_SEMI_JOIN" = off;
  alter session set "_ALWAYS_ANTI_JOIN" = off;
  
  
• Set to a value less than "9.0.0"
  This is a static parameter and cannot be changed on the fly. It has to be set in one of the instance initialisation files (e.g. initSID.ora).
• Set (underscore) = OFF for not exists subqueries.
• Use a /*+ NO_UNNEST */ hint in the subquery
• Rewrite the query to modify or remove the subquery

当从8i升级到9i后遇到关于子查询的性能问题，可以尝试用以下方法进行诊断：

• Look for subqueries in the query text
• Examine the explain plan and determine if the subquery has been unnested
• Compare the current explain plan with the plan from the earlier version to see if the subquery is being handled differently
• Disable subquery unnesting and see if that resolves the problem