Zmienne bindowane

 

W Oracle funkcjonują zmienne bindowane, powiązane z sesją. Możemy wprowadzić do takiej
zmiennej wartość, a następnie wykonać zapytanie z użyciem tej zmiennej. Jakie to ma znaczenie
przy strojeniu SQL?
 
Jeżeli wykonujemy wiele podobnych zapytań:
 
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=100;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=104;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=107;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=109;
 
dla każdego z tych zapytań wymyślany jest osobny plan wykonania. To nie jest dobre z
przynajmniej dwóch powodów:
– Oracle musi poświęcić czas i zasoby na analizę i opracowanie explain planu (planu
wykonania).
– Zapełniana jest przestrzeń w shared_pool w sumie niemal identycznymi zapytaniami – a
właściwie to związanymi z nimi planami wykonania.
 
Sytuacja taka jest dość powszechna. Wykorzystujemy słowniki, a te jako z reguły intensywnie
eksploatowane powinny być wykorzystywane w sposób możliwie najbardziej optymalny.
Poszukaj w słowniku v$sql zapytań o podobnej konstrukcji np.
select * from v$sql where lower(sql_text) like '%from%employees%';
Dzięki temu sprawdzisz czy taka sytuacja ma również miejsce u Ciebie.
 
Aby się przed tym uchronić możemy nieco zmodyfikować zapytania. Przede wszystkim tworzymy
zmienną bindowaną :
variable x number;
następnie przypisz do niej wartość:
begin
:x:=100;
end;
 
Pamiętaj że nazwy zmiennych bindowanych przy przypisaniu i odwołaniu poprzedzamy
dwukropkiem. Teraz możesz odwołać się do tej zmiennej w zapytaniach:
 
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:X;
 
Pamiętaj że wartość zmiennej możesz zmieniać dowolną ilość razy. Jednak niezależnie od tego, dla
optymalizatora kosztowego będzie to to samo zapytanie. Będzie mógł więc wykorzystać
wcześniejszy plan wykonania. Na tym polega „trick”. Po prostu zamiast produkować właściwie
identyczne zapytania różniące się tylko jedną wartością, korzystamy ze zmiennej bindowanej i
wykonujemy zapytanie z jej użyciem. Podmieniamy tylko wartość tej zmiennej.
Strojenie SQL w Oracle 53/58
Zmienne bindowane a CURSOR_SHARING
 
Możesz ustawić parametr cusor_sharing na force:
 
ALTER SESSION SET CURSOR_SHARING=FORCE;
ALTER SYSTEM SET CURSOR_SHARING=FORCE;
 
dzięki czemu w przypadku takich drobnych różnic literalnych (jak employee_id w poprzednich
przykładach), Oracle sam sobie stworzy zmienną bindowaną na potrzeby tej wartości i będzie
działał tak, jakbyśmy stworzyli zmienną bindowaną i podstawiali jej wartości.
W przypadku ustawienia CURSOR_SHARING na SIMILAR i jednoczesnym korzystaniu ze
zmiennych bindowanych, Oracle będzie traktował tak samo dwa zapytania o takiej budowie:
 
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:x;
 
ponieważ employee_id jest kolumną na którą jest założony klucz główny. Jakakolwiek wartość trafi
do zmiennej bindowanej X, sens zapytania i sposób wykonania się nie zmienią. Zawsze uzyskamy
jeden wiersz. W przypadku takiego zapytania:
 
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=:x;
 
sens zapytania w zależności od wartości X może ulec zmianie. Może być wiele wierszy które mają
daną wartość w kolumnie department_id, a ich liczebność oraz rozłożenie może ulec zmianie w
zależności od wartości x.