می دانیم که shared pool فرم پارس شده دستورات sql و pl/sql را در صورت دارا بودن فضای کافی در خود نگهداری می کند و نسخه قابل اجرای دستورات، در این قسمت از حافظه باقی خواهند ماند تا در صورت اجرای مکرر یک دستور، از کامپایل مجدد آن و یا به بیانی دقیق تر، از انجام عملیات hard pars جلوگیری شود.

فرض کنید به دلایلی چون عدم استفاده از bind variable در قسمتی از برنامه، فضای خالی ای در shared pool باقی نمانده است در این صورت، اوراکل فرمهای پارس شده را بر اساس الگوریتم LRU از حافظه خارج خواهد کرد این مسئله ممکن است سبب age out شدن objectهای سیستمی و یا applicationای شود که به کررات مورد دستیابی قرار می گیرند.

با در نظر داشتن این مسئله، قصد داریم به این سوال پاسخ دهیم که چگونه می توان این objectها را در shared pool سنجاق کرد تا الگوریتم LRU نتواند آنها را برای خروج از shared pool انتخاب کند.

نحوه pin کردن objectها با کمک DBMS_SHARED_POOL

برای pin کردن objectها در shared pool می توان از بسته DBMS_SHARED_POOL استفاده کرد این کار با کمک پروسیجر keep در این بسته قابل انجام خواهد بود.

در ادامه متن قصد داریم با کمک این پروسیجر، بسته STANDARD که یکی از بسته های پرکاربرد سیستمی است را در share pool سنجاق کنیم.

برای pin کردن این بسته، اجرای دستور زیر کافی خواهد بود:

SQL> exec sys.DBMS_SHARED_POOL.KEEP(‘SYS.STANDARD’,’P’);

PL/SQL procedure successfully completed

پروسیجر KEEP دو پارامتر ورودی با نامهای name و flag دارد که name، نام object و flag نوع object را مشخص می کند.

نکته:بسته های زیر می توانند گزینه های خوبی برای pin شدن در shared pool باشند:

STANDARD

DBMS_STANDARD

DBMS_UTILITY

DBMS_DESCRIBE

DBMS_OUTPUT

در مثال قبلی، P به معنی Procedure/Function/Package می باشد و برای انوع دیگر objectها، باید از flagهای زیر استفاده کرد:

T : Type – Q : Sequence – C : Cursor – R : Trigger

نکته: برای مشاهده اسامی بسته هایی که در shared pool سنجاق شده اند، می توان از دستور زیر استفاده کرد:

select owner,name,kept from V$db_object_cache where kept=’YES’ and  type like ‘%PACKAGE%’ ;

در صورتی که از تصمیم خود مبنی بر pin کردن بسته STANDARD منصرف شده ایم، می توانیم از پروسیجر DBMS_SHARED_POOL.UNKEEP استفاده کنیم:

SQL> exec sys.DBMS_SHARED_POOL.UNKEEP(‘SYS.STANDARD’,’P’);

PL/SQL procedure successfully completed

pin کردن objectهایی که فضای زیادی را در حافظه مصرف نمی کنند، شاید چندان کارآمد نباشد از این رو، باید به دنبال objectهایی باشیم که در کنار استفاده مکرر، فضای مصرفی قابل توجهی را هم از shared_pool می گیرند.

برای تعیین سایز مصرفی objectها در shared pool، می توان از پروسیجر DBMS_SHARED_POOL.SIZES استفاده کرد.

این پروسیجر، عددی که بیانگر سایز حداقلی objectها می باشد را به عنوان ورودی دریافت کرده(بر مبنای کیلوبایت) و مشخصات و اندازه همه objectهایی که سایز انها بیشتر از این عدد ورودی هست را بر می گرداند.

دستور زیر، objectهایی که سایز انها بیشتر از 6 مگابایت می باشد را برمیگرداند.

SQL>  set serveroutput on size 10000000

SQL> execute sys.dbms_shared_pool.sizes(6000);

با کمک ویوی V$db_object_cache هم می توان حجم objectهایی که در shared pool ذخیره شده اند را مشخص کرد:

select OWNER, NAME, TYPE, SHARABLE_MEM, EXECUTIONS,PINS, KEPT from V$db_object_cache where sharable_mem > 10000 and type like ‘%PACKAGE%’ order by sharable_mem desc;

همچنین پرس و جوی زیر، اطلاعاتی نظیر تعداد دفعات اجرا و حجم مصرفی را برای objectهایی چون TRIGGER، PROCEDURE، PACKAGE BODY، PACKAGEرا نمایش خواهد داد:

SELECT substr(owner,1,10)||’.’||substr(name,1,35) “Object Name”,

‘ Type: ‘||substr(type,1,12)||

‘ size: ‘||sharable_mem ||

‘ execs: ‘||executions||

‘ loads: ‘||loads||

‘ Kept: ‘||kept

FROM v$db_object_cache

WHERE type in (‘TRIGGER’,’PROCEDURE’,’PACKAGE BODY’,’PACKAGE’)

AND executions > 0

ORDER BY executions desc,

loads desc,

sharable_mem desc;