Freitag, Juni 29, 2012

Lokale bitmap Indizes und COUNT(*)

Zu den Vorzügen von Bitmap Indizes gehört, dass sie NULL-Werte enthalten und deshalb sehr gut zum Zählen der Datensätze einer Tabelle geeignet sind. Dieser Vorteil wird dadurch noch verstärkt, dass ein Bitmap Index in der Regel relativ klein ist - für entsprechend geclusterte Daten jedenfalls deutlich kleiner als ein adäquater B*Tree-Index. Dieser Tage ist mir allerdings aufgefallen, dass ich mir zum Zusammenspiel von lokalen Bitmap Indizes mit COUNT-Queries bisher falsche Hoffnungen gemacht habe. Meine Idee dabei war folgende:
  • Eine partitionierte Tabelle gliedert ein größeres Segment in kleinere Teil-Segmente.
  • Lokale Indizes beschränken sich immer auf eine Partition.
  • Deshalb sollte eine Query der Form: SELECT COUNT(*) FROM partitioned_table WHERE partition_key = ... dazu in der Lage sein, zunächst durch partition elimination das passende Tabellen-Segment und seine Indizes zu ermitteln und dann einen beliebigen bitmap index zu verwenden, um daraus die erforderlichen Daten zu ziehen, ohne dabei auf die Tabelle zugreifen zu müssen.
Das schien mir plausibel, ist es aber anscheinend nicht - jedenfalls funktioniert es so nicht:

drop table test_partition_bidx;
create table test_partition_bidx
( id number
, startdate date
, col1 number
, padding varchar2(100) 
)
   partition by range (startdate)
  (partition p1 values less than ( to_date('30.06.2012','dd.mm.yyyy')),
   partition p2 values less than ( to_date('01.07.2012','dd.mm.yyyy')),
   partition p3 values less than ( to_date('02.07.2012','dd.mm.yyyy')),
   partition p4 values less than ( to_date('03.07.2012','dd.mm.yyyy')),
   partition p5 values less than ( to_date('04.07.2012','dd.mm.yyyy'))
  );

-- Daten einfügen
insert into test_partition_bidx
select rownum
     , to_date('30.06.2012','dd.mm.yyyy') + trunc((rownum -1)/200000) 
     , trunc(rownum - 1/10000)
     , lpad('*', 100, '*') 
  from dual 
connect by level <= 1000000;

-- Statistiken erzeugen
exec dbms_stats.gather_table_stats(user, 'test_partition_bidx')

-- bitmap index anlegen
create bitmap index ix_test_partition_bidx on test_partition_bidx(col1) local;

-- Query-Ausführung mit autotrace
set autot on
SQL> select count(*) from test_partition_bidx where startdate = '01.07.2012';

  COUNT(*)
----------
    250000

Abgelaufen: 00:00:00.35

Ausführungsplan
----------------------------------------------------------
Plan hash value: 788106769

---------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |     1 |     8 |  1199   (1)| 00:00:15 |       |       |
|   1 |  SORT AGGREGATE         |                     |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|                     |   250K|  1953K|  1199   (1)| 00:00:15 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL    | TEST_PARTITION_BIDX |   250K|  1953K|  1199   (1)| 00:00:15 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4360  consistent gets
       4354  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Demnach funktioniert die partition elimination, aber der Zugriff erfolgt dann über FTS, obwohl der Index deutlich kleiner ist als die Tabelle (laut DBA_SEGMENTS: 3976 BLOCKS im Index gegenüber 17928 BLOCKS in der Tabelle).

Wenn man dem CBO den Index-Zugriff per Hint nahelegt, ergibt sich Folgendes:

select /*+ index (t) */ count(*) from test_partition_bidx t where startdate = '01.07.2012';

Abgelaufen: 00:00:03.35

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3608709152

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |     8 |  7873   (1)| 00:01:35 |       |       |
|   1 |  SORT AGGREGATE                     |                        |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |                        |   250K|  1953K|  7873   (1)| 00:01:35 |     3 |     3 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PARTITION_BIDX    |   250K|  1953K|  7873   (1)| 00:01:35 |     3 |     3 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                        |       |       |            |          |       |       |
|   5 |      BITMAP INDEX FULL SCAN         | IX_TEST_PARTITION_BIDX |       |       |            |          |     3 |     3 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5185  consistent gets
       5142  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Der Hint erzwingt den Index-Zugriff - aber nicht in gewünschter Form: statt eines INDEX FAST FULL SCANs erfolgt ein INDEX FULL SCAN und anschließend wird eine teuere BITMAP CONVERSION TO ROWIDS durchgeführt, gefolgt von einem TABLE ACCESS BY LOCAL INDEX ROWID, der eigentlich nicht erforderlich sein sollte. Die Zahl der LIOs ist dabei dann höher als für den FTS-Fall und die Laufzeit steigt dramatisch. Der Versuch, den Zugriff über einen INDEX_FFS-Hint auf einen INDEX FAST FULL SCAN zu lenken, bringt leider keine Besserung: in diesem Fall wählt der CBO wieder den FTS.

Um das Zählen der Sätze in den Partitionen zu beschleunigen, hilft ein bitmap Index auf dem partition key:


create bitmap index ix2_test_partition_bidx on test_partition_bidx(startdate) local;

select count(*) from test_partition_bidx where startdate = '01.07.2012';

Abgelaufen: 00:00:00.03

Ausführungsplan
----------------------------------------------------------
Plan hash value: 296465563

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |     1 |     8 |     6   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                |                         |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE       |                         |   250K|  1953K|     6   (0)| 00:00:01 |     3 |     3 |
|   3 |    BITMAP CONVERSION COUNT     |                         |   250K|  1953K|     6   (0)| 00:00:01 |       |       |
|*  4 |     BITMAP INDEX FAST FULL SCAN| IX2_TEST_PARTITION_BIDX |       |       |            |          |     3 |     3 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          7  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Hier ergibt sich dann das, was ich mir gewünscht habe: ein INDEX FAST FULL SCAN mit anschließendem BITMAP CONVERSION COUNT und ohne BITMAP CONVERSION TO ROWIDS und Tabellen-Zugriff. Die LIOs sinken auf übersichtliche 12 und die Laufzeit ist geringfügig.

