PL/SQL w SQL: dwa proste eksperymenty [WITH, PRAGMA UDF, Result Cache, DETERMINISTIC, Scalar Subquery Caching]

W związku z częstymi pytaniami o to, jak optymalnie wykorzystywać funkcje PL/SQL w poleceniach SQL, przyjrzyjmy się następującym dwóm eksperymentom wydajnościowym. Naszym celem będzie przeszukiwanie tabeli liczącej milion rekordów, a klazula WHERE będzie zawierać odwołanie do bardzo prostej funkcji PL/SQL.

Eksperyment 1

Dane wejściowe: tabela MROWS zawierająca milion unikatowych rekordów, funkcja PL/SQL DBL() zwraca podwojoną wartość parametru wywołania:

CREATE TABLE mrows (id NUMBER);
BEGIN
  FOR i IN 0..999999 LOOP
    INSERT INTO mrows VALUES (i);
  END LOOP;
END;

CREATE OR REPLACE FUNCTION dbl(x NUMBER) RETURN NUMBER IS
BEGIN
 RETURN x*2;
END;

Będziemy badać wydajność zapytania SQL wykorzystującego funkcję DBL() w klauzuli WHERE:

SELECT * FROM mrows WHERE id=dbl(id);

Zmierzony początkowy czas wykonania wyniósł 4,6 sekundy.

Główny wpływ na czas wykonania powyższego zapytania mają: odczyt wszystkich rekordów tabeli MROWS, wywołanie funkcji DBL() dla każdego rekordu (milion razy) oraz wykonanie obliczeń przez funkcje DBL() (w naszym przypadku jest to proste mnożenie). Z wywoływaniem funkcji PL/SQL z poziomu SQL wiąże odwieczny problem przełączania kontekstów SQL:PL/SQL.

Całość do PL/SQL: dwa razy szybciej!

Pierwszy pomysł na optymalizację będzie wiązać się z wyeliminowaniem przełączania kontekstów SQL:PL/SQL. Przepiszmy całe polecenie do czystego języka PL/SQL i dokonajmy pomiaru czasu wykonania.

BEGIN
  FOR r IN (SELECT id FROM mrows) LOOP
     IF r.id=DBL(r.id)
       THEN DBMS_OUTPUT.PUT_LINE(r.id);
     END IF;
  END LOOP;
END;

Tym razem czas wykonania wyniósł 2,4 sekundy!

Całość do SQL: dziesięć razy szybciej!

A gdybyśmy tak w celu eliminacji przełączania kontekstów SQL:PL/SQL całość przepisali do czystego SQL?

SELECT * FROM mrows WHERE id=id*2;

Czas wykonania: 0,4 sekundy!

Niestety nie zawsze uda się przepisać funkcję PL/SQL do czystego SQL – bo przecież ktoś z jakiegoś powodu wybrał PL/SQL. Szukajmy zatem rozwiązań alternatywnych.

Funkcja inline: prawie trzy razy szybciej!

Skorzystajmy teraz z wprowadzonej w 12c składni WITH umożliwiającej zdefiniowanie funkcji PL/SQL wewnątrz zapytania SQL.

WITH FUNCTION dbl(x NUMBER)
RETURN NUMBER IS
BEGIN
 RETURN x*2;
END;
SELECT * FROM mrows WHERE id=dbl(id);

Obecny czas wykonania: 1,7 sekundy! To bardzo dobry wynik, jednak samo rozwiązanie jest mało wygodne, ponieważ pozbawia nas możliwości współdzielenia tej samej funkcji przez wiele zapytań. Rozważmy jeszcze jedną opcję.

Pragma UDF: prawie pięć razy szybciej!

Enigmatyczna wskazówka PRAGMA UDF pojawiła się w wersji 12c serwera Oracle Database i służy do optymalizacji kodu funkcji PL/SQL pod kątem jej wydajnego wywoływania z poziomu SQL.

CREATE OR REPLACE FUNCTION dbl(x NUMBER)
RETURN NUMBER IS
PRAGMA UDF;
BEGIN
 RETURN x*2;
END;

SELECT * FROM mrows WHERE id=dbl(id);

Czas: 1 sekunda. To chyba jest rozwiązanie, które nas najbardziej usatysfakcjonuje. Nie wymaga bowiem modyfikacji kodu poleceń SQL, a modyfikacje funkcji PL/SQL są raczej kosmetyczne.

Eksperyment 2

Dane wejściowe: tabela MROWS z milionem rekordów zawierających 100 powtarzających się wartości, funkcja PL/SQL DBL() zwraca podwojoną wartość parametru wywołania. Powtarzalność wartości wprowadziliśmy po to, by przebadać skuteczność różnych technik buforowania wyników funkcji.

CREATE TABLE mrows (id NUMBER);
BEGIN
  FOR i IN 0..1000000 LOOP
    INSERT INTO mrows VALUES (MOD(i,100));
  END LOOP;
END;

CREATE OR REPLACE FUNCTION dbl(x NUMBER) RETURN NUMBER IS
BEGIN
 RETURN x*2;
END;

Będziemy badać wydajność zapytania SQL wykorzystującego funkcję DBL() w klauzuli WHERE:

SELECT COUNT(*) FROM mrows WHERE dbl(id)=0;

Zmierzony początkowy czas wykonania wyniósł 4,3 sekundy.

Result Cache: gorzej!

Na początek spróbujmy zastosować Result Cache (wymaga aktywacji i konfiguracji przez DBA) w celu buforowania wyników funkcji DBL():

CREATE OR REPLACE FUNCTION dbl(x NUMBER)
RESULT_CACHE
RETURN NUMBER IS
BEGIN
 RETURN x*2;
END;

SELECT COUNT(*) FROM mrows WHERE dbl(id)=0;

Czas wykonania: 5,2 sekundy! To dużo gorzej niż bez stosowania Result Cache! Przyczyną jest to, że nasza funkcja DBL() wykonuje się bardzo sprawnie (jedno mnożenie), a problemem jednak jest przełączanie kontekstów SQL:PL/SQL, czego akurat Result Cache nie eliminuje…

Funkcja DETERMINISTIC: cztery razy szybciej!

Dzięki oznaczeniu funkcji PL/SQL jako deterministycznej, serwer Oracle Database nie musi wywoływać jej ponownie dla identycznej wartości parametru wywołania.

CREATE OR REPLACE FUNCTION dbl(x NUMBER)
DETERMINISTIC
RETURN NUMBER IS
BEGIN
 RETURN x*2;
END;

SELECT COUNT(*) FROM mrows WHERE dbl(id)=0;

Czas wykonania: 1,1 sekundy!

Scalar subquery caching: ponad cztery razy szybciej

Interesującym rozwiązaniem jest przepisanie wywołania
funkcji PL/SQL do postaci podzapytania skalarnego, gdyż serwer Oracle Database potrafi automatycznie buforować wyniki takich podzapytań, zmniejszając liczbę wywołań funkcji PL/SQL. Szkoda tylko, że rozmiar tego bufora jest ograniczony (niekonfigurowalny i nieudokumentowany):

SELECT COUNT(*) FROM mrows
WHERE 0 = (SELECTdbl(id) FROM DUAL);

Czas wykonania: 1 sekunda. I to właśnie jest zwycięzca naszego drugiego eksperymentu!

 

Na zakończenie garść referencji do dokumentacji:

Using a PL/SQL Function in the WITH Clause: Examples

PRAGMA UDF

PL/SQL Function Result Cache

DETERMINISTIC Clause

Scalar Subquery Caching

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *