Indexstatistiken (INDEX_STATS) im XML-Format: Oracle TEXT Management einfach automatisieren!

Heute geht es um Statistiken für einen Oracle TEXT Index - vor einiger Zeit gab es dazu schon mal ein Posting - heute soll es darum gehen, wie man diese Statistiken so zur Verfügung stellt, dass man mit Reporting- oder Management-Tools einfach darauf zugreifen oder diese zur Automatisierung von Aufgaben verwenden kann. Indexstatistiken kann man mit CTX_REPORT.INDEX_STATS abrufen. In nahezu allen Beispielen (so auch im erwähnten Blog-Posting) werden die Statistiken als Textausgabe erzeugt - in etwa wie folgt ...

===========================================================================
                    STATISTICS FOR "TESTIT"."IDX_TEXT"
===========================================================================

indexed documents:                                                     10
allocated docids:                                                      10
$I rows:                                                               56

---------------------------------------------------------------------------
                             TOKEN STATISTICS
---------------------------------------------------------------------------
:

Dieses Format ist zwar gut lesbar, zur Automatisierung jedoch völlig ungeeignet. Allerdings bietet Oracle TEXT noch eine andere Variante zur Ausgabe der Statistiken an: XML - und das geht wie folgt.

DROP TABLE ausgabe
/

CREATE TABLE ausgabe (
  index_name  varchar2(200),
  zeitstempel date,
  resultat    xmltype
)
xmltype column resultat store as binary xml
/

 
declare
  ergebnis clob := null;
begin
  ctx_report.index_stats(
    index_name     => 'IDX_TEXT',
    report         => ergebnis,
    report_format  => ctx_report.fmt_xml,
    stat_type      => null
  );
  insert into ausgabe values ('IDX_TEXT', sysdate, xmltype(ergebnis));
  dbms_lob.freetemporary(ergebnis);
end;
/

 
set long 32000
set head off
set pagesize 10000

SELECT * FROM ausgabe
/

Das generierte Format sieht nun wie folgt aus ...

<CTXREPORT>
  <INDEX_STATS>
    <STAT_INDEX_NAME>"TESTIT"."IDX_TEXT"</STAT_INDEX_NAME>
    <STAT_INDEX_STATS>
      <STAT_STATISTIC NAME="indexed documents">10</STAT_STATISTIC>
      <STAT_STATISTIC NAME="allocated docids">10</STAT_STATISTIC>
      <STAT_STATISTIC NAME="$I rows">56</STAT_STATISTIC>
      :

Bei großen Indizes braucht der Aufruf von INDEX_STATS sehr lange - hier ist es sicher sinnvoll, mit Hilfe von DBMS_SCHEDULER einen Job zu erzeugen, welcher die Statistiken regelmäßig (bspw. über Nacht) aktualisiert. Das Interessante am XML-Format ist nun, dass es sich maschinell auswerten lässt. Dazu nutzen wir die XML-Funktionen in der Oracle-Datenbank und erstellen eine (relationale) View auf die XML-Ausgabe von CTX_REPORT.INDEX_STATS.

create or replace view view_index_stats as
select
  a.index_name,
  a.zeitstempel,
  x.indexed_documents,
  x.allocated_docids,
  x.dollar_i_rows,
  x.dollar_i_dsize,
  x.index_frag,
  x.garbage_docids,
  x.garbage_size
from 
  ausgabe a,
  xmltable(
    '/CTXREPORT/INDEX_STATS'
    passing a.resultat
    columns 
      indexed_documents number path '/INDEX_STATS/STAT_INDEX_STATS/STAT_STATISTIC[@NAME="indexed documents"]',
      allocated_docids  number path '/INDEX_STATS/STAT_INDEX_STATS/STAT_STATISTIC[@NAME="allocated docids"]',
      dollar_i_rows     number path '/INDEX_STATS/STAT_INDEX_STATS/STAT_STATISTIC[@NAME="$I rows"]',
      dollar_i_dsize    number path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="total size of $I data"]',
      index_frag        varchar2(10) path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="estimated row fragmentation"]',
      garbage_docids    number path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="garbage docids"]',
      garbage_size      number path '/INDEX_STATS/STAT_FRAG_STATS/STAT_STATISTIC[@NAME="estimated garbage size"]'
  ) x
