Dienstag, 28. April 2009

Index-Optimierung: Einige Grundlagen

Häufige Indexsynchronisierung nach DML Operationen kann zu Indexfragmentierungen führen. Indexfragmentierungen wiederum können dabei die Antwortzeiten der Textabfragen negativ beeinflussen. Weniger häufige Synchronisierungen - also Batchoperation und längere Listen beim Synchronisieren - führen zu weniger Zeilen im Index und daher geringerer Fragmentierung. Häufig hängt allerdings das entsprechende Intervall der Synchronisierung von Anwenderanforderungen ab. Falls Sie mehr Informationen zur Synchronisierung bzw. zur Indexfragmentierung benötigen, schauen Sie einfach in den folgenden Blog. Regelmässige Optimierung ist also wichtige Operation, da die Indexfragmentierung und Indexgröße dabei reduziert werden und somit die Performance der Abfragen erhöht werden kann. Optimiert wird der Index mit der Prozedur CTX_DDL.OPTIMIZE_INDEX, die in unterschiedlichen Modi durchgeführt werden können:
  • FAST: Aufhebung der fragmentierten Zeilen, allerdings keine Optimierung der gelöschten Daten
  • FULL: Aufhebung der fragmentierten Zeilen und Optimierung der gelöschten Daten (Garbage)
  • REBUILD: wie FULL nur dass die $I Tabelle neuaufgebaut wird-u.U. schneller als FULL
  • TOKEN: volle Optimierung einzelner Tokens
  • TOKEN_TYPE: volle Optimierung bezogen auf einen bestimmten Tokentyp

Der Anwender kann auch während einer OPTIMIZE-Operation weitersuchen und einfügen. Je nach Modus kann die OPTIMIZE-Operation allerdings sehr lange dauern, CPU und I/O intensiv sein und somit das ganze System beeinflussen. Daher ist es im Modus FULL möglich die OPTIMIZE-Operation mithilfe des Arguments MAXTIME auf eine gewisse Zeit zu beschränken. Dabei wird sich der letzte optimierte Zeitpunkt gemerkt und beim nächsten Anlauf dort weiter fortgeführt. Um eine genauere Vorstellung von der Funktionsweise zu erhalten, werden wir im Folgenden einige Optimierungen durchführen. Folgende Indexfragmentierung sei beim Index vorhanden, ausgelöst durch INSERT und DELETE Operationen. Die Ausgabe erfolgt analog zum Blog.


---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                270,000 (263.67 KB)

$I rows:                                                           90,000
estimated $I rows if optimal:                                          72
estimated row fragmentation:                                        100 %

garbage docids:                                                     7,001
estimated garbage size:                                 52,297 (51.07 KB)

most fragmented tokens:
  SAT (0:TEXT)                                                      100 %
  MAT (0:TEXT)                                                      100 %
  MARY (0:TEXT)                                                     100 %
  LAMB (0:TEXT)                                                     100 %
  CAT (0:TEXT)                                                      100 %
  LITTLE (0:TEXT)                                                   100 %

Offensichtlich ist der Index fragmentiert und besitzt Garbage (gelöschte Daten). Wir werden nun den Index mit folgendem Kommando optimieren und dabei den Modus FAST verwenden:

execute ctx_ddl.optimize_index (idx_name => 'TESTX', optlevel => 'FAST', parallel_degree => 1);

Nach Abschluss der OPTIMIZE-Operation monitoren wir wieder die Fragmentieren. Die Fragmentierung ist verschwunden, allerdings ist noch Garbage vorhanden.

---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                270,003 (263.67 KB)

$I rows:                                                               72
estimated $I rows if optimal:                                          72
estimated row fragmentation:                                          0 %

garbage docids:                                                     7,001
estimated garbage size:                                 52,298 (51.07 KB)

most fragmented tokens:
  SAT (0:TEXT)                                                        0 %
  MAT (0:TEXT)                                                        0 %
  MARY (0:TEXT)                                                       0 %
  LAMB (0:TEXT)                                                       0 %
  CAT (0:TEXT)                                                        0 %
  LITTLE (0:TEXT)                                                     0 %


Nun führen wir das Ganze statt im FAST Modus im FULL Modus aus:

execute ctx_ddl.optimize_index (idx_name => 'TESTX', optlevel => 'FULL', parallel_degree => 1);

Parallel können wir in einer zusätzlichen Session, die Optimierung beobachten:

SQL>set pagesize 100
SQL>col indx_name format a30
SQL>col idx_opt_type format a20

SQL> SELECT idx_name,idx_opt_token,idx_opt_type,idx_opt_count FROM ctxsys.dr$index; 
IDX_NAME                              IDX_OPT_TOKEN IDX_OPT_TYPE IDX_OPT_COUNT
-------------------- ------------------------------ ------------ -------------
D01_F_ALL
SUP_TEXT_IDX
AUTH_IDX
IDX_TEXT
TESTX MAT                                                      0          7001
TEXT_IND

Mit unserem Monitoring Skript erhalten wir nun folgende Information über die Fragmentierung:

---------------------------------------------------------------------------
FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                206,997 (202.15 KB)
$I rows:                                                               57
estimated $I rows if optimal:                                          57
estimated row fragmentation:                                          0 %

garbage docids:                                                         0
estimated garbage size:                                                 0

most fragmented tokens:
  SAT (0:TEXT)                                                        0 %
  MAT (0:TEXT)                                                        0 %
  MARY (0:TEXT)                                                       0 %
  LAMB (0:TEXT)                                                       0 %
  CAT (0:TEXT)                                                        0 %
  LITTLE (0:TEXT)                                                     0 %



Stellt man fest, dass häufig nach einzelnen Tokens gesucht wird, könnte man die Optimierung auch auf einzelne Tokens oder Token-Types (im Fall der MDATA Nutzung) beschränken. Im folgenden Beispiel werden wir dies für das Token LITTLE durchführen: Das Ergebnis sieht dann folgendermassen aus:

---------------------------------------------------------------------------
                         FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                263,998 (257.81 KB)

$I rows:                                                           80,007
estimated $I rows if optimal:                                          71
estimated row fragmentation:                                        100 %

garbage docids:                                                     7,001
estimated garbage size:                                 51,134 (49.94 KB)

most fragmented tokens:
  SAT (0:TEXT)                                                      100 %
  MAT (0:TEXT)                                                      100 %
  MARY (0:TEXT)                                                     100 %
  LAMB (0:TEXT)                                                     100 %
  CAT (0:TEXT)                                                      100 %
  LITTLE (0:TEXT)                                                     0 %

Mehr zur Optimierung in einem der nächsten Blogs....

Montag, 16. März 2009

Einstellungen für einen Textindex: Komposita, Printjoins, Skipjoins, Mixed Case und mehr ...

Beim Erstellen eines Oracle TEXT-Index kann man eine ganze Menge Einstellungen vornehmen. Im letzten Posting ging es um das Thema Stopwörter, heute schauen wir uns ein paar andere Einstellungen an.
Wir beginnen ganz einfach und erzeugen zunächst eine Tabelle mit ein paar Textzeilen.
drop table texte
/

create table texte (
  id number,
  text varchar2(4000)
)
/

insert into texte values (1, 'Das Treffen am Bahnhofsplatz heute abend war schön');
insert into texte values (2, 'Dem Chat trat der Nutzer "user_7642" bei');

commit
/
Erstellen wir nun einen Index - zunächst mal ohne jede Parametrisierung ...
drop index idx_texte
/

create index idx_texte on texte (text)
indextype is ctxsys.context
/
Anschließend kann man sich "den Index" mit einem Blick auf die Token-Tabelle ansehen ...
SQL> select token_text from dr$idx_texte$i;

TOKEN_TEXT
--------------------------------------------------
7642
Bahnhof
Bahnhofsplatz
Chat
Das
Dem
Nutzer
Platz
Treffen
abend
heute
schön
trat
user
Die erste Auffälligkeit ist die Tatsache, dass die Wörter (Tokens) im Mixed Case in der Token-Tabelle stehen. Das ist für die meisten Fälle ungeeignet, da eine Suche nach "chat" (alles kleingeschrieben) zu keinem Ergebnis führen würde. Dies gälte es also durch Einstellung von Parametern zu ändern. An anderer Stelle es gut erkennbar, dass der Index die deutsche Sprache erkannt hat; das Token "Bahnhofsplatz" wurde korrekt in die zusätzlichen Tokens "Bahnhof" und "Platz" zerlegt. Experimentieren wir nun ein wenig mit den Parametern: Als erstes soll der Index nicht mehr Case-Sensitiv sein ...
drop index idx_texte
/

begin
  ctx_ddl.drop_preference( 
    preference_name => 'MY_LEXER'
  );
end;
/

begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
end;
/

create index idx_texte on texte (text)
indextype is ctxsys.context
parameters ('LEXER MY_LEXER')
/
Die Parameter werden in die sog. Preference MY_LEXER eingestellt. Anschließend wird der Index neu erstellt - die Token-Tabelle sieht dann so aus:
SQL> select token_text from dr$idx_texte$i;

TOKEN_TEXT
----------------------------------------------------------------
7642
ABEND
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
SCHÖN
TRAT
TREFFEN
USER
OK ... damit ist das Mixed-Case-Problem behoben. Allerdings wurde der Bahnhofsplatz nun nicht mehr zerlegt - und das war ja eigentlich ganz gut so ... Das Erstellen der Preference MY_LEXER ändern wir also nochmals und schalten die Kompositazerlegung wieder ein (von nun an stelle ich nur noch die create_preference Aufrufe hier vor.
begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
end;
/
Ergebnis ..
SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------------------
7642
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER
Das Token user_7642 wurde offensichtlich ebenfalls zerlegt: Oracle TEXT behandelt den Unterstrich (_) als Trenner von Tokens. Auch dies kann man mit dem Parameter PRINTJOINS abschalten ...
begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
  -- Den Unterstrich (_) als "Printjoin" deklarieren
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'PRINTJOINS',
    attribute_value => '_'
  );
end;
/
Das Ergebnis ...
SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------------
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER_7642
Doch hierbei Vorsicht: Der Unterstrich wirkt nun überhaupt nicht mehr als Trennzeichen für Tokens - die Aufnahme eines Zeichens zu den Printjoins sollte also nur dann erfolgen, wenn man sich sicher ist, dass dies auch für den gesamten Dokumentbestand in Ordnung geht. Weiterhin könnt Ihr nur einzelne Zeichen als Printjoins deklarieren, keine Zeichenketten. Hierbei muss man also ein wenig aufpassen ...
Eine andere Variante wäre, den Unterstrich als Skipjoin zu deklarieren ...

begin
  ctx_ddl.create_preference(
    preference_name => 'MY_LEXER',
    object_name     => 'BASIC_LEXER'
  );
  -- Mixed Case abschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
  -- Kompositazerlegung einschalten
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'COMPOSITE',
    attribute_value => 'GERMAN'
  );
  -- Den Unterstrich (_) als "Skipjoin" deklarieren
  ctx_ddl.set_attribute(
    preference_name => 'MY_LEXER',
    attribute_name  => 'SKIPJOINS',
    attribute_value => '_'
  );
end;
/
... was dann so aussieht; der Unterstrich wäre dann verschwunden und würde bei Abfragen ignoriert.
SQL>  select token_text from dr$idx_texte$i;

TOKEN_TEXT
---------------------------------------------
ABEND
BAHNHOF
BAHNHOFSPLATZ
CHAT
HEUTE
NUTZER
PLATZ
SCHÖN
TRAT
TREFFEN
USER7642
Eine vollständige Übersicht über alle Parameter (es gibt noch ein paar mehr) findet Ihr im Handbuch Text Reference. So ... das war's für heute - mehr zu Textindex-Parametern und Einstellungsmöglichkeiten in den nächsten Postings ...

Montag, 16. Februar 2009

Stopwörter und Stoplisten: Unwichtige Wörter ... (wirklich unwichtig? ...)

