Das Original-SQL-Statement ist relativ einfach. Es soll ein Equi-Join zwischen zwei Tabellen vorgenommen werden. Die eine Tabelle (T087_ITEMSALE_HOURS) hat ca. 410 Millionen Zeilen und ist partitioniert, die andere Tabelle (T012_WH) hat ca. 250 Zeilen. Auf beiden Tabellen sind mehrere Indizes.
Die Ausgaben habe ich zur besseren Lesbarkeit aufs Wesentliche reduziert.
- „Was ist an diesem Statement falsch?“
SELECT DISTINCT
C012_COUNTRYID AS CMV087_COUNTRYID,
C087_WHID AS CMV087_WHID,
TO_CHAR(C087_RECEIPTDATE,'IYYY') AS CMV087_CAL_YEAR,
TO_CHAR(C087_RECEIPTDATE,'IW') AS CMV087_CAL_WEEK,
TO_CHAR(C087_RECEIPTDATE,'DDD') AS CMV087_CAL_DAY,
1 + TRUNC (C087_RECEIPTDATE) - TRUNC (C087_RECEIPTDATE,'IW') AS CMV087_CAL_WEEK_DAY,
C087_RECEIPTDATE AS CMV087_RECEIPTDATE
FROM T087_ITEMSALE_HOURS
INNER JOIN T012_WH ON C012_WHID = C087_WHID;
104440 Zeilen ausgewählt.
Abgelaufen: 00:12:06.91
siehe rechts Ausführungsplan 1 (Bild durch Anklicken vergrößerbar)
2. „Nichts
3. „Es läuft zu lange“
Natürlich konnte ich mit dieser Antwort nicht zufrieden sein. Also habe ich eine Tuningmaßnahme vorgenommen. Ich habe einen /*+parallel */ Hint eingefügt.
SELECT /*+parallel */ DISTINCT
C012_COUNTRYID AS CMV087_COUNTRYID,
C087_WHID AS CMV087_WHID,
TO_CHAR(C087_RECEIPTDATE,'IYYY') AS CMV087_CAL_YEAR,
TO_CHAR(C087_RECEIPTDATE,'IW') AS CMV087_CAL_WEEK,
TO_CHAR(C087_RECEIPTDATE,'DDD') AS CMV087_CAL_DAY,
1 + TRUNC (C087_RECEIPTDATE) - TRUNC (C087_RECEIPTDATE,'IW') AS CMV087_CAL_WEEK_DAY,
C087_RECEIPTDATE AS CMV087_RECEIPTDATE
FROM T087_ITEMSALE_HOURS
INNER JOIN T012_WH ON C012_WHID = C087_WHID;
104440 Zeilen ausgewählt.
Abgelaufen: 00:06:06.91
siehe rechts Ausführungsplan 2 (Bild durch Anklicken vergrößerbar)
Der Parallel Hint hat die Antwortzeit um 50 % reduziert. Das ist zwar schön, aber nicht sehr zufriedenstellend.
Das Statement muss darauf hingehend genauer analysiert werden.
Durch die beiden oberen Statements werden 410 Millionen Zeilen mit dem Equi-Join erzeugt. Danach werden diese Zeilen sortiert und die doppelten eliminiert. Das ist eine Menge unnötiger Arbeit für die Datenbank. Das Ziel sollte daher sein, so viele Daten wie möglich zu reduzieren, bevor der Equi-Join stattfinden muss. Mit dem Subquery-factoring kann eine temporäre Ergebnismenge erzeugt werden, die später in dem Join benutzt wird.
Ich kam zu folgender Lösung:
with Temp_Receiptdate
as
(
SELECT DISTINCT C087_WHID,
C087_RECEIPTDATE
FROM T087_ITEMSALE_HOURS
)
SELECT /*+parallel */DISTINCT
C012_COUNTRYID AS CMV087_COUNTRYID,
C087_WHID AS CMV087_WHID,
TO_CHAR(C087_RECEIPTDATE,'IYYY') AS CMV087_CAL_YEAR,
TO_CHAR(C087_RECEIPTDATE,'IW') AS CMV087_CAL_WEEK,
TO_CHAR(C087_RECEIPTDATE,'DDD') AS CMV087_CAL_DAY,
1 + TRUNC (C087_RECEIPTDATE) - TRUNC (C087_RECEIPTDATE,'IW') AS CMV087_CAL_WEEK_DAY,
C087_RECEIPTDATE AS CMV087_RECEIPTDATE
FROM Temp_Receiptdate T087_ITEMSALE_HOURS
INNER JOIN T012_WH ON C012_WHID = C087_WHID;
104440 Zeilen ausgewählt.
Abgelaufen: 00:00:02.41
siehe rechts Ausführungsplan 3 (Bild durch Anklicken vergrößerbar)
Während der Laufzeit der drei Statements war Folgendes zu sehen:
Alle drei Statements haben in etwa die gleichen I/Os verursacht. Die CPUs waren während der Ausführungen zu 100 % belastet (12 Minuten, 6 Minuten und 2 Sekunden). Die Problematik ist, dass die CPUs für die Dauer der Ausführung für andere Arbeiten nicht zur Verfügung stehen. Daher sollte bei dieser Art des Statements darauf geachtet werden, dass die Belastung der CPUs so klein wie möglich ist.
Auch wenn es korrekt, logisch und nachvollziehbar ist, sollten Sie ab und zu das eine oder andere Statement kritisch betrachten und ggf. andere Wege gehen.
Daher: „Auch das beste Lehrbuch kann die Praxis nicht ersetzen“.
Bruno Cirone
DOAG Themenverantwortlicher Sicherheit
