_b_tree_bitmap_plans参数的设置

SELECT /*+ opt_param('_b_tree_bitmap_plans', 'false') */   T.THIRD_CALLBACK_CODE, T.CHANNEL_CODE, C.CHANNEL_NAME, T.N
  FROM table_name;

       

转自:http://yaanzy.itpub.net/post/1263/228290

最近做一个应用系统的升级工作,其中数据库升级部分执行到一半就停下来了,检查数据库,发现是停在一
个批量调用insert语句中.将语句找出来手工执行,发现一次insert操作都要125秒, 仔细检查执行计划,
发现居然没有进行并行扫描(本来系统设置了并行扫描功能),而且在别的系统中都可以正常执行.经过对比
执行计划后发现, 有问题的系统中把B树索引转换为bitmap索引后再进行比较,导致并行功能没有启动.
查阅相关资料后确定：
该转换由隐藏参数_b_tree_bitmap_plans决定。如果该参数为true，则进行转换，否则不进行转换。而8i里，
该参数缺省为false，所以只有存在bitmap索引时才会考虑将B树索引转换为bitmap; 而到了9i里，该参数
缺省为true了，则对任何索引都有可能进行bitmap转换。

下面是在9.2.0.7 for solaris中的例子：

索引PK_CMS_CONTENT_ENTRY上设置了degree 4

9.2.0.7> alter session set "_b_tree_bitmap_plans"=true;

Session altered.

9.2.0.7> explain plan for
2 INSERT
3 INTO FMS_ATTRIBUTES(ID, AUTHOR, TITLE, KEYWORDS, CREATE_TS, LASTMODIFY_TS)
4 SELECT E.ID, WD.AUTHOR, WD.TITLE, WD.KEYWORDS, :B1 , :B1
5 FROM ODM_WEBCTFILE WF, ODM_DOCUMENT D, ODM_PUBLICOBJECT O, ODM_CLASSOBJECT C, ODM_WEBCTDOCUMENT WD, CMS_CONTENT_ENTRY E
6 WHERE
7 WF.ID = D.ID
8 AND D.ID = O.ID
9 AND O.CLASSID = C.ID
10 AND C.DATABASEOBJECTNAME = 'webctfile'
11 AND O.ID = WD.ID
12 AND WD.PRIVATE = 1 AND O.ID = E.ID
13 /

Explained.

9.2.0.7> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 379 | 30699 | 1987 |
| 1 | NESTED LOOPS | | 379 | 30699 | 1987 |
| 2 | NESTED LOOPS | | 3806 | 185K| 1226 |
| 3 | NESTED LOOPS | | 7251 | 318K| 1225 |
| 4 | NESTED LOOPS | | 18304 | 679K| 1223 |
| 5 | NESTED LOOPS | | 1281K| 31M| 81 |
| 6 | TABLE ACCESS BY INDEX ROWID | ODM_CLASSOBJECT | 1 | 19 | 1 |
|* 7 | INDEX UNIQUE SCAN | ODMI_109_UNI | 1 | | |
| 8 | INDEX FULL SCAN | PK_CMS_CONTENT_ENTRY | 1281K| 8758K| 400 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | |
| 10 | BITMAP AND | | | | |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 12 | INDEX RANGE SCAN | SYS_C001683 | 1 | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 14 | INDEX RANGE SCAN | IDX_ODM_PUBLICOBJECT_CLASSID | 1 | | |
|* 15 | INDEX UNIQUE SCAN | SYS_C001685 | 1 | 7 | |
|* 16 | INDEX UNIQUE SCAN | SYS_C001858 | 1 | 5 | |
|* 17 | TABLE ACCESS BY INDEX ROWID | ODM_WEBCTDOCUMENT | 1 | 31 | 1 |
|* 18 | INDEX UNIQUE SCAN | SYS_C001818 | 1 | | |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("C"."DATABASEOBJECTNAME"='webctfile')
12 - access("O"."ID"="E"."ID")
14 - access("O"."CLASSID"="C"."ID")
15 - access("D"."ID"="O"."ID")
16 - access("WF"."ID"="D"."ID")
17 - filter("WD"."PRIVATE"=1)
18 - access("O"."ID"="WD"."ID")

Note: cpu costing is off

37 rows selected.

9.2.0.7> alter session set "_b_tree_bitmap_plans"=false;

Session altered.

9.2.0.7.WCTU> explain plan for
2 INSERT
3 INTO FMS_ATTRIBUTES(ID, AUTHOR, TITLE, KEYWORDS, CREATE_TS, LASTMODIFY_TS)
4 SELECT E.ID, WD.AUTHOR, WD.TITLE, WD.KEYWORDS, :B1 , :B1
5 FROM ODM_WEBCTFILE WF, ODM_DOCUMENT D, ODM_PUBLICOBJECT O, ODM_CLASSOBJECT C, ODM_WEBCTDOCUMENT WD, CMS_CONTENT_ENTRY E
6 WHERE
7 WF.ID = D.ID
8 AND D.ID = O.ID
9 AND O.CLASSID = C.ID
10 AND C.DATABASEOBJECTNAME = 'webctfile'
11 AND O.ID = WD.ID
12 AND WD.PRIVATE = 1 AND O.ID = E.ID
13 /

Explained.

