SYSDATE in SQL und PL/SQL

Heute fühle ich mich genötigt, ein Thema anzusprechen, das zwar ein Grundlagenthema ist, das aber immer wieder gerne in Vergessenheit gerät. ;-)

Anlass ist ein Fund in einer Produktionsumgebung, in der eine Überlastung der CPU bei sehr hoher Anzahl an SQL-Ausführungen zu beobachten war. Die Umgebung basiert sehr stark auf PL/SQL, wobei häufig verwendete SQL-Abfragen gerne in einer Function gekapselt und dann aus SQL heraus aufgerufen werden.
Zur Vereinfachung nehmen wir eine kleine Tabelle und eine Function, die nichts anderes macht, als eine Sekunde zu warten und dann den Übergabewert sowie das aktuelle Datum (also SYSDATE) auszugeben:

create table uk_dummy( x varchar2(1));
insert into uk_dummy values('1');
insert into uk_dummy values('2');
insert into uk_dummy values('3');
commit;

create or replace function UK_SYSDT ( p_date in date := SYSDATE )
return varchar2
as
begin
  SYS.DBMS_LOCK.SLEEP(1);
  return 'Date IN: '||to_char(p_date, 'hh24:mi:ss')
    ||', sysdate: '||to_char(sysdate, 'hh24:mi:ss');
end;
/

Bevor ich das Beispiel weiter verfolge, möge sich der geneigte Leser kurz folgende Gedanken machen:

  1. Wenn ich in einer SQL-Abfrage statisch ein SYSDATE mit ausgebe, ändert sich dies bei der Ausgabe eines Result Sets oder bleibt es gleich?
  2. Wenn ich das aktuelle Datum nicht über SYSDATE sondern über eine eigene Function aufrufe, verhält es sich dann genau so?

Hier die Antwort/Beweisführung:

col fn for a40
select X, UK_SYSDT( sysdate ) fn from uk_dummy;

X FN
- ----------------------------------------
1 Date IN: 17:36:53, sysdate: 17:36:54 
2 Date IN: 17:36:53, sysdate: 17:36:55
3 Date IN: 17:36:53, sysdate: 17:36:56

3 rows selected.

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| UK_DUMMY |     3 |     6 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
  1. SQL arbeitet mengenorientiert; Oracle erzeugt zu Beginn der Transaktion (bzw. hier zu Beginn des SELECTs) einen Schnappschuss der Daten, der während der Transaktion konsistent bleibt. Dies umfasst auch eingebaute Funktionen wie SYSDATE, das für die Laufzeit einer Abfrage konstant und damit unabhängig von der Dauer der Abfrage bleibt.
  2. PL/SQL-Funktionen hingegen werden nicht der Lesekonsistenz unterworfen sondern werden pro Ergebniszeile ausgeführt.
  3. Daher bleibt das vom SQL übergebene Datum („Date IN“) gleich, während das von PL/SQL ermittelte Datum ansteigt.

Ruft man also PL/SQL aus SQL heraus auf, besteht die Gefahr, dass die Ergebnisse von der Dauer der Abfrage abhängig werden! Das wird besonders kritisch, wenn PL/SQL-Functions innerhalb der WHERE-Clause aufgerufen werden.

Auch aus Performance-Sicht kann das heikel werden: Wenn wir die o.g. Function gegen eine Tabelle mit einer Million Zeilen laufen lassen, werden eine Million Sekunden vergehen, bis das Ergebnis geliefert wird. Das dürfte für die meisten Anwender kaum akzeptabel sein.

Gegenmaßnahmen

Wenn man die Wahl hat

und das SQL nicht unbedingt kapseln muss, dann sollte man anstelle der Function einfach ein Sub-Select verwenden.

Wenn man nur das SQL ändern darf,

z.B., weil Richtlinien eines Drittherstellers das so vorschreiben, dann sollte man prüfen, ob der Funktionsaufruf in eine Inline View verlagert werden kann.

