In the Oracle Database 12c: Performance Management and Tuning course, learn about the performance analysis and tuning tasks expected of a DBA: proactive management through built-in performance analysis features and tools, diagnosis and tuning of the Oracle Database instance components, and diagnosis and tuning of SQL-related performance issues.

 Schulungsunterlage - Inhaltsverzeichnis

Learn Oracle from Oracle - Original Oracle University Seminar


Im Kurs "Oracle Database: SQL Tuning for Developers" lernen die Teilnehmer Oracle SQL-Tuning kennen und erfahren, wie sie die Tuningverfahren auf ihren SQL-Code anwenden. Die Teilnehmer lernen die verschiedenen Möglichkeiten kennen, mit denen sie effizient auf Daten zugreifen können.

In Anlehnung an Tanel Poders interessanten Artikel über Zeilenmigration hier eine Zusammenfassung.

Was ist Zeilenmigration.

Speziell bei Tabellen mit variablen Datentypen kann ein Problem auftreten, das als Zeilenmigration bekannt ist. Man stelle sich vor, eine Tabelle besitzt eine Spalte „Nachname“ des Datentyps VARCHAR2(100). Eine Zeile dieser Tabelle muss geändert werden, indem der Spaltenwert dieser Zeile von „Meier“ auf „Meier-Schmidt“ geändert wird. Das Problem ist allerdings, dass der Block, in dem sich diese Zeile befindet, voll ist und sich somit die Zeile nicht ausdehnen kann. Da aber eventuell Indizes auf die ROWID dieser Zeile verweisen, kann die Zeile nicht einfach in einen freien Block verschoben werden.

Entstehung von Zeilenmigration

Was geschieht: Dort, wo die Zeile ursprünglich war, wird ein Zeiger erzeugt, die Zeile wird in einen freien Block verschoben und der Zeiger zeigt dann auf den neuen Ort der Zeile.

Erfolgte Zeilenmigration

Erfolgt also eine Suche über einen Index, so zeigt der Index auf die alte ROWID der Zeile, an der sich aber nun der Zeiger befindet, der wiederum auf den neuen Block der Zeile zeigt.

Wichtig:

Problematisch hierbei ist, dass nun für das Auslesen dieser Zeile über einen Index zwei Blöcke (plus Index-Blöcke) gelesen werden müssen, nämlich der Block mit dem Zeiger und der neue Block der Zeile, was wiederum zwei I/O-Operationen nach sich zieht. Befindet sich ein hoher Grad an Zeilenmigration in einer Tabelle, so müssen mehr Blöcke gelesen werden als eigentlich notwendig, was einen großen Einfluss auf die Performance bei Lesevorgängen der Datenbank hat.

Werden variable Datentypen innerhalb einer Tabelle verwendet, so muss darauf geachtet werden, dass die Migration von Zeilen weitestgehend vermieden wird. Dies kann durch die Verwendung eines geeigneten Wertes für den Parameter PCTFREE realisiert werden, da dieser bewirkt, dass ein prozentualer Anteil des Blocks frei bleibt, wenn der Block als voll gekennzeichnet wird. Dieser übrig gebliebene freie Speicher innerhalb des Blocks kann dann für Änderungen an Spalten variabler Datentypen verwendet werden.

Um die Anzahl der migrierten Zeilen herauszufinden, müssen die Tabellen mit dem ANALYZE-Befehl analysiert werden, wodurch die Spalte CHAIN_CNT mit dessen Anzahl befüllt wird. Das Befüllen erfolgt leider nicht mit dem Package DBMS_STATS.

 

SQL> ANALYZE TABLE TABPCTTEST COMPUTE STATISTICS;
Tabelle wurde analysiert.

SQL> SELECT BLOCKS "BLOECKE", NUM_ROWS/BLOCKS "ZEILEN PRO BLOCK",
2 CHAIN_CNT KETTEN, PCT_FREE
3 FROM DBA_TABLES
4 WHERE TABLE_NAME='TABPCTTEST';

   BLOECKE ZEILEN PRO BLOCK     KETTEN   PCT_FREE
---------- ---------------- ---------- ----------
       13       153,846154       358         1

 

Da das Analysieren von großen Tabellen viel Zeit und Performance in Anspruch nimmt, sollte im Vorfeld eine Tendenz von migrierten Zeilen ermittelt werden. Herfür kann auf Sitzungsebene das Verhältnis von gelesenen Zeilen über einen Index und die davon abhängigen Weiterleitungen durch migrierte Zeilen bestimmt werden. Oracle besitzt dafür die Statistik TABLE FETCH BY ROWID, die die Zeilen bestimmt, die durch direkten Zugriff über die ROWID gelesen wurden und die Statistik TABLE FETCH CONTINUED ROW, die die Anzahl der Weiterleitungen wiedergibt.

Im folgenden Beispiel wird eine Tabelle TESTTAB erstellt, mit 100000 Zeilen befüllt und auf die Spalte S1 ein Index erstellt. Alle Werte der VARCHAR2-Spalte S2 werden dann im nächsten Schritt expandiert, so dass zwangsläufig eine hohe Anzahl an migrierten Zeilen entstehen muss. Danach wird dann über eine Schleife jeder Wert einzeln ausgelesen, um bei jedem Zugriff den Index zu verwenden und mögliche Weiterleitung zu erhalten.

Erstellung der Testtabelle:


SQL> CREATE TABLE HR.TESTTAB (S1 NUMBER, S2 VARCHAR2(100)) PCTFREE 1 TABLESPACE USERS;
Table created.

SQL> BEGIN
2 FOR I IN 1..100000
3 LOOP
4 INSERT INTO HR.TESTTAB VALUES (I, 'WERT' || I);
5 END LOOP;
6 COMMIT;
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX HR.IDX_TESTTAB ON HR.TESTTAB(S1) TABLESPACE USERS;

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TESTTAB')

PL/SQL procedure successfully completed.

 
Expandieren der Spaltenwerte zur Forcierung von Zeilenmigration:

 

SQL> UPDATE HR.TESTTAB
2 SET S2='DAS IST JETZT EIN GANZ LANGER WERT FÜR DAS TESTEN DER ZEILENMIGRATION' || S1;

