Oracle Scratchpad

June 24, 2011

Mything 2

Filed under: Execution plans,Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 5:51 pm BST Jun 24,2011

It’s about time I wrote a sequel to Mything in Action – and funnily enough it’s also about bitmap indexes. It starts with a note on the OTN database forum that prompted me to run up a quick test to examine something that turned out to be a limitation in the optimizer. The problem was that the optimizer didn’t do a “bitmap and” between two indexes when it was obviously a reasonable – possibly even good – idea. Here’s some sample code:

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
)
select
	mod(rownum-1,10)	c1,
	mod(rownum-1,100)	c2,
	mod(rownum-1,101)	c3,
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000000
;

-- stats collection goes here.

create bitmap index t1_b1b2 on t1(c1,c2);
create bitmap index t1_b1b3 on t1(c1,c3);

This was a reasonable model of the situation described in the original posting; and here’s the critical query with the surprise execution path:

select
	/*+
		index_combine(t1 t1_b1b2 t1_b1b3)
	*/
	*
from
	t1
where
	c1 = 5
and	c2 = 50
and	c3 = 50
;

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |   231 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |   231 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE | T1_B1B2 |       |       |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C3"=50)
   3 - access("C1"=5 AND "C2"=50)

You might look at the query and the indexing and decide (as I did) that Oracle ought to be able to manage a “bitmap index single value” on both the indexes, then do a “bitmap and” to minimise the work – something like:

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |     6 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |     6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
|   3 |    BITMAP AND                |         |       |       |       |
|*  4 |     BITMAP INDEX SINGLE VALUE| T1_B1B2 |       |       |       |
|*  5 |     BITMAP INDEX SINGLE VALUE| T1_B1B3 |       |       |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"=5 AND "C2"=50)
   5 - access("C1"=5 AND "C3"=50)

But it doesn’t – and there’s a clue about why not in the “Predicate Information”. To create this plan the optimizer would have to duplicate an existing predicate (c1 = 5) so that it could find the second index after selecting the first one. There’s no reason, of course, why the optimizer code couldn’t do this – but at present, even in 11.2.0.2, it just doesn’t. Perhaps this is another opportunity for thinking about manual optimisation strategies – or perhaps ensuring that you’ve created the right set of indexes.

You might notice, of course, that Oracle seems to have ignored my index_combine() hint. Oracle doesn’t ignore hints, of course (apart from cases suffering from problems with syntax, legality, or bugs) but remember that index_combine() is only supplying a list of indexes that Oracle should consider, it doesn’t require the optimizer to use every index in the list. In this case, of course, the hint also has an error because it’s naming an index that can’t be used.

Anyway, I wrote a note suggesting that there was a gap in the optimizer’s strategies, specifically:

I’ve just spent a few minutes playing with a data set where this (c1,c2) (c1,c3) type of index combination is obviously a good idea – and can’t get the bitmap_tree() hint to force the path. I think this means there’s a hole in the optimizer’s legal strategies that you might have to fill by other methods.

Here’s where the mything starts. The OP replied as follows:

Do I right understand that it is impossible to combine bitmap non-one-column indexes?

ABSOLUTELY NOT!, the OP has jumped from the particular to the general; fortunately he asked the question, rather than silently making the assumption then spreading the gospel. Of course I was at fault because I could have pointed out explicitly that the pattern was dependent on the two indexes starting with the same column – but is it so hard to interpret patterns.

What’s more annoying is that the OP was already using a model to test what happened – would it have been so hard for him to try a few different combinations of indexes – switching the column order on both indexes. For example what happens if the indexes are (c2, c1)(c3,c1) ?


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |    12   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |            |          |
|   3 |    BITMAP AND                |         |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| T1_B2B1 |       |       |            |          |
|   5 |     BITMAP MERGE             |         |       |       |            |          |
|*  6 |      BITMAP INDEX RANGE SCAN | T1_B3B1 |       |       |            |          |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C2"=50 AND "C1"=5)
   6 - access("C3"=50)
       filter("C3"=50)

See how easy it is to show that the optimizer can combine multi-column bitmap indexes; but we can observe, at the same time, that it doesn’t make “perfect” use of the (c3, c1) index. Oracle still does excess work in the index because it hasn’t repeated the use of the c1 predicate.

Maxim:

When you see some unexpected behaviour the least you should do when investigating it is to ask yourself: “in what way might this be a special case?”

3 Comments »

  1. Completely interesting, and great advice to avoid overgeneralizing when going from facts and logic to a theory. (And especially avoiding “mything” a a theory based on one fact and no case analysis.)

    And, of course, since all the columns in the example have specified values, it begs another myth: Why does Oracle go to the table at all (even in the plan) when it has all the values for all the attributes in hand? You’d like to think it only needs to count the satisfaction of the bitmap and and produce the requisite number of rows. (That’s a less trivial optimization if it potentially eliminates several tables from a join or facilitates skipping the return of the data from storage to memory on platforms where that is possible.)

    But does Oracle always fail to skip tables you can prove from a semantic analysis are not needed? I don’t know – at this point that would be a myth, or more kindly a suspicion.

    Thanks for throwing down the guantlet challenging folks to keep logic tight and avoid overgeneralization.

    Comment by Mark W. Farnham — June 25, 2011 @ 1:59 pm BST Jun 25,2011 | Reply

  2. Hi jonathan

    I have a query which is using 2 indexes both are bitmap indexes (sizes are 37 and 24 Mbs) and table size is 17gb. While i ran the following query
    which can very well get the index itself, it takes around 6-8 minutes and using pga around 3 gb.

    could you please explain me why ?

    SQL_ID  5z0a50a2yzdky, child number 0
    -------------------------------------
    select count(1) from (select distinct SRC_SYS,PROD_KEY from dds.REV_F)
    
    Plan hash value: 867747470
    
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                      |       |       |   221K(100)|          |       |       |
    |   1 |  SORT AGGREGATE                   |                      |     1 |       |            |          |       |       |
    |   2 |   VIEW                            |                      | 24533 |       |   221K  (6)| 00:44:22 |       |       |
    |   3 |    HASH UNIQUE                    |                      | 24533 |   479K|   221K  (6)| 00:44:22 |       |       |
    |   4 |     VIEW                          | index$_join$_002     |    63M|  1209M|   212K  (2)| 00:42:28 |       |       |
    |*  5 |      HASH JOIN                    |                      |       |       |            |          |       |       |
    |   6 |       PARTITION LIST ALL          |                      |    63M|  1209M|  3591   (1)| 00:00:44 |     1 |   145 |
    |   7 |        BITMAP CONVERSION TO ROWIDS|                      |    63M|  1209M|  3591   (1)| 00:00:44 |       |       |
    |   8 |         BITMAP INDEX FULL SCAN    | REV_F_IDX1           |       |       |            |          |     1 |   145 |
    |   9 |       PARTITION LIST ALL          |                      |    63M|  1209M| 13724   (1)| 00:02:45 |     1 |   145 |
    |  10 |        BITMAP CONVERSION TO ROWIDS|                      |    63M|  1209M| 13724   (1)| 00:02:45 |       |       |
    |  11 |         BITMAP INDEX FULL SCAN    | REV_F_IDX5           |       |       |            |          |     1 |   145 |
    --------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access(ROWID=ROWID)
    
    
    28 rows selected.
    
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2    610.89    1464.86     707459      17090          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4    610.90    1464.87     707459      17090          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: SYS
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=17090 pr=707459 pw=446115 time=1464867976 us)
      26066   VIEW  (cr=17090 pr=707459 pw=446115 time=1464795748 us)
      26066    HASH UNIQUE (cr=17090 pr=707459 pw=446115 time=1464769678 us)
    63422824     VIEW  index$_join$_002 (cr=17090 pr=707459 pw=446115 time=1084846545 us)
    63422824      HASH JOIN  (cr=17090 pr=707459 pw=446115 time=958000889 us)
    63422824       PARTITION LIST ALL PARTITION: 1 145 (cr=3561 pr=0 pw=0 time=63423134 us)
    63422824        BITMAP CONVERSION TO ROWIDS (cr=3561 pr=0 pw=0 time=9554 us)
       7112         BITMAP INDEX FULL SCAN REV_F_IDX1 PARTITION: 1 145 (cr=3561 pr=0 pw=0 time=155525 us)(object id 366074)
    63422824       PARTITION LIST ALL PARTITION: 1 145 (cr=13529 pr=8864 pw=0 time=190268771 us)
    63422824        BITMAP CONVERSION TO ROWIDS (cr=13529 pr=8864 pw=0 time=63553723 us)
     432700         BITMAP INDEX FULL SCAN REV_F_IDX5 PARTITION: 1 145 (cr=13529 pr=8864 pw=0 time=3157351 us)(object id 366658)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      direct path write temp                      29741        1.62        107.05
      db file sequential read                      8864        0.20          2.35
      direct path read temp                       46573        0.79        211.02
      SQL*Net message from client                     2       29.22         29.22
    

    Comment by goiyala3 — June 28, 2011 @ 7:37 am BST Jun 28,2011 | Reply

    • goiyala3,

      This isn’t really the right place to ask the question – but it’s an interesting example and IS associated (loosely) with the topic, so I’m going to see if I can find some time later on this week to explain what’s going on. I’ll copy this comment into a new post to do so.

      The first thing to note, of course, is that Oracle can only do the index hash join after translating the bitmaps for the whole table into rowids – and this is why you use so much PGA. Oracle has, internally, no mechanismm for taking advantage of partitionwise processing to do the job you want – and that’s key to rewriting the query.

      Comment by Jonathan Lewis — June 28, 2011 @ 8:45 am BST Jun 28,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers