Poprawa wydajności aplikacji PL/SQL w Oracle Database 11g

Anna Kowalczyk-Niewiadomy, Dominik Niewiadomy

Stary dobry PL/SQL towarzyszy nam od zarania dziejów. Co więcej, mimo upływu lat nic a nic się nie starzeje, a tę cudowną młodość język ten zawdzięcza zapewne ścisłemu powiązaniu z wiecznie młodą wyrocznią – bazą danych Oracle. Spoglądając okiem „historyka informatyki” nie trudno znaleźć języki, które swoją świetność mają już zdecydowanie za sobą. Wiele jest również takich, o których już dawno zapomniano. Tymczasem PL/SQL żyje i ma się bardzo dobrze – podobnie jak cały Oracle, a tym bardziej jego nowa wersja, czyli Oracle Database 11g. Sprawdźmy zatem, co nowego spotkać może programistów w kontekście poprawy wydajności programów napisanych w PL/SQL.

Kompilacja natywna

Na początek może to, co twórcy obiecują nam z każdą kolejną wersją bazy, czyli zwiększenie wydajności poprzez poprawę formy skompilowanego kodu. Mówiło się już o tym przy wersji Oracle9i Database, gdzie reklamowana była kompilacja natywna (native compilation). W Oracle Database 10g wydajność podniesiono poprzez składowanie podprogramów w katalogach w postaci natywnych bibliotek DLL. A o co właściwie chodzi? Jak wszyscy wiemy, PL/SQL jest językiem interpretowanym. Nie oznacza to jednak wcale, że podczas wykonywania kodu każda jego linia jest pobierana i wykonywana „oddzielnie”. Tak naprawdę, kod PL/SQLa kompilowany jest do postaci pośredniego kodu maszynowego, tzw. M-Code. Ponieważ kod ten wykonywany jest na maszynie wirtualnej, często używana jest również nazwa PVM (PL/SQL virtual machine). Czym jest zatem PVM? Zbiorem procedur uruchamianych w celu przeskanowania M-Code’u w poszukiwaniu informacji, co w zasadzie powinno zostać zrobione w trakcie wykonania danego zestawu poleceń. To właśnie PVM kieruje te informacje do innych procedur motoru bazy, niezbędnych do realizacji kodu. Oczywiście operacja ta kosztuje nas zasoby i czas. Aby proces ten nieco skrócić zaproponowano, aby faza skanowania M-Code’u wykonywana była podczas kompilacji procedury. Jeśli w naszej bazie ustawimy parametr Plsql_Code_Type na native, proces kompilacji odbywał się będzie w zasadzie dokładnie w ten sam sposób, co poprzednio, czyli w trybie interpretera. Różnica pojawia się w chwili zakończenia kompilacji do M-Code. Kompilacja standardowa w tym miejscu się kończy. Kompilacja natywna wykonuje jeszcze jedną operację – skanowanie. To samo skanowanie, które uruchamiane jest na procedurach skompilowanych „tradycyjnie”.

Jak zatem widać, proces skanowania przeniesiony został z fazy wykonania kodu do fazy jego kompilacji. Skróciliśmy w ten sposób czas realizacji fazy wykonania kodu, ale nieco wydłużyliśmy czas kompilacji. Jak by nie liczyć, i tak wyszliśmy na plus. Praktyka mówi bowiem, że procedury są znacznie częściej uruchamiane niż kompilowane. No, może za wyjątkiem fazy ich tworzenia i testowania. Niewątpliwym plusem całego tego rozwiązania jest to, że zarówno w fazie kompilacji jak i skanowania, w obu typach kompilacji procedur użyte są te same mechanizmy. Dzięki temu zmiana sposobu kompilacji ze standardowej na natywną nie wymaga wprowadzania żadnych zmian w kodzie i może zostać włączona w momencie przejścia z trybu deweloperskiego w tryb produkcyjny.

Dalsze poszukiwania sposobów zwiększenia wydajności kodu przyniosły w Oracle Database 10g pomysł na użycie kompilatora C. Tak powstały składowane poza bazą biblioteki DLL. Z początku biblioteki te składowane były bezpośrednio w filesystemie, później do ich lokalizacji użyty został mechanizm katalogów. Wtedy właśnie zaczęliśmy używać parametrów Plsql_Native_Library_Dir i Plsql_Native_Library_Subdir_Count. Pomysł w zasadzie wydawał się dobry. Problemy zaczęły występować w produkcyjnych jego zastosowaniach. Użycie takich zewnętrznych bibliotek wymagało bowiem zainstalowania na poziomie systemu operacyjnego maszyny produkcyjnej kompilatora C. Wymaganie to bardzo się nie spodobało administratorom maszyn pracującym na rzecz systemów produkcyjnych – nie tylko z powodu konieczności nabycia odpowiednich licencji, dodatkowej pracy z tym związanej, ale również z powodu zmniejszenia poziomu bezpieczeństwa systemu.

