Oracle SQL - Bind variables (zmienne bindowane) vs literals (łańcuchy znaków)

Instancja bazy Oracle przechowuje w swoich przepastnych, ale skończonych zasobach pamięci ;) mnóstwo ciekawych informacji. Do wielu z nich mamy dostęp przez widoki v$. Ich listę możemy przejrzeć w specjalnych perspektywach dictionary oraz fixed_table.

select * from dictionary;
select * from fixed_table;

Jedną z ciekawszych informacji, którą można wydobyć z tych perspektyw to lista aktualnych zapytań do bazy danych (historyczne również są do "wydobycia").

select * from v$sql;
/* lub */
select * from v$sqlarea;

Odpowiednie grupowanie pozwala na sprawdzenie czy przypadkiem pamięć nie przechowuje 2 różnych zapytań z takim samym planem wykonania.

select sql.plan_hash_value, count(*) ile
from v$sql sql
group by sql.plan_hash_value 
having count(*) > 1
order by ile desc;

Sytuacja w której w/w zapytanie coś zwróci pośrednio świadczy o tym, że parsowanie odbywało się na zapytaniu, w którym zamiast zmiennych bindowanych programiści dynamicznie przyklejali konkatenacją wartości wyszukiwane do słów kluczowych. Np

'select t.id, t.wartosc from tabelka t 
where t.id' || '>' || to_char(100)

Aby sprawdzić co to za SQL postawiamy plan, który odnaleźliśmy powyżej do kolejnego zapytania

select sa.sql_text 
from v$sql_area
where plan_hash_value = ...;

Konsekwencje takiego lenistwa są straszne, baza danych "myśli", że otrzymała całkowicie nowe zapytanie i wrzuca je do parsowania. Proces parsowania zapytań jest bardzo kosztowny, szczególnie dla skomplikowanych i długich zapytań.

Ostatecznie po parsowaniu okaże się, że plan zapytania jest identyczny jak w jakimś poprzednio analizowanym przypadku, jednak na to instancja bazy już wpływu nie ma. W efekcie pamięć zapełnia się bezsensownymi wpisami a procesor zajmuje się w kółko przeliczaniem tego samego.

Sytuacja ma jeszcze jeden minus. Zapychanie pamięci SGA (konkretnie Shared Pool) przeznaczonym na SQL-e tym samym zapytaniem powoduje, że wypadają z niej zapytania rzadziej wykonywane, często bardzo kosztowne.

Proces development-u powinien uwzględniać poniżej opisane sytuacje cyklicznie wykonywanych tych samych zapytań.

Niepoprawny pod względem wydajności cykl wykonywania podobnych zapytań

CONNECT
PARSE
  BIND
  EXEC
  FETCH
DISCONNECT
CONNECT
PARSE
  BIND
  EXEC
  FETCH
DISCONNECT
...

Wydajny na bazie Oracle cykl wykonywania podobnych zapytań

CONNECT
PARSE
  BIND
  EXEC
  FETCH
  BIND
  EXEC
  FETCH
...
DISCONNECT

SQL Injections

Kolejną przewagą bind-a nad literałami jest bezpieczeństwo danych. Zapytanie ze zmiennymi bindowanymi jest parsowane przed podstawieniem wartości. Co to daje? Jeśli jakiś osobnik próbowałby podstawić do zapytania

select count(*) from appuser 
where user='xyz' and password='abc';

coś w stylu

select count(*) from appuser 
where user='xyz' and password='abc' or '1'='1';

To w przypadku binda zupełnie się to nie sprawdzi. Parsowanie nastąpi wcześniej niż postawienie tych danych i najzwyczajniej baza będzie przeszukiwać kolumnę password szukając ciągu: abc' or '1'='1. Wynikiem zapytania będzie 0 więc w tym przykładzie bardzo uproszczonej weryfikacji użytkownika nie udało by się dostać do konta użytkownika xyz.

cursor sharing

Istnieje możliwość wymuszenia użycia planów zapytania dla podobnych zapytań nawet gdy zmienne nie są bindowane a wysyłane do bazy jako literały (sklejone z resztą). Mechanizm ten nazywa się cursor_sharing.

alter system set cursor_sharing = force;

Opcja ta wymusi, że przed procesem parsowania wszystkie literały zostaną przekształcone w parametry :1 :2 itp. I dopiero wtedy baza sprawdzi sobie czy już takiego zapytania przypadkiem nie było.

Niestety baza nie potrafi wtedy stwierdzić czy jest to rozwiązanie optymalne. Jeśli bowiem zmienną była płeć to plan zapytania dla kobiet może być zupełnie inny niż dla mężczyzn (np w populacji żołnierzy służby zasadniczej). Efekt może okazać się katastrofalny pod względem przyrostu czasu wykonania w tego typu zapytaniach.

W bazie Oracle 10g domyślnie jest ustawione exact czyli, żeby nie odbyło się parsowanie zapytania powinny być identyczne

CURSOR_SHARING = EXACT

W efekcie posługiwanie się literałami przy budowaniu ma niewiele wspólnego z wydajnością. Z przejrzystością kodu źródłowego może być podobnie...

Dość ciekawym pomysłem jest ustawienie CURSOR_SHARING w opcji SIMILAR - wtedy optymaliztor Oracle w pseudo-inteligentny sposób będzie lawirował pomiędzy EXACT a FORCE. Jednak wiąże się to z bardzo silnym obciążeniem procesora a to kłóci się z wydajnością. No chyba, że mamy spory zapas na CPU. Wtedy można spróbować

Oracle PL/SQL

Pozytywnym aspektem całej sprawy jest to, że wszystkie literały używane w procedurach pl/sql-owych są automatycznie zamieniane na zmienne bindowane co w znaczący sposób podnosi komfort programowania.

2010.03.28 11:48:35.