Gdy konteksty się zmieniają, deweloperzy cierpią na równi z adminami

Kiedy programista zaczyna przygodę z bazami Oracle, jedna z pierwszych rzeczy, która przypada mu do gustu, to integracja PL/SQL z językiem SQL. No bo jak tu nie polubić faktu, że żadnego połączenia definiować nie trzeba, że SELECT może zwrócić dane bezpośrednio do zmiennych lub kolekcji PL/SQL, że własne funkcje mogą być użyte w dowolnym zapytaniu, że można sobie swoje typy zdefiniować i że to wszystko w ogóle tak ładnie ze sobą zintegrowane i połączone.

Niestety, po kilku latach pracy w branży IT, mamy tendencję do zawodowego cynizmu, który każe w takiej sytuacji zawsze się zastanawiać – gdzie jest haczyk? Jakim kosztem? Kiedy będę musiał za to zapłacić i z jakimi odsetkami? Takie paranoiczne myślenie zdaje się chronić naszą szczękę od permanentnego opadu, spowodowanego pracą w iście pratchettowym świecie IT, w którym sytuacja 1 na milion zdarza się w 90% przypadków. I nie chodzi tu nawet o to, czy szklanka jest w połowie pusta czy pełna. Pytanie brzmi: czy nie eksploduje z siłą stu słońc, tuż po odjęciu od ust – gdy moja czujność będzie osłabiona delektowaniem się wyraźnym dębowym smakiem z nutkami wanilii i cytrusów.

Czym jest zmiana kontekstu w praktyce?

A to zależy, w jakim kontekście… W omawianym scenariuszu jest to przełączenie się z jednego języka (np. SQL) na drugi (np. PL/SQL) lub odwrotnie. Język PL/SQL ma swój wbudowany silnik, który tylko z pozoru jest w 100% zintegrowany z językiem zapytań. W rzeczywistości za każdym razem, kiedy PL/SQL prosi o jakiś zestaw danych za pomocą SQL lub kiedy SQL potrzebuje przekazać coś do obliczenia językowi proceduralnemu, następuje właśnie ta uprzykrzona zmiana kontekstów.

Jest to ułamek sekundy potrzebny na wyostrzenie wzroku w innej perspektywie lub na powrót z drzemki do rzeczywistości – lub może bardziej trafnie: jest to ten moment między skończeniem ulubionej książki a powrotem do rzeczywistości, w którym nasz mózg czuje się totalnie bezradny w świecie, w którym trafienie kogoś kulą ognia przestaje być opcją. Na poziomie serwera bazodanowego możemy zaobserwować opóźnienia na zmianę kontekstu w dwóch moich ulubionych przypadkach.

1. Z PL/SQL do SQL
Pętla od 1 do 1 000 000, polegająca na podstawieniu jakieś określonej wartości do zmiennej, nie powinna w żadnym języku programowania trwać dłużej niż ułamek sekundy. Zobaczmy jednak co się stanie, jeśli to podstawienie wartości do zmiennej odbędzie się poprzez odpytanie serwera SQL zapytaniem do tabeli DUAL.

 SQL> declare
2 x number;
3 begin
4 for i in 1..1000000 loop
5 select 1 into x from dual;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.08

Czas 20 sekund nie jest wybitnym osiągnięciem i powinien dać nam dużo do myślenia. Dla porównania, usuńmy zapytanie do tabeli DUAL i wykonajmy tę samą pętlę, pozostając w kontekście języka PL/SQL.

SQL>ed
Wrote file afiedt.buf
1 declare
2 x number;
3 begin
4 for i in 1..1000000 loop
5 x:=1;
6 end loop;
7* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

Wygląda troszkę lepiej. Skąd te gigantyczne różnice w czasie? No cóż, jest to wynik właśnie zmian kontekstów. Pojedyncza zmiana kontekstu jest praktycznie niezauważalna, ale efekt skali powoduje kumulację opóźnień, podobnie jak w przypadku kropli ulubionych trunków z piwnicy Dionizosa.

2. Z SQL do PL/SQL

Podobnie sytuacja wygląda, gdy wykorzystujemy własne funkcje SQL w zapytaniach. Załóżmy prostą operację mnożenia.

SQL> get by2
1 create or replace
2 function by2(x number) return number is
3 begin
4 return x*2;
5* end;
SQL> /
SQL> select sum(by2(amount_sold)) from sales_big;
Elapsed: 00:01:59.01

Tak jak w poprzednim przypadku – najwięcej czasu zostało tu skonsumowane na zmianę kontekstu z języka SQL do maszyny PL/SQL.

Jak zdiagnozować skutki zmiany kontekstów?

Serwer bazodanowy Oracle jest wyposażony w fantastyczne narzędzia diagnostyczne, bazujące na tzw. wait eventach, czyli zdarzeniach oczekiwania. Z każdym razem, kiedy nasza sesja oczekuje na jakieś zdarzenie, jest to odnotowane i możemy zobaczyć ten fakt w perspektywach wydajnościowych V$ lub raportach AWR albo STATSPACK.

Przykładowo, jeśli nasza sesja oczekuje na odczyt 128 bloków, które mają zostać następnie wczytane do Buffer Cache, zostanie odnotowane zdarzenie oczekiwania „db file scattered read”.

Dzięki temu mechanizmowi możemy przeprowadzać diagnozę wydajności na bardzo szczegółowym poziomie i szybko wykrywać wąskie gardła naszej aplikacji.

Niestety, nie istnieje żaden wait event oznaczający zmianę kontekstów, więc na pierwszy rzut oka problem jest niediagnozowalny. Czujemy pikantny finisz z nutkami czekolady, kiedy nagle szklanka imploduje, generując niewielką czarną dziurę, co przyjmujemy ze spokojem mistrza Zen.

Na szczęście zawsze możemy sięgnąć po techniki niskopoziomowe, oparte na profilowaniu użycia funkcji C, a będąc bardziej precyzyjnym, do symboli użytych przez śledzone biblioteki na poziomie systemu operacyjnego. Niewielkie dochodzenie z użyciem np. narzędzi perf i gdb pokazuje nam, że za każdym razem – kiedy uruchamiany jest silnik PL/SQL w danej sesji – wykonywana jest funkcja o nazwie „plsql_run”.

Breakpoint ustawiony w gdb na tę właśnie funkcję pokazuje, że jest ona uruchamiana dla każdego wiersza z tabeli źródłowej.

(gdb) info b
Num Type DispEnb Address What
3 breakpoint keep y 0x000000000ce1c090 breakpointalready hit 1918123times
c

Na Linuksie wystarczy więc użyć narzędzia perf, np. dla konkretnego procesu serwera obsługującego podejrzaną sesję, aby ustalić, czy plsql_run nie znajduje się czasem wśród głównych funkcji zużywających czas procesora.

Badanie zmian kontekstów z PL/SQL do SQL opiera się na podobnej zasadzie. Trzeba wyśledzić funkcję C, która ma największe tendencje do zużywania zasobów CPU. W tym przypadku jest to funkcja opifch2, która odpowiada za operację FETCH z poziomu języka SQL do klienta. Niekoniecznie musi to być PL/SQL.

Badanie funkcji opifch2 z użyciem narzędzia gdb pokazuje, że rejestr RCX na procesorach x86_64 przechowuje wartość, oznaczającą liczbę wierszy zażądanych jednorazowo z silnika SQL w trakcie realizacji operacji FETCH.

Założenie breakpointa z badaniem stanu rejestru RCX dla każdego wykonania funkcji wygląda tak:

[root@rico~]#gdb -p 5543
(gdb) b opifch2
Breakpoint 1 at 0xcb0acb0
(gdb) command 1
Type commands for breakpoint(s) 1, one per line.
End with a line saying just “end”.
>p/d $rcx
>c
>end
(gdb) set pagination off
(gdb) c
Continuing.

Wykonanie bloku anonimowego z żądaniem przechwytywania 25 rekordów naraz z użyciem funkcjonalności BULK COLLECT:

SQL> ;
1 declare
2 cursor c_sql is
3 select salary from employees;
4 type t_sql is table of c_sql%ROWTYPE index by pls_integer;
5 v_sqlt_sql;
6 v_x number;
7 begin
8 open c_sql;
9 loop
10 fetch c_sql bulk collect into v_sql limit 25;
11 exit when c_sql%NOTFOUND;
12 for i in v_sql.first..v_sql.last loop
13 v_x:=v_x+v_sql(i).salary;
14 end loop;
15 end loop;
16 close c_sql;
17* end;
SQL> /

Po wykonaniu tego bloku anonimowego, możemy zaobserwować w gdb następujące zachowanie:

Breakpoint 1, 0x000000000cb0acb0 in opifch2 ()
$1 = 140159623635016
Breakpoint 1, 0x000000000cb0acb0 in opifch2 ()
$2 = 25
Breakpoint 1, 0x000000000cb0acb0 in opifch2 ()
$3 = 25
Breakpoint 1, 0x000000000cb0acb0 in opifch2 ()
$4 = 25
Breakpoint 1, 0x000000000cb0acb0 in opifch2 ()
$5 = 25
Breakpoint 1, 0x000000000cb0acb0 in opifch2 ()
$6 = 25

Po dokładniejszych obserwacjach okazuje się, że każda pętla kursorowa w PL/SQL jest transformowana automatycznie na składnie BULK COLLECT LIMIT 100. Dzięki tej wiedzy możemy zbadać globalne zachowanie sesji na naszej maszynie.

[root@rico~]# perf probe -x /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle opifch2 fetchsize=%cx
Added new event:
probe_oracle:opifch2 (on 0xc70acb0 with fetchsize=%cx)
You can now use it in all perf tools, such as:
perf record -e probe_oracle:opifch2 -aR sleep 1
[root@rico~]# perf record -e probe_oracle:opifch2 -aR
^C[ perf record: Woken up 149 times to write data ] [ perf record: Captured and wrote 37.395 MB perf.data (~1633825 samples) ] [root@rico~]# perf script > opifch2.out

Krótki skrypt AWK zobrazuje nam rozkład liczby operacji FETCH dla wszystkich śledzonych procesów serwera.

[root@rico~]#awk ‘/oracle_/ {fsize=strtonum(“0x”substr($NF,11));fsize_tab[$1 ” was “fsize]++} END {for(fs in fsize_tab) {print “Fetchsize for ” fs ” (executed ” fsize_tab[fs] ” times)”}}’ opifch2.out | sort -n -k7
Fetchsize for oracle_5857_orc was 2 (executed 1 times)
Fetchsize for oracle_5857_orc was 2147483647 (executed 1 times)
Fetchsize for oracle_5857_orc was 100 (executed 2 times)
Fetchsize for oracle_5857_orc was 140171241660488 (executed 2 times)
Fetchsize for oracle_5857_orc was 1 (executed 4 times)
Fetchsize for oracle_5857_orc was 227183808 (executed 4 times)
Fetchsize for oracle_5857_orc was 25 (executed 5 times)
Fetchsize for oracle_5755_orc was 2 (executed 22 times)
Fetchsize for oracle_5755_orc was 140058172228192 (executed 36 times)
Fetchsize for oracle_5543_orc was 100 (executed 441045 times)

 
Prawidłowe projektowanie systemów na bazie środowiska Oracle

Powyższa technika pozwala nam zdiagnozować istnienie problemu pod tytułem „zmiana kontekstów”. Jednak od diagnozy do uleczenia pozostaje długa droga. W tym przypadku, niestety, jedynym rozwiązaniem jest zmiana kodu. Tricki w postaci zmiany parametrów czy innych sztuczek administracyjnych pozostają bezskuteczne.

Prawidłowe projektowanie systemów opartych na środowisku bazodanowym Oracle jest kluczowe dla wydajności oraz bezpieczeństwa. Niestety, poza rozległymi traktatami quasi-filozoficznymi, seriami szkoleń i uczeniem się na własnych błędach – trudno jest pozyskać odpowiednią wiedzę.

Na szczęście istnieją weterani wojen o wydajność, którzy chętnie się dzielą wieloletnią wiedzą, a robią to zwykle na konferencjach rozsianych po całym świecie. Jedna z nich będzie po raz pierwszy w Polsce w dniach 7-8 października 2016 roku. Na konferencji POUG (Pint With Oracle User Group) w Warszawie zbiorą się eksperci z całego świata, chętni do podzielenia się swoimi doświadczeniami i spostrzeżeniami, po latach spędzonych jako administratorzy, programiści i konsultanci Oracle. Nastawcie się więc na przyswajanie pokładów wiedzy, których zużycie będzie trudniejsze niż światowych zapasów cynizmu.

Kamil Stawiarski,
ORACLE ACE ASSOCIATE, organizator konferencji POUG. Jest to pierwsza, międzynarodowa konferencja użytkowników Oracle w Polsce. Przy organizacji i promocji tego wydarzenia pomagają przedstawiciele innych europejskich grup Oracle: UKOUG Scotland, BGOUG, HOUG.
Więcej na stronie http://poug.org/