QUERY_REWRITE_ENABLED

며칠전 9.2.0.8 에서 10.2.0.4 로 DB 업그레이드후 성능에 문제가 있어
OPTIMIZER_FEATURES_ENABLE parameter를 10.2.0.4 에서 9.2.0.8 로 변경을 했다.
변경후 성능은 나아졌다.

SQL plan이나 실행계획상에 특별한 문제가 없어보였고, 기술담당자도 이것저것 해보다 안되어서
위의 파라미터를 변경했다.

10g EM에서 확인해보니 QUERY_REWRITE_ENABLED 파라미터가 TRUE -> FALSE
                              SKIP_UNUSABLE_INDEXES  TRUE -> FALSE 로 변경된 것 확인
위의 두 init parameter는 임의적으로 변경하지 않았다.

옵티마이저와 관련된 hidden parameter를 비교해서 다른 부분을 바꿔가며 query 를 수행해 본 결과
다른것들은 별 차이가 없었고,

_optimizer_undo_cost_change = '10.2.0.4' 로 변경하니 query 의 결과가 아예 나오지 않았다.

http://orcl.tistory.com/entry/optimizer-hidden-parameter-비교9i-10gR2

QUERY_REWRITE_ENABLED

Property	Description
Parameter type	String
Syntax	QUERY_REWRITE_ENABLED = { false | true | force }
Default value	If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then true If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0 or lower, then false
Modifiable	ALTER SESSION, ALTER SYSTEM
Real Application Clusters	Multiple instances can have different values.

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database.

Values:

• false

  Oracle does not use rewrite.

• true

  Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.

• force

  Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is important.
  
  To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.
  

SQL> show parameter optimizer_features
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_features_enable            string                 10.2.0.1

SQL> show parameter query
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_enabled                string                 TRUE
query_rewrite_integrity              string                 enforced
SQL> show parameter skip

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
skip_unusable_indexes                boolean                TRUE

SQL> alter system set optimizer_features_enable ='9.2.0.8';
alter system set optimizer_features_enable ='9.2.0.8'
*
ERROR at line 1:
ORA-00096: invalid value 9.2.0.8 for parameter optimizer_features_enable, must be from among 10.2.0.
1.1, 10.2.0.1, 10.1.0.5, 10.1.0.4,
10.1.0.3, 10.1.0, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5
, 8.0.4, 8.0.3, 8.0.0

SQL> alter system set optimizer_features_enable ='9.2.0';
System altered.

SQL> show parameter optimizer_features
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_features_enable            string                 9.2.0

SQL> show parameter query
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
query_rewrite_enabled                string                 FALSE
query_rewrite_integrity              string                 enforced

SQL> show parameter skip
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
skip_unusable_indexes                boolean                FALSE