Posts mit dem Label $I werden angezeigt. Alle Posts anzeigen
Posts mit dem Label $I werden angezeigt. Alle Posts anzeigen

Montag, 1. Juni 2015

12c Feature: $I Tabelle mit neuer Storage Preference BIG_IO

Wir haben in den letzten Blogeinträgen schon Einiges zum Thema Oracle Database 12c veröffentlicht. Auch in diesem Blog wollen wir uns wieder einem neuen Oracle Text 12c Feature widmen. Es geht dabei um neue Möglichkeiten innerhalb der Text Indexstruktur, Änderungen an dem Default Speicherverhalten vorzunehmen, um unter Umständen bei Indexzugriffen weniger I/Os durchführen zu müssen. Gemeint ist damit die $I Tabelle und die Spalte TOKEN_INFO.

Welche Informationen speichert die Spalte TOKEN_INFO? Zur Erinnerung: Jedes Wort (besser Token) wird über die DOCID (Dokumenten ID des Dokuments, das das Token enthält) und die entsprechenden Wortpositionen in diesem Dokument gefunden. Beide Informationen werden in der Spalte TOKEN_INFO in binärer Form gespeichert. Standardmässig ist diese Spalte vom Datentyp BLOB und kann Informationen bis zu 4000 Bytes (dies ist eine interne Begrenzung) speichern. Müssen viele Informationen (mehr als 4000 Bytes) in der TOKEN_INFO Spalte gespeichert werden, werden neue Zeilen hinzugefügt. Die Idee in 12c ist nun, weniger Zeilen für große TOKEN_INFO Einträge speichern zu müssen. Aber schauen wir uns zuerst das Standrrdverhalten an einem einfachen Beispiel an.

SQL> create table my_table( id number primary key, text varchar2(2000) );
Table created.

SQL> create index my_index on my_table( text ) indextype is ctxsys.context;
Index created.

SQL> desc DR$MY_INDEX$I
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(10)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB

Überprüft man die genaue Definition der Tabelle DR$MY_INDEX$I, kann man feststellen, dass sich die Defaultspeicherung von LOBs in 12c geändert hat und nun statt der Basicfile eine Securefile Speicherung vorliegt. Für diejenigen, die es genau wissen wollen: Dies liegt an dem geänderten Parameterwert PREFERRED für den Initialisierungsparameter DB_SECUREFILE.
SQL> set long 10000 pagesize 100  

SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>-
     DBMS_METADATA.SESSION_TRANSFORM, name=>'STORAGE', value=>false);
SQL> SELECT DBMS_METADATA.GET_DDL(object_type=>'TABLE', name=>'DR$MY_INDEX$I') AS ausgabe 
     FROM dual;

AUSGABE
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."DR$MY_INDEX$I"
   (    "TOKEN_TEXT" VARCHAR2(64) NOT NULL ENABLE,
        "TOKEN_TYPE" NUMBER(10,0) NOT NULL ENABLE,
        "TOKEN_FIRST" NUMBER(10,0) NOT NULL ENABLE,
        "TOKEN_LAST" NUMBER(10,0) NOT NULL ENABLE,
        "TOKEN_COUNT" NUMBER(10,0) NOT NULL ENABLE,
        "TOKEN_INFO" BLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("TOKEN_INFO") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES )
  MONITORING
Dies ändert allerdings noch nichts an der internen Begrenzung auf 4000 Bytes. Ein einfaches Beispiel demonstriert das Standardverhalten - auch in 12c. Dazu legen wir eine einfache Tabelle mit einer Spalte an und speichern 5000 Mal den Eintrag HELLO ab.
SQL> drop table my_table purge;
Table dropped
SQL> create table my_table (text varchar2(80));
Table created
SQL> begin
  for i in 1 .. 5000 loop
    insert into my_table values ('hello');
    commit;
  end loop;
end;
/
SQL> create index my_index on my_table (text) indextype is ctxsys.context;
Index created
Selektieren wir nun die $I Tabelle, stellen wir fest, dass 5 Zeilen für das Token HELLO verwendet wurden - mit einer Länge von 2499 bzw. 3501 Bytes.

SQL> column token_text format a15
SQL> select token_text, length(token_info) from dr$my_index$i;
 
TOKEN_TEXT LENGTH(TOKEN_INFO)
--------------- ------------------
HELLO         2499
HELLO         3501
HELLO         3501
HELLO         3501
HELLO         3501

Nun wenden wir das neue Feature BIG_IO an, dass diese Grenze aufheben soll. Dazu ist die neue Option BIG_IO als Attribute in der Preference BASIC_STORAGE auf TRUE zu setzen.
SQL> begin 
     ctx_ddl.create_preference( 'my_storage', 'BASIC_STORAGE' );
     ctx_ddl.set_attribute ( 'my_storage', 'BIG_IO', 'true' );
     end;
/ 
-- entweder Neuanlegen des Index oder mit ALTER INDEX
SQL> alter index my_index rebuild parameters ('replace storage my_storage');
Index altered.
Wenn wir nun die Token Information überprüfen, finden wir nur noch einen einzigen (!) Eintrag vor - allerdings der Länge 15023.
SQL> select token_text, length(token_info) from dr$my_index$i;

TOKEN_TEXT LENGTH(TOKEN_INFO)
--------------- ------------------
HELLO        15023


