[20131111]参数optimizer_features_enable.txt
optimizer_features_enable可以使系统升级后保持原来的执行计划。我自己很少做这种升级操作，昨天在给别人做优化时遇到一个问题,
自己做一个记录，用户改动了参数optimizer_features_enable，不知道什么原因要改这个参数，导致一些执行计划很慢！
举一个例子：
1.建立测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> show parameter optimizer_features_enable ;
NAME TYPE VALUE
------------------------------------ ---------------------------------------- ---------
optimizer_features_enable string 11.2.0.3
drop table t1 purge;
drop table t2 purge;
create table t1 as select rownum id ,lpad('t1',6,'x') v1 from dual connect by level
create table t2 as select rownum+1 id ,lpad('t2',6,'x') v1 from dual connect by level
exec dbms_stats.gather_table_stats(user,'T1',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user,'T2',cascade=>TRUE);
2.测试：
SCOTT@test> select t1.* from t1 where not exists ( select 1 from t2 where t2.id=t1.id);
ID V1
---------- ------
1 xxxxt1
10000 xxxxt1
SCOTT@test> select t1.* from t1 where t1.id not in ( select id from t2 );
ID V1
---------- ------
1 xxxxt1
10000 xxxxt1
--使用not in ，not exists都很快出结果。
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 18h1xv8u3xwa5, child number 0
-------------------------------------
select t1.* from t1 where t1.id not in ( select id from t2 )
Plan hash value: 2739594415
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 19 (100)| | | |
|* 1 | HASH JOIN RIGHT ANTI NA| | 100 | 19 (6)| 1517K| 1517K| 1946K (0)|
| 2 | TABLE ACCESS FULL | T2 | 9998 | 9 (0)| | | |
| 3 | TABLE ACCESS FULL | T1 | 10000 | 9 (0)| | | |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="ID")
SCOTT@test> alter session set optimizer_features_enable = '9.2.0.8';
Session altered.
SCOTT@test> SET TIMING ON
SCOTT@test> select t1.* from t1 where t1.id not in ( select id from t2 );
ID V1
---------- ------
1 xxxxt1
10000 xxxxt1
Elapsed: 00:00:03.45
--需要4秒，实际的系统比这个还慢！
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 18h1xv8u3xwa5, child number 1
-------------------------------------
select t1.* from t1 where t1.id not in ( select id from t2 )
Plan hash value: 895956251
----------------------------------------------------
| Id | Operation | Name | E-Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | 12 |
|* 1 | FILTER | | | |
| 2 | TABLE ACCESS FULL| T1 | 500 | 6 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 6 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("ID":B1))
Note
-----
- cpu costing is off (consider enabling it)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
28 rows selected.
--我开始以为not in与not exists的区别。但是执行计划的note中出现一个很奇怪的提示
- cpu costing is off (consider enabling it),好像没有见过。不过当时没特别的在意。
--看看not exists的情况，发现执行计划不同。注意我以前写的一篇blog
-- http://space.itpub.net/267265/viewspace-747842/
-- [20121028]not in与NULL问题.txt
SCOTT@test> select t1.* from t1 where not exists ( select 1 from t2 where t2.id=t1.id);
ID V1
---------- ------
10000 xxxxt1
1 xxxxt1
Elapsed: 00:00:00.02
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID ajfxx4ykgyxm6, child number 1
-------------------------------------
select t1.* from t1 where not exists ( select 1 from t2 where
t2.id=t1.id)
Plan hash value: 2706079091
-------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 15 | | | |
|* 1 | HASH JOIN ANTI | | 2 | 15 | 1278K| 1278K| 1670K (0)|
| 2 | TABLE ACCESS FULL| T1 | 10000 | 6 | | | |
| 3 | TABLE ACCESS FULL| T2 | 9998 | 6 | | | |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."ID"="T1"."ID")
Note
-----
- cpu costing is off (consider enabling it)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
28 rows selected.
--建立的索引看看。
create index i_t1_id on t1(id);
create index i_t2_id on t2(id);
SCOTT@test> select t1.* from t1 where t1.id not in ( select id from t2 );
ID V1
---------- ------
1 xxxxt1
10000 xxxxt1
Elapsed: 00:00:03.59
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 18h1xv8u3xwa5, child number 0
-------------------------------------
select t1.* from t1 where t1.id not in ( select id from t2 )
Plan hash value: 895956251
----------------------------------------------------
| Id | Operation | Name | E-Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | 12 |
|* 1 | FILTER | | | |
| 2 | TABLE ACCESS FULL| T1 | 500 | 6 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 6 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("ID":B1))
Note
-----
- cpu costing is off (consider enabling it)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
28 rows selected.
--而使用not exists确实是快一些，主要是使用I_T2_ID索引。
SCOTT@test> select t1.* from t1 where not exists ( select 1 from t2 where t2.id=t1.id);
ID V1
---------- ------
1 xxxxt1
10000 xxxxt1
Elapsed: 00:00:00.05
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID ajfxx4ykgyxm6, child number 1
-------------------------------------
select t1.* from t1 where not exists ( select 1 from t2 where
t2.id=t1.id)
Plan hash value: 43495135
-------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | 7 |
|* 1 | FILTER | | | |
| 2 | TABLE ACCESS FULL| T1 | 500 | 6 |
|* 3 | INDEX RANGE SCAN | I_T2_ID | 1 | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - access("T2"."ID"=:B1)
Note
-----
- cpu costing is off (consider enabling it)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
29 rows selected.
--我再仔细观察发现就是执行 select sysdate from dual ;这样的语句，执行计划里面也有这些信息，是否用户使用什么隐含参数？
- cpu costing is off (consider enabling it)
3.检查参数文件：
发现用户修改了参数optimizer_features_enable = '9.2.0.8'，修改回来后，执行一些正常。
SCOTT@test> alter session set optimizer_features_enable = '11.2.0.3';
Session altered.
SCOTT@test> select t1.* from t1 where t1.id not in ( select id from t2 );
ID V1
---------- ------
1 xxxxt1
10000 xxxxt1
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 18h1xv8u3xwa5, child number 1
-------------------------------------
select t1.* from t1 where t1.id not in ( select id from t2 )
Plan hash value: 2739594415
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 19 (100)| | | |
|* 1 | HASH JOIN RIGHT ANTI NA| | 100 | 19 (6)| 1517K| 1517K| 1843K (0)|
| 2 | TABLE ACCESS FULL | T2 | 9998 | 9 (0)| | | |
| 3 | TABLE ACCESS FULL | T1 | 10000 | 9 (0)| | | |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="ID")
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
26 rows selected.
--问修改原因，对方也讲不出来，看来取消设置，应该没有问题。
总结：
以后要注意看执行计划的细节，这样才能避免走弯路。
