مقدمه :
اگر شما تجربه نوشتن برنامه هایی رو داشته باشید که با پایگاه داده اوراکل کار کرده باشند می دانید که استفاده از Bind variable ها در اوراکل از دو نظر زیر از اهمیت زیادی برخوردار هستند:
- افزایش کارایی
- بهبود امنیت
در این مستند این دو موضوع را مورد بررسی قرار می دهیم.
چرا Bind Variable :
هر زمان که یک دستور به سمت پایگاه داده اوراکل ارسال می شود، در ابتدا بررسی می شود که آیا این دستور در فضای Shared Pool وجود دارد یا خیر( اخیرا این دستور اجرا شده ؟). اگر وجود داشته باشه کارساده تر می شود و فقط احتیاج به یک Soft Parse می باشد در غیر اینصورت باید یک Hard Parse که همراه با استفاده منابع می باشد اجرا گردد. هر تغییر کوچکی در ساختار نوشتاری دستورات، منجر به این می شود که اوراکل آن را دستور جدید در نظر بگیرد و عملا Hard Parse هم اتفاق بیفتد. مخصوصا در حوزه دستوراتی که از سمت سامانه ها ارسال می شوند . این دستورات عملا یکسان و از قبل تعریف شده هستند که فقط به دلیل قرار گرفتن مقدار متغییر ها در قسمت Where به عنوان دستور جدید شناسایی می شوند. برای مقابله با این مشکل و همچنین مقابله با Sql Injection، استفاده از Bind Variable ها به شدت کارا و تاثیر گذارند.
در ادامه با اجرای چند سناریو تفاوت ها را مورد بررسی قرار می دهیم.
استفاده از Literals در دستورات:
عبارت ها در شروط و دستورات منجر به این می شوند که اوراکل دستورات مربوطه را به عنوان دستور جدید شناسایی کند. به دستورات زیر توجه کنید.
برای بررسی بهتر نتایج، قبل از اجرای دستورات ابتدا فضای Shared Pool را پاک می کنیم . ( در محیط عملیاتی، بدون بررسی این عمل را انجام ندهید)
alter system flush shared_pool;
select * from dual where dummy = ‘LITERAL1’;
select * from dual where dummy = ‘LITERAL2’;
column sql_text format a60
select sql_text,
executions
from v$sqlarea
where instr(sql_text, ‘select * from dual where dummy’) > 0
and instr(sql_text, ‘sql_text’) = 0
order by sql_text;
SQL_TEXT EXECUTIONS
———————————————————— ———-
select * from dual where dummy = ‘LITERAL1’ 1
select * from dual where dummy = ‘LITERAL2’ 1
2 rows selected.
SQL>
همانطور که مشاهده می کنید هر دستور به صورت جداگانه اجرا شده ( اوراکل هر کدام را یک دستور جدید در نظر گرفته )
استفاده از Substitution Variables در دستورات:
Substitution Variables تاثیر گذاری همانند Literal ها دارند. به نمونه کد در ادامه توجه کنید.
SQL> alter system flush shared_pool;
System altered.
SQL> select * from dual where dummy = ‘&dummy’;
Enter value for dummy: SUBSTITUTION_VARIABLE1
old 1: select * from dual where dummy = ‘&dummy’
new 1: select * from dual where dummy = ‘SUBSTITUTION_VARIABLE1’
no rows selected
SQL> select * from dual where dummy = ‘&dummy’;
Enter value for dummy: SUBSTITUTION_VARIABLE2
old 1: select * from dual where dummy = ‘&dummy’
new 1: select * from dual where dummy = ‘SUBSTITUTION_VARIABLE2’
no rows selected
SQL>
column sql_text format a60
select sql_text,
executions
from v$sqlarea
where instr(sql_text, ‘select * from dual where dummy’) > 0
and instr(sql_text, ‘sql_text’) = 0
order by sql_text;
SQL_TEXT EXECUTIONS
———————————————————— ———-
select * from dual where dummy = ‘SUBSTITUTION_VARIABLE1’ 1
select * from dual where dummy = ‘SUBSTITUTION_VARIABLE2’ 1
2 rows selected.
SQL>
همانطورکه مشاهده می کنید این دستورات را 2 دستور جداگانه در نظر گرفته است.
استفاده از Bind Variable ها و تاثیرات آنها :
همانطور که در ادامه مشاهده می کنید، هنگامی که از Bind Variable ها استفاده می شود، دستورات در Shared Pool یکسان در نظر گرفته می شود و فقط تعداد اجرای آنها در نظر گرفته می شود. در این حالت اوراکل از Soft Parse استفاده می کند. به نمونه کد در ادامه توجه کنید.
alter system flush shared_pool;
variable dummy varchar2(30);
exec :dummy := ‘BIND_VARIABLE1’;
select * from dual where dummy = :dummy;
exec :dummy := ‘BIND_VARIABLE2’;
select * from dual where dummy = :dummy;
column sql_text format a60
select sql_text,
executions
from v$sqlarea
where instr(sql_text, ‘select * from dual where dummy’) > 0
and instr(sql_text, ‘sql_text’) = 0
order by sql_text;
SQL_TEXT EXECUTIONS
———————————————————— ———-
select * from dual where dummy = :dummy 2
1 row selected.
SQL>
حال با فرض مواردی که در بالا به آنها اشاره به تفاوتی که در کارایی ایجاد می شود می پردازیم.
در ابتدا Literal ها :
در نمونه کد زیر یک dynamic query ایجاد می شود که با 10 مقدار مختلف با ساختار Literal اجرا می شود. مقدار زمان cpu از نوع Parse (hard و soft ) را بدست آورده ایم (مبنای واحد عدد خروجی 10 میلی ثانیه می باشد).
conn sys/password as sysdba
alter system flush shared_pool;
conn sys/password as sysdba
declare
l_dummy dual.dummy%type;
begin
for i in 1 .. 10 loop
begin
execute immediate ‘select dummy from dual where dummy = ”’ || to_char(i) || ””
into l_dummy;
exception
when no_data_found then
null;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL>
select sn.name, ms.value
from v$mystat ms, v$statname sn
where ms.statistic# = sn.statistic#
and sn.name = ‘parse time cpu’;
NAME VALUE
—————————————————————- ———-
parse time cpu 63
1 row selected.
SQL>
column sql_text format a60
select sql_text,
executions
from v$sqlarea
where instr(sql_text, ‘select dummy from dual where dummy’) > 0
and instr(sql_text, ‘sql_text’) = 0
and instr(sql_text, ‘declare’) = 0
order by sql_text;
SQL_TEXT EXECUTIONS
———————————————————— ———-
select dummy from dual where dummy = ‘1’ 1
select dummy from dual where dummy = ’10’ 1
select dummy from dual where dummy = ‘2’ 1
select dummy from dual where dummy = ‘3’ 1
select dummy from dual where dummy = ‘4’ 1
select dummy from dual where dummy = ‘5’ 1
select dummy from dual where dummy = ‘6’ 1
select dummy from dual where dummy = ‘7’ 1
select dummy from dual where dummy = ‘8’ 1
select dummy from dual where dummy = ‘9’ 1
10 rows selected.
SQL>
همانطور که مشاهده می کنید از نگاه اوراکل، 10 دستور جداگانه با مجموع زمان CPU 630 ثانبه اجرا شده است.
حالا همین دستورات را با ساختار Bind Variable اجرا می کنیم.
conn sys/password as sysdba
alter system flush shared_pool;
conn sys/password as sysdba
declare
l_dummy dual.dummy%type;
begin
for i in 1 .. 10 loop
begin
execute immediate ‘select dummy from dual where dummy = to_char(:dummy)’
into l_dummy using i;
exception
when no_data_found then
null;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL>
select sn.name, ms.value
from v$mystat ms, v$statname sn
where ms.statistic# = sn.statistic#
and sn.name = ‘parse time cpu’;
NAME VALUE
—————————————————————- ———-
parse time cpu 40
1 row selected.
SQL>
column sql_text format a60
select sql_text,
executions
from v$sqlarea
where instr(sql_text, ‘select dummy from dual where dummy’) > 0
and instr(sql_text, ‘sql_text’) = 0
and instr(sql_text, ‘declare’) = 0
order by sql_text;
SQL_TEXT EXECUTIONS
———————————————————— ———-
select dummy from dual where dummy = to_char(:dummy) 10
1 row selected.
SQL>
همانطور که مشاهده می کنید از نگاه اوراکل یک دستور 10 مرتبه با زمان CPU 400 ثانیه اجرا شده است!!
Bind Variable و مقابله با Sql Injection:
یکی از راه های نفوذ به سامانه ها Sql injection می باشد. در این حالت دستوراتی را به عنوان ورودی به سامانه منتقل می کنند و با اجرای آن خروجی مد نظر خود را دریافت می کنند، استفاده از Bind Variable باعث می شود ساختار دستورات همیشه ثابت باشد و ورودی صرفا به عنوان مقداری به پایگاه داده منتقل گردد.