100000 rows updated.

SQL> COMMIT;

Commit complete.

Auslesen jeder einzelnen Zeile über den Index:

 

SQL> DECLARE
2     V_S2 VARCHAR2(100);
3 BEGIN
4     FOR J IN 1..10
5     LOOP
6             FOR I IN 1..100000
7             LOOP
8                     SELECT S2
9                     INTO V_S2
10                    FROM HR.TESTTAB
11                     WHERE S1=I;
12             END LOOP;
13     END LOOP;
14 END;
15 /

PL/SQL procedure successfully completed.

SQL>

Bestimmen des Verhältnisses zwischen gelesenen Zeilen und den erfolgten Weiterleitungen:

SQL> SELECT
2     NVL(VSE.USERNAME,'HINTERGRUND') BENUTZER,
3     VSE.SID,
4     VSE.PROGRAM AS PROGRAMM,
5     TFCR.VALUE AS "ZEILENWEITERLEITUNG",
6     TFBR.VALUE AS "ZEILEN LESEN MIT ROWID",
7     ROUND(((1-(TFBR.VALUE-TFCR.VALUE)/TFBR.VALUE))*100,2) AS RATIO
8 FROM
9     (
10     SELECT
11             VSE.SID, VSE.VALUE
12     FROM
13             V$SESSTAT VSE
14                     INNER JOIN V$STATNAME VSN
15                            ON VSE.STATISTIC#=VSN.STATISTIC#
16     WHERE
17             UPPER(VSN.NAME)='TABLE FETCH CONTINUED ROW'
18     ) TFCR
19             INNER JOIN
20                     (
21                     SELECT
22                             VSE.SID, VSE.VALUE
23                     FROM
24                             V$SESSTAT VSE
25                                     INNER JOIN V$STATNAME VSN
26                                             ON VSE.STATISTIC#=VSN.STATISTIC#
27                   WHERE
28                             UPPER(VSN.NAME)='TABLE FETCH BY ROWID'
29                     ) TFBR
30                             ON TFCR.SID=TFBR.SID
31     INNER JOIN
32             V$SESSION VSE
33                     ON TFCR.SID=VSE.SID
34 WHERE
35     TFCR.VALUE>0
36* ORDER BY VSE.USERNAME

BENUTZER                  SID PROGRAMM            ZEILENWEITERLEITUNG ZEILEN LESEN MIT ROWID   RATIO
------------------ ---------- -------------------- ------------------- ---------------------- --------
HR                           15 sqlplus.exe                      118204                 134128   88,13
HINTERGRUND                 98 ORACLE.EXE (Q000)                     29                    360    8,06
HINTERGRUND                 94 ORACLE.EXE (MMON)                      2                   1605     ,12

 

Das Ergebnis dieses Experiments zeigt, dass in der Sitzung des Benutzers HR von 100 Zugriffen auf Zeilen über einen Index 88 Zeilen migriert waren.

Der aktuelle Fortschritt des Statistiksammelprozess von großen Tabellen kann sehr gut über die View v$session_longops ausgelesen werden. Speziell im heutigen Fall wurde eine Oracle 9i Datenbank von einem Kunden über einen Datenbankimport wieder hergestellt. Hierbei fehlte nach dem Import ein Großteil der Statistiken, welches sich durch starke Performance-Probleme bemerkbar machte. Da das problematische Schema 37 sehr große Tabellen mit fehlenden Statistiken besaß, dauerte dieser Sammelprozess sehr lange. Um nun den aktuellen Fortschritt des Sammelprozesses einzusehen, verwendete ich deshalb folgende Abfrage:

SQL> select OPNAME, SOFAR, TOTALWORK, MESSAGE
2    from v$session_longops where TOTALWORK<>SOFAR;

OPNAME                    SOFAR      TOTALWORK  MESSAGE
------------------------- ---------- ---------- ------------------------------------------------------------
Gather Schema Statistics           3         37 Gather Schema Statistics: Schema : 3 out of 37 Objects done
Table Scan                     22503     753585 Table Scan:  DBW.DWH_TABSQG: 22503 out of 753585 Blocks done

Über die Spaten SOFAR und TOTALWORK konnte nun der aktuelle Fortschritt des Sammelprozesses ermittelt werden.

Sammeln von Systemstatistiken. Bei der Berechnung der Kosten für die Zugriffspfade durch den Optimizer sind Informationen über Zugriffszeiten des I/O-Systems und die Geschwindigkeit des Prozessors notwendig. Diese Informationen können über die Prozedur GATHER_SYSTEM_STATS mit dem Package DBMS_STATS gesammelt werden. Sind diese Informationen nicht verfügbar, verwendet der Optimizer Standardwerte, die vom realen System abweichen und somit evtl. falsche Informationen bei der Auswahl eines geeigneten Zugriffspads liefern. Die Sammlung der Systemstatistiken sollte bei Belastung des Systems durchgeführt werden, um entsprechend aussagekräftige Werte zu erhalten. Zusätzlich könnte es sinnvoll sein, die Systemstatistiken zu unterschiedlichen Zeiträumen sammeln zu lassen, falls das System in diesen Zeiträumen unterschiedliche Lastprofile besitzt. Diese könnten beispielsweise Tages- und Nachtbetrieb sein, weil nachts Jobs ausgeführt werden, während tagsüber die Datenbank im reinen OLTP-Betrieb läuft.

Systemstatistiken werden nicht automatisch gesammelt, und es gibt auch keinen Menüpunkt im Database Control für ihre Sammlung. Das Sammeln der Systemstatistiken muss manuell zu repräsentativen Zeiträumen eingeleitet werden.

Die aktuellen Systemstatistiken befinden sich in der Tabelle SYS.AUX_STATS$.

SQL> DESCRIBE SYS.AUX_STATS$

 Name                    Null?    Typ
 ----------------------- -------- --------------
 SNAME                   NOT NULL VARCHAR2(30)
 PNAME                   NOT NULL VARCHAR2(30)
 PVAL1                            NUMBER
 PVAL2                            VARCHAR2(255)

 

