Verschlechterte Leistung einer Abfrage nach Hinzufügen eines Index

Question

Ich habe eine Abfrage, die Teil einer SP, die ziemlich regelmäßig ausgeführt wird, und die Abfrage dauerte eine Weile, um auszuführen, so dass ich beschlossen, einen Blick auf sie haben. Ich habe einen Autotrace für die Abfrage durchgeführt und dies war die Ausführung Plan zurückgegeben [ aufgrund der übermäßigen Größe in pastebin eingefügt ]

Ich fügte Indizes zu den Tabellen hinzu, auf die ein vollständiger Tabellenzugriff erfolgte, und führte die Abfrage aus. Die Abfrageleistung war schlechter als vorher, trotz der die Kosten sind deutlich niedriger .

Warum ist das so, kann jemand Licht in diese Angelegenheit bringen?

Die Datenbank ist ein Oracle 10gR2 ( Release 10.2.0.1.0 ).

Die folgende Abfrage wird ausgeführt

SELECT DISTINCT CAC_FLEX_03, CAC_FLEX_04
        FROM PCOM_CUST_PRACTICE_INFO A,
             PGIM_ZIP_CODES          C,
             PGIM_PROD_TARIFF_DATA   B,
             PCOM_CODES_APPL_CODES   D
       WHERE A.PCPI_CUST_CODE IN ('002023', '002025')
         AND C.ZC_ZIP_CODE = A.PCPI_PIN_CODE
         AND C.ZC_CITY_CODE = A.PCPI_CITY
         AND C.ZC_COUNTY_CODE = A.PCPI_COUNTY
         AND C.ZC_STATE_CODE = A.PCPI_STATE
         AND B.PTD_CVR_CODE = 'TF-001'
         AND B.PTD_VALUE_SET2 = A.PCPI_STATE
         AND B.PTD_VALUE_SET4 = A.PCPI_COUNTY
         AND B.PTD_VALUE_SET5 = D.CAC_FLEX_03
         AND D.CAC_FLEX_04 IS NOT NULL
         AND ZC_STATE_CODE =
             (SELECT POL_FLEX_04
                FROM PGIT_POLICY
               WHERE POL_SYS_ID = 541332)
         AND B.PTD_VALUE_SET3 =
             (SELECT POL_FLEX_01
                FROM PGIT_POLICY
               WHERE POL_SYS_ID = 541332)
         AND CAC_TYPE = 'TERR-CODE'
         AND CAC_FLEX_03 = 0;

Answer 1

Ein paar Dinge:

Erstens, wenn Sie auf mehr als die Hälfte der Datenblöcke zugreifen, ist der vollständige Scan schneller, weil das Lesen des Indexblocks ein weiterer IO-Aufruf ist, so dass das Lesen einer indizierten Zeile im Allgemeinen doppelt so viel Zeit in Anspruch nimmt wie das Lesen einer sequenziellen Zeile.

Zweitens müssen Sie sich Ihre Pläne mit und ohne den Index ansehen. Hier finden Sie Informationen darüber, was sich geändert hat. Wenn Sie ein "Merge Join Cartesian" sehen, hat der Planer einen Fehler gemacht. Dieser Plan ist NIEMALS gut. Innere Schleifen von vollständigen Scans haben die gleichen IO-Kosten, benötigen aber weniger Speicher und temporären Platz.

Drittens haben Sie mit ANALYZE TABLE Statistiken erstellt. Tun Sie das nicht. Sogar Oracle sagt, dass das schlecht und kaputt ist. Verwenden Sie das Paket dbms_stats, um Ihre Statistiken zu erstellen, und Sie werden genauere Statistiken erhalten. Wenn es immer noch seltsam ist, ändern Sie die Stichprobengröße oder erstellen Sie vollständige Statistiken anstelle von geschätzten Statistiken.

Answer 2

Ich habe gesehen, dass Abfragen auf diese Weise langsamer werden, wenn die indizierte Tabelle klein war. Der Abfrageplan änderte sich von der Erstellung einer temporären Hash-Tabelle zur Verwendung des (baumbasierten) Index, was langsamer war (aber besser skalieren würde). Kostenbasierte Optimierer werden mit den verfügbaren Statistiken nicht immer fündig, ja sie können es gar nicht, wenn man darüber nachdenkt. Bei einem ausreichend komplexen Abfrageplan ist es nicht möglich, die Leistung perfekt vorherzusagen, ohne die Abfrage durchzuführen.

Answer 3

Ist die Tabelle PGIM_ZIP_CODES wirklich für die Abfrage erforderlich? Es sieht für mich so aus, als ob Sie PGIM_ZIP_CODES aus dem Abfragetext entfernen können, wenn Sie "AND ZC_STATE_CODE =" [Zeile 16 der SQL] in "and A.PCPI_STATE =" ändern.

Zweitens sieht es so aus, als ob der Index, der auf PGIM_PROD_TARIFF_DATA erstellt wurde, seine Aufgabe nicht richtig erfüllen konnte - in meiner begrenzten Erfahrung ist eine Tabelle mit nur 78k Zeilen normalerweise schneller zu scannen, es sei denn, der hinzugefügte Index ist eindeutig oder reduziert den Plan für diese Tabelle auf eine reine Indexsuche (der zweite Plan sieht so aus, als ob 2 Indizes auf der Tabelle erstellt wurden, und sie waren nicht eindeutig).

Drittens, jetzt, wo ich etwas genauer hingesehen habe, sieht es so aus, als ob Ihre Anfrage in :

select distinct CAC_FLEX_03, CAC_FLEX_04

von PCOM_CODES_APPL_CODES

wobei CAC_FLEX_03 = 0

und CAC_TYPE = 'TERR-CODE'

wo existiert (blah blah blah)

• immer unter der Annahme, dass das "where exists" erforderlich ist - weil das Vorhandensein einer Ergebniszeile aus Tabelle A, B und C tatsächlich alle Zeilen aus Tabelle D mit CAC_FLEX_03 = 0 und TYPE = 'TERR-CODE' zurückgibt

PS: Ich habe die Frage nicht missverstanden!

Answer 4

Wir hatten ein ähnliches Problem, und es stellte sich heraus, dass es sich um eine Fragmentierung des Indexes handelte. Lassen Sie Ihren DBA alle Statistiken zu den von Ihnen verwendeten Indizes überprüfen und sehen Sie, ob einer davon neu erstellt werden muss.

Denken Sie daran, dass Sie mit einer Online-Wiederherstellung noch eine Weile weitermachen können, aber irgendwann wird wahrscheinlich eine Offline-Wiederherstellung erforderlich sein.

Answer 5

Keine Abfrage " sollte " durch das Hinzufügen eines neuen Indexes beeinträchtigt werden. Nur Schreibvorgänge (die den Index ändern müssen) sollten durch das Vorhandensein eines Index verlangsamt werden. Auch ein Index mit Fragmentierung sollte schneller sein als gar kein Index. Und wenn Sie den Index gerade erst hinzugefügt haben, ist er brandneu (mehr oder weniger) und sollte nicht in nennenswertem Umfang fragmentiert sein. Da Sie außerdem sagen, dass die Kosten niedriger sind, vermute ich, dass hier etwas anderes vor sich geht, das nichts mit dem Index zu tun hat. Ist es möglich, dass einige andere Transaktionen vorübergehend Lesesperren auf einige der von der Abfrage benötigten Datenzeilen gesetzt haben und die Abfrage für einen längeren Zeitraum blockiert haben?