Mehr Performance durch Index-Preloading

Wie kann man die Performance von Zugriffen auf den Textindex erhöhen? Eine Möglichkeit besteht darin, Tabellen und Indizes im Cache zu halten, damit möglichst wenig I/O durchgeführt wird. Roger Ford, Development Manager für Oracle Text, hat zu diesem Thema schon vor längerer Zeit einen Artikel auf OTN verfasst. Da das Thema nicht an Relevanz verloren hat, wollen wir auch hier in unserem Blog das Thema besprechen.
Die Technik, die man zum optimierten Speichern von Segmenten im Cache verwendet, ist die Nutzung von unterschiedlichen Pools im Datenbank Cache. Es ist zum Beispiel sinnvoll, Objekte, auf die sehr häufig zugegriffen wird (z.B. Lookup-Tabellen), dem sogenannten KEEP Pool Cache zuzuordnen. Dabei ist der KEEP Cache ein eigener Bereich im Datenbank Cache, der mit dem Parameter DB_KEEP_CACHE_SIZE konfiguriert wird. Standardmässig ist der Wert auf 0 gesetzt.
Drei Schritte sind zur Nutzung des KEEP Pools nötig:

• die Bestimmung der Größe des KEEP Pools und Setzen von DB_KEEP_CACHE_SIZE
• das Setzen der KEEP Storage Option im Index- und Tabellen-Segment
• das Laden der Objekte in den Cache mit den entsprechenden SQL-Kommandos

Um ein Gefühl für die Größeneinstellung des Pools zu erhalten, kann man sich zuerst die Größe des Index ansehen. Dazu eignet sich die Funktion CTX_REPORT, wie folgt:

SET long 10000
SELECT ctx_report.index_size('TXT_IDX') FROM dual;

TOTALS FOR INDEX US.TXT_IDX
--------------------------------------------------------------------------
CTX_REPORT.INDEX_SIZE('TXT_IDX')
--------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED:                                              51496
TOTAL BLOCKS USED:                                                   51167
TOTAL BYTES ALLOCATED:                             421,855,232 (402.31 MB)
TOTAL BYTES USED:                                  419,160,064 (399.74 MB)

In unserem Beispiel ist der Index insgesamt 400 MB groß. Nach einigen typischen Abfragen können wir überprüfen, welche Objekte mit wievielen Blöcken sich im Cache befinden. Eine Abfrage auf die V$BH und DBA_OBJECTS Tabelle listet die Objekte auf, die sich momentan im Cache befinden. Die Abfrage sieht dann folgendermassen aus:

COLUMN OWNER FORMAT A10
COLUMN OBJECT_NAME FORMAT A25
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999

SELECT o.object_name, o.owner, o.object_type, COUNT(*) NUMBER_OF_BLOCKS
FROM dba_objects o, v$bh bh
WHERE o.data_object_id = bh.objd AND o.owner ='US'
GROUP BY o.object_name, o.owner, o.object_type
ORDER BY COUNT(*);

OBJECT_NAME               OWNER      OBJECT_TYPE         NUMBER_OF_BLOCKS
------------------------- ---------- ------------------- ----------------
DR$TXT_IDX$R              US         TABLE                              7
SYS_LOB0000143350C00002$$ US         LOB                              345
DR$TXT_IDX$X              US         INDEX                            440
DR$TXT_IDX$I              US         TABLE                            495
BASIC_LOB                 US         TABLE                          5,634

Die Basistabelle BASIC_LOB oder Teile davon befinden sich offensichtlich im Cache. Der Textindex besteht aus mehreren Komponenten, die ebenfalls in der Abfrage aufgelistet werden: die Token Tabelle $I, der Index $X der $I Tabelle, die ROWID Tabelle $R und ein zusätzliches Lobsegment SYS_LOB0000143350C00002$$ der $R-Tabelle. Folgende Abfrage zeigt die Tabellen und die zugehörigen LOB-Segmente:

SELECT table_name, segment_name, in_row, cache FROM user_lobs;

TABLE_NAME                     SEGMENT_NAME                   IN_ CACHE
------------------------------ ------------------------------ --- ----------
BASIC_LOB                      SYS_LOB0000094513C00010$$      YES NO
DR$TXT_IDX$I                   SYS_LOB0000143761C00006$$      YES NO
DR$TXT_IDX$R                   SYS_LOB0000143766C00002$$      YES YES
...

Die Texte (hier: Spalte TEXT) der Basistabelle liegen in einem LOB-Segment. Ob LOB-Segmente generell den Buffer Cache verwenden oder nicht, hängt von dem Storage-Parameter CACHE ab. Standardmässig werden LOB-Segmente nicht in den Buffer geladen. Um dies einzustellen, ist folgendes Kommando notwendig.

ALTER TABLE BASIC_LOB MODIFY LOB(text) CACHE;

Nun stellt sich die Frage, wie man die Textkomponenten wie $I usw. und die Tabelle in den Cache laden kann. Folgende Abfragen können dabei hilfreich sein:

SELECT /*+ FULL(ITAB) */ SUM(token_count), SUM(LENGTH(token_info))
                                             FROM dr$txt_idx$i ITAB;

SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(token_text))
                                             FROM dr$txt_idx$i ITAB;