/

Diese View lässt sich nun wie eine Tabelle verwenden ...

SQL> select * from view_index_stats

INDEX_NAME ZEITSTEMPEL          INDEXED_DOCUMENTS ALLOCATED_DOCIDS
---------- -------------------- ----------------- ----------------
DOLLAR_I_ROWS DOLLAR_I_DSIZE INDEX_FRAG GARBAGE_DOCIDS GARBAGE_SIZE
------------- -------------- ---------- -------------- ------------
IDX_TEXT   18.04.2013 14:43:49                 10               10
           56            198  0 %                    0            0


1 Zeile wurde ausgewählt.

Nicht so schön ist die Spalte INDEX_FRAG - denn das XML, welches von INDEX_STATS generiert wurde, enthält hier tatsächlich das Prozentzeichen - weshalb es nicht als NUMBER aufgefasst werden kann - hierfür müssen wir in der View-Definition noch ein wenig was tun ...

create or replace view view_index_stats as
select
  a.index_name,
  a.zeitstempel,
  x.indexed_documents,
  x.allocated_docids,
  x.dollar_i_rows,
  x.dollar_i_dsize,
  to_number(replace(x.index_frag, ' %', '')) as index_frag,
  x.garbage_docids,
 :

Danach ist auch die Spalte INDEX_FRAG vom Typ NUMBER und man kann nun numerische Vergleiche durchführen. Analog dazu lassen sich auch die Token-Statistiken entsprechend aufbereiten ... Dazu bauen wir eine zweite View ...

create or replace view index_stats_frag_tokens as
select
  a.index_name,
  a.zeitstempel,
  x.token_text,
  x.token_type,
  to_number(replace(x.token_frag, ' %', '')) as token_frag
from 
  ausgabe a,
  xmltable(
    '/CTXREPORT/INDEX_STATS/STAT_FRAG_STATS/STAT_TOKEN_LIST/STAT_TOKEN'
    passing a.resultat
    columns 
      token_text   varchar2(64) path '/STAT_TOKEN/STAT_TOKEN_TEXT',
      token_type   varchar2(50) path '/STAT_TOKEN/STAT_TOKEN_TYPE',
      token_frag   varchar2(20) path '/STAT_TOKEN/STAT_TOKEN_STATISTIC'
  ) x
/
  
select * from index_stats_frag_tokens
/

... deren Inhalt nun wie folgt aussieht ...

SQL> select * from index_stats_frag_tokens

INDEX_NAME ZEITSTEMPEL          TOKEN_TEXT           TOKEN_TYPE           TOKEN_FRAG
---------- -------------------- -------------------- -------------------- ----------
IDX_TEXT   18.04.2013 14:55:28  ZU                   0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WOLLEN               0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WIRTSCHAFT           0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WIRD                 0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WICHTIGER            0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WERDEN               0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WER                  0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WEITER               0:TEXT                        0
IDX_TEXT   18.04.2013 14:55:28  WAHLKAMPF            0:TEXT                        0
:

Lässt man das XML nun, wie schon erwähnt, per DBMS_SCHEDULER regelmäßig aktualisieren, so hat man (wie im Data Dictionary) bequem nutzbare Views mit Statistiken zum Index. Diese können nun natürlich auch für eine automatisierte Verarbeitung genutzt werden. So könnte ein Job regelmäßig alle Tokens optimieren (CTX_DDL.OPTIMIZE mit OPTLEVEL_TOKEN), die eine bestimmte Fragmentierung überschreiten. Gleiches gilt natürlich auf Indexebene.

