Solution: Runtimestatistiken lesen

Runtime statistiken sind für mich das wichtigste Optimierungswerkzeug für nicht parallele Abfragen.
Runtime statistiken lassen sich schnell erzeugen und beinhalten in der Regel schon alle nötigen Informationen für eine Optimierung.
Ich wiederhole zunächst die Runtimestatistiken der Rätselfrage:

 

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:15.42 |    3540 |      3262 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:15.42 |    3540 |      3262 |
|*  2 |   TABLE ACCESS BY INDEX ROWID|   T1 |      1 |  52695 |     10 |00:00:15.42 |    3540 |      3262 |
|*  3 |    INDEX RANGE SCAN          |   I1 |      1 |     17M|   4577 |00:00:00.16 |      11 |         9 |
-----------------------------------------------------------------------------------------------------------

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

   2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))
   3 - access("T1_ID"=:B2 AND "T2_ID"=:B4 AND "T3_ID"=:B1)

Bei dem Problem fällt zunächst einmal auf, dass die Schätzung des Optimizers beim Indexzugriff in Operation 3 weit daneben liegt. Hierzu werden die „estimated rows“  (E-rows) mit den „actual rows“ (A-Rows) verglichen. Da es sich um eine sehr einfach zusammengesetzte Bedingung mit lauter „=“ Vergleichen handelt liegt es nahe, hier eine funktionale Abhängigkeit zu vermuten.

Die entsprechende Gegenmassnahme wären extended statistics.
Jedoch wird die meiste Zeit in der Operation 2 verloren. Hier fallen die „actual rows“ von 4577 Datensätzen in Operation 3 auf 10 Datensäte ab. Verantwortlich dafür ist die Filterbedingung in Operation 2:

2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))

Gemäss der Tuning Technik „Throw-away“ erfunden vom Oak Table Mitglied Martin Berg müssen wir zusätzliche Spalten in den Index hinzuzufügen um den „throw-away“ in operation 2 zu vermeiden.

Ich habe sehr kluge Empfehlungen in meinem Kommentaren gefunden. Allerdings muss man in diesem Falle nicht sehr kompliziert vorgehen. Wir haben die Status Felder getestet und herausgefunden welche die Selektivität des Indexes erhöhen. Diese haben wir einfach in den Index eingefügt. Fälschlicherweise war der Entwickler der Applikation der Meinung die Statusfelder würden Nichts nützen, weil bei einem ungleich Vergleich kein Index verwendet werden kann.

Das ist nicht korrekt. Die Baumstruktur kann nicht verwendet werden. Jedoch wird bei einem Index Range Scan über die Baumstruktur nur der erste Leaf Block gesucht. Ab dann wird serial in den Leaf Blöcken weiter gesucht.

Bei dieser seriellen Suche kann über beliebige Vergleichsoperatoren gefiltert werden. Wie wir anhand der unten stehenden Runtime Statistiken sehen, reicht das schon um eine gute Performance zu gewährleisten. Der Verbesserungsfaktor liegt bei run 700.

Nicht einmal die Verbesserung der Statistiken ist für diesen Fall letztlich erforderlich, da der Optimizer zwar falsch schätzt, aber die zusätzlichen Spalten dennoch als Vorteil wahrnimmt.

Natürlich wäre die extened statistics unabhängig davon trotzdem sinnvoll.

 

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |      1 |00:00:00.02 |   12 |         4 |
|   1 |  SORT AGGREGATE              |                     |      1 |      1 |      1 |00:00:00.02 |   12 |         4 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MM_SALES_DELIVERIES |      1 |  52695 |     10 |00:00:00.02 |   12 |         4 |
|*  3 |    INDEX RANGE SCAN          | PR_SALE_DEL_03      |      1 |  52695 |     10 |00:00:00.01 |    6 |         0 |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - filter((NVL("MM_DISTRIBUTION_DEL_STATUS",'H')<>'D' AND NVL("MM_OUTBOUND_DEL_STATUS",'H')<>'D' AND
              NVL("MM_OUTBOUND_STATUS",'H')<>'U'))
   3 - access("MM_WAREHOUSE_ID"=:B4 AND "MM_FIRM_ID"=:B1 AND "MM_ITEM_ID"=:B2 AND "MM_FROM_LOCATION_ID"=:B3)
       filter((NVL("MM_DISTRIBUTION_STATUS",'H')<>'U' AND NVL("MM_DIRECT_DEL_STATUS",'H')<>'D'))

Advertisements

Kommentar verfassen

Bitte logge dich mit einer dieser Methoden ein, um deinen Kommentar zu veröffentlichen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s