Trying to understand the BITMAP AND operation a bit better

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 6 Jan 2016 14:50:57 -0600
Message-ID: <CAPZQniURVO3hQEukt78WNwvdSs3=jYavqP5NUYkctwVa=DZbRQ_at_mail.gmail.com>

Good day, Oracle-l folks,

Over the past few weeks I have had an occasion to dive into bitmap indexes. I am working on a performance issue and I am trying to understand what the output of dbms_xplan is telling me; specifically, I am trying to learn why the number of buffers (or blocks, "consistent reads") varies so widely for what I believe are the exact same operations. In the examples below, I use the following command to display the plan: select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

For example, here is one query plan:

---

| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers |

---

| 0 | SELECT STATEMENT | | 1 | |

 8 |00:00:00.01 |     134 |


|   1 |  NESTED LOOPS                  |            |      1 |      1 |

 8 |00:00:00.01 |     134 |


|   2 |   NESTED LOOPS                 |            |      1 |      1 |

 8 |00:00:00.01 |     126 |


|   3 |    NESTED LOOPS                |            |      1 |      5 |

 5 |00:00:00.01 |       7 |


|   4 |     TABLE ACCESS BY INDEX ROWID| DIMENSION1 |      1 |      1 |

 1 |00:00:00.01 |       3 |
|*  5 |      INDEX UNIQUE SCAN         | AK_DIM1    |      1 |      1 |
 1 |00:00:00.01 |       2 |
|*  6 |     TABLE ACCESS FULL          | DIMENSION2 |      1 |      5 |
 5 |00:00:00.01 |       4 |


|   7 |    BITMAP CONVERSION TO ROWIDS |            |      5 |        |

 8 |00:00:00.01 |     119 |


|   8 |     BITMAP AND                 |            |      5 |        |

 1 |00:00:00.01 |     119 |
|*  9 |      BITMAP INDEX SINGLE VALUE | FACT_DIM1  |      5 |        |
20 |00:00:00.01 |      28 |
|* 10 |      BITMAP INDEX SINGLE VALUE | FACT_DIM2  |      5 |        |
 140 |00:00:00.01 |      78 |
|* 11 |      BITMAP INDEX SINGLE VALUE | FACT_0     |      5 |        |
 5 |00:00:00.01 |      13 |


|  12 |   TABLE ACCESS BY INDEX ROWID  | FACT       |      8 |      1 |

 8 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   5 - access("DIMENSION1"."ID2"=2)
   6 - filter("DIMENSION2"."ID2"=100)
   9 - access("FACT"."C1"="DIMENSION1"."ID")
  10 - access("FACT"."C2"="DIMENSION2"."ID")
  11 - access("FACT"."C0"=243001)

Here is the same query but with different FACT table stats:

---

| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers |

---

| 0 | SELECT STATEMENT | | 1 | |

   8 |00:00:00.01 |      67 |


|   1 |  NESTED LOOPS                   |            |      1 |      1 |

   8 |00:00:00.01 |      67 |


|   2 |   NESTED LOOPS                  |            |      1 |      1 |

  24 |00:00:00.01 |      43 |


|   3 |    NESTED LOOPS                 |            |      1 |      1 |

  24 |00:00:00.01 |      34 |


|   4 |     TABLE ACCESS BY INDEX ROWID | DIMENSION1 |      1 |      1 |

   1 |00:00:00.01 |       3 |
|*  5 |      INDEX UNIQUE SCAN          | AK_DIM1    |      1 |      1 |
   1 |00:00:00.01 |       2 |


|   6 |     TABLE ACCESS BY INDEX ROWID | FACT       |      1 |      1 |

  24 |00:00:00.01 |      31 |


|   7 |      BITMAP CONVERSION TO ROWIDS|            |      1 |        |

  24 |00:00:00.01 |       7 |


|   8 |       BITMAP AND                |            |      1 |        |

   1 |00:00:00.01 |       7 |
|*  9 |        BITMAP INDEX SINGLE VALUE| FACT_0     |      1 |        |
   1 |00:00:00.01 |       3 |
|* 10 |        BITMAP INDEX SINGLE VALUE| FACT_DIM1  |      1 |        |
   4 |00:00:00.01 |       4 |
|* 11 |    INDEX UNIQUE SCAN            | PK_DIM2    |     24 |      1 |
  24 |00:00:00.01 |       9 |
|* 12 |   TABLE ACCESS BY INDEX ROWID   | DIMENSION2 |     24 |      1 |
   8 |00:00:00.01 |      24 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   5 - access("DIMENSION1"."ID2"=2)
   9 - access("FACT"."C0"=243001)
  10 - access("FACT"."C1"="DIMENSION1"."ID")
  11 - access("FACT"."C2"="DIMENSION2"."ID")
  12 - filter("DIMENSION2"."ID2"=100)

Note how the number of buffers for the BITMAP INDEX SINGLE VALUE operations are quite different between the two plans. But I believe they are retrieving the exact same data (look at the "predicate information").

What exactly is the "Buffers" information telling me?

I have a test case using Jonathan Lewis's "Mything 2 <https://jonathanlewis.wordpress.com/2011/06/24/mything-2/>" example if anyone wants the create scripts. Running 11.2.0.4 EE on Solaris 10. Also, I realize the timings are very fast and indistinguishable between the two examples - this is a simplified example of a larger problem. :)

Thanks for your time and attention.

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 06 2016 - 21:50:57 CET