[20131111]参数optimizer_features_enable.txt

[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.

--问修改原因，对方也讲不出来，看来取消设置，应该没有问题。

总结：
以后要注意看执行计划的细节，这样才能避免走弯路。

时间： 2024-07-30 10:49:36

[20131111]参数optimizer_features_enable.txt的相关文章

[20171117]参数filesystemio_options.txt

[20171117]参数filesystemio_options.txt --//前几天看别人的awr报表发现设置参数filesystemio_options=setall,问为什么?对方给出一个链接,某某人都是这样设置的, --//自己很无语,我希望对方能提出自己的见解. --//首先给出oracle官方的解析: https://docs.oracle.com/cd/E11882_01/server.112/e41573/os.htm#PFGRF94410 9.1.1.2 FILESYSTEM

[20171105]exp imp buffer参数解析.txt

[20171105]exp imp buffer参数解析.txt oracle官方所给的关于buffer的解释如下: https://docs.oracle.com/cd/A84870_01/doc/server.816/a76955/ch01.htm BUFFER Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the defaul

[20171109]查看隐含参数脚本.txt

[20171109]查看隐含参数脚本.txt --//查看隐含参数的脚本,今天没事修改一下增加查询description字段的内容.也有网友要求提供这个脚本,实际上这些脚本都是自己工作 --//中不断收集整理.网上许多地方都能找到. $ cat hide.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_va

[20170516]11G use_large_pages参数2.txt

[20170516]11G use_large_pages参数2.txt //前面我提到如果设置use_large_pages=auto.设置页面大小不足时,oracle会oradism经常修改内核参数vm.nr_hugepages. //忘记测试是否在退出后可以收回.链接如下: http://blog.itpub.net/267265/viewspace-2135210/ --//不知道什么回事,以前写的,忘记发了,补上. 1.环境 SYS@book> @ &r/ver1 PORT_STR

[20151021]理解dbms_xplan.display_cursor的format参数all.txt

[20151021]理解dbms_xplan.display_cursor的format参数all.txt --今天才理解dbms_xplan.display_cursor的format参数all,看来看书与看文档不够仔细. --我一般看执行计划使用我自己的脚本: $ cat dpcz.sql set verify off --select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEK

[20151208]隐含参数_trace_pin_time.txt

[20151208]隐含参数_trace_pin_time.txt --使用隐含参数可以trace how long a current pin is held,作为测试与学习了解oracle内部相关知识. --自己测试看看. 1.测试环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------

[20150325]关于参数archive_lag_target.txt

[20150325]关于参数archive_lag_target.txt --今天同事在测试设置参数archive_lag_target遇到一些问题,自己也做一些测试: DGMGRL>  show database test ArchiveLagTarget   ArchiveLagTarget = '0' DGMGRL> edit database test set PROPERTY ArchiveLagTarget=30; Error: ORA-16790: the value of th

[20160516]统计分析参数method_opt.txt

[20160516]统计分析参数method_opt.txt --统计分析参数method_opt我个人感觉是最能折腾人的参数.我自己曾经在这个参数上栽过跟头,通过例子来说明看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------

[20160501]查看包参数脚本.txt

[20160501]查看包参数脚本.txt --我以前写的脚本 SCOTT@book> @ &r/desc_proc sys dbms_stats get_table% INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE AR