خوب کردن حالا پایگاه داده اوراکل و سامانه ها کاربردی با استفاده از Bind Variable

مقدمه :اگر شما تجربه نوشتن برنامه هایی رو داشته باشید که با پایگاه داده اوراکل کار کرده باشند می دانید که استفاده از 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 a60select sql_text,executionsfrom v$sqlareawhere instr(sql_text, 'select * from dual where dummy') > 0and instr(sql_text, 'sql_text') = 0order by sql_text;SQL_TEXT EXECUTIONS------------------------------------------------------------ ----------select * from dual where dummy = 'LITERAL1' 1select * from dual where dummy = 'LITERAL2' 12 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_VARIABLE1old 1: select * from dual where dummy = '&dummy'new 1: select * from dual where dummy = 'SUBSTITUTION_VARIABLE1'no rows selectedSQL> select * from dual where dummy = '&dummy';Enter value for dummy: SUBSTITUTION_VARIABLE2old 1: select * from dual where dummy = '&dummy'new 1: select * from dual where dummy = 'SUBSTITUTION_VARIABLE2'no rows selectedSQL>column sql_text format a60select sql_text,executionsfrom v$sqlareawhere instr(sql_text, 'select * from dual where dummy') > 0and instr(sql_text, 'sql_text') = 0order by sql_text;SQL_TEXT EXECUTIONS------------------------------------------------------------ ----------select * from dual where dummy = 'SUBSTITUTION_VARIABLE1' 1select * from dual where dummy = 'SUBSTITUTION_VARIABLE2' 12 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 a60select sql_text,executionsfrom v$sqlareawhere instr(sql_text, 'select * from dual where dummy') > 0and instr(sql_text, 'sql_text') = 0order by sql_text;SQL_TEXT EXECUTIONS------------------------------------------------------------ ----------select * from dual where dummy = :dummy 21 row selected.SQL>حال با فرض مواردی که در بالا به آنها اشاره به تفاوتی که در کارایی ایجاد می شود می پردازیم.در ابتدا Literal ها :در نمونه کد زیر یک dynamic query ایجاد می شود که با 10 مقدار مختلف با ساختار Literal اجرا می شود. مقدار زمان cpu از نوع Parse (hard و soft ) را بدست آورده ایم (مبنای واحد عدد خروجی 10 میلی ثانیه می باشد).conn sys/password as sysdbaalter system flush shared_pool;conn sys/password as sysdbadeclarel_dummy dual.dummy%type;beginfor i in 1 .. 10 loopbeginexecute immediate 'select dummy from dual where dummy = ''' || to_char(i) || ''''into l_dummy;exceptionwhen no_data_found thennull;end;end loop;end;/PL/SQL procedure successfully completed.SQL>select sn.name, ms.valuefrom v$mystat ms, v$statname snwhere ms.statistic# = sn.statistic#and sn.name = 'parse time cpu';NAME VALUE---------------------------------------------------------------- ----------parse time cpu 631 row selected.SQL>column sql_text format a60select sql_text,executionsfrom v$sqlareawhere instr(sql_text, 'select dummy from dual where dummy') > 0and instr(sql_text, 'sql_text') = 0and instr(sql_text, 'declare') = 0order by sql_text;SQL_TEXT EXECUTIONS------------------------------------------------------------ ----------select dummy from dual where dummy = '1' 1select dummy from dual where dummy = '10' 1select dummy from dual where dummy = '2' 1select dummy from dual where dummy = '3' 1select dummy from dual where dummy = '4' 1select dummy from dual where dummy = '5' 1select dummy from dual where dummy = '6' 1select dummy from dual where dummy = '7' 1select dummy from dual where dummy = '8' 1select dummy from dual where dummy = '9' 110 rows selected.SQL>همانطور که مشاهده می کنید از نگاه اوراکل، 10 دستور جداگانه با مجموع زمان CPU 630 ثانبه اجرا شده است.حالا همین دستورات را با ساختار Bind Variable اجرا می کنیم.conn sys/password as sysdbaalter system flush shared_pool;conn sys/password as sysdbadeclarel_dummy dual.dummy%type;beginfor i in 1 .. 10 loopbeginexecute immediate 'select dummy from dual where dummy = to_char(:dummy)'into l_dummy using i;exceptionwhen no_data_found thennull;end;end loop;end;/PL/SQL procedure successfully completed.SQL>select sn.name, ms.valuefrom v$mystat ms, v$statname snwhere ms.statistic# = sn.statistic#and sn.name = 'parse time cpu';NAME VALUE---------------------------------------------------------------- ----------parse time cpu 401 row selected.SQL>column sql_text format a60select sql_text,executionsfrom v$sqlareawhere instr(sql_text, 'select dummy from dual where dummy') > 0and instr(sql_text, 'sql_text') = 0and instr(sql_text, 'declare') = 0order by sql_text;SQL_TEXT EXECUTIONS------------------------------------------------------------ ----------select dummy from dual where dummy = to_char(:dummy) 101 row selected.SQL>همانطور که مشاهده می کنید از نگاه اوراکل یک دستور 10 مرتبه با زمان CPU 400 ثانیه اجرا شده است!!Bind Variable و مقابله با Sql Injection:یکی از راه های نفوذ به سامانه ها Sql injection می باشد. در این حالت دستوراتی را به عنوان ورودی به سامانه منتقل می کنند و با اجرای آن خروجی مد نظر خود را دریافت می کنند، استفاده از Bind Variable باعث می شود ساختار دستورات همیشه ثابت باشد و ورودی صرفا به عنوان مقداری به پایگاه داده منتقل گردد.