Bekanntlich unterstützt Oracle TEXT Stopwortlisten. Wird ein Wort als Stopwort deklariert, wird es von Oracle TEXT völlig ignoriert - Stopwörter werden nicht in den Index aufgenommen und bei Abfragen insofern nicht berücksichtigt. Bei Abfragen auf einzelne Wörter (Tokens) ist das relativ klar; eine Suche nach "und" wäre auch sinnlos. Stopwörter haben allerdings einen Einfluß auf die Phrasensuche - sie passen auf jedes Wort. Sucht man beispielsweise nach der Phrase "Radio und Fernseher", so sucht Oracle TEXT (wenn die Stopwortliste aktiv ist und bspw. die Wörter "und", "oder" und "unter" enthält) im Index nach "Radio {?} Fernseher". Gefunden würden auch Dokumente, in denen die Phrase Radio oder Fernseher oder Radio im Fernseher vorkommt. Wenn solcherlei Effekte nicht gewünscht sind, muss man die Stopwortliste abschalten.
Aber welche Wörter sind überhaupt Stopwörter ...? Dazu schaut man erstmal nach, welche Stopwortlisten es überhaupt gibt - die Stopwörter sind in Listen organisiert ...
SQL> select * from ctx_stoplists

SPL_OWN SPL_NAME                   SPL_COUNT SPL_TYPE
------- ------------------------- ---------- ----------------
CTXSYS  DEFAULT_STOPLIST                 235 BASIC_STOPLIST
WKSYS   WK_STOPLIST                       76 BASIC_STOPLIST
CTXSYS  EMPTY_STOPLIST                     0 BASIC_STOPLIST
CTXSYS  EXTENDED_STOPLIST                  0 BASIC_STOPLIST
TM      SWL_BRANCHEN                      10 BASIC_STOPLIST

5 Zeilen ausgewählt.
Die CTXSYS.DEFAULT_STOPLIST ist eine Standardliste für die Sprache, mit der die Datenbank aufgesetzt wurde - Hierzulande ist das im Normalfall Deutsch. Und auch da kann man reingucken ...
SQL> select spw_type, SPW_WORD from ctx_stopwords where SPW_STOPLIST='DEFAULT_STOPLIST'

SPW_TYPE   SPW_WORD
---------- ------------------------------
STOP_WORD  Ihnen
STOP_WORD  Ihre
STOP_WORD  Ihrem
STOP_WORD  Ihren
STOP_WORD  Ihrer
STOP_WORD  Ihres
:          :
Enthalten sind also Bindewörter, Präpositionen, Artikel und ähnliche Wörter; also alles, was typischerweise in jedem Dokument vorkommt und daher ignoriert werden kann.
Die Skripte, mit denen diese Defaults erzeugt werden befinden sich in $ORACLE_HOME/ctx/admin/defaults. So spielt das darin vorhandene Skript drdefd.sql die deutsche Standard-Stopliste ein; drdefus.sql entsprechend die US-amerikanische. Wenn in der Datenbank also eine englische Stopliste enthalten ist (weil beim Aufsetzen alles mit englischer Sprache gelaufen ist), so lässt sich das (als User CTXSYS) einfach ändern ...
SQL> start ?/ctx/admin/defaults/dr0defdp.sql
SQL> start ?/ctx/admin/defaults/drdefd.sql
Stoplisten sind wie alle anderen Datenbankobjekte einem Schema zugeordnet; das Schema CTXSYS enthält die Standardlisten. Eigene Stoplisten sollten stets auch ins eigene Datenbankschema (und nicht in CTXSYS) abgelegt werden. Die Skripte können auch als Basis für Erweiterungen verwendet werden. Möchte man mit Stopwortlisten arbeiten, kann es durchaus sinnvoll sein, diese durch branchentypische Fachbegriffe zu erweitern. Die Standardlisten enthalten nur solche Wörter, von denen Oracle sicher ist, dass sie in nahezu jedem Dokument vorkommen. Es mag nun branchenspezifische Wörter geben, die man hinzufügen möchte: In einer Bank könnten dies Begriffe wie "Bank", "Betrag", "Konto" oder ähnliche sein. Beim Festlegen solcher Begriffe sollte man aber stets darauf achten, dass die Stopwörter (wie eingangs erwähnt) auch bei der Phrasensuche ignoriert werden.
begin
  ctx_ddl.add_stopword(
    'MY_STOPLIST',
    'bank'
  );
end;
Ein Index mit dieser Stopliste wird dann so gebaut:
create index idx_volltext on dokument_tab (spalte)
indextype is ctxsys.context
parameters ('stoplist my_stoplist')
/
Diese Syntax hat keinen Einfluß auf schon bestehende Volltextindizes. Möchte man auf einem schon erzeugten Index ein (zusätzliches) Stopwort einrichten, gibt es eine spezielle Syntax:
  alter index idx_volltext add stopword bank;