SQL> SELECT * FROM  SYS.AUX_STATS$; 

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ----------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-30-2010 13:29
SYSSTATS_INFO   DSTOP                      09-30-2010 13:31
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_MAIN   CPUSPEEDNW      1683,65129
SYSSTATS_MAIN   IOSEEKTIM               10
SYSSTATS_MAIN   IOTFRSPEED            4096
SYSSTATS_MAIN   SREADTIM          78311,25
SYSSTATS_MAIN   MREADTIM         60531,066
SYSSTATS_MAIN   CPUSPEED              1091
SYSSTATS_MAIN   MBRC                     2
......

13 Zeilen ausgewählt.

 

Beschreibung der Systemstatistiken:

STATUS

Status der Sammlung

DSTART

Startzeit der Sammlung

DSTOP

Endzeit der Sammlung

CPUSPEEDNW

CPU-Geschwindigkeit. Wird bei jedem Instanzstart gemessen.

IOSEEKTIM

Latenzzeit für des I/O-Systems in Millisekunden

IOTFRSPEED

Transferrate des I/O-Systems in Millisekunden

SREADTIM

Lesezeit eines einzelnen Blockes in Millisekunden

MREADTIM

Lesezeit von Multiblöcken bei Full-Table- oder Fast-Full-Index-Scans in Millisekunden

CPUSPEED

CPU-Geschwindigkeit.

MBRC

Durchschnittliche Anzahl der Blöcke beim Lesen einer Blocksequenz bei Full-Table- und Fast-Full-Index-Scans

 

Das Sammeln der Systemstatistiken kann entweder durch ein manuelles Starten und Stoppen oder durch Angabe eines Zeitintervalls durchgeführt werden.

Starten und Stoppen des Sammelvorgangs der Systemstatistiken:


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START')
PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP')
PL/SQL-Prozedur erfolgreich abgeschlossen.

 Sammeln von Systemstatistiken über ein Intervall von 10 Minuten:


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS(INTERVAL=>10,
                     GATHERING_MODE=>'INTERVAL')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Besitzt die Datenbank zu unterschiedlichen Zeiträumen ein anderes Lastprofil, so kann für jeden Zeitraum die entsprechende Systemstatistik gesammelt und geladen werden. Dafür muss eine Statistiktabelle mit der Prozedur CREATE_STAT_TABLE erstellt werden, in der die zu sammelnden Statistiken abgespeichert werden. Beim Start des Sammelvorgangs wird der Prozedur GATHER_SYSTEM_STATS diese Tabelle und ein Bezeichner für die zu speichernde Statistik übergeben. Soll nun eine Statistik geladen werden, so wird die Prozedur IMPORT_SYSTEM_STATS verwendet, indem man wieder die Tabelle und den Bezeichner für die Statistik übergibt.

Im folgenden Beispiel wird eine Tabelle TABSYSSTATS erstellt, in der für das tägliche Lastprofil die Systemstatistik mit dem Bezeichner STATDAY abgelegt wird. Das Gleiche wird für das nächtliche Lastprofil durchgeführt, für das die Statistik den Bezeichner STATNIGHT erhält. Morgens werden nun die Statistiken für den Tag und abends die Statistiken für die Nacht geladen.

 

Erstellen der Statistiktabelle:


SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('SYS','TABSYSSTATS','SYSAUX')
PL/SQL-Prozedur erfolgreich abgeschlossen.

 Erstellen der Systemstatistiken für den Tag:


SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START', STATTAB=>'TABSYSSTATS',
                                          STATID=>'STATDAY')
 PL/SQL-Prozedur erfolgreich abgeschlossen.

 

 SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP', STATTAB=>'TABSYSSTATS',
                                          STATID=>'STATDAY')

 PL/SQL-Prozedur erfolgreich abgeschlossen.
 

Erstellen der Systemstatistiken für die Nacht:

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START', STATTAB=>'TABSYSSTATS',
                                          STATID=>'STATNIGHT')
PL/SQL-Prozedur erfolgreich abgeschlossen.

 

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP', STATTAB=>'TABSYSSTATS',
                                         STATID=>'STATNIGHT')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Laden der Systemstatistiken für den Tag:

SQL> EXEC DBMS_STATS.IMPORT_SYSTEM_STATS(STATTAB=>'TABSYSSTATS',
                                        STATID=>'STATDAY')
PL/SQL-Prozedur erfolgreich abgeschlossen.

Laden der Systemstatistiken für die Nacht:


SQL> EXEC DBMS_STATS.IMPORT_SYSTEM_STATS(STATTAB=>'TABSYSSTATS',
                                       STATID=>'STATNIGHT')
PL/SQL-Prozedur erfolgreich abgeschlossen.

SET SERVEROUTPUT ON
DECLARE
 V_OWNER VARCHAR2(30) :='SYSMAN'; --Hier zu analysierendes Schema angeben
 V_SHOW_REBUILD_INDEXES_ONLY BOOLEAN NOT NULL:=FALSE;
 V_PCT_DEL_ROWS NUMBER(2) NOT NULL:=30;
 V_INDEX_MAX_SIZE_MB NUMBER(5,2) NOT NULL:=1;
 REC_INDEXSTATS INDEX_STATS%ROWTYPE;
 CURSOR C_INDEXES_FOR_REBUILD
 IS
  SELECT DISTINCT
   DI.OWNER,
   DI.INDEX_NAME
  FROM
   DBA_INDEXES DI
    INNER JOIN DBA_SEGMENTS DS
     ON DI.OWNER=DS.OWNER AND DI.INDEX_NAME=DS.SEGMENT_NAME
  WHERE
   DI.OWNER =V_OWNER AND
   DI.INDEX_TYPE LIKE '%NORMAL%' AND
   ((DS.BYTES/1024)/1024)>=V_INDEX_MAX_SIZE_MB;
 