9.2.0.7> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 379 | 30699 | 4028 | | | |
| 1 | NESTED LOOPS | | 379 | 30699 | 4028 | 30,01 | P->S | QC (RAND) |
| 2 | NESTED LOOPS | | 3808 | 185K| 3266 | 30,01 | PCWP | |
| 3 | NESTED LOOPS | | 7255 | 318K| 3265 | 30,01 | PCWP | |
| 4 | NESTED LOOPS | | 18310 | 679K| 3264 | 30,01 | PCWP | |
| 5 | NESTED LOOPS | | 126K| 3818K| 3263 | 30,00 | S->P | RND-ROBIN |
| 6 | TABLE ACCESS BY INDEX ROWID| ODM_CLASSOBJECT | 1 | 19 | 1 | | | |
|* 7 | INDEX UNIQUE SCAN | ODMI_109_UNI | 1 | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| ODM_PUBLICOBJECT | 126K| 1478K| 3262 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ODM_PUBLICOBJECT_CLASSID | 126K| | | | | |
|* 10 | INDEX RANGE SCAN | PK_CMS_CONTENT_ENTRY | 1 | 7 | | 30,01 | PCWP | |
|* 11 | INDEX UNIQUE SCAN | SYS_C001685 | 1 | 7 | | 30,01 | PCWP | |
|* 12 | INDEX UNIQUE SCAN | SYS_C001858 | 1 | 5 | | 30,01 | PCWP | |
|* 13 | TABLE ACCESS BY INDEX ROWID | ODM_WEBCTDOCUMENT | 1 | 31 | 1 | 30,01 | PCWP | |
|* 14 | INDEX UNIQUE SCAN | SYS_C001818 | 1 | | | 30,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("C"."DATABASEOBJECTNAME"='webctfile')
9 - access("O"."CLASSID"="C"."ID")
10 - access("O"."ID"="E"."ID")
11 - access("D"."ID"="O"."ID")
12 - access("WF"."ID"="D"."ID")
13 - filter("WD"."PRIVATE"=1)
14 - access("O"."ID"="WD"."ID")

Note: cpu costing is off

33 rows selected.

不过也可以只通过改变表的连接顺序, 并添加ordered提示来强制按照from中的顺序连接, 来避免
位图索引转化：

9.2.0.7> alter session set "_b_tree_bitmap_plans"=true;

Session altered.

9.2.0.7> explain plan for
2 INSERT INTO FMS_ATTRIBUTES(ID, AUTHOR, TITLE, KEYWORDS, CREATE_TS, LASTMODIFY_TS)
3 SELECT /*+ ordered */E.ID, WD.AUTHOR, WD.TITLE, WD.KEYWORDS, :B1 , :B1
4 FROM ODM_CLASSOBJECT C,
5 ODM_PUBLICOBJECT O,
6 CMS_CONTENT_ENTRY E,
7 ODM_WEBCTDOCUMENT WD,
8 ODM_DOCUMENT D,
9 ODM_WEBCTFILE WF
10 WHERE wf.id = d.id
11 AND d.id = o.id
12 AND o.classid = c.id
13 AND c.databaseobjectname = 'webctfile'
14 AND o.id = wd.id
15 AND wd.private = 1
16 AND o.id = e.id;

Explained.

9.2.0.7> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 379 | 30699 | 6927 | | | |
| 1 | NESTED LOOPS | | 379 | 30699 | 6927 | 43,01 | P->S | QC (RAND) |
| 2 | NESTED LOOPS | | 721 | 54796 | 6926 | 43,01 | PCWP | |
| 3 | NESTED LOOPS | | 1821 | 122K| 6925 | 43,01 | PCWP | |
| 4 | NESTED LOOPS | | 18304 | 679K| 3264 | 43,01 | PCWP | |
| 5 | NESTED LOOPS | | 126K| 3818K| 3263 | 43,00 | S->P | RND-ROBIN |
| 6 | TABLE ACCESS BY INDEX ROWID| ODM_CLASSOBJECT | 1 | 19 | 1 | | | |
|* 7 | INDEX UNIQUE SCAN | ODMI_109_UNI | 1 | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| ODM_PUBLICOBJECT | 126K| 1478K| 3262 | | | |
|* 9 | INDEX RANGE SCAN | IDX_ODM_PUBLICOBJECT_CLASSID | 126K| | | | | |
|* 10 | INDEX RANGE SCAN | PK_CMS_CONTENT_ENTRY | 1 | 7 | | 43,01 | PCWP | |
|* 11 | TABLE ACCESS BY INDEX ROWID | ODM_WEBCTDOCUMENT | 1 | 31 | 1 | 43,01 | PCWP | |
|* 12 | INDEX UNIQUE SCAN | SYS_C001818 | 1 | | | 43,01 | PCWP | |
|* 13 | INDEX UNIQUE SCAN | SYS_C001685 | 1 | 7 | | 43,01 | PCWP | |
|* 14 | INDEX UNIQUE SCAN | SYS_C001858 | 1 | 5 | | 43,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
----------------------------------------------------

7 - access("C"."DATABASEOBJECTNAME"='webctfile')
9 - access("O"."CLASSID"="C"."ID")
10 - access("O"."ID"="E"."ID")
11 - filter("WD"."PRIVATE"=1)
12 - access("O"."ID"="WD"."ID")
13 - access("D"."ID"="O"."ID")
14 - access("WF"."ID"="D"."ID")

Note: cpu costing is off

33 rows selected.

不过有时这个转化有时候也是比较高效的, 不可一概而论, 所以当检查到执行计划中有BITMAP CONVERSION时,
最好测试一下性能.

 

 

from:

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

转载于:http://blog.itpub.net/27573546/viewspace-758582/