Der Index wird dadurch nicht neu erzeugt, auch werden diese Wörter (so sie vorhanden sind) nicht aus dem Index gelöscht. Das neue Stopwort wird einfach nur in die Metadaten eingetragen, so dass es bei künftigen Abfragen und neuen Dokumenten wirken kann. Diese Operation kann es logischerweise nicht für das Entfernen von Stopwörtern geben; denn dazu müssen diese ja neu in den Index aufgenommen werden. Möchte man Stopwörter aus einem Index entfernen, so muss dieser neu gebaut werden.
begin
  ctx_ddl.remove_stopword( 'MY_STOPLIST', 'bank');
  ctx_ddl.remove_stopword( 'MY_STOPLIST', '{...}');
end;
/

ALTER INDEX idx_volltext REBUILD PARAMETERS('replace stoplist MY_STOPLIST')
/
Mehr zum Thema findet sich in der Oracle-Dokumentation: TEXT Application Developers' Guide und TEXT Reference.

Donnerstag, 29. Januar 2009

Viele Abfragen auf einmal: Query Relaxation

Wenn man in Dokumentbeständen sucht, ist es ja vielfach so, dass man zunächst mit recht vielen Suchbegriffen anfängt und dann (wenn man nichts findet), die Abfrage allgemeiner macht, eben bis etwas kommt ...
Für eine CONTAINS-Abfrage mit Oracle TEXT könnte das in etwa folgendes bedeuten:
  • 1. Abfrage (keine Treffer)
    select * from dokument_tabelle
    where contains (dokument, 'oracle and datenbank and text and contains and parameters and 11g') > 0
    /
      
  • 2. Abfrage (bspw. 1 Treffer, passt aber nicht)
    select * from dokument_tabelle
    where contains (dokument, 'oracle and datenbank and (text or contains)') > 0
    /
      
  • 3. Abfrage (ausreichend Treffer)
    select * from dokument_tabelle
    where contains (dokument, 'oracle or datenbank') > 0
    /
    