Cóż zatem wymyślono w tym temacie w Oracle Database 11g? Zrezygnowano z kompilacji i linkowania kodu „poza motorem bazy danych” i zaimplementowano w nim mechanizmy umożliwiające kompilację natywnych bibliotek PL/SQL. W ten sposób zrezygnowano z materializowania natywnych bibliotek PL/SQL w systemie plików. Parametry Plsql_Native_Library_Dir i Plsql_Native_Library_Subdir_Count nie są już więc potrzebne, a prędkość kompilacji wzrosła dwukrotnie. Wzrosła również wydajność wykonywania procedur. W rezultacie tych zmian do dyspozycji programisty oddano jeden parametr Plsql_Code_Type, przyjmujący wartości native oraz interpreted.

alter session set plsql_code_type = native;

To on decyduje, czy kompilacja ma odbywać się według schematu native DLL czy M-Code, a decyzja ta honorowana jest przez wszystkie instalacje Oracle Database 11g. Aby sprawdzić, w jaki sposób zostały skompilowane procedury PL/SQL należy wykonać następujące zapytanie:

select name , plsql_code_type from user_plsql_object_settings;

Wynikiem takiego zapytania będzie lista skompilowanych procedur i funkcji wraz z podanym trybem kompilacji.

Modyfikacja algorytmu kaskadowej inwalidacji obiektów

A teraz słów kilka o procedurach i funkcjach ze statusem kompilacji INVALID. Pojawia się on zwykle wtedy, gdy motor bazy uzna, że zmiany, jakie zaistniały w strukturze kodu PL/SQLa lub modelu danych bazy mogły mieć wpływ na poprawność działania instrukcji zapisanych w procedurach. Problem w tym, że algorytm sprawdzania poprawności zależności w kodzie w bazach wersji niższych niż 11g działał zdecydowanie zbyt często i wymuszał częstą nadmiarową rekompilację kodu. Mechanizm ten nie uwzględniał bowiem wewnętrznej struktury obiektów złożonych – takich, jak pakiety. Poprzednie wersje Oracle Database przyjmowały, że każda zmiana w pakiecie wymuszała inwalidacje wszystkich zależnych od niego obiektów, co wiązało się z kaskadowym przekompilowaniem zależnych pakietów. Zatem, jeśli pakiety PakietB i PakietC posiadały odwołania do choć jednej metody z pakietu PakietA, to w momencie kompilacji pakietu A wymuszana była ich rekompilacja.

W Oracle Database 11g granulacja informacji służących do określania, które elementy kodu należy przekompilować, spadła z poziomu pakietu do poziomu poszczególnych procedur i funkcji. Załóżmy, że w pakiecie PakietA znajduje się procedura procA, która odwołuje się do procedury procB. W takim przypadku w metadanych procedury procA znajdują się informacje o odwołaniu do procedury procB. Jeśli teraz do pakietu PakietA dodamy procedurę procC, żadna z istniejących już w pakiecie procedur nie będzie musiała być rekompilowana, dopóki procedura procC nie zostanie z nimi jawnie powiązana poprzez wywołanie w swoim ciele.

Podsumowując, w Oracle Database 11g rekompilacja wymagana jest jedynie na elementach wzajemnie ze sobą powiązanych, a nie całych pakietach.

Wykorzystanie dyrektywy pragma inline

Każde wywołanie procedury bądź funkcji obarczone jest niewielkim, lecz mierzalnym kosztem obliczeniowym, szczególnie dostrzegalnym, kiedy występuje ono w pętli. Pisanie kodu w sposób pomijający tworzenie procedur i funkcji jest sposobem, który rozwiązałby ten problem. Z drugiej jednak strony, taki styl kodowania odbiega od obecnych standardów zakładających modularność aplikacji oraz jak największą ich przejrzystość. Dobrze napisany kod powinien składać się z bloków, które będą w stanie zmieścić się na jednym, bądź kilku ekranach, co pozwoli je w łatwy sposób objąć wzrokiem i wyciągnąć z nich interesujące nas informacje. Prostym, a zarazem optymalnym rozwiązaniem tego problemu jest stosowanie dyrektywy pragma inline oraz parametru włączającego ręczną bądź automatyczną optymalizację preprocesora kodu aplikacji (plsql_optimize_level). Zastosowanie dyrektywy inline analogicznie – jak w innych językach – powoduje eliminację kosztu wywołania procedury na rzecz wstawiania jej treści w miejsce jej wywołania.

