RE: bitmap conversion to rowids operation with btree indexes?
- From: "Joze Senegacnik" <Joze.Senegacnik@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Jan 2005 06:28:36 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
