ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131111]参数optimizer_features_enable.txt

[20131111]参数optimizer_features_enable.txt

原创 Linux操作系统 作者:lfree 时间:2013-11-11 11:42:10 0 删除 编辑
[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<=1e4;
create table t2 as select rownum+1 id ,lpad('t2',6,'x') v1 from dual connect by level<=1e4-2;

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.

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

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




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-776277/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2470
  • 访问量
    6277855