Anomaly in “BITMAP CONVERSION TO ROWIDS” estimation

Let’s create our test harness first:

CREATE TABLE IDXTEST AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad(‘a’,399,’a’) filler
from dual connect by level <2e6
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST_IDX1 ON IDXTEST (id2);

I’ve created a table with column id2 having a single unique value per 1000 rows.

Let’s have a look at 2 execution plans (of 2 a bit different queries):
1st one uses bitmap index to return all rows for given indexed column


SQL_ID  bv039034q966g, child number 1                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select id2 FROM IDXTEST                                                                                                                                                                                 
                                                                                                                                                                                                        
Plan hash value: 1842016195                                                                                                                                                                             
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------                                                                                                           
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
|   0 | SELECT STATEMENT             |              |       |       |   900 (100)|          |                                                                                                           
|   1 |  BITMAP CONVERSION TO ROWIDS |              |  1999K|    11M|   900   (0)| 00:00:01 |                                                                                                           
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |       |       |            |          |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
                                                                                                                                                                                                    

Second one returns distinct value of bitmap indexed column:

SQL_ID  0gtn94fcg82hj, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select distinct id2 FROM IDXTEST                                                                                                                                                                        
                                                                                                                                                                                                        
Plan hash value: 473080087                                                                                                                                                                              
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------                                                                                                           
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
|   0 | SELECT STATEMENT             |              |       |       |   949 (100)|          |                                                                                                           
|   1 |  HASH UNIQUE                 |              |  2000 | 12000 |   949   (6)| 00:00:01 |                                                                                                           
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |  1999K|    11M|   900   (0)| 00:00:01 |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
                                                                                                                                                                                                        

You can see that Oracle estimate the 2nd query a bit higher and it doesn’t estimate “BITMAP CONVERSION TO ROWIDS” at all which is very suspicious for me as that operation on big datasets can be both CPU and IO(temp) intensive.
Let’s see what is in reality?

Let’s repeat our exercise one more time with ALTER SESSION SET statistics_level=ALL
And show execution plan as select * from TABLE(dbms_xplan.display_cursor(null,null,’typical +iostats last’));
instead of select * from TABLE(dbms_xplan.display);

So here is my results:
1st execution:

SQL_ID  bv039034q966g, child number 2                                                                                                  
-------------------------------------                                                                                                  
select id2 FROM IDXTEST                                                                                                                
                                                                                                                                       
Plan hash value: 1842016195                                                                                                            
                                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------                                                                 
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
---------------------------------------------------------------------------------------------------------------------------------------                                                                 
|   0 | SELECT STATEMENT             |              |      1 |        |       |   900 (100)|          |   1999K|00:00:05.93 |    2012 | 
|   1 |  BITMAP CONVERSION TO ROWIDS |              |      1 |   1999K|    11M|   900   (0)| 00:00:01 |   1999K|00:00:05.93 |    2012 | 
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |      1 |        |       |            |          |   2000 |00:00:00.05 |    2012 | 
---------------------------------------------------------------------------------------------------------------------------------------

2nd execution:

SQL_ID  0gtn94fcg82hj, child number 1                                                                                                  
-------------------------------------                                                                                                  
select distinct id2 FROM IDXTEST                                                                                                       
                                                                                                                                       
Plan hash value: 473080087                                                                                                              
                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
--------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |       |   949 (100)|          |   2000 |00:00:00.02 |    1012 | 
|   1 |  HASH UNIQUE                 |              |      1 |   2000 | 12000 |   949   (6)| 00:00:01 |   2000 |00:00:00.02 |    1012 | 
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |      1 |   1999K|    11M|   900   (0)| 00:00:01 |   2000 |00:00:00.01 |    1012 | 
--------------------------------------------------------------------------------------------------------------------------------------- 

You can see that in reality 2nd execution is faster more than 100 times (0.02 sec vs 5 sec).
It seems a severe limitation of Oracle optimizer that can block potentially very beneficial (from performance perspective) execution plans. I’m going to publish some “near real life” example in my next blog.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s