Donnerstag, 18. April 2013

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

Beliebte Postings