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

Ähnlich wie Charles Brentana erklärte, sollte das Hinzufügen eines Indexes die Leistung einer Abfrage nicht beeinträchtigen.

Dies wäre der Fall, wenn alle Statistiken auf dem neuesten Stand sind, so dass der kostenbasierte Optimierer einen guten Ausführungsplan auswählt. Bitte überprüfen Sie die Statistiken sowohl für die Tabellen als auch für die Indizes noch einmal.

Wenn dort alles in Ordnung ist, kann ich nur vermuten, dass es einen anderen Faktor gibt, der die Leistung der Abfrage beeinträchtigt. Ist der DB-Server anderweitig belastet (lange laufende Batch-Jobs, Backups usw.), so dass die Abfrage länger läuft? Gibt es größere Aktualisierungsaktivitäten in einer der Tabellen, die Sie in der Abfrage verwenden? Waren die Daten beim ersten Mal im DB-Cache, beim zweiten Mal aber nicht mehr? Ich habe keine Ahnung, wie Sie die beiden Anweisungen nebeneinander testen können, aber es muss einen Grund für dieses seltsame Verhalten geben ...

Answer 2

Sie müssen sich darüber im Klaren sein, dass der kostenbasierte Optimierer zwar recht ausgeklügelt und meistens korrekt ist, aber manchmal auch falsch liegen kann.

Es gibt reichlich Oracle-spezifische Literatur (siehe z. B. den Blog von Tom Kyte), die eindeutig beweist, dass das Hinzufügen eines Index die Leistung von SELECT-Anweisungen tatsächlich verringern kann. Unter anderem kann der Zugriff auf einen Datensatz über einen Index teurer sein als ein vollständiger Scan, wenn die Dichte der Daten hoch genug ist.

Manchmal kann man dem kostenbasierten Optimierer die Datenverteilung bewusst machen, indem man Histogramme für die betroffenen Spalten erzeugt, aber auch das kann manchmal fehlschlagen. Ich würde zuerst versuchen, die Histogramme für die indizierten Spalten zu generieren. Wenn das fehlschlägt, müssen wir uns Ihre Anweisung und Ihren Zugriffsplan erneut ansehen.