BITMAP CONVERSION (TO ROWIDS)

Oracle SQL Tuning
One fine Monday morning a DBA in our group contacted me for a SQL performance problem in
a production system.  

Description of the problem::
A 8i database has been running for several years w/o any problem. One Sunday the DBA
applied a critical patch update (CPU) as per the business policy, and the performance problem
started on the next day morning. He identified the query which was executed several
thousands time in a day and he showed me the execution plan for the SQL.  The optimizer
has chosen "BITMAP CONVERSION (TO ROWIDS)" operation which was not selected prior to
critical patch update. He tested the same SQL in test database and the plan was different and
optimal.

The plan looks like:

select /*+ AND_EQUAL(T1, T1_IDX_1, T1_IDX_2) */     *
from T1
where object_id between 1000 and 1020
and object_name like 'T%' ;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=1 Bytes=96)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=21 Card=1 Bytes=96)
2    1     BITMAP CONVERSION (TO ROWIDS)
3    2       BITMAP AND
4    3         BITMAP CONVERSION (FROM ROWIDS)
5    4           SORT (ORDER BY)
6    5             INDEX (RANGE SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=2 Card=20)
7    3         BITMAP CONVERSION (FROM ROWIDS)
8    7           SORT (ORDER BY)
9    8             INDEX (RANGE SCAN) OF 'T1_IDX_2' (NON-UNIQUE) (Cost=2 Card=20

The above SQL was not the original SQL statement that gave performance problem.  I just
simulated a test case to show BITMAP CONVERSION operation in the plan.

Coming back to original problem.

The original query used 2 tables join - one permanent table and another global temporary
table.  Statistics are collected for both tables. But still the CBO chooses the "BITMAP
CONVERSION" operation and the query was running slow.  

The application first populates the global temporary table around 800 rows prior to execute
the SQL statement.

The root cause of the problem was improper statistics available  on the global temporary table.
Even if I load the data into global temporary table, and collect statistics, the CBO still  chose
the "BITMAP CONVERSION" operation, and the run time was around 2 min.

I guessed some important statistics was missing on the global temporary table. Remember
this was a 8i database.  

To solve the problem, first I created a permanent table equivalent to the global temporary
table and asked the developer to load data into the permanent table.
Then I analyzed the table and used DBMS_STATS.CREATE_STAT_TABLE, and
DBMS_STATS.EXPORT_TABLE_STATS to export the table statistics in a table.

In this table, I updated the table name (Column C1)  with the global temporary table name
and used  DBMS_STATS.IMPORT_TABLE_STATS to import the statistics.
I also verified the new statistics in the USER_TABLES view.  They were perfect now.

I ran the SQL statement and enabled the trace.

Bingo, the new execution plan did not use BITMAP CONVERSION (TO ROWIDS) and the run
time was less than 5 seconds.

And the problem was solved.
Admonition:
1.  Always test, measure and document the performance first a test environment.
2.  If the performance is OK, then apply the change in production DB.
3.  Watch carefully the db performance next few days. You will be surprised by new
problems or bug in the CBO after the change has been done.
Hope, this helps.
Tamilselvan G