Donnerstag, 29. August 2013

DBMS_PCLXUTIL zur Erzeugung von lokalen Indizes

Um die Performance beim Aufbau eines Index zu erhöhen, können Oracle Text Indizes mit dem Schlüsselwort PARALLEL aufgebaut werden. Geht es um den parallelen Aufbau eines lokalen partitionierten Index, kann allerdings auch das altbewährte Package DBMS_PCLXUTIL eine Alternative darstellen. Wer eine Beschreibung des Package sucht, wird im PL/SQL Packages Guide fündig; die Verwendung im Oracle Text Umfeld ist etwas versteckt im Oracle Text Reference Guide nachzuschlagen. Da es in letzter Zeit vermehrt Anfragen dazu gab, haben wir uns entschlossen, die Verwendung an einem Beispiel zu demonstrieren.
Zuerst zur Definition des Package: Das Package DBMS_PCLXUTIL sieht folgendermassen aus:
desc dbms_pclxutil
PROCEDURE BUILD_PART_INDEX
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOBS_PER_BATCH                 NUMBER                  IN     DEFAULT
 PROCS_PER_JOB                  NUMBER                  IN     DEFAULT
 TAB_NAME                       VARCHAR2                IN     DEFAULT
 IDX_NAME                       VARCHAR2                IN     DEFAULT
 FORCE_OPT                      BOOLEAN                 IN     DEFAULT
Die Verwendung von TAB_NAME und IDX_NAME muß sicherlich nicht erklärt werden. Anzumerken ist höchstens, dass man nur als Eigentürmer der Tabellen und des Index das Package verwenden kann; eine Benennung über eine zusätzliche Schemabezeichnung ist nicht möglich. Die beiden Parameter JOBS_PER_BATCH und PROCS_PER_JOB bestimmen dabei im Unterschied zu anderen Methoden die Parallelisierung auf zwei (!) Ebenen. JOBS_PER_BATCH ist für die sogenannte Inter Parallelität zuständig, die mit DBMS_JOB Prozessen realisiert wird, und PROCS_PER_JOB für die sogenannte Intra Parallelität, die mit parallelen Prozessen ausgeführt wird.

Genauer bedeutet dies:
JOBS_PER_BATCH steht für die Anzahl der Job Prozesse, die gleichzeitig arbeiten. Hier sollte gelten: Das Minimum ist 1, das Maximum stellt die Anzahl der Partitionen dar.

PROCS_PER_JOB bestimmt die Anzahl der parallelen Query Prozesse pro Job. Auch hier stellt die Zahl 1 das Minimum dar.

FORCE_OPT kann den Wert TRUE oder FALSE haben. FALSE führt nur ein REBUILD für UNUSABLE Indizes aus, TRUE hingegen für alle Partitionen.

Bevor wir starten, sollte zuerst die Einstellung der Job Prozesse geprüft werden. Der Initialisierungsparameter JOB_QUEUE_PROCESSES gibt die maximale Anzahl der Job Prozesse an.
show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
Wichtig zu wissen ist, dass die Prozedur BUILD_PART_INDEX davon ausgeht, dass die Data Dictionary Informationen zum Index schon existieren. Falls nicht erhält man folgende Fehlermeldung:
*
ERROR at line 1:
ORA-20001: Specified local index name 'PROD_DESC_IDX' does not exist
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 301
ORA-06512: at line 1
Vorab ist also folgendes Kommando sinnvoll:
CREATE INDEX prod_desc_idx ON product_part (prod_desc) INDEXTYPE IS ctxsys.context LOCAL UNUSABLE;
Ab 11g Release 2 wird für UNUSABLE Index Partitionen übrigens kein Speicherplatz mehr verbraucht, so dass die Information nicht in USER_SEGMENTS verzeichnet ist. Die Data Dictionary Informationen sind aber wie immer in USER_INDEXES gespeichert.
SELECT * FROM user_segments WHERE segment_name='PROD_DESC_IDX';

no rows selected

SELECT index_name, index_type FROM user_indexes WHERE index_name LIKE 'PROD_DESC%'

INDEX_NAME           INDEX_TYPE
-------------------- ---------------------------
PROD_DESC_IDX        DOMAIN
Um eine Anwendung zu demonstrieren, erzeugen wir einen lokalen Text Index auf die Spalte PROD_DESC der partitionierten Tabelle PRODUCTS_PART mit den 4 Partitionen P_10, P_100, P_1000 und PARTMAXVALUE.
EXECUTE dbms_pclxutil.build_part_index
(JOBS_PER_BATCH => 4,
 PROCS_PER_JOB  => 1,
 TAB_NAME       => 'PRODUCT_PART',
 IDX_NAME       => 'PROD_DESC_IDX',
 FORCE_OPT      => TRUE); 
Parallel dazu lassen sich die Job Prozesse überwachen - es werden 4 Jobs gestartet jeweils mit Parallelität 1.
SELECT job, this_date, next_date, failures, what FROM dba_jobs;

       JOB THIS_DATE        NEXT_DATE        FAILURES
---------- ---------------- ---------------- ----------
WHAT
--------------------------------------------------------------------------------
        42                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_100" parallel (degree 1)');

        43                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_1000" parallel (degree 1)');

        44                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "PARTMAXVALUE" parallel (degree 1)');

        41                  27.08.2013 17:41
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_10" parallel (degree 1)');
Am Schluss sollte natürlich auch die erfolgreiche Indexerstellung überprüft werden.
SELECT err_timestamp, err_text FROM ctx_user_index_errors
ORDER BY err_timestamp DESC;

no rows selected
Möglich wäre allerdings auch folgendes Kommando mit insgesamt 2 Jobs - dabei jeweils 2 parallele Prozessen pro Job.
EXECUTE dbms_pclxutil.build_part_index
(JOBS_PER_BATCH => 2,
 PROCS_PER_JOB  => 2,
 TAB_NAME       => 'PRODUCT_PART',
 IDX_NAME       => 'PROD_DESC_IDX',
 FORCE_OPT      => TRUE);
Prüft man die Jobs, stellt man folgende Aufrufe fest.
SELECT job, this_date, next_date, failures, what FROM dba_jobs;

       JOB THIS_DATE NEXT_DATE FAILURES
---------- --------- --------- ----------
WHAT
--------------------------------------------------------------------------------
       221 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "PARTMAXVALUE" parallel (degree 2)');

       222 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_10" parallel (degree 2)');
Dann nach einer gewissen Zeit ...
SELECT job, this_date, next_date, failures, what FROM dba_jobs;

       JOB THIS_DATE NEXT_DATE FAILURES
---------- --------- --------- ----------
WHAT
--------------------------------------------------------------------------------
       241 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_100" parallel (degree 2)');

       242 28-AUG-13 28-AUG-13
dbms_utility.exec_ddl_statement('alter index "SH"."PROD_DESC_IDX" rebuild partit
ion "P_1000" parallel (degree 2)');
Er laufen also immer zwei Jobs zu einer Zeit. Prüft man gleichzeitig die Parallelität über v$px_session und V$session, erkennt man, dass wirklich 2 Prozesse pro Job arbeiten.
Username     QC/Slave   Slave Set  SID    QC SID Requested DOP Actual DOP
------------ ---------- ---------- ------ ------ ------------- ----------
SH           QC                    34     34
 - p001      (Slave)    1          43     34                 2          2
 - p000      (Slave)    1          87     34                 2          2
SH           QC                    94     94
 - p003      (Slave)    1          95     94                 2          2
 - p002      (Slave)    1          98     94                 2          2
Wo liegt nun der Unterschied zwischen den beiden Läufen? Beim zweiten Lauf arbeiten mehrere -hier 2 - Prozesse pro Job gleichzeitig. Unter Umständen sind dadurch die Index Partitionen schneller erstellt, was bei großen Indizes mit langen Laufzeiten sicherlich wünschenswert ist. Allerdings ist folgendes zu beachten: Da wir mit mehreren Prozessen pro Job arbeiten, kann es zu einer höheren Fragmentierung der einzelnen Index Partitionen kommen. Hier wäre dann ein anschliessendes CTX_DDL.OPTIMIZE_INDEX nötig.

Zum Schluss vielleicht noch ein wichtiger Hinweis: Vergessen Sie bei all diesen Operationen die Memory Einstellung nicht. Die Überprüfung kann zum Beispiel über CTX_PARAMETERS erfolgen.
SELECT * FROM ctx_parameters WHERE par_name LIKE '%MEM%';

PAR_NAME                       PAR_VALUE
------------------------------ -----------------------------------
DEFAULT_INDEX_MEMORY           67108864
MAX_INDEX_MEMORY               1073741824
Da DBMS_PCLXUTIL ein Package zur allgemeinen Verwendung ist, sind keine Text spezifischen Einstellungen über den Package Aufruf möglich - also auch keine Memory Einstellungen. Möchte man eine spezielle Memory Einstellung verwenden, kann beispielsweise die Prozedur CTXSYS.CTX_ADM.SET_PARAMETER verwendet werden.

Keine Kommentare:

Beliebte Postings