Aby skorzystać z tej funkcjonalności należy pozwolić na automatyczne dobranie elementów inline lub w trybie ręcznym przed wywołaniem procedury napisać:

pragma inline (nazwa_procedur_lub_funkcji, 'YES');

Natomiast, jeśli chcemy jawnie zabronić wykorzystania podstawiania kodu należy wykonać:

pragma inline (nazwa_procedur_lub_funkcji, 'NO');

Gdzie w kodzie dopuszczalne jest stosowanie opisywanego trybu dla podprogramów? Wszędzie tam, gdzie mamy do czynienia z przypisaniem, warunkiem, wywołaniem, instrukcjami case, continue-when, execute immediate, exit-when, pętlami, instrukcją zwracania wartości return.

Domyślnie ustawiony parametr plsql_optimize_level przyjmuje wartość 2 i oznacza, że to programista w kodzie wybiera miejsca gdzie należy zastosować podstawienie. Poniższy przykład prezentuje funkcję sumującą dwie liczby, wywołaną w pętli 15 milionów razy bez zastosowania dyrektywy inline (wykomentowana):

alter session set plsql_optimize_level = 2;

declare
zlicznik number := 15000000;
zstart number;
zwartosc number;

function dodaj (a number, b number) return number as
begin
return a + b;
end dodaj;

begin
zstart := dbms_utility.get_time();

for i in 0 .. zlicznik loop
-- pragma inline(dodaj, 'YES');
zwartosc := dodaj(1, i);
end loop;

dbms_output.put_line('Czas: '||(dbms_utility.get_time()-zstart)/100 || 's');
end;

Czas wykonania powyższego kodu to w przybliżeniu 6,3 sekundy. Dla tego samego przykładu, w momencie odkomentowania linijki pragma inline czas ulega drastycznemu skróceniu i wynosi w przybliżeniu 3,6 sekundy. Wiemy zatem, jak używać dyrektywy inline dla domyślnej wartości parametru plsql_optimize_level równej 2. Istnieje również inna możliwość optymalizacji naszego kodu. Polega ona na tym, że pozwalamy kompilatorowi/optymalizatorowi na podjęcie decyzji, czy ma wstawić funkcję w trybie inline, czy zastosować jej jawne wywołanie. W tym celu należy ustawić parametr plsql_optimize_level na 3. Na podstawie analizy kosztów i zysku kompilator (moduł optymalizacyjny) oszacuje sam, które funkcje bądź procedury należy wywołać standardowo, a które nie. Znajdujące się w kodzie dyrektywy pragma inline nie będą miały wpływu na podejmowaną decyzję, o ile nie są ustawione na ‚NO’ (jawne zablokowanie trybu inline).

Wydawałoby się, że ten tryb pracy jest idealnym wynalazkiem i zawsze przyniesie poprawę wydajności kodu. Czy tak jest naprawdę? W większości przypadków tak. Należy wziąć pod uwagę fakt, iż modyfikacja kodu źródłowego jest jedną z pierwszych faz optymalizacji i czasami może spowodować, że wydajniejsze, lecz późniejsze fazy optymalizacji nie będą w stanie zastosować swoich skuteczniejszych algorytmów na kodzie. Innym obciążeniem związanym z opisywaną funkcjonalnością preprocesora jest rozrastanie się wielkości kodu (pomimo występowania mechanizmów kontrolnych). Należy zatem w szczególnie wąskich gardłach aplikacji dokładnie sprawdzić, czy zastosowanie parametru plsql_optimize_level równego 3 przynosi oczekiwany rezultat i daje poprawę wydajności.

Cache w zapytaniach SQL