BIG_IO sorgt also dafür, dass wir unter Umständen weniger Einträge pro Token in der $I Tabelle speichern. Dies kann dann dabei helfen, die Zugriffe auf große Indexfragmente zu reduzieren.

Montag, 20. Februar 2012

Statistiken im Oracle Text Umfeld

Spätestens seit Oracle 10g besitzt das Sammeln von Statistiken bzw. die Relevanz von korrekten Statistiken einen hohen Stellenwert und stellt einen Garant für gute Performance dar. Um das Management für das Sammeln von Statistiken zu vereinfachen, führte Oracle daher mit 10g die sogenannten Maintenance Jobs ein, die meist in einem nächtlichen Job diese Aufgabe übernehmen.

Was bedeutet dies nun für den Oracle Text Index?
Zieht man das Handbuch Oracle Text Application Developer's Guide 11g Release 2 (11.2) zu rate, findet man im Abschnitt "Tuning Oracle Text" auf die Frage, ob man Statistiken sammeln sollte oder nicht, folgende nicht eindeutige Antwort dazu:
"Yes. Collecting statistics on your tables enables Oracle Text to do cost-based analysis. This helps Oracle Text choose the most efficient execution plan for your queries. If your queries are always pure text queries (no structured predicate and no joins), you should delete statistics on your Oracle Text index."

Noch einmal zur Erinnerung, wenn wir einen Text Index (z. B. TEXT_IDX) erzeugen, werden automatisch mindestens folgende Objekte erzeugt:
  • DR$TEXT_IDX$I: Token Tabelle
  • DR$TEXT_IDX$X: Index
  • DR$TEXT_IDX$K: DOCID (nach ROWID) Mapping Tabelle
  • DR$TEXT_IDX$N: Tabelle für Negativliste für alle gelöschten DOCIDs
  • DR$TEXT_IDX$R: ROWID (nach DOCID) Mapping Tabelle
Was bedeutet dies für die einzelnen Objekte des Textindex?
  1. Sollten Statistiken auf den einzelnen DR$ Objekten existieren?
    Die Antwort darauf lautet nein! Alle internen rekursiven Abfragen haben Hints (wie zum Beispiel /*+DYNAMIC_SAMPLING(0) INDEX(T "DR$TEST_IDX$X")*/) um einen festgelegten Ausführungsplan zu verwenden. Dieser Plan hat sich als bester Plan erwiesen. Statistiken auf den Objekten könnten zu einem veränderten und somit schlechteren Plan führen.
  2. Werden Statistiken im Maintenance Job für Oracle Text mitgepflegt?
    Auch hier lautet die Antwort nein. DBMS_STATS.GATHER_SCHEMA_STATS oder DBMS_STATS.GATHER_DATABASE_STATS analysiert keine internen Texttabellen und Indizes.

Um zu verifizieren, ob Ihre Umgebung die Regel 1) erfüllt, führen Sie einfach folgenden Test durch:
SQL> select table_name,last_analyzed 
     from user_tables where table_name like '%TEST_IDX%';

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
DR$TEST_IDX$I
DR$TEST_IDX$R
DR$TEST_IDX$K
DR$TEST_IDX$N
Für die Indizes ergibt sich folgende Abfrage:
SQL> select index_name, user_stats, global_stats, last_analyzed
     from user_indexes where table_name like '%TEST_IDX%';

INDEX_NAME                     USE GLO LAST_ANAL
------------------------------ --- --- ---------
SYS_IOT_TOP_223146             NO  NO
DR$TEST_IDX$X                  NO  NO  
SYS_IL0000223143C00002$$       NO  NO
SYS_IOT_TOP_223141             NO  NO
SYS_IL0000223138C00006$$       NO  NO
Haben die Einträge in der Spalte LAST_ANALYZED den Wert "null", müssen Sie nichts unternehmen. Finden Sie Statistiken auf einem dieser Objekte, löschen Sie diese wie folgendes Beispiel zeigt.
SQL> select index_name, user_stats, global_stats, last_analyzed
     from user_indexes where table_name like '%TEST_IDX%';

INDEX_NAME                     USE GLO LAST_ANAL
------------------------------ --- --- ---------
SYS_IOT_TOP_223146             NO  NO
DR$TEST_IDX$X                  NO  NO  20-FEB-12
SYS_IL0000223143C00002$$       NO  NO
SYS_IOT_TOP_223141             NO  NO
SYS_IL0000223138C00006$$       NO  NO

SQL> execute dbms_stats.delete_index_stats('US','DR$TEST_IDX$X');

--- zur Kontrolle

SQL> select index_name, user_stats, global_stats, last_analyzed
     from user_indexes where table_name like '%TEST_IDX%';

INDEX_NAME                     USE GLO LAST_ANAL
------------------------------ --- --- ---------
SYS_IOT_TOP_223146             NO  NO
DR$TEST_IDX$X                  NO  NO
SYS_IL0000223143C00002$$       NO  NO
SYS_IOT_TOP_223141             NO  NO
SYS_IL0000223138C00006$$       NO  NO
Weitere Informationen zu dem Thema finden Sie auch in der öffentlichen My Oracle Support Note DOC ID 139979.1.

Freitag, 25. März 2011

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...

Beliebte Postings