Auch die Integration mit Management-Werkzeugen wie dem Oracle Enterprise Manager ist kein Problem. In der deutschsprachigen DBA Community ist beschrieben, wie man eine eigene "Metrik", basierend auf einer SQL-Abfrage einrichtet. Nimmt man hierfür die erstellten Views, so kann der Enterprise Manager einen Alert senden, sobald die Indexfragmentierung einen bestimmten Schwellenwert übersteigt. Die professionelle Wartung einer Oracle TEXT-Installation ist damit kein Problem mehr ...

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

Monitoren von Oracle Text Indizes: Grundsätzliche Funktionen

Wie kann man den Textindex monitoren? Welche Möglichkeiten gibt es? Dazu stehen spezielle Data Dictionary Views, Packages oder ab Oracle Database 11g die Enterprise Manager Funktionalität zur Verfügung. Im folgenden Beispiel nutzen wir eine Tabelle die ca. 600 000 Zeilen besitzt und die mit einer TEXT-Spalte mit Kurztexten ausgestattet ist.
Zuerst erzeugen wir den Textindex TEXT_COMP und monitoren gleichzeitig den Indexvorgang. Mit dem Aufruf CTX_ADM.SET_PARAMETER läßt sich dabei das Logverzeichnis festlegen - in unserem Fall /tmp. Mit CTX_OUTPUT.START_LOG starten wir den Logvorgang. Folgender Aussschnitt zeigt die Verwendung:

SQL> execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/tmp'); 
PL/SQL procedure successfully completed.

SQL> execute CTX_OUTPUT.START_LOG('textlog');
PL/SQL procedure successfully completed.

SQL> CREATE INDEX text_comp ON comp_test(text)
  2  INDEXTYPE IS ctxsys.context PARAMETERS('memory 100M');
Index created.

SQL> execute CTX_OUTPUT.END_LOG;
PL/SQL procedure successfully completed.

Bevor wir uns die Logtabelle ansehen, sollten wir zuerst überprüfen, ob der Index ohne Fehler erzeugt worden ist. Folgendes Kommando gibt Aufschluss darüber. Diese Überprüfung sollte nach jeder Indexerzeugung durchgeführt werden.

SQL> SELECT err_index_name, err_timestamp, err_textkey, err_text
   2 FROM ctx_user_index_errors ORDER BY err_index_name, err_timestamp;
no rows selected

In unserem Fall liegt kein Fehler vor. Falls diese Tabelle Zeilen enthält, gibt die Spalte ERR_TEXTKEY Aufschluss über die ROWID des entsprechenden Dokuments, das den Fehler hervorgerufen hat. Damit hat man einen Startpunkt, um den Fehler zu beheben.

Die Datei "textlog" im Verzeichnis /tmp gibt nun Auskunft über den Vorgang der Indizierung. Sehr wichtig dabei ist, dass die Angabe von "PARAMETERS ('MEMORY 100M')" zu einer Beschleunigung des Indizierungsvorgangs führt. Der Wert 100M bestimmt die Größe der Token und Mapping Tabelle im Memory, bevor die Informationen in die entsprechenden Tabellen geschrieben werden. Der Vorgang wird so lange wiederholt bis der Index erzeugt worden. Daher ist es sehr sinnvoll den Parameter Memory zu setzen und den Wert so gross wie möglich zu wählen. Folgender Ausschnitt zeigt ein Beispiel einer Logtabelle und gibt Aufschluss über die Nutzung des Memory Parameters.

