Performance beim Erzeugen eines Oracle Text Index

Häufig erhalten wir die Frage, ob und wie sich die Performance beim Erzeugen eines Oracle Text Index beeinflussen lässt. Die Frage taucht in der Regel dann auf, wenn dem Anwender das Indexanlegen zu lange dauert. Eine Überprüfung der Oracle Text Memory Einstellung ist eine erste einfache Massnahme bei dieser Fragestellung. Setzt man nämlich einen Oracle Text Index ohne zusätzliche Memory Parameter Einstellung ab, wird die Default Einstellung, die häufig zu klein ist, genommen.

Wie kann man nun die Default Einstellungen einsehen? Eine einfache Abfrage auf die Tabelle CTX_PARAMETERS zeigt die Einstellungen für den Oracle Text Index.

col PAR_NAME format a40
col PAR_VALUE format a30
select * from ctx_parameters where PAR_NAME like '%MEMORY%';

PAR_NAME                                 PAR_VALUE
---------------------------------------- ------------------------------
DEFAULT_INDEX_MEMORY                     67108864
MAX_INDEX_MEMORY                         274877906944

Die Defaulteinstellung DEFAULT_INDEX_MEMORY liegt also bei 64 MB. Das bedeutet, beim CREATE INDEX werden normalerweise 64 MB verwendet. Der zweite Parameter gibt die maximale Einstellung an, die beim Indexanlegen überhaupt genutzt werden können. Hier - in einer 12.1.0.2 Umgebung - ist der Wert des Parameters 256 GB.

Es gibt nun zwei Möglichkeiten, die Memoryeinstellung zu ändern. Entweder während der Laufzeit über das CREATE INDEX Statement oder über die Änderung der Gesamteinstellung mit dem Package CTX_ADM. Folgendes Beispiel zeigt die Nutzung beim Indexanlegen.

create index my_idx on customers(CUST_STREET_ADDRESS) 
indextype is ctxsys.context parameters ('memory 100M');

Folgendes Beispiel zeigt die Verwendung von CTX_ADM.

begin
ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','100G');
end;
/

begin
ctxsys.ctx_adm.set_parameter('DEFAULT_INDEX_MEMORY','512M');
end;
/

Ziel sollte sein, das Index Memory so hoch wie möglich zu setzen - allerdings unter Vermeidung von Paging. Einerseits kann mit dieser Massnahme das Indexanlegen beschleunigt werden, andererseits wird dabei auch die Fragmentierung des Index geringer.

Wichtiger Hinweis: Die maximale Einstellung für MAX_INDEX_MEMORY hat sich übrigens von 11g nach 12c verändert. In 11g lag der Wert noch bei 2 GB, in 12c ist dieser Wert auf 256 GB erhöht worden. Dies wird übrigens auch in den Defaulteinstellung sichtbar (siehe obige Abfrage auf CTX_PARAMETERS). Beachten Sie auch: 256 GB ist eine theoretische Grenze, die tatsächliche Grenze von MAX_INDEX_MEMORY könnte je nach Umgebung niedriger ausfallen.

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