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