Oracle Database 11g wprowadza mechanizm cache do zapytań i podzapytań języka SQL. Mechanizm ten umożliwia za pomocą hinta result_cache wymuszenie na bazie zapamiętywania i w przyszłości ponownego użycia wyników zrealizowanych zapytań. Rozważmy przypadek, w którym użytkownik wykonuje wiele zapytań odwołujących się do tabeli z dużą liczbą rekordów, które nie podlegają częstym zmianom. Najprostszą sytuacją tego typu jest zapytanie o wyszukiwanie określonej grupy danych ze słownika zawierającego np. miejscowości, bądź kody pocztowe. Nawet, jeśli obszar SGA jest wystarczająco duży aby pomieścić wszelkie dane, i tak narzut związany z wybraniem szukanych rekordów jest kosztowny. W szczególności obciążenie to dotyczy zapytań, w których wymagane jest odczytywanie danych z dysku. Aby zatem wykorzystać opisany mechanizm cache należy umieścić w zapytaniu hint w postaci:

select /*+ result_cache */ idmiasta, nazwa from miasta where idPowiatu=1;

Jeśli tak skonstruowane zapytanie było wcześniej wykonane, a wynik znajduje się w pamięci cache, dostaniemy znacznie szybszą odpowiedź niż przy standardowym zapytaniu.

Dane tymczasowe w Oracle Database 11g przechowywane są we współdzielonym obszarze pamięci dla wszystkich sesji. Skutkuje to tym, że zapytanie znajdzie się w cache i będzie dla nas dostępne, jeśli którykolwiek inny użytkownik je wykonał. Należy pamiętać, że tak wykorzystywany cache składowany jest w pamięci. Maksymalna wielkość pamięci przeznaczona na cache ustawiana jest za pomocą parametru result_cache_max_size. Należy zatem odpowiednio dobrać tę wielkość tak, aby nie ograniczyć pamięci operacyjnej potrzebnej innym procesom.

Oracle Database 11g dodatkowo umożliwia odwrócenie standardowego trybu generowania wyników zapytań, przez wymuszenie cache’owania wszystkich zapytań bez konieczności wstawiania hinta. W tym celu można skorzystać z parametru result_cache_mode. Domyślnie parametr ten ustawiony jest na MANUAL. Jeśli jednak chcemy wymusić automatyczne cache’owanie należy ustawić go na FORCE:

alter session set result_cache_mode=FORCE

Dla takiego stanu sesji warto wiedzieć o istnieniu hinta no_result_cache:

select /*+ no_result_cache */ idmiasta, nazwa from miasta where idPowiatu=1;

Cache w funkcjach PL/SQL

Kolejną wartą opisania funkcjonalnością Oracle Database 11g jest cache’owanie wartości zwracanych przez funkcje PL/SQL. Aby dostrzec plusy wynikające z tej cechy silnika bazy danych, rozpatrzmy następujący przykład. Przyjmijmy, że posiadamy funkcję zwracającą wartość liczbową wyliczoną na podstawie kilku osobnych zapytań SQL na różnych tabelach. Pierwsze, co nam się nasuwa to skonstruowanie tej funkcji tak, aby wartości były wyliczane na podstawie optymalnie napisanych zapytań SQL zawierających hinta result_cache. Czy jest to optymalne? W pewnym sensie tak, ponieważ jest to rozwiązanie szybsze, niż przetwarzanie bez hinta. A czy można by było to zrobić jeszcze szybciej? Tak, wystarczy zachować w pamięci cache sam rezultat naszej funkcji. W tym celu należy dodać do definicji funkcji informację, że wynik podlega cache’owaniu. Poniżej zaprezentowany został sposób oznaczenia funkcji z cache’owaniem wyniku:

function fun (p1 typ1,p2 typ2, ...) return typX
result_cache is

Powyższa składnia definicji funkcji przyjmuje dość ryzykowne założenie, że wartość pochodząca z cache będzie zależna tylko i wyłącznie od parametrów funkcji. Należy jednak zwrócić uwagę, iż wartość ta jest także zależna od obiektów, do których odwołuje się nasza funkcja. W przypadku cache’owania zapytań SQL wystarczyło dodać samego hinta, ponieważ już samo zapytanie było świadome, od czego jest zależne i jakie są jego tabele składowe. Dlatego też w przypadku funkcji należy ręcznie dopisać zmienne elementy zależne. W tym celu należy rozszerzyć składnię definicji o słowo kluczowe relies_on z listą tabel separowanych przecinkami:

function fun (p1 typ1,p2 typ2, ...) return typX
result_cache relies_on (tabela1, tabela2, ...) is

