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