SELECT SUM(row_no) FROM dr$txt_idx$r;
SELECT /*+ FULL(BTAB) */ SUM(dok_id) FROM basic_lob BTAB;

Bei den Full Table Scans (FTS) muss allerdings berücksichtigt werden, dass Oracle ein optimiertes Verfahren verwendet, um Objekte in den Cache zu laden. Falls die Größe der Tabelle 2% des Buffer Cache übersteigt - was häufig der Fall sein kann - wird über direct load gelesen und nicht über den Buffer Cache. Möchte man das Laden über den Buffer Cache erzwingen, ist der Einsatz folgendes Parameters notwendig:

ALTER SESSION SET "_small_table_threshold"=zahl;
-- wobei Zahl die Blockgröße angibt, die grösser als das Segment ist.
-- danach kann der FTS ausgeführt werden
SELECT /*+ FULL(BTAB) */ SUM(dok_id) FROM basic_lob BTAB;

Nachlesen kann man diese Information auch in der Oracle Support Note Doc ID 787373.1.
Damit die Segmente auch im KEEP Pool Cache gespeichert werden, müssen nun noch die Buffer Pool Einstellungen der Objekte verändert werden. Folgende Kommandos passen die Storage-Klausel an:

ALTER TABLE dr$txt_idx$i STORAGE (buffer_pool keep);

ALTER INDEX dr$txt_idx$x STORAGE (buffer_pool keep);

ALTER TABLE dr$txt_idx$r STORAGE (buffer_pool keep);

ALTER TABLE basic_lob STORAGE (buffer_pool keep);

Allerdings ist ein weiterer Schritt notwendig, um die Lob-Segmente der $R Tabelle im KEEP Pool zu speichern. Die LOB-Segmente der $I und $R Tabellen werden standardmässig "in-row" gespeichert. Falls die Werte kleiner als 4K sind, wird der Inhalt in der Tabelle selbst gespeichert, wächst er darüber hinaus, wird ein separates Segment angelegt. Da die LOB-Länge der Token Tabellen $I auf 4K limitiert ist, werden die TOKEN-Informationen immer im Segment selbst abgespeichert.
Ganz im Gegensatz dazu kann die Liste der ROWIDs, die in der $R Tabelle gespeichert sind, größer als 4K sein. Dann kommt es zu einer Speicherung in einem separaten Segment. Um sicherzustellen, dass die Informationen der LOBs auch im KEEP Pool gespeichert werden, sollte folgende Einstellung durchgeführt werden.

ALTER TABLE dr$txt_idx$r MODIFY LOB (data) (STORAGE (buffer_pool keep));

Mit den oben aufgeführten Abfragen lassen sich allerdings die LOB-Segmente im separat gespeicherten Segment NICHT in den Cache laden. Folgende Prozedur erledigt diese Aufgabe. Die einzelnen ROWID-Segmente, die grösser als 4K sind, werden dabei ausgelesen.

create or replace procedure loadAllDollarR (idx_name varchar2) is
  v_idx_name varchar2(30) := upper(idx_name);
  type c_type is ref cursor;
  c2 c_type;
  s varchar2(2000);
  b blob;
  buff varchar2(100);
  siz number;
  off number;
  cntr number;
begin
-- wenn Index partitioniert, dann mehr als eine Tabelle
  for c1 in (select table_name t from user_tables
             where table_name like 'DR_'v_idx_name'%$R') loop
    dbms_output.put_line('loading from table 'c1.t);
    s := 'select data from 'c1.t;
    open c2 for s;
    loop
       fetch c2 into b;
       exit when c2%notfound;
       siz := 10;
       off := 1;
       cntr := 0;
-- falls ROWIDs gespeichert sind, dann ROWID (Laenge 10) lesen und OFFSET plus4096
   if dbms_lob.getlength(b) > 4096 then
         begin
           loop
             dbms_lob.read(b, siz, off, buff);
             cntr := cntr + 1;
             off := off + 4096;
           end loop;
         exception when no_data_found then
           if cntr > 0 then
             dbms_output.put_line('4K chunks fetched: 'cntr);
           end if;
         end;
       end if;
    end loop;
  end loop;
end;
/
--Ausführung
exec LoadAllDollarR('TXT_IDX')

Zum Laden der Daten sind insgesamt folgende Abfragen notwendig:

ALTER SESSION SET "_small_table_threshold"= wert;

SELECT /*+ FULL(ITAB) */ SUM(token_count), SUM(LENGTH(token_info))
                                           FROM dr$index_name$i ITAB
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(token_text))
                                           FROM dr$index_name$i ITAB
SELECT SUM(row_no) FROM dr$index_name$r;
SELECT /*+ FULL(BTAB) */ SUM(spalte_name) FROM table_name BTAB;
exec LoadAllDollarR('index_name')

Summiert man die Anzahl der Blöcke der V$BH Tabelle auf, erhält man den Wert für die Größe von DB_KEEP_CACHE_SIZE.
Folgende Kommandos zeigt die Einstellung dazu:

-- DB_CACHE_SIZE anpassen
ALTER SYSTEM SET DB_CACHE_SIZE=wert;
-- DB_KEEP_CACHE_SIZE setzen
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=wert;

Viel Spass beim Ausprobieren...