Here is the CPU profile of the parse phase on a SPARC IIII you get from a number of SELECTs that comes out of the Siebel (now bought by Oracle Corp.) Customer Relationship Management (version 18.104.22.168 on Oracle 22.214.171.124):
One of the problems this application has is the high number of hard parses and as seen above their cost, even several days after the DB started. This product can be seen as SQL generator whose syntax is dynamically assembled according to the user screen entries.
Knowing that 42 tables were nested loop joins, I suspected a lot of optimizer permutations (the default 126.96.36.199 optimizer_max_permutations is 2000, a limit which will be easily reached). A 10053 trace is worth several hundred pages, but a grep shows that many of the options considered are bitmap conversions to and from rowids. I tried to set both OPTIMIZER_MAX_PERMUTATIONS to 100 and _B_TREE_BITMAP_PLANS to false:
alter session set “_b_tree_bitmap_plans”=false;
PARSE #1:cpu=1680000,elapsed=2060159 (a 35% improvement).
Changing OPTIMIZER_MAX_PERMUTATIONS to 100 did not sped up the SQL call, whatever the _B_TREE_BITMAP_PLANS value.
It seems that the optimizer is using an unusual quantity of CPU cycles to evaluate the b-tree to bitmap transformations options.
Just to make things plain clear, I looked in Metalink and googled “B-tree conversions”,” _B_TREE_BITMAP_PLANS” and a few other similar keywords: it looks like many inefficient plans appeared in 9i, because of Bitmap conversions (I translated that in “when people migrated from RBO to CBO”). This is not what I experienced here: my problem was not the execution phase (for which incidentally no bitmap transformation was used), but the parse phase.
Bug 3537086 seems to be the best candidate for this problem, but it is not publicly documented enough to be affirmative.
What is bitmap conversion (to and from ROWIDS)? It is basically a B-tree transformation to bitmap (hence no bitmap indexes involved here). Using the bitmap conversion, the CBO will convert each set of rowids into an array of bits, which will be used exactly as it would be in a bitmap index operation. Access to table data is performed after the join using a bitmap conversion to rowids.