Aber warum funktioniert das nur mit einem bitmap Index, der den partition key enthält (geeignet wäre auch ein mehrspaltiger bitmap index, der den partition key enthält)? Die Antwort lautet: ich weiß es nicht. Anscheinend verknüpft der CBO in diesem Fall nicht alle Informationen, die ihm zur Verfügung stehen und führt deshalb im Step 3 TABLE ACCESS BY LOCAL INDEX ROWID die Filterung filter("STARTDATE"=TO_DATE(' 2012-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) durch, die eigentlich redundant ist, weil sie bereits durch die partition elimination sichergestellt ist. Bis mir jemand eine bessere Erklärung liefert, gehe ich davon aus, dass es sich hier wieder um einen Fall handelt, in dem der CBO weniger clever ist, als er sein könnte.

Nachtrag 03.07.2012: Die Erklärung des Verhaltens liefert Randolf Geist in seinem Kommentar zum Artikel: ich weiß zwar, dass jede Partition nur einen Tag enthält, aber der CBO kann das nicht wissen und muss die bitmap Struktur deshalb auspacken. Mit der von Randolf vorgeschlagenen LIST-Partitionierung bekomme ich ohne Hints immer noch einen FTS, aber mit Index-Hint ergibt sich ein INDEX FULL SCAN mit folgendem BITMAP CONVERSION COUNT.

Kommentare:

  1. Hallo Martin,

    die Erklärung dürfte recht einfach sein:

    Du hast eine RANGE-Partitionierung vorgenommen, und gibst ein Filter an auf STARTDATE, um auf eine Partition einzuschränken.

    Theoretisch können aber viele STARTDATEs in dieser einen Partition sein (RANGE von ... bis), insofern kann Oracle das Prädikat nicht einfach ignorieren nach der Partitionseinschränkung.

    Wenn STARTDATE also nicht im Index enthalten ist, muss Oracle auf die Tabelle zugreifen, um den Filter anwenden zu können.

    Du kannst es ja spaßeshalber mal mit einer LIST-Partitionierung probieren, mit nur einem gültigen Wert pro Partition, wie sich dann der CBO verhält.

    Randolf

    AntwortenLöschen
  2. Hallo Randolf,

    ja, das leuchtet ein - Danke.

    Martin

    AntwortenLöschen
  3. Hallo Randolf,

    mit der LIST Partitionierung ergibt sich ein INDEX FULL SCAN mit folgendem BITMAP CONVERSION COUNT. Noch nicht so recht klar ist mir, wieso es ein INDEX FULL SCAN und kein INDEX FAST FULL SCAN geworden ist - wobei ich den INDEX FULL SCAN als sukzessives Lesen der sortierten Index-Struktur definieren würde, im Gegensatz zum INDEX FAST FULL SCAN als einer FTS-artigen Operation, bei der das Index-Segment unsortiert über multi block I/O gelesen wird. Beim Vergleich der Session-Statistiken der FTS- und Index-Variante ist mir als auffälligstes Element das Auftreten von "physical reads cache prefetch" und "physical reads prefetch warmup" (jeweils 531 mal) im Index-Fall begegnet, über das Tanel Poder kürzlich geschrieben hat: http://blog.tanelpoder.com/2012/05/02/advanced-oracle-troubleshooting-guide-part-10-index-unique-scan-doing-multiblock-reads/. Aber einen unmittelbaren inhaltlichen Zusammenhang zwischen dem Prefetching und dem INDEX FULL SCAN sehe ich erst mal noch nicht.

    Martin

    AntwortenLöschen
  4. Hallo Martin,

    noch ein paar Anmerkungen:

    1. Ich hatte vergessen zu erwähnen, dass Du auch mit der RANGE-Partitionierung es erreichen kannst, dass Oracle den Filter weglassen kann, z.B. mittels folgender Einschränkung:

    where startdate >= to_date('01.07.2012', 'DD.MM.YYYY') and startdate < to_date('02.07.2012', 'DD.MM.YYYY')

    Dann würde der Optimizer erkennen, dass eine gesamte Partition abgedeckt ist und daher kein Filter mehr angewendet werden muss

    2. Du bekommst den INDEX FULL SCAN wahrscheinlich weil Du einen INDEX-Hint verwendest, der nunmal kein INDEX FAST FULL SCAN beinhaltet - der entsprechende Hint wäre INDEX_FFS

    3. Es scheint ein Problem im CBO zu geben, da der INDEX_FFS-Hint in einem solchen Fall, obwohl möglich, nicht akzeptiert wird, während der INDEX-Hint akzeptiert wird

    Offensichtlich hängt es damit zusammen, dass die Angabe der WHERE-Klausel mit dem STARTDATE den INDEX_FFS-Fall verhindert. Wenn direkt explizit auf die Partition eingeschränkt wird (...from test_partition_bidx partition(p3)...), dann wird sehr wohl der INDEX_FFS akzeptiert bzw. automatisch vom CBO gewählt, wenn kostengünstiger als der FTS.

    Du hast also doch noch eine Schwachstelle des CBOs gefunden, wenn auch eine etwas andere, als ursprünglich erwähnt - das Problem ist auch in 11.2.0.3 noch reproduzierbar, also auch in aktuellen Versionen noch vorhanden.

    Randolf

    AntwortenLöschen
  5. Hallo Randolf,

    vielen Dank für die Hinweise. Mit dem INDEX_FFS-Hint war ich im Fall der List-partitionierten Tabelle wieder beim FTS gelandet, was ich im Kommentar vergessen hatte zu erwähnen. Aber das passt ja dann exakt zu Deiner Erklärung.

    Martin

    AntwortenLöschen