cursor sharing و bind variable

وقتی که اوراکل دستوری را اجرا می کند، سعی دارد تا فرم قابل اجرای دستور اجرا شده(که plan آن مشخص شده) را در library cache نگهدارد تا در صورت امکان از آن استفاده مجدد کند حال اگر این فرم پارس شده برای دستور دیگری مورد استفاده قرار بگیرد می گویند یک soft parse انجام شده است و در صورتی که اوراکل مجبور شود یک فرم جدید قابل اجرا(که در library cache موجود نیست) برای دستور وارده بسازد، hard parse صورت گرفته است.

شرایطی را در نظر بگیرید که دو دستور  که یکی قبلا اجرا شده و فرم قابل اجرای آن در داخل library cache قرار دارد و دیگری تازه توسط کاربر صادر شده، به هم شباهت دارند ولی کاملا یکسان نیستند حال تکلیف چیست؟ می شود از فرم قابل اجرای موجود در حافظه استفاده کرد یا نه؟ به طور مثال دو دستور زیر را در نظر بگیرید:

select * from usef_pars where code=5;

select * from usef_pars where code=6;

همانطور که می بینید این دو دستور تنها در مقدار از هم متمایز می شوند ولی از لحاظ تعداد کارکتر، بزرگی و کوچکی حروف، تعداد space و .... دقیقا یکسان هستند در این حالت معمولا باید از برنامه نویس خواست تا به جای عدد از متغیر استفاده کند تا دو دستور کاملا یکسان شوند و فرم پارس شده هر دستور، برای دیگری قابل استفاده باشد ولی باز با این حال، بانک اطلاعاتی  پارامتری به نام cursor_sharing  دارد که می تواند در این زمینه موثر باشد. ابتدا باید منظور از cursor را مشخص کنیم.

cursor یک ناحیه حافظه در library cache می باشد که به یک دستور SQLای تخصیص داده می شود و اطلاعات مختلفی از آن دستور از قبیل execution plan و statistics را در خودش ذخیره می کند. معمولا هر دستور SQLای دو نوع cursor را به خود اختصاص می دهد:

 

1. Parent cursor: شامل متن دستور sql می باشد. زمانی که یک دستور چند بار اجرا شود تنها یک parent cursor را به خود اختصاص می دهد البته به شرط اینکه در هنگام اجرای دوباره، هنوز در shared pool موجود باشد. به ازای هر Parent cursor تنها یک رکورد در v$SQLAREA خواهیم داشت.parent cursor بصورت استاتیک است یعنی اطلاعاتش تغییر نمی کند.

2. child cursor : هر parent cursor یک یا چند child cursor را در بردارد child اطلاعات دستور sql از قبیل execution statistics، Bind variable و execution plan را شامل می شود پس اگر دو دستور یکسان با مقدارbind variable متفاوت تنها یک cursor parent و یک child cursor داشته باشند، execution plan آنها هم یکسان خواهد بود(که این به مقدار پارامتر cursor_shring بستگی دارد).child cursor به صورت داینامیک است.

بدیهی است که هر چه تعداد child cursor و یا parent cursor بیشتر باشد، حافظه مصرفی بیشتری هم مصرف خواهد شد.

اطلاعات parent cursor در V$SQLAREA و اطلاعات child cursor در V$SQL ذخیره می شود همچنین child cursor فضای کمتری را نسبت به parent مصرف می کند. در ویوی v$sqlarea منظور از ستون version_count، همان تعداد child cursor می باشد.

در این قسمت سعی شده تا تاثیر مقادیر cursor_sharing بر روی رفتار cursorها نشان داده شود. همچنین از دستور زیر برای بررسی رفتار cursorها استفاده می کنیم ضمنا وقتی که sql_id/hash_value در جداول یکسان باشند، به معنی یکسان بودن parent cursorها می باشد.

select executions,sql_text, sql_id, child_number, hash_value, address  from v$sql where upper(sql_text) like '%USEF_PARS%';

دستور زیر بیانگر دستوری است که توسط کاربر صادر می شود و منظور از n، یک مقدار است که در صورت امکان به variable تخصیص داده می شود. برای درک تاثیر مقدار پارامتر cursor_sharing، مقدار n را تغییر می دهیم.

select * from usef_pars where code=n;

 

بررسی اثر مقدار cursor_sharing

حالت اول: اگر cursor_sharing  برابر با exact باشد، هر بار که مقدار n تغییر کند(مقدار تخصیصی به code در دستور بالا)، به هیچ وجه از فرمهای قابل اجرای قبلی موجود در حافظه که مقدار n آنها با مقدار دستور فعلی یکسان نیست، استفاده نخواهد شد و یک sql_id/hash_value جدید به این دستور تخصیص می یابد و هر دفعه یک parent cursor جدید به دستور تخصیص داده می شود همچنین یک child cursor نیز برای آن ایجاد می شود.

 با توجه به جدول زیر، اگر مقدار n ثابت باشد، در صورت تکرار در اجرا، از همان فرم قبلی استفاده می شود(بر تعداد child هم افزوده نمی شود).

در صورتی که مقدار پارامتر cursor_sharing برابر با exact نبود، می توان با استفاده از hint،cursor_sharing_exact  در یک دستور خاص، رفتار optimizer را تغییر داد تا از exact به جای روشهای دیگر استفاده کند.

همانطور که در ردیف آخر جدول می بینیم، هر تغییر کوچکی در عبارتهای دستور، سبب می شود تا از cursorهای قبلی استفاده نشود و cursor جدیدی ایجاد شود به طور مثال عبارت code به coDE تغییر کرده که سبب شده از فرمهای قبلی استفاده نشود.

alter system set cursor_sharing=exact;

statment	executions	sql_text	hash_value	sql_id	child_ number	PLAN_HASH_VALUE
where code=99	1	select * from usef.usef_pars where code=99	2101158911	bdsqajjymu8zz	0	3830149903
where code=98	1	select * from usef.usef_pars where code=98	3413937134	2rj9bb75rt2zf	0	3830149903
where code=98	2	select * from usef.usef_pars where code=98	3413937134	2rj9bb75rt2zf	0	3830149903
where coDE=98	1	select * from usef.usef_pars where coDE=98	639175319	04wpvr4m1k2nr	0	3830149903

 

ادامه مطلب در pdf

به دلیل طولانی بودن مطلب، لطفا pdf این مطلب را مطالعه بفرمایید(لطفا اینجا کلیک کنید)