BEGIN
 
 --Alle Objekte ausgeben, die zu analysieren sind
 DBMS_OUTPUT.PUT_LINE('REBUILD INDEXES:');
 DBMS_OUTPUT.PUT_LINE('=======================');
 FOR REC_INDEXES_FOR_REBUILD IN C_INDEXES_FOR_REBUILD
 LOOP
  DECLARE
   EX_LOCKED_INDEX EXCEPTION;
   PRAGMA EXCEPTION_INIT(EX_LOCKED_INDEX, -04022);
  BEGIN
   EXECUTE IMMEDIATE 'ANALYZE INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.'  ||
              REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' VALIDATE STRUCTURE';
   SELECT
    *
   INTO
    REC_INDEXSTATS
   FROM
    INDEX_STATS;
   IF (REC_INDEXSTATS.DEL_LF_ROWS/REC_INDEXSTATS.LF_ROWS)*100 >=V_PCT_DEL_ROWS
   THEN
     IF V_SHOW_REBUILD_INDEXES_ONLY=FALSE
     THEN
      EXECUTE IMMEDIATE 'ALTER INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.'  ||
                REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' REBUILD ONLINE';
     END IF;
     DBMS_OUTPUT.PUT_LINE(ROUND((REC_INDEXSTATS.DEL_LF_ROWS/REC_INDEXSTATS.LF_ROWS)*100,2) || ' ' ||
           REC_INDEXES_FOR_REBUILD.OWNER || '.' ||
           REC_INDEXES_FOR_REBUILD.INDEX_NAME);
   END IF;
  EXCEPTION
   WHEN EX_LOCKED_INDEX
   THEN
    DBMS_OUTPUT.PUT_LINE('INDEX ' || REC_INDEXES_FOR_REBUILD.OWNER || '.' ||
             REC_INDEXES_FOR_REBUILD.INDEX_NAME || ' COULD NOT BE RBUILD. DDL TIMOEOUT');
  END;
 END LOOP;
END;
/

Nach Auftreten des im vorherigen Artikel beschriebenen Problems mit Histogrammen, habe ich versucht dieses Problem mit einem eigenen Skript zu reproduzieren. Hierfür erstellte ich folgende Tabelle:

SQL> CREATE TABLE TESTTAB1 (S1 NUMBER,S2 VARCHAR2(100),
2  S3 VARCHAR2(100), S4 VARCHAR2(1000)) TABLESPACE USERS;

Tabelle wurde erstellt.

In diese Tabelle fügte ich dann mit 100000 Datensätze ein, wobei die Spalte s4 mit 1000 Zeichen befüllt wurde, die sich aber nur in den letzten 6 Zeichen unterschieden.

SQL> BEGIN
2  FOR I IN 1..1000000
3  LOOP
4  INSERT INTO TESTTAB1 VALUES(I, 'DAS IST  EIN LANGER WERT ' || I, 'DAS IST EIN LANGER WERT ' || I,
5  LPAD(TO_CHAR(I),1000,'X'));
6  END LOOP;
7  COMMIT;
8  END;
9  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

Im nächsten Schritt wurde ein Index auf die Spalte s4 erstellt und die Statistiken gesammelt.

SQL> CREATE INDEX IDX_TESTTAB1 ON TESTTAB1(S4) TABLESPACE USERS;

Index wurde erstellt.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTTAB1',CASCADE=>TRUE)

PL/SQL-Prozedur erfolgreich abgeschlossen.

Nach Erstellung von Index und Statistiken wurde dann ein Wert aus der Tabelle selektiert. Interessanterweise verwendete Oracle nun den Index.

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM TESTTAB1 WHERE S4=LPAD('111',1000,'X');

Ausfuhrungsplan
----------------------------------------------------------
PLAN HASH VALUE: 1964488939

--------------------------------------------------------------------------------------------
| ID  | OPERATION                   | NAME         | ROWS  | BYTES | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1    |  1070 |  8   (0)   | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTTAB1     |  1    |  1070 |  8   (0)   | 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TESTTAB1 |  1    |       |  7   (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------

2 - ACCESS("S4"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXX111')

STATISTIKEN
----------------------------------------------------------
   0  RECURSIVE CALLS
   0  DB BLOCK GETS
   9  CONSISTENT GETS
   0  PHYSICAL READS
   0  REDO SIZE
1791  BYTES SENT VIA SQL*NET TO CLIENT
 523  BYTES RECEIVED VIA SQL*NET FROM CLIENT
   2  SQL*NET ROUNDTRIPS TO/FROM CLIENT
   0  SORTS (MEMORY)
   0  SORTS (DISK)
   1  ROWS PROCESSED

Als nächstes leerte ich den Shared Pool und erzeugte die Statistiken auf gleicher Art und Weise neu.
Bei erneuter Ausführung der SQL-Anweisung wurde nun der Index ignoriert.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System wurde geandert.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTTAB1',CASCADE=>TRUE)

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT * FROM TESTTAB1 WHERE S4=LPAD('111',1000,'X');

ausfuhrungsplan
----------------------------------------------------------
PLAN HASH VALUE: 3404082979

------------------------------------------------------------------------------
| ID  | OPERATION         | NAME     |  ROWS| BYTES| COST (%CPU)| TIME       |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  999K| 1020M| 46591   (1)| 00:09:20   |
|*  1 |  TABLE ACCESS FULL| TESTTAB1 |  999K| 1020M| 46591   (1)| 00:09:20   |
------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------

1 - FILTER("S4"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXX111')

STATISTIKEN
----------------------------------------------------------
     0  RECURSIVE CALLS
     1  DB BLOCK GETS
167560  CONSISTENT GETS
166904  PHYSICAL READS
     0  REDO SIZE
  1784  BYTES SENT VIA SQL*NET TO CLIENT
   523  BYTES RECEIVED VIA SQL*NET FROM CLIENT
     2  SQL*NET ROUNDTRIPS TO/FROM CLIENT
     0  SORTS (MEMORY)
     0  SORTS (DISK)
     1  ROWS PROCESSED

Beim Zwingen zur Verwendung des Indexes konnte man nun sehen, dass die Kosten extrem anstiegen, die Anzahl der gelesenen Blöcke aber drastisch sank.

SQL> SELECT /*+ INDEX(TESTTAB1 IDX_TESTTAB1) */ * FROM TESTTAB1 WHERE S4=LPAD('111',1000,'X');

Ausfuhrungsplan
----------------------------------------------------------
PLAN HASH VALUE: 1964488939

--------------------------------------------------------------------------------------------
| ID  | OPERATION                   | NAME        | ROWS  | BYTES | COST (%CPU)| TIME      |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   999K|  1020M|   427K  (1)| 01:25:33  |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTTAB1    |   999K|  1020M|   427K  (1)| 01:25:33  |
|*  2 |   INDEX RANGE SCAN          | IDX_TESTTAB1|   999K|       |   142K  (1)| 00:28:35  |
--------------------------------------------------------------------------------------------

PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------

2 - ACCESS("S4"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXX111')

STATISTIKEN
----------------------------------------------------------
   1  RECURSIVE CALLS
   0  DB BLOCK GETS
   9  CONSISTENT GETS
   0  PHYSICAL READS
   0  REDO SIZE
1791  BYTES SENT VIA SQL*NET TO CLIENT
 523  BYTES RECEIVED VIA SQL*NET FROM CLIENT
   2  SQL*NET ROUNDTRIPS TO/FROM CLIENT
   0  SORTS (MEMORY)
   0  SORTS (DISK)
   1  ROWS PROCESSED

Bei Überprüfung des Histogramms für die Spalte S4 wurde nun die Ursache klar, denn es wurde nur ein Bucket innerhalb der Wertebereichs für die Spalte S4 erstellt, wodurch Oracle davon ausgehen musste, dass alle Werte der Spalte S4 identisch sind und somit die Verwendung eines Index keinen Sinn macht.

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE
2  FROM DBA_HISTOGRAMS
3  WHERE OWNER=USER AND TABLE_NAME='TESTTAB1' AND
4  COLUMN_NAME='S4';

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         5458            4,5871E+35

1 Zeile ausgewahlt.

Auch beim manuellen Erzeugen von Histogrammen ignoriert Oracle den Index, weil diese nicht richtig berechnet wurden.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTTAB1',CASCADE=>TRUE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS S4 SIZE 200')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE
2  FROM DBA_HISTOGRAMS
3  WHERE OWNER=USER AND TABLE_NAME='TESTTAB1' AND
4  COLUMN_NAME='S4';

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         5458            4,5871E+35

1 Zeile ausgewahlt.

Im nächsten Schritt löschte ich die Statistiken, um das Dynamic Sampling zu verwenden und siehe da, der Index wird verwendet.

SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(USER,'TESTTAB1')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT * FROM TESTTAB1 WHERE S4=LPAD('111',1000,'X');

Ausfuhrungsplan
----------------------------------------------------------
Plan hash value: 1964488939

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1    |   619 |  3      (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTTAB1     |  1    |   619 |  3      (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TESTTAB1 |  1    |       |  1      (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 

2 - access("S4"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXX111')

 Note
-----
- dynamic sampling used for this statement (level=2)

 Statistiken
----------------------------------------------------------
     0  recursive calls
     1  db block gets
167432  consistent gets
166788  physical reads
     0  redo size
  1783  bytes sent via SQL*Net to client
   523  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

Wurden die Werte der Spalte s4 so modifiziert, dass die führenden X-Zeichen entfernt wurden, so wurden die Histogramme ordnungsgemäß berechnet und der Optimizer die Verwendung des Index vorschlug.

SQL> UPDATE TESTTAB1 SET S4=REPLACE(S4,'X','');

1000000 Zeilen wurden aktualisiert.

SQL> COMMIT;

Transaktion mit Commit abgeschlossen.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTTAB1',CASCADE=>TRUE, METHOD_OPT=>'FOR COLUMNS S4 SIZE 250')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT COLUMN_NAME,ENDPOINT_NUMBER, ENDPOINT_VALUE
2  FROM DBA_HISTOGRAMS
3  WHERE OWNER=USER AND TABLE_NAME='TESTTAB1' AND
4  COLUMN_NAME='S4';

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         236     2,9704E+35
S4         237     2,9704E+35
S4         238     2,9706E+35
S4         239     2,9706E+35
S4         240     2,9706E+35
S4         241     2,9708E+35
S4         242     2,9708E+35
S4         243     2,9708E+35
S4         244     2,9710E+35
S4         245     2,9710E+35
S4         246     2,9710E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         247     2,9710E+35
S4         248     2,9712E+35
S4         249     2,9712E+35
S4         250     2,9712E+35
S4    0     2,5540E+35
S4    1     2,5540E+35
S4    2     2,5540E+35
S4    3     2,5542E+35
S4    4     2,5542E+35
S4    5     2,5542E+35
S4    6     2,5544E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4    7     2,5544E+35
S4    8     2,5546E+35
S4    9     2,5546E+35
S4   10     2,5546E+35
S4   11     2,5548E+35
S4   12     2,5548E+35
S4   13     2,5548E+35
S4   14     2,5550E+35
S4   15     2,5550E+35
S4   16     2,5552E+35
S4   17     2,5552E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   18     2,5552E+35
S4   19     2,5554E+35
S4   20     2,5554E+35
S4   21     2,5556E+35
S4   22     2,5556E+35
S4   23     2,5556E+35
S4   24     2,5558E+35
S4   25     2,5558E+35
S4   26     2,5558E+35
S4   27     2,5558E+35
S4   28     2,6059E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   29     2,6059E+35
S4   30     2,6061E+35
S4   31     2,6061E+35
S4   32     2,6061E+35
S4   33     2,6063E+35
S4   34     2,6063E+35
S4   35     2,6063E+35
S4   36     2,6065E+35
S4   37     2,6065E+35
S4   38     2,6067E+35
S4   39     2,6067E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   40     2,6069E+35
S4   41     2,6069E+35
S4   42     2,6069E+35
S4   43     2,6071E+35
S4   44     2,6071E+35
S4   45     2,6071E+35
S4   46     2,6073E+35
S4   47     2,6073E+35
S4   48     2,6073E+35
S4   49     2,6075E+35
S4   50     2,6075E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   51     2,6076E+35
S4   52     2,6077E+35
S4   53     2,6078E+35
S4   54     2,6578E+35
S4   55     2,6578E+35
S4   56     2,6578E+35
S4   57     2,6579E+35
S4   58     2,6581E+35
S4   59     2,6581E+35
S4   60     2,6583E+35
S4   61     2,6583E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   62     2,6585E+35
S4   63     2,6585E+35
S4   64     2,6585E+35
S4   65     2,6585E+35
S4   66     2,6587E+35
S4   67     2,6587E+35
S4   68     2,6587E+35
S4   69     2,6589E+35
S4   70     2,6589E+35
S4   71     2,6591E+35
S4   72     2,6591E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   73     2,6593E+35
S4   74     2,6593E+35
S4   75     2,6593E+35
S4   76     2,6595E+35
S4   77     2,6595E+35
S4   78     2,6597E+35
S4   79     2,6597E+35
S4   80     2,6597E+35
S4   81     2,7098E+35
S4   82     2,7098E+35
S4   83     2,7100E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   84     2,7100E+35
S4   85     2,7100E+35
S4   86     2,7102E+35
S4   87     2,7102E+35
S4   88     2,7102E+35
S4   89     2,7104E+35
S4   90     2,7104E+35
S4   91     2,7104E+35
S4   92     2,7106E+35
S4   93     2,7106E+35
S4   94     2,7108E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4   95     2,7108E+35
S4   96     2,7110E+35
S4   97     2,7110E+35
S4   98     2,7110E+35
S4   99     2,7112E+35
S4         100     2,7112E+35
S4         101     2,7114E+35
S4         102     2,7114E+35
S4         103     2,7114E+35
S4         104     2,7116E+35
S4         105     2,7116E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         106     2,7116E+35
S4         107     2,7617E+35
S4         108     2,7617E+35
S4         109     2,7617E+35
S4         110     2,7619E+35
S4         111     2,7619E+35
S4         112     2,7619E+35
S4         113     2,7621E+35
S4         114     2,7621E+35
S4         115     2,7623E+35
S4         116     2,7623E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         117     2,7623E+35
S4         118     2,7625E+35
S4         119     2,7625E+35
S4         120     2,7625E+35
S4         121     2,7627E+35
S4         122     2,7627E+35
S4         123     2,7627E+35
S4         124     2,7629E+35
S4         125     2,7629E+35
S4         126     2,7629E+35
S4         127     2,7631E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         128     2,7631E+35
S4         129     2,7631E+35
S4         130     2,7633E+35
S4         131     2,7633E+35
S4         132     2,7635E+35
S4         133     2,7635E+35
S4         134     2,7635E+35
S4         135     2,8136E+35
S4         136     2,8136E+35
S4         137     2,8136E+35
S4         138     2,8138E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         139     2,8138E+35
S4         140     2,8138E+35
S4         141     2,8140E+35
S4         142     2,8140E+35
S4         143     2,8142E+35
S4         144     2,8142E+35
S4         145     2,8142E+35
S4         146     2,8142E+35
S4         147     2,8144E+35
S4         148     2,8144E+35
S4         149     2,8144E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         150     2,8146E+35
S4         151     2,8146E+35
S4         152     2,8146E+35
S4         153     2,8148E+35
S4         154     2,8148E+35
S4         155     2,8148E+35
S4         156     2,8150E+35
S4         157     2,8150E+35
S4         158     2,8150E+35
S4         159     2,8152E+35
S4         160     2,8152E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         161     2,8154E+35
S4         162     2,8154E+35
S4         163     2,8154E+35
S4         164     2,8655E+35
S4         165     2,8655E+35
S4         166     2,8655E+35
S4         167     2,8655E+35
S4         168     2,8657E+35
S4         169     2,8657E+35
S4         170     2,8657E+35
S4         171     2,8659E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         172     2,8659E+35
S4         173     2,8659E+35
S4         174     2,8661E+35
S4         175     2,8661E+35
S4         176     2,8662E+35
S4         177     2,8664E+35
S4         178     2,8664E+35
S4         179     2,8666E+35
S4         180     2,8666E+35
S4         181     2,8666E+35
S4         182     2,8668E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         183     2,8668E+35
S4         184     2,8668E+35
S4         185     2,8670E+35
S4         186     2,8670E+35
S4         187     2,8670E+35
S4         188     2,8672E+35
S4         189     2,8672E+35
S4         190     2,8672E+35
S4         191     2,8674E+35
S4         192     2,8674E+35
S4         193     2,8674E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         194     2,9175E+35
S4         195     2,9175E+35
S4         196     2,9177E+35
S4         197     2,9177E+35
S4         198     2,9177E+35
S4         199     2,9179E+35
S4         200     2,9179E+35
S4         201     2,9179E+35
S4         202     2,9181E+35
S4         203     2,9181E+35
S4         204     2,9183E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         205     2,9183E+35
S4         206     2,9183E+35
S4         207     2,9185E+35
S4         208     2,9185E+35
S4         209     2,9185E+35
S4         210     2,9187E+35
S4         211     2,9187E+35
S4         212     2,9187E+35
S4         213     2,9189E+35
S4         214     2,9189E+35
S4         215     2,9189E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         216     2,9191E+35
S4         217     2,9191E+35
S4         218     2,9191E+35
S4         219     2,9193E+35
S4         220     2,9193E+35
S4         221     2,9193E+35
S4         222     2,9694E+35
S4         223     2,9694E+35
S4         224     2,9696E+35
S4         225     2,9696E+35
S4         226     2,9696E+35

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
S4         227     2,9698E+35
S4         228     2,9698E+35
S4         229     2,9698E+35
S4         230     2,9700E+35
S4         231     2,9700E+35
S4         232     2,9700E+35
S4         233     2,9702E+35
S4         234     2,9702E+35
S4         235     2,9702E+35

251 ZEILEN AUSGEWAHLT.

 

Bei Performanceproblemen eines Upgrade-Skripts einer Datenbankanwendung stieß ich auf folgende interessante Problematik von Optimizer-Statistiken.


Ein Upgrade-Skript sollte Datensätze innerhalt einer Tabelle mit mehr als 4 Millionen Zeilen konvertieren. Hierfür wurde auf eine Tabelle TAB1 eine SELECT-Anweisung ausgeführt, die Zeilen über eine Spalte S4 filtern sollte. Die Werte der Spalte S4 hatten allerdings die Eigenart, dass bei allen 4 Millionen Zeilen die ersten 45 Zeichen der Werte dieser Spalte identisch waren. Erst ab diesen Zeichen unterscheiden sie sich. Da diese Anweisung mehrere tausendmal auf die Tabelle ausgeführt wurde, aber in Abhängigkeit des Suchwertes der Spalte S4 nur 10 bis 20 Zeilen zurückkommen sollten, wurde ein Index auf diese Spalte erstellt und Optimizer-Statistiken gesammelt.

Interessanterweise ignorierte der Optimizer diesen Index und führte einen Full-Table-Scan aus, was das Upgrade-Skript sehr stark verlangsamte.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

Ausführungsplan
----------------------------------------------------------
Plan hash value: 915916058

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  4374K|   471M|       |   130K  (3)| 00:01:48 |
|   1 |  SORT ORDER BY     |       |  4374K|   471M|   560M|   130K  (3)| 00:01:48 |
|*  2 |   TABLE ACCESS FULL| TAB1  |  4374K|   471M|       | 24299   (5)| 00:00:21 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234'
AND "S5" = '0')

Statistiken
----------------------------------------------------------
    0  recursive calls
    1  db block gets
80499  consistent gets
    0  physical reads
    0  redo size
  487  bytes sent via SQL*Net to client
  509  bytes received via SQL*Net from client
    1  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
   15  rows processed

Auch das erneute Sammeln der Optimizer-Statistiken brachte kein Erfolg.

SQL> exec dbms_stats.gather_table_stats('MEINUSER','TAB1',cascade=>true)

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 915916058

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  4416K|   475M|       |   131K  (3)| 00:01:49 |
|   1 |  SORT ORDER BY     |       |  4416K|   475M|   565M|   131K  (3)| 00:01:49 |
|*  2 |   TABLE ACCESS FULL| TAB1  |  4416K|   475M|       | 24304   (5)| 00:00:21 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234'
AND "S5" = '0')

Statistiken
----------------------------------------------------------
    0  recursive calls
    1  db block gets
80499  consistent gets
    0 physical reads
    0  redo size
 1134  bytes sent via SQL*Net to client
  520  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
   15  rows processed

Aus diesem Grund entschloss ich mich dediziert ein Histogramm für die Spalte S4 zu erstellen, welches aber ebenfalls kein Erfolg brachte (Ich experimentierte mit den Histogrammen und erstellte sie auf unterschiedlicher Art und Weise).

SQL> BEGIN
2  DBMS_STATS.GATHER_TABLE_STATS(
3  OWNNAME=> 'MEINUSER',
4  TABNAME=> 'TAB1' ,
5  ESTIMATE_PERCENT=> NULL,
6  CASCADE=> DBMS_STATS.AUTO_CASCADE,
7  DEGREE=> NULL,
8  NO_INVALIDATE=> DBMS_STATS.AUTO_INVALIDATE,
9  GRANULARITY=> 'AUTO',
10  METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS S4 SIZE 120');
11  END;
12  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 915916058

------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  4374K|   471M|       |   130K  (3)| 00:01:48 |
|   1 |  SORT ORDER BY     |       |  4374K|   471M|   560M|   130K  (3)| 00:01:48 |
|*  2 |   TABLE ACCESS FULL| TAB1  |  4374K|   471M|       | 24299   (5)| 00:00:21 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234'
AND "S5" = '0')

Statistiken
----------------------------------------------------------
    0  recursive calls
    1  db block gets
80499  consistent gets
    0  physical reads
    0  redo size
  487  bytes sent via SQL*Net to client
  509  bytes received via SQL*Net from client
    1  SQL*Net roundtrips to/from client
    1  sorts (memory)
    0  sorts (disk)
   15  rows processed

Im nächsten Schritt entschloss ich mich den Optimizer zur Verwendung des Index zu zwingen und erhielt ein interessantes Ergebnis:
Die Kosten für die Verwendung des Index schienen noch einmal zusätzlich angestiegen zu sein. Aber die Ausführungsstatistiken zeigten, dass anstatt 80499 nur noch 6 Blöcke gelesen wurden. Zudem wurde diese Anweisung mit dem Hint für die Verwendung des Index um ein Vielfaches schneller ausgeführt. Also gab es in der Tat ein Problem mit den Statistiken. Meine Vermutung war, dass Oracle beim Sammeln der Statistiken die Histogramme aufgrund der ersten 45 gleichen Zeichen für jeden Datensatz der Spalte S4 nicht richtig erzeugte.

SQL> SELECT  /*+ INDEX(TAB1 IDX_S4) */ s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2461020050

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  4374K|   471M|       |   631K  (1)|00:08:42 |
|   1 |  SORT ORDER BY               |          |  4374K|   471M|   560M|   631K  (1)|00:08:42 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TAB1     |  4374K|   471M|       |   525K  (1)|00:07:15 |
|*  3 |    INDEX RANGE SCAN          | IDX_S4   |  4516K|       |       |  44711  (1)|00:00:37 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S5" ='0')
3 - access("S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234')

Statistiken
----------------------------------------------------------
  0  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
853  bytes sent via SQL*Net to client
520  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
 15  rows processed

Aus diesem Grund entschied ich mich nun die Statistiken der Tabelle TAB1 zu löschen, um auf das Dynamic Sampling der Statistiken zu schwenken. Nach erneuter Ausführung der Anweisung verwendete Oracle nun auf einmal den Index. Ein Vergleich der Kosten zeigte, dass der Optimizer sie anhand der Statistiken nicht richtig berechnete, denn sie sanken nun von 631000 auf 3!!.

SQL> exec dbms_stats.delete_table_stats('MEINUSER','TAB1')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT  s1, s2, s3
2 FROM MEINUSER.TAB1
3 WHERE s4 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234' AND
4 s5 = '0'
5 ORDER BY s1, s2;

15 Zeilen ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3167567956

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   542 |   135K|     (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |          |   542 |   135K|     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1     |   542 |   135K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_S4   |    87 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("S5"='0' AND "S4" = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1234')

Note
-----
- dynamic sampling used for this statement (level=2)

Statistiken
----------------------------------------------------------
  1  recursive calls
  0  db block gets
  6  consistent gets
  0  physical reads
  0  redo size
853  bytes sent via SQL*Net to client
520  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
 15  rows processed

Ab Oracle 11gR2 wurde Automatic Degree of Parallelism (ADOP) eingeführt, um Oracle die Entscheidung des Parallelitätsgrad für die Ausführung von SQL-Anweisungen bestimmen zu lassen. In vorherigen Versionen von Oracle musste der Parallelitätsgrad über das Hint PARALLEL in der SQL-Anweisung, oder über den Parallelitätsgrad der Tabelle festgelegt werden.

 SQL> select count(*) from testtab2;

COUNT(*)
----------
100000

1 Zeile wurde ausgewählt.

SQL> set autotrace traceonly
SQL> select count(*) from testtab2;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 103376238

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   206   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TESTTAB2 |   100K|   206   (1)| 00:00:03 |
-----------------------------------------------------------------------

Statistiken
----------------------------------------------------------
0    recursive calls
0    db block gets
678  consistent gets
670  physical reads
0    redo size
429  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2    SQL*Net roundtrips to/from client
0    sorts (memory)
0    sorts (disk)
1    rows processed

SQL> select /*+parallel 2 */ count(*) from testtab2;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2843183767

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   114   (1)| 00:00:02 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   100K|   114   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| TESTTAB2 |   100K|   114   (1)| 00:00:02 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2

Statistiken
----------------------------------------------------------
12   recursive calls
4    db block gets
845  consistent gets
670  physical reads
0    redo size
429  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2    SQL*Net roundtrips to/from client
0    sorts (memory)
0    sorts (disk)
1    rows processed

SQL> set autotrace off

In Oracle 11GR2 wurden neue Parameter eingeführt, die die automatische Parallelisierung aktivieren.

 SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     20
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     8
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

Hierbei sind die Parameter prallel_degree_policy und parallel_min_time_treshold von besonderer Bedeutung. Der Parameter parallel_degree_policy aktiviert ADOP, wenn er auf AUTO gesetzt wird. Der Parameter parallel_min_time_threshold bestimmt den zeitlichen Schwellwert, den ein serieller Plan in seiner Abarbeitung überschreiten muss, ehe der Optimizer versucht einen adäquaten parallelen Plan zu erstellen. Der Standard dieses Parameters liegt bei 30 Sekunden. Dieses bedeutet, überschreitet die geschätzte Laufzeit des seriellen Plans diesen Schwellwert, entwickelt der Optimizer einen alternativen parallelen Plan und vergleicht die Laufzeit dieses Plans mit dem seriellen. Ist der parallele Plan schneller in seiner Abarbeitung, als der serielle, so wird der parallele Plan verwendet, ansonsten der serielle.

SQL> alter session set parallel_degree_policy='AUTO';

Session wurde geändert.

SQL> select count(*) from testtab2;

COUNT(*)
----------
100000

1 Zeile wurde ausgewählt.

SQL> set autotrace traceonly
SQL> select count(*) from testtab2;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 103376238

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   206   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TESTTAB2 |   100K|   206   (1)| 00:00:03 |
-----------------------------------------------------------------------

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistiken
----------------------------------------------------------
0    recursive calls
0    db block gets
678  consistent gets
0    physical reads
0    redo size
429  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2    SQL*Net roundtrips to/from client
0    sorts (memory)
0    sorts (disk)
1    rows processed

In der vorhergehenden Anweisung entschied sich der Optimizer zur Verwendung eines seriellen Ausführungsplans, da der zeitliche Schwellwert nicht überschritten wurde.

Im nächsten Beispiel wird der Parameter  parallel_min_time_threshold drastisch zu Demonstrartionszwecken verkleinert, wodurch die Entscheidung auf einen parallen Plan fällt. 

 SQL> alter session set parallel_min_time_threshold=1;

Session wurde geändert.

SQL> select count(*) from testtab2;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2843183767

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   114   (1)| 00:00:02 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   100K|   114   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| TESTTAB2 |   100K|   114   (1)| 00:00:02 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2

Statistiken
----------------------------------------------------------
53   recursive calls
8    db block gets
905  consistent gets
0    physical reads
0    redo size
429  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2    SQL*Net roundtrips to/from client
0    sorts (memory)
0    sorts (disk)
1    rows processed

SQL>

 

 

 

 

CONN / AS SYSDBA
SET LINESIZE 300
SET ECHO ON


ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

CREATE TABLE TESTDOP (S1 NUMBER(8), S2 VARCHAR2(30), S3 VARCHAR2(30)) TABLESPACE USERS;
PAUSE
BEGIN
FOR I IN 1..1000000
LOOP
    INSERT INTO TESTDOP VALUES(I,'DAS IST WERT '||I, 'HIER AUCH NOCHMAL '||I);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TESTDOP')
PAUSE

CREATE TABLE TESTDOP2 (S1 NUMBER(8), S2 VARCHAR2(30), S3 VARCHAR2(30)) TABLESPACE USERS;
PAUSE
BEGIN
FOR I IN 1..10000
LOOP
        INSERT INTO TESTDOP VALUES(I,'DAS IST WERT '||I, 'HIER AUCH NOCHMAL '||I);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TESTDOP')
PAUSE
ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
PAUSE
SET AUTOTRACE TRACEONLY EXPLAIN
PAUSE
SELECT COUNT(*) FROM TESTDOP;
PAUSE
SELECT COUNT(*) FROM TESTDOP2;
PAUSE
ALTER SESSION SET PARALLEL_DEGREE_POLICY=MANUAL;
PAUSE
SELECT COUNT(*) FROM TESTDOP;
PAUSE
SET AUTOTRACE OFF
DROP TABLE TESTDOP PURGE;
DROP TABLE TESTDOP2 PURGE;