Das bedeutet nun allerdings, dass man drei Abfragen absetzt. Man kann nun darüber nachdenken, einen solchen Prozeß zu automatisieren - wenn ein Suchwort in eine Maske eingegeben wird, wird zunächst genau danach gesucht, wenn nicht genug Treffer gefunden werden, wird das Stemming ($-Operator) verwendet und wenn es dann immer noch nicht reicht, probiert man es mit Fuzzy (?).
Es ist allerdings recht aufwändig, das selbst zu tun - man müsste jedesmal die Treffer zählen und bei Bedarf eine neue Abfrage absetzen: Die Antwortzeiten wären damit sicherlich irgendwann nicht mehr akzeptabel ...
Es gibt hierfür allerdings seit Oracle10g eine Funktion: Query Relaxation. Man kann alle diese Abfragen auf einmal übergeben und auch festlegen, wieviele Treffer man gerne haben möchte. Oracle TEXT erledigt dann alles mit nur einem CONTAINS-Aufruf.
select * from dokument_tabelle
where contains (
  dokument, 
  '<query>
     <textquery lang="ENGLISH" grammar="CONTEXT">
       <progression>
         <seq>{oracle} and {datenbank} and {text} and {contains} and {parameters} and {11g}</seq>
         <seq>{oracle} and {datenbank} and ({text} or {contains})</seq>
         <seq>{oracle} or {datenbank}</seq>
       </progression>
     </textquery>
     <score datatype="INTEGER" algorithm="DEFAULT"/>
  </query>'
)>0
and rownum <= 10;
Die CONTAINS-Abfrage wird im XML-Format übergeben; die sog. Query Templates werden hier verwendet (mehr Informationen). Man sieht sehr schön, dass die Abfragen der Reihe nach aufgeführt werden. Oracle TEXT führt alle Abfragen der Reihe nach aus, bis entweder das Ende erreicht ist (letztes seq-Tag oder bis die gewünschte Zahl der Treffer gefunden wurde. Die gewünschte Anzahl Treffer steckt in der zusätzlichen Bedingung and rownum <= 10.
Da nun alle Abfragen in ein und demselben CONTAINS Aufruf stecken, ist diese Variante wesentlich schneller und effizienter als das manuelle "Nacheinander-Aufrufen" von CONTAINS-Abfragen. Die Funktionalität zum Zugriff auf den Index wird eben nur einmal anstatt mehrmals aufgerufen; alle evtl. Initialisierungen finden nur einmal statt.
Das im Text oben beschriebene Beispiel (zuerst "normal", dann mit Stemming, danach Fuzzy) würde als Aufruf dann so aussehen ...
select * from dokument_tabelle
where contains (
  dokument, 
  '<query>
     <textquery lang="GERMAN" grammar="CONTEXT">
       <progression>
         <seq>{[suchwort]}</seq>
         <seq>{$[suchwort]}</seq>
         <seq>{FUZZY([suchwort], 75, 100, weight)}</seq>
         <seq>{FUZZY([suchwort], 60, 200, weight)}</seq>
         <seq>{FUZZY([suchwort], 40, 300, weight)}</seq>
       </progression>
     </textquery>
     <score datatype="INTEGER" algorithm="DEFAULT"/>
  </query>'
)>0
where rownum < 10;
Hier habe ich mal den Operator FUZZY anstelle des einfachen Fragezeichens (?) verwendet - der Unterschied ist, dass FUZZY parametrisiert werden kann. Mehr Informationen zur Syntax des FUZZY-Operators finden sich in der Oracle Dokumentation: TEXT Reference.

Donnerstag, 8. Januar 2009

Fragmentierung feststellen mit INDEX_STATS

Wie fragmentiert ist mein Text Index? Wann soll ich den Index optimieren? Dies sind wichtige Fragestellungen, um eine optimale Zugriffsperformance gewährleisten zu können. Ein nicht optimaler Index zeigt sich in der Fragmentierung der $I-Tabelle z.B. bewirkt durch zu kleine SYNC Intervalle bzw. durch vorhandenen Garbage entstanden durch DELETE bzw. UPDATE Operationen.
Wie kann ich nun den Grad der Fragmentierung monitoren? Das Package CTX_REPORT liefert mit der Prozedur INDEX_STATS eine einfache Möglichlichkeit. Folgender Aufruf demonstriert die Nutzung. Die Hilfstabelle REPORT ist dabei notwendig, um die Inhalte zu speichern.
DROP TABLE ausgabe;
CREATE TABLE ausgabe (resultat CLOB);
 
  declare
    ergebnis clob := null;
  begin
    ctx_report.index_stats(index_name=>'IDX_TEXT',report=>ergebnis,stat_type=>null);
    insert into ausgabe values (ergebnis);
    commit;
    dbms_lob.freetemporary(ergebnis);
  end;
  /
 
set long 32000
set head off
set pagesize 10000
SELECT * FROM ausgabe; 
 ....
-- ein Ausschnitt aus dem Ergebnis
---------------------------------------------------------------------------
                         FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                                                398

$I rows:                                                              105
estimated $I rows if optimal:                                         105
estimated row fragmentation:                                          0 %

garbage docids:                                                         0
estimated garbage size:                                                 0

most fragmented tokens:
  WIRTSCHAFT (0:TEXT)                                                 0 %
  WIRD (0:TEXT)                                                       0 %
  WI
Das Ergebnis zeigt, dass weder Garbage noch Fragmentierung des Index vorhanden ist.
Da die Durchführung dieser Prozedur bei grossen Indizes oder Partitionen sehr lange dauern kann, kann es sinnvoll sein, Logging einzuschalten. So ist es möglich den Fortschritt (d.h. die ge-scannten Zeilen der $I Tabelle) der jeweiligen Operation monitoren zu können.
Im nächsten Beispiel wird das ganze Prozedere mit zusätzlichem Logging an einem größeren Index demonstriert. Paralleles Monitoren der LOGGING-Tabelle würde den Stand der gescannten Zeilen anzeigen.
DROP TABLE ausgabe;
CREATE TABLE ausgabe (resultat CLOB);
  declare
    ergebnis clob := null;
  begin
        ctx_output.start_log('index_statistik'); 
    ctx_report.index_stats(index_name=>'TEXT_IND',report=>ergebnis,stat_type=>null);
    insert into ausgabe values (ergebnis);
        ctx_output.end_log; 
    commit;
    dbms_lob.freetemporary(ergebnis);
  end;
  /
Nachdem die Prozedur INDEX_STATS erfolgreich durchgeführt ist, kann man wie eben die ERGEBNIS Tabelle monitoren mit:
spool fragment.lst
set long 32000
set head off
set pagesize 10000
SELECT * FROM ausgabe;
... 
spool off
Im Abschnitt "FRAGMENTATION STATISTICS" ist nun folgende Information zu finden.
---------------------------------------------------------------------------
                         FRAGMENTATION STATISTICS
---------------------------------------------------------------------------

total size of $I data:                            105,598,019 (100.71 MB)

$I rows:                                                        2,325,672
estimated $I rows if optimal:                                     538,712
estimated row fragmentation:                                         77 %

garbage docids:                                                         0
estimated garbage size:                                                 0

most fragmented tokens:
  126 (0:TEXT)                                                       96 %
  124 (0:TEXT)                                                       96 %
  12

Montag, 8. Dezember 2008

Ergebnisse zählen: Nicht mit count(*), sondern mit COUNT_HITS!

Wenn man Oracle TEXT in einer Applikation nutzt und Abfragen mit der CONTAINS-Funktion durchführt, möchte man fast immer auch die Anzahl der Treffer auf der Ergebnisseite anzeigen. Und der erste, naheliegende Gedanke ist, hierfür ein SELECT COUNT(*) zu verwenden - die Abfrage findet ja auch mit einem SELECT statt.
select count(*) from dokument_tab where contains(dokument, 'Bundeskanzler') > 0
Gerade bei Oracle TEXT ist diese Variante jedoch sehr ineffektiv - denn die Datenbank muss hierfür sowohl auf den Volltextindex als auch auf die Tabelle zugreifen. Besser ist es, die dafür vorgesehene Funktion COUNT_HITS im PL/SQL-Paket CTX_QUERY zu nutzen.
set serveroutput on

declare
  v_number number;
begin
  v_number := ctx_query.count_hits(
    index_name => 'MY_FULLTEXT_IDX',
    text_query => 'oracle and text',
    exact =>      true
  );
  dbms_output.put_line('Anzahl Treffer: '||v_number);
end;
/
 
Anzahl Treffer: 2657
Man muss den Namen des Index kennen, um die Funktion nutzen zu können; mit der Dictionary View CTX_USER_INDEXES ist dieser aber leicht zu ermitteln. Interessant ist noch der Parameter exact - gibt man hier false an, so arbeitet die Funktion schneller, dafür ungenauer: Seit der letzten Indexoptimierung gelöschte und veränderte Dokumente werden dann nicht berücksichtigt; der Wert ist also zu hoch. Direkt nach einer Indexoptimierung liefern sowohl exact => true als auch exact => false gleiche Ergebnisse zurück. In allen Fällen ist CTX_QUERY.COUNT_HITS schneller als ein SELECT COUNT(*).
Mehr Information in der Oracle Dokumentation - TEXT Reference.

Montag, 24. November 2008

Suchverhalten der Anwender monitoren: Abfrage-Analyse mit CTX_REPORT

Was sind die häufigsten Begriffe, nach denen die Anwender suchen? Welche Abfragen laufen ständig ins Leere und liefern kein Resultat? Diese Fragen sind häufig wichtig, um "anwenderfreundliche" Applikationen gestalten zu können oder um die Suchanfragen optimal zur Verfügung zu stellen.
Ab Oracle 10g ist es möglich mit CTX_OUTPUT ein spezielles Logging für Suchanfragen anzustossen und mit CTX_REPORT eine Analyse dieser Loginformationen durchzuführen.
Im Folgenden demonstriert ein Beispiel die Anwendung.

Wie in einem unserer vorangegangenen
Blogs schon demonstriert, wird das Logging in den Sessions, in denen die Abfragen laufen, aktiviert. Dazu legen wir zuerst das Log Verzeichnis fest.

execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/tmp'); 

Danach starten wir das Query-Logging mit folgendem Aufruf:

exec CTX_OUTPUT.START_QUERY_LOG('querylog');

Die folgenden SELECT Statements werden nun in einer Logdatei aufgezeichnet. Dabei nehmen als Grundlage, die in Blog generierte Tabelle TEXTTABELLE und den dazugehörenden Textindex.

SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'USA and Messe')>0; 
SELECT * FROM texttabelle WHERE contains(dokument, 'Software')>0;
SELECT * FROM texttabelle WHERE contains(dokument, 'Hamburg')>0;