Ponieważ kluczem cache’owania są argumenty funkcji, należy spodziewać się pewnych ograniczeń z nimi związanych. Podstawowym ograniczeniem na parametry wejściowe jest ich skalarność. Argument wejściowy musi być skalarem, ponieważ tego wymaga mechanizm budowy klucza haszującego. Argument funkcji nie może być parametrem OUT lub IN OUT oraz nie może być typem pochodnym od LOB, kursorem referencyjnym, kolekcją, typem wierszowym ani obiektowym. Kolejnym ograniczeniem związanym z prawidłową funkcjonalnością cache jest typ zwracany przez funkcję. Typ danych rezultatu funkcji nie może być także typem pochodnym od LOB, kursorem referencyjnym, typem obiektowym oraz typem wierszowym, zawierającym choć jeden z wcześniej wymienionych typów. Dodatkowo, aby cache w funkcjach działał poprawnie zaleca się, aby funkcje w swoim kodzie nie dokonywały zmian stanu systemu (modyfikacja danych itp.), były niezależne od zmiennych sesyjnych oraz od kontekstu aplikacji, w jakim są uruchamiane. Wynika to wprost z faktu, iż wartość cache powinna być tylko zależna od parametrów wywołania funkcji oraz tabel zależnych.

Zważywszy na powyższe wady i zalety, a także restrykcje związane z cache’owaniem wyników należy rozpatrzyć, czy możliwe i opłacalne jest ich używanie Każdy wprawny programista i analityk w prosty sposób odpowie na to pytanie patrząc na rozpostarty przed sobą kod PL/SQLa.

Hierarchical Profiler w PL/SQL

Baza danych Oracle 11g daje nam nowe narzędzie – hierarchiczny profiler języka PL/SQL. W skrócie można powiedzieć, że jest to narzędzie podobne do SQL Trace. Aby skorzystać z profilera należy włączyć tryb zbierania danych, a następnie uruchomić testowany kod. Po wykonaniu badanych operacji należy wyłączyć zbieranie danych. Ponieważ profiler zbiera wszelkie możliwe informacje, które zaistniały w trakcie testowania kodu, aby nie zagubić się w natłoku danych należy za pomocą formatera wygenerować interesujący nas raport. W celu zwiększenia wygody raporty generowane są do postaci stron HTML. Po otrzymaniu tak wygenerowanego raportu można odpowiedzieć między innymi na takie pytania, jak: jakie podprogramy zostały wywołane podczas testu, jakie zapytania zostały wykonane podczas testu, ile czasu zabrały poszczególne zapytania, jakie programy wywołały się w obrębie konkretnego podprogramu. Listę tych pytań można mnożyć długo, istotny jest jednak fakt, iż dzięki uzyskaniu odpowiedzi na nie jesteśmy w stanie rozpoznać wąskie gardła aplikacji i podnieść jej wydajność w miejscach, które wcześniej wydawały się nam optymalne.

Podsumowanie

Jak widać, Oracle Database 11g daje programiście PL/SQL całą gamę nowych możliwości, pozwalających w prosty sposób zoptymalizować zarówno nowy, jak i już wcześniej stworzony kod aplikacji. Wreszcie pojawiła się natywna kompilacja kodu, która ma szansę na pojawienie się w większości środowisk produkcyjnych, gdyż nie obciąża administratora bazy danych dodatkowymi obowiązkami, a także nie dokłada niebezpiecznych zewnętrznych aplikacji do systemu bazodanowego. Dodatkowo, wszystkie opisywane nowe cechy bazy nie stanowią obciążenia dla programisty, gdyż nie wymagają przepisywania setek tysięcy linii kodu; wiążą się jedynie z drobnymi modyfikacjami. Niekiedy operacje te wymagają przemyślenia, tak jak w przypadku stosowania cache dla funkcji PL/SQL lub zapytań SQL. Innym razem – jak w przypadku dyrektywy pragma inline – wymagana jest tylko zmiana parametru plsql_optimize_level w sesji i rekompilacji. Dodatkowo, jesteśmy w stanie oszacować dokładnie czy każda z opisywanych zmian jest dla nas opłacalna dzięki wprowadzeniu wygodnego Hierarchical Profilera. Podsumowując: Oracle Database 11g wyposaża nas w zestaw cech pozwalających zwiększyć wydajność pisanych programów, a także w wygodne narzędzie ułatwiające lokalizację, kontrolę i eliminację wąskich gardeł aplikacji.