In unserem Beispiel (wie auch dem Fall aus der „realen Welt“) ist das Ergebnis der Function nicht vom Inhalt der abgefragten Daten abhängig. Es gibt also keinen Grund, die Function für jede Zeile erneut aufzurufen.

Scalar Subquery Caching

Die eleganteste Lösung nutzt ein Feature aus, das schon seit Oracle 8i verfügbar ist: Die Verwendung einer skalaren Unterabfrage, die nur einmal initial ausgeführt und deren Ergebnis dann wiederverwendet wird. Die Funktionsweise hat Tom Kyte in einem Artikel im Oracle Magazine gut beschrieben.

select X
     , ( select UK_SYSDT( sysdate ) from dual ) fn
  from uk_dummy;
X FN
- ----------------------------------------
1 Date IN: 10:16:55, sysdate: 10:16:56    
2 Date IN: 10:16:55, sysdate: 10:16:56    
3 Date IN: 10:16:55, sysdate: 10:16:56    

3 rows selected.

-------------------------------------------------------------------------------
| Id  | Operation         | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |        |       |     2 (100)|          |
|   1 |  FAST DUAL        |          |      1 |       |     2   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL| UK_DUMMY |      3 |     6 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Vielen Dank an Matthias Rogel, der mir die Augen für einen Fehler beim Testen dieses Szenarios geöffnet hat!

Materialisierte WITH-Clause

Die Verlagerung in eine Inline View erfolgt über eine WITH-Clause, bei der ein MATERIALIZE-Hint sicherstellt, dass der Optimizer die WITH-Clause nicht wieder umschreibt:

WITH dt as (
  select /*+ MATERIALIZE */ UK_SYSDT( sysdate ) fn
    from dual
)
select x, fn from uk_dummy, dt;
X FN
- ----------------------------------------
1 Date IN: 18:04:29, sysdate: 18:04:30
2 Date IN: 18:04:29, sysdate: 18:04:30
3 Date IN: 18:04:29, sysdate: 18:04:30

3 rows selected.

------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |        |       |     6 (100)|
|   1 |  TEMP TABLE TRANSFORMATION |                             |        |       |            |
|   2 |   LOAD AS SELECT           |                             |        |       |            |
|   3 |    FAST DUAL               |                             |      1 |       |     2   (0)|
|   4 |   MERGE JOIN CARTESIAN     |                             |      3 |  6012 |     4   (0)|
|   5 |    VIEW                    |                             |      1 |  2002 |     2   (0)|
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6A52_BA798AF0 |      1 |  2002 |     2   (0)|
|   7 |    BUFFER SORT             |                             |      3 |     6 |     4   (0)|
|   8 |     TABLE ACCESS FULL      | UK_DUMMY                    |      3 |     6 |     2   (0)|
------------------------------------------------------------------------------------------------

Wenn man nur PL/SQL ändern darf,

dann ist noch nicht alle Hoffnung verloren! Ab Oracle 11g kann der Function Result Cache eingesetzt werden:

create or replace function UK_SYSDT ( p_date in date := SYSDATE )
return varchar2
RESULT_CACHE
as
begin
  SYS.DBMS_LOCK.SLEEP(1);
  return 'Date IN: '||to_char(p_date, 'hh24:mi:ss')
    ||', sysdate: '||to_char(sysdate, 'hh24:mi:ss');
end;
/

select X, UK_SYSDT( sysdate ) fn from uk_dummy;
X FN
- ----------------------------------------
1 Date IN: 18:31:59, sysdate: 18:32:00
2 Date IN: 18:31:59, sysdate: 18:32:00
3 Date IN: 18:31:59, sysdate: 18:32:00

3 rows selected.

Der Result Cache ist allerdings nur dann eine Lösung, wenn

  1. Die Übergabewerte an die Funktion nicht variieren
  2. Die Funktion ihrerseits nicht von weiteren Tabellen abhängig ist, deren Inhalt sich häufig verändert (und damit den Result Cache invalidiert).

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

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