Das Logging wird dann mit folgendem Aufruf beendet:

execute CTX_OUTPUT.END_QUERY_LOG;

Wie schon erwähnt, ist diese Art von Logging vergleichbar mit dem Logging mit CTX_OUTPUT.START_LOG (siehe Blog). Es wird eine Datei mit Namen QUERYLOG im Logverzeichnis /tmp erzeugt. Allerdings liegt diese Logdatei in einem speziellen XML Format vor, wie in unserem Beispiel zu sehen ist:


<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>USA and Messe</Query><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>Software</Query>><ReturnHit>Yes</ReturnHit><QueryTag></QueryTag></QuerySet>
<QuerySet><TimeStamp>10:30:42 11/20/08 </TimeStamp><IndexName>IDX_TEXT</IndexName><Query>Oracle</Query>><ReturnHit>No</ReturnHit><QueryTag></QueryTag></QuerySet>

Wie lässt sich nun diese Datei, besonders wenn sie grösser als in unserem Minibeispiel ist, bequem auslesen?
Das Package CTX_REPORT mit der Prozedur QUERY_LOG_SUMMARY liefert eine mögliche Vorgehensweise. Diese Prozedur, als CTXSYS User ausgeführt, liest die TOP N Abfragen und liefert die Resultate in einer PL/SQL Table vom Typ CTX_REPORT.QUERY_TABLE. Die Zahl N ist dabei definierbar über den Parameter ROW_NUM. Folgender Beispiel-Code liest unsere Log-Datei QUERYLOG aus und liefert das Ergebnis der TOP 10 Abfragen:

SQL> connect ctxsys
Enter password:
Connected.
SQL> set serveroutput on

SQL> declare
  2  logentry ctx_report.query_table;
  3  begin
  4  ctx_report.query_log_summary('querylog','idx_text', logentry, row_num=>10);
  5  for i in 1..logentry.count 
  6  loop
  7  dbms_output.put_line(logentry(i).query || ' kommt '||logentry(i).times||' Mal vor');
  8  end loop;
  9  end;
  10  /
USA kommt 4 Mal vor
USA and Messe kommt 1 Mal vor
Software kommt 1 Mal vor

PL/SQL procedure successfully completed.

Standardmässig werden dabei nur die häufigsten Treffer der erfolgreichen Abfragen ausgewertet, da der Parameter MOST_FREQ auf TRUE steht. Manchmal ist es allerdings interessanter, die fehlgeschlagenen Abfragen zu dokumentieren. Der Parameter HAS_HIT (Defaultwert: TRUE) kontrolliert das Verhalten der Auswertung bzgl. der erfolgreichen Treffer. Setzen wir diesen Wert auf FALSE, werden nur die Resultate angezeigt, die von fehlgeschlagenen Suchabfragen stammen.
Folgendes Beispiel demonstriert das Verhalten:

SQL> set serveroutput on
SQL> declare
  2  logentry ctx_report.query_table;
  3  begin
  4  ctx_report.query_log_summary('querylog','idx_text', logentry, row_num=>10, has_hit=>false);
  5  for i in 1..logentry.count 
  6  loop
  7  dbms_output.put_line(logentry(i).query || ' kommt '||logentry(i).times||' Mal vor');
  8  end loop;
  9  end;
 10  /
Hamburg kommt 1 Mal vor

PL/SQL procedure successfully completed.

Um solches Fehlschlagen von Suchabfragen zu verhindern, könnte man z.B. in den Applikationen bessere Hilfestellung für den User anbieten oder über Synonyme (Thesaurus) alternative Schreibweisen ermöglichen.
Mehr dazu in einem unserer nächsten Blog-Veröffentlichungen. Viel Spass beim Ausporbieren ...

Beliebte Postings