RE: bitmap conversion to rowids operation with btree indexes?

One of my customers had this problem just two days ago=20 after upgrade to 9.2.0.6. The query was using a lot of=20 CPU time - performing only LIO. Wes solved this by putting:

alter session set "_b_tree_bitmap_plans" =3D false=20

in the after logon trigger to resolve the problem.=20 I had to set this hidden parameter in several cases. =20
>From the below run-time plan from this last case=20
you can see that bitmap conversion from rowids=20 was very inefficient. The stats was fresh.

Regards, Joze

Rows Row Source Operation

---------- ---------------------------------------------------
        75  SORT ORDER BY=20
        75   UNION-ALL =20
        72    NESTED LOOPS =20
        72     NESTED LOOPS =20
        72      NESTED LOOPS =20
        72       NESTED LOOPS =20
    429463        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
    558622         INDEX RANGE SCAN POLICE_11  (object id 104077)
        72        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
       226         BITMAP CONVERSION TO ROWIDS=20
       110          BITMAP AND =20
    429463           BITMAP CONVERSION FROM ROWIDS=20
  98347027            INDEX RANGE SCAN PK_3  (object id 103815)
    406451           BITMAP CONVERSION FROM ROWIDS=20
   2262366            INDEX RANGE SCAN PK_1  (object id 103814)
        72       TABLE ACCESS BY INDEX ROWID DOK_PLACILA  (object id =
103466)
        72        INDEX UNIQUE SCAN DOK_PLACILA_1  (object id 103467)
        72      TABLE ACCESS BY INDEX ROWID PLAC_MESTA  (object id =
103912)
        72       INDEX UNIQUE SCAN PLAC_MESTA_1  (object id 103916)
        72     TABLE ACCESS BY INDEX ROWID FI_OSEBE  (object id 103479)
        72      INDEX UNIQUE SCAN FI_OSEBE_1  (object id 103494)
         3    NESTED LOOPS =20
         3     NESTED LOOPS =20
         3      NESTED LOOPS =20
         3       NESTED LOOPS =20
    129159        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
    558622         INDEX RANGE SCAN POLICE_11  (object id 104077)
         3        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
         3         BITMAP CONVERSION TO ROWIDS=20
         2          BITMAP AND =20
    129159           BITMAP CONVERSION FROM ROWIDS=20
  29577411            INDEX RANGE SCAN PK_3  (object id 103815)
    120469           BITMAP CONVERSION FROM ROWIDS=20
    772730            INDEX RANGE SCAN PK_1  (object id 103814)
         3       TABLE ACCESS BY INDEX ROWID DOK_PLACILA  (object id =
103466)
         3        INDEX UNIQUE SCAN DOK_PLACILA_1  (object id 103467)
         3      TABLE ACCESS BY INDEX ROWID PLAC_MESTA  (object id =
103912)
         3       INDEX UNIQUE SCAN PLAC_MESTA_1  (object id 103916)
         3     TABLE ACCESS BY INDEX ROWID PR_OSEBE  (object id 104162)
         3      INDEX UNIQUE SCAN PR_OSEBE_1  (object id 104165)

On Tue, 2005-01-11 at 11:12, Jonathan Lewis wrote:

>The default value is FALSE in 8i, and true in 9i,
>and some people have reported performance
>problems because there setup started doing this
>conversion too frequently in inappropriate cases.

>Regards

>Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l