Re: Weird explain plan

slavko.brkic_at_swisslife.ch schrieb:
> We have upgraded from Oracle 8 to Oracle 9.2.0.6.
>
> I am getting a bitmap conversion. We do not have a single bitmap index
> in our system. Is this possible ? The query is now slow as opposed to
> before. We are using an ORDERED hint in the query. Grateful for any
> help. Thanks
>
> SELECT STATEMENT Optimizer MODE=CHOOSE 1 153987
>
> SORT ORDER BY 1 155 153987
> NESTED LOOPS 1 155 153985
> NESTED LOOPS 1 145 153984
> NESTED LOOPS 1 133 153983
> NESTED LOOPS 1 120 153982
> NESTED LOOPS 1 71 153981
> MERGE JOIN CARTESIAN 20 M 1G 46641
>
> VIEW SYS.VW_SQ_1 23 K 382 K 599
>
> SORT GROUP BY 23 K 382 K 599
>
> TABLE ACCESS FULL AD251LCL.trans_log_ip 80 K 1
> M 129
> BUFFER SORT 910 31 K 46641
>
> TABLE ACCESS BY INDEX
> ROWID AD251LCL.insured_person 910 31 K 2
> INDEX RANGE SCAN AD251LCL.IX_PKY_2001009 208 1
>
> TABLE ACCESS BY INDEX ROWID AD251LCL.trans_log_ip 1 19
> 153981
> BITMAP CONVERSION TO ROWIDS
>
> BITMAP AND
> BITMAP CONVERSION FROM ROWIDS
>
> INDEX RANGE SCAN AD251LCL.IX_PKY_2001021 1
>
> BITMAP CONVERSION FROM ROWIDS
>
> INDEX RANGE
> SCAN AD251LCL.IX_FGN_2000999_2002266 1
> TABLE ACCESS BY INDEX ROWID AD251LCL.party 1 49 1
>
> INDEX UNIQUE SCAN AD251LCL.IX_PKY_2001044 1
>
> TABLE ACCESS BY INDEX ROWID AD251LCL.staff_group 1 13 1
>
> INDEX UNIQUE SCAN AD251LCL.IX_PKY_2001208 1
>
> TABLE ACCESS BY INDEX ROWID AD251LCL.employer 1 12 1
>
> INDEX UNIQUE SCAN AD251LCL.IX_PKY_2001030 1
>
> TABLE ACCESS BY INDEX ROWID AD251LCL.party 1 10 1
>
> INDEX UNIQUE SCAN AD251LCL.IX_PKY_2001044 1
>

I suggest, you should open a TAR on this issue, additionally you can have a look on the paper from Wolfgang Breitling about the difference between 9i and 8i optimizer http://www.centrexcc.com/What%20is%20new%20in%20the%20Oracle%209i%20CBO.pdf

I had some cases where setting "_b_tree_bitmap_plans" to FALSE did the workaround ( in some cases it was not enough to achieve good plans, optimizer_features_enable=8.1.0 should be set, but , IIRC it was before 9.2.0.6 Patchset ).

Best regards

Maxim Received on Wed Aug 17 2005 - 05:41:37 CDT