Używanie zmiennych bindowanych

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.

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.