Oracle Text, 11.1.0.6.0
14:00:28 10/24/08 begin logging
14:00:49 10/24/08 populate index: COMP.TEXT_COMP
14:00:49 10/24/08 Begin document indexing
14:00:49 10/24/08 100 documents indexed
14:00:49 10/24/08 200 documents indexed
14:00:49 10/24/08 300 documents indexed
14:00:49 10/24/08 400 documents indexed
14:00:49 10/24/08 500 documents indexed
...
14:01:58 10/24/08 118200 documents indexed
14:01:58 10/24/08 Errors reading documents: 0
14:01:58 10/24/08 Index data for 118236 documents to be written to database
14:01:58 10/24/08    memory use: 98846623
14:01:58 10/24/08 Begin sorting the inverted list.
14:01:58 10/24/08 End sorting the inverted list.
14:01:58 10/24/08 Writing index data ($I) to database.
14:02:06 10/24/08 Wrote 313223 rows (12067 big rows) to $I.
14:02:06 10/24/08 Writing index data ($R) to database.
14:02:06 10/24/08    index data written to database.
... 

Zusätzlich bietet die Verwendung des Package CTX_REPORT weitere Möglichkeiten, Informationen zum Textindex zu erhalten. Im folgenden sollen 2 Beipiele dies illustrieren.
Möchte man z.B. die Größe des Textindex herausfinden, kann der Aufruf von CTX_REPORT.INDEX_SIZE einen detaillierten Bericht dazu liefern. Abgesehen von der Gesamtgröße des Index erhält man eine detaillierte Auflistung über die Größe der zugehörigen $I, $K, $R Tabellen usw. und der Zuordnung zu den Tablespaces.
Folgender Abschnitt zeigt einen Auszug aus dem Report:

SQL> col ausgabe format a150
SQL> set long 2000000000 head off pagesize 10000
SQL> select ctx_report.index_size('TEXT_COMP') as ausgabe from dual;

===========================================================================
                       INDEX SIZE FOR COMP.TEXT_COMP
===========================================================================
TABLE:                               COMP.DR$TEXT_COMP$I
TABLESPACE NAME:                     USERS
BLOCKS ALLOCATED:                                                    13056
BLOCKS USED:                                                         12752
BYTES ALLOCATED:                                   106,954,752 (102.00 MB)
BYTES USED:                                         104,464,384 (99.63 MB)
LOB SEGMENT:                         COMP.SYS_LOB0000094903C00006$$
TABLE NAME:                          COMP.DR$TEXT_COMP$I
LOB COLUMN:                          TOKEN_INFO
TABLESPACE NAME:                     USERS
BLOCKS ALLOCATED:                                                        8

...

TOTALS FOR INDEX COMP.TEXT_COMP
---------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED:                                              16040
TOTAL BLOCKS USED:                                                   15487
TOTAL BYTES ALLOCATED:                             131,399,680 (125.31 MB)
TOTAL BYTES USED:                                  126,869,504 (120.99 MB)

Benötigt man ein zugehöriges CREATE INDEX Skript, ist es nicht notwendig, im Data Dictionary nach den Eigenschaften des Index zu suchen. Das einfache Ausführen der Funktion CREATE_INDEX_SCRIPT, liefert eine Liste von DDL Kommandos, die einen vergleichbaren Index erzeugen:

select ctx_report.create_index_script('TEXT_COMP') from dual
begin
  ctx_ddl.create_preference('"TEXT_COMP_DST"','DIRECT_DATASTORE');
end;
/

...
begin
  ctx_output.start_log('TEXT_COMP_LOG');
end;
/

create index "COMP"."TEXT_COMP"
  on "COMP"."COMP_TEST"
      ("TEXT")
  indextype is ctxsys.context
  parameters('
    datastore       "TEXT_COMP_DST"
    filter          "TEXT_COMP_FIL"
    section group   "TEXT_COMP_SGP"
    lexer           "TEXT_COMP_LEX"
    wordlist        "TEXT_COMP_WDL"
    stoplist        "TEXT_COMP_SPL"
    storage         "TEXT_COMP_STO"
  ')
/

begin
  ctx_output.end_log;
end;
/

Wer allerdings schon Oracle Database 11g einsetzt und die Möglichkeit besitzt auf Enterprise Manager Database Control zuzugreifen, kann sich das Skripting sparen. Alle hier aufgelisteten Funktionalitäten sind durch einfaches Navigieren im Text Manager unter "Schema => Text Indexes" zu finden.