قبلا در مقاله ای در مورد cursor sharing و bind variable مطالبی را ارائه کردیم و فواید و مخاطرات استفاده از bind variable را توضیح دادیم.

همانطور که در آن مطلب اشاره شد، زمانی که پارامتر cursor_sharing به مقدار exact تنظیم شده و در کنار آن از bind variable هم استفاده نشده باشد، با کمترین تغییر در متن دستور(به طور مثال تغییر مقدار id از 10 به 11)، برای اجرای بعدی آن، نیاز به پارس مجدد خواهیم داشت. این مسئله می تواند کندی اجرای دستور و یا حتی در مواردی کندی کلی دیتابیس را به همراه داشته باشد.
برای مثال، در شرایط گفته شده، با اجرای متوالی دو دستور زیر، دو بار عملیات پارس انجام خواهد شد:

delete mytbl where file#=1 and ts#=0;

delete mytbl where file#=1 and ts#=2;

در صورتی که با تبدیل آن به فرم زیر، دستورات با یک بار پارس شدن اجرا می شوند:

delete mytbl where file#=:B1 and ts#=:B2;

با در نظر داشتن این مسئله و نکاتی که در مطلب cursor sharing و bind variable ارائه شد، استفاده از bind variable در دیتابیس اوراکل، می تواند به پرفورمنس دیتابیس کمک کند.

نکته ای که در این مطلب قصد داریم به آن بپردازیم، چگونگی تعیین مقادیر متناظر استفاده شده برای bind variableها می باشد.

توجه: مقادیر مربوط به bind variableها برای هر session در فضای اختصاصی اش یعنی PGA قرار می گیرند و در نهایت در SGA هم ثبت خواهند شد تا از طریق dynamic performance viewها قابل رویت باشند.

قبل از آشنایی با ویوهای موجود در این زمینه، دستوری را با کمک bind variable اجرا می کنیم:

SQL> variable B1 number;

SQL> variable B2 number;

SQL> exec :B1:=1;

PL/SQL procedure successfully completed.

SQL> exec :B2:=0;

PL/SQL procedure successfully completed.

SQL> delete mytbl where file#=:B1 and ts#=:B2;

1572864 rows deleted

SQL> commit;

Commit complete

بعد از اجرای دستور delete فوق، خواهیم دید که  مقادیر متناظر bind variableها در ویوی v$sql قرار نمی گیرند:

SQL> select p.SQL_FULLTEXT,p.SQL_ID from v$sql p where p.SQL_TEXT like ‘%delete mytbl where %’ and SQL_TEXT not like ‘%v$sql%’;

در خروجی بالا می بینید که خبری از مقادیر متناظر با متغیرهای B1 و B2 در متن دستور delete نیست.

همانطور که قبلا هم اشاره شد، یکی از راهکارهای پیدا کردن مقادیر متناظر با bind variableها استفاده از dynamic performance viewها می باشد. البته اگر دستور به تازگی اجرا شده باشد!! و متن آن دستور از حافظه خارج نشده باشد!!

در این صورت برای یافتن مقادیر متناظر با bind variableها، می توانیم از داینامیک پرفورمنس ویوی v$sql_bind_capture و V$SQL_MONITOR استفاده کنیم:

SQL> SELECT sql_id, b.name BIND_NAME, b.value_string BIND_STRING  from gv$sql_bind_capture b WHERE  value_string IS NOT NULL   AND sql_id=’64nbj9y7ys99n’;

 

SQL> select BINDS_XML from V$SQL_MONITOR WHERE sql_id=’64nbj9y7ys99n’;

<binds><bind name=”:B1” pos=”1″ dty=”2″ dtystr=”NUMBER” maxlen=”22″ len=”2″>1</bind>

<bind name=”:B2” pos=”2″ dty=”2″ dtystr=”NUMBER” maxlen=”22″ len=”1″>0</bind></binds>

توجه: ویوی V$SQL_MONITOR بازه محدودتری از دستورات را پشتیبانی می کند و به دلیل کندی احتمالی، بیشتر اطلاعاتی را در مورد long running queryها نمایش خواهد داد.

اگر اطلاعات دستور اجرا شده هنوز در حافظه موجود باشد، علاوه بر دو ویوی ذکر شده، استفاده از روش زیر هم برای پیدا کردن مقدار bind variable قابل استفاده است:

SQL> select CHILD_NUMBER from v$sql where sql_id=’64nbj9y7ys99n’;

CHILD_NUMBER

————

           0

SQL> select * from table (dbms_xplan.display_cursor(’64nbj9y7ys99n’,0, format => ‘TYPICAL +PEEKED_BINDS‘));

 

در صورتی که از اجرای دستور، مدت زمان زیادی گذشته باشد و در عین حال بک گراند پروسس MMON این دستور را به عنوان top sql تشخیص داده و به دیسک منتقل کرده باشد، می توانیم از ویوی DBA_HIST_SQLBIND برای یافتن مقادیر bind variableهای این دستور استفاده کنیم.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed

SQL> SELECT name,s.POSITION,s.VALUE_STRING from dba_hist_sqlbind s WHERE sql_id=’64nbj9y7ys99n’;

 

بیرون راندن دستورات از حافظه ممکن است به دلایلی چون کمبود فضای shared pool و یا اجرای دستور alter system flush shared_pool و … رخ دهد که در این صورت هم باز می توان از ویوی فوق (dba_hist_sqlbind) استفاده کرد:

SQL> alter system flush shared_pool;

System altered

SQL> select p.SQL_FULLTEXT,p.SQL_ID from v$sql p where p.SQL_TEXT like ‘%delete mytbl where %’ and SQL_TEXT not like ‘%v$sql%’;

no rows selected

SQL> SELECT sql_id, b.name BIND_NAME, b.value_string BIND_STRING  from gv$sql_bind_capture b WHERE  value_string IS NOT NULL   AND sql_id = ’64nbj9y7ys99n’;

no rows selected

 SQL> SELECT name,s.POSITION,s.VALUE_STRING,DATATYPE_STRING from dba_hist_sqlbind s WHERE sql_id=’64nbj9y7ys99n’;

 

نکته پایانی: اوراکل به دلیل جلوگیری از کندی دیتابیس، همه مقادیر متناظر با bind variable ها را ثبت نمی کند و این کار را در بازه زمانی مشخص و به صورت sample در سطح cursor انجام خواهد داد پس با روشهای ذکر شده، ممکن است برای بعضی از دستورات اطلاعاتی ثبت نشده باشد.

بنابرین برای ثبت حتمی این دسته از اطلاعات(مقادیر متناظر با bind variableها)، می توان تریس(trace) را در یکی از سطوح session، system و statement فعال کرد:

فعال کردن تریس در سطح session:

SQL> alter session set events ‘10046 trace name context forever, level 12‘;

Session altered

مثال:

SQL> variable A1 number;

SQL> exec :A1:=5874;

PL/SQL procedure successfully completed

SQL> delete mytbl where file#=:A1;

0 rows deleted

 [root@ol7 ~]# tail -f  /18c/base/diag/rdbms/db18c/db18c/trace/db18c_ora_20917.trc

delete mytbl where file#=:A1

Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=7f63b1241fe8  bln=22  avl=03  flg=05

  value=5874

فعال کردن تریس برای یک session مشخص:

SQL> exec dbms_monitor.session_trace_enable(session_id => 384,serial_num => 22963,waits => TRUE,binds => TRUE);

PL/SQL procedure successfully completed