LIO Optimierung

Randolf Geist hat eine Artikelserie begonnen, in der er verspricht diverse Optimierungen für die Durchführung von logical I/O zu erläutern, die in den letzten Versionen des Oracle Servers eingeführt wurden. Der erste Artikel Logical I/O - Evolution: Part 1 - Baseline stellt zunächst die Voraussetzungen dar - und das in sehr strukturierter Form mit einem griffigen Beispiel und ausführlichen Erläuterungen zu den Ergebnissen. Zum Artikel noch zwei Anmerkungen:

MINIMIZE RECORDS_PER_BLOCK

Interessant ist die verwendete Option "MINIMIZE RECORDS_PER_BLOCK", die die maximale Anzahl von Datensätzen pro Block begrenzt - offenbar ein Feature, das eigentlich zur Optimierung von Bitmap Indizes eingeführt wurde. Laut Doku gilt: "The records_per_block_clause lets you specify whether Oracle Database restricts the number of records that can be stored in a block. This clause ensures that any bitmap indexes subsequently created on the table will be as compressed as possible. [...] Specify MINIMIZE to instruct Oracle Database to calculate the largest number of records in any block in the table and to limit future inserts so that no block can contain more than that number of records."

Das Verhalten dieser Option entspricht übrigens nicht ganz meinen Vorstellungen, wie folgender Test (mit 11.2.0.1) zeigt:

-- Anlage einer Tabelle mit einem Datensatz in einem non-assm-Tablespace
create table t1 tablespace test_ts
as
select rownum id
  from dual;

-- Erzeugung von Statistiken
exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

-- Angaben aus USER_TABLES
select num_rows
     , blocks
  from user_tables
 where table_name = 'T1';

NUM_ROWS     BLOCKS
-------- ----------
       1          1

-- ohne minimize records_per_block
insert into t1
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

NUM_ROWS     BLOCKS
-------- ----------
   10000         20

-- mit minimize records_per_block
-- Neuanlage der Tabelle mit einem Satz
alter table t1 minimize records_per_block;


insert into t1
select rownum id
  from dual
connect by level < 10000;

exec dbms_stats.gather_table_stats(user, 'T1', estimate_percent=>100)

NUM_ROWS     BLOCKS
-------- ----------
   10000       5001 

Demnach komme ich auch zwei Sätze pro Block, obwohl vor dem ALTER TABLE nur ein Satz im Block vorliegen kann; allerdings sagt die Doku auch: "Oracle recommends that a representative set of data already exist in the table before you specify MINIMIZE" - möglicherweise ergibt sich der beobachtete Effekt also daraus, dass die Abschätzung der rows per block nicht 100% akkurat ist. Für das Beispiel des Herrn Geist ist das aber irrelevant, da er zum gewünschten rows per block-Verhältnis kommt.

Nachtrag 17.07.2011: in den Kommentaren zu Randolf Geists Blog-Eintrag findet man noch ein paar Ergänzungen zum Thema (etwa, dass der Hakan factor, der die Anzahl möglicher Sätze in einem Block angibt, in der SPARE1 Spalte von sys.tab$ zu finden ist); möglicherweise kann man über "minimize records_per_block" keine Block-Füllung mit einem einzelnen Satz hervorrufen.

Nachtrag 19.07.2011: mal wieder eine interessante Koinzindenz: Richard Foote hat gerade auch über die "minimize records_per_block" Option geschrieben.

Pinned Buffers

Im verwendeten Nested Loops Join liegt die Gesamtzahl der Blockzugriffe deutlich niedriger als erwartet, da die per FTS ermittelten Blocks der inneren Tabelle und der Root Block des Index gepinnt werden können, so dass kein Get für das cache buffers chains latch erforderlich ist - und damit kein logical I/O - vgl. dazu Jonathan Lewis Ausführungen, die ich gelegentlich hier verlinkt hatte. Die Anzahl gepinnter Blocks liefert die Statistik "buffer is pinned count". Das Pinnen der Buffer ist auch die Ursache für unterschiedliche LIO-Werte bei veränderter Arraysize:

Note that buffer pinning is not possible across fetch calls - if the control is returned to the client the buffers will no longer be kept pinned. This is the explanation why a the "fetchsize" or "arraysize" for bulk fetches can influence the number of logical I/Os required to process a result set.