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: