setting of _b_tree_bitmap_plans = true in optimizer trace...

[Former Member]

Hi Experts,

when i check a query on a transactional cube in BI 7.0 the btree_bitmap_plans parameter is set to true in the optimizer trace (ST05).

The fact table only has normal indexes (no bitmaps , because it's transactional cube).

My query plan contains some expensive "BITMAP CONVERSION FROM ROWIDS" accesses (turns normal index into bitmaps, and later back to rowids).

If I set the parameter

_b_tree_bitmap_plans = false

everything's fine and a fast plan is generated.

Now my questions:

What is the default setting of btree_bitmap_plans in Oracle 10.2.0.4 for SAP BI 7.0?

I assume it should be

_b_tree_bitmap_plans =  false

but the session uses btree_bitmap_plans = true .

I've also got in a note (Note 723879 - Explain: System displays an incorrect access path )

that the execution of queries in BI sets the parameter explicitly to true when accessing a transactional cube.

can someone shed some light on this?

bye

yk

[stefan_koehler]

Hello Yukon,

i have a similiar issue in our OLTP (ECC 6.0) system with the "_b_tree_bitmap_plans" parameter.

> What is the default setting of btree_bitmap_plans in Oracle 10.2.0.4 for SAP BI 7.0?

The default value is true. SAP recommends to set the parameter to false until 10.2.0.4 (check sapnote #830576). SAP changed the recommendation with 10.2.0.4 and you should remove the parameter from your pfile/spfile .. so the default value "true" is used.

There is also a known bug which is described in sapnote #1284478 .. maybe you hit this one.

> that the execution of queries in BI sets the parameter explicitly to true when accessing a transactional cube.

Perform a CBO trace and you will know it.

In general SAP told me, that the recommendations work for round about 95% of the customers. The other 5% (i am one of them) may have problems with it and can differ from the recommendations. So i set this underscore parameter to false and everything works fine )

Regards

Stefan