1. 首页
  2. IT资讯

[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<=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/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182934.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code