در مطلب “بررسی پارامترهای PGA” با پارامترهایی آشنا شدیم که قرار است محدودیتی را برای مصرف PGA در سطح پروسس و یا instance ایجاد کنند. در این مطلب خواهیم دید که در بعضی از شرایط مقدار در نظر گرفته شده برای پارامترهای pga_max_size، pga_aggrigate_target_ و حتی پارامتر PGA_AGGREGATE_LIMIT، نمی تواند PGA مصرف شده توسط پروسسها را محدود کند و پروسسها تا جایی که سرور و محدودیتهای سیستم عاملی اجازه می دهد، از RAM و SWAP استفاده می کنند.

به عنوان نمونه، مطابق با داکیومنتهای اوراکل، حداکثر PGA مورد استفاده یک پروسس توسط پارامتر مخفی pga_max_size_ کنترل می شود و علاوه بر این پارامتر، مقدار PGA استفاده شده برای یک پروسس به تنهایی نمی تواند بزرگتر از مقدار تنظیم شده برای پارامتر pga_aggrigate_target  باشد.

اما این قوائد در شرایطی نقض خواهد شد مثلا زمانی که از pl/sql memory collectionها نظیر pl/sql table، pl/sql variable و pl/sql varrays استفاده می شود، هیچ کدام از دو پارامتر فوق نمی توانند فضای مورد نیاز پروسسها را محدود کنند و مانع از تخصیص فضا به پروسس شوند.

برای مثال، در قسمت زیر می بینید که با آنکه پارامتر PGA_MAX_SIZE_ به مقدار 50 مگابایت و پارامتر PGA_AGGRIGATE_TARGET  به مقدار 100 مگابایت تنظیم شده اند، باز هم session شماره 2835 که پروسیجر prc1 را اجرا کرده است، بیش از یک گیگابایت از حافظه را به تصرف خود درآورده است:

توجه: پارامتر  pga_aggregate_limit به مقدار صفر تنظیم شده است.

SQL> alter system set “_pga_max_size”=50M;

System altered

SQL> alter system set pga_aggregate_target=100M;

System altered

SQL> select value from v$parameter where name=’pga_aggregate_limit’;

VALUE

————-

0

SQL> conn usef/a

Connected.

SQL> select sid from v$mystat where rownum<=1;

       SID

———-

      2835

SQL> create procedure prc1 as

  2   TYPE MYTBL_TYPE IS TABLE OF mytbl%ROWTYPE ;

  3   V MYTBL_TYPE;

  4   BEGIN

  5   SELECT *   BULK COLLECT INTO V FROM mytbl;

  6   END;

  7  /

Procedure created

 

SQL> exec prc1();

PL/SQL procedure successfully completed

 

SQL> SELECT TRUNC(PGA_MAX_MEM/ 1024 / 1024) PGA_MAX_MEM_MB FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR and sid = 2835;

PGA_MAX_MEM_MB

————————-

          1190

 

همانطور که می بینید، پارامترهای pga_aggregate_target و pga_max_size_ نتوانستند مصرف PGA را برای session شماره 2835 محدود کنند.

 

پارامتر pga_aggregate_limit و pl/sql memory collectionها

در اوراکل 12c با تنظیم پارامتر pga_aggregate_limit به مقداری بزرگتر از صفر، می توان مصرف PGA پروسس را بعد از حد مشخصی متوقف کرد البته تا قبل از اوراکل نسخه 18c، هیچ محدودیتی برای کاربر sys در این زمینه وجود ندارد و حتی تنظیم پارامتر pga_aggregate_limit هم نمی تواند کاربر sys را در مصرف PGA متوقف کند البته jobها را هم می توان به آن اضافه کرد.

 

یک پروسس حداکثر می تواند به چه اندازه از فضای حافظه سرور استفاده کند؟

راستی اگر در پروسیجر prc1، جدول رجوع شده، حجیم تر از جدول mytbl باشد، اجرای پروسیجر(قبل از تنظیم event شماره 10261)، تا کجا پیش خواهد رفت؟

در این قسمت با تغییر متن پروسیجر prc1 و استفاده از جدول حجیم largetlb به جای mytbl، به این سوال پاسخ خواهیم داد.

SQL> select sid from v$mystat where rownum<=1;

       SID

———-

      3309

SQL> create procedure prc2 as

  2   TYPE MYTBL_TYPE IS TABLE OF usef. largetlb%ROWTYPE ;

  3   V MYTBL_TYPE;

  4   BEGIN

  5   SELECT *   BULK COLLECT INTO V FROM usef.largetlb;

  6   END;

  7  /

Procedure created

SQL> exec prc2();

ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pl/sql vc2)

 

SQL> SELECT TRUNC(PGA_MAX_MEM/ 1024 / 1024) PGA_MAX_MEM_MB FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR and sid = 3309;

PGA_MAX_MEM_MB

————–

          4089

همانطور که می بینید، دستور با خطا متوقف شده است! این خطا به دلیل مصرف بیش از حد پروسس 3309 از حافظه رخ داده است. این پروسس به تنهایی چهار گیگابات از حافظه را مصرف کرده است!

اما چرا با چهار گیگابایت مصرف حافظه، پروسس متوقف شده است؟ چگونه می توان مجوز استفاده بیشتر از 4 گیگابایت را به این پروسس داد؟

علت توقف این پروسس، به محدودیتی که در کرنل لینوکس تنظیم شده برمی گردد و برای ممانعت از رخ دادن آن می توان پارامتری به نام max_map_count را در کرنل لینوکس تنظیم کرد. البته پارامترهای مخفی اوراکل هم می توانند در این زمینه به ما کمک کنند. مقدار پیش فرض پارامتر max_map_count را می توان با دستور زیر مشخص کرد:

[root@myhost ~]# sysctl -a|grep max_map_count

vm.max_map_count = 65530

همانطور که می بینید، مقدار جاری این پارامتر برابر با 65KB می باشد و با توجه به آنکه اختصاص فضا در واحد 64KB انجام می شود، بنابرین هر پروسس حداکثر می تواند به اندازه 4GB فضا مصرف کند:

65530*64000=4,193,920,000(4GB)

با افزایش عدد 65530 به 256000، سقف تخصیص حافظه به یک پروسس را به 16GB افزایش خواهد یافت پس از این تغییر،  مجددا پروسیجر prc2 را اجرا می کنیم:

[root@ol6 ~]# echo vm.max_map_count = 256000 >>/etc/sysctl.conf

[root@ol6 ~]# sysctl -p

SQL> exec prc2();

PL/SQL procedure successfully completed

همانطور که می بینید، این بار دستور بدون خطا و با مصرف حدودا 7 گیگابایت حافظه اجرا شده است:

SQL>SELECT TRUNC(PGA_MAX_MEM / 1024 / 1024) PGA_MAX_MEM_MB FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR   and sid = 2363;

PGA_MAX_MEM_MB

————–

7312

بسیار روشن است که در صورت استفاده بی رویه پروسسی از حافظه، ممکن است سرویس دهی با اختلال مواجه شود پس تغییر این پارامترها باید با احتیاط انجام شود.

علاوه روش فوق، راهکار اوراکلی هم برای برطرف کردن خطای ORA-04030 وجود دارد!

روش اوراکلی حل این مسئله، استفاده از پارامترهای مخفی use_realfree_heap_ و realfree_heap_pagesize_hint_ می باشد به طور مثال، با تنظیم پارامتر مخفی use_realfree_heap_ به false و یا افزایش عدد تنظیم شده برای پارامتر realfree_heap_pagesize_hint_ خطای ORA-04030 رخ نخواهد داد.

روش اول:

SQL>select i.ksppinm name , v.ksppstvl cur_val, v.ksppstdf default_val,v.ksppstvf from x$ksppi i, x$ksppcv v where i.indx = v.indx and i.ksppinm in (‘_realfree_heap_pagesize_hint’, ‘_use_realfree_heap’);

NAME                                            CUR_VAL              DEFAULT_V   KSPPSTVF

——————————             ——————–         —————          ———-

_realfree_heap_pagesize_hint   65536                             TRUE               0

_use_realfree_heap                   TRUE                               TRUE               0

SQL> alter system set “_use_realfree_heap”=false;

System altered.

SQL> startup force;

SQL> conn usef/a

Connected.

SQL> exec prc1();

PL/SQL procedure successfully completed

 

روش دوم: 

SQL> alter system set “_use_realfree_heap”=true;

System altered.

SQL> alter system set “_realfree_heap_pagesize_hint” = 256000  scope=spfile;

System altered.

 

چگونه می توان از مصرف بی رویه PGA توسط این قبیل پروسسها جلوگیری کرد؟

جلوگیری از مصرف بی رویه حافظه برای PGA می تواند از طریق event شماره 10261 انجام شود. برای مثال، با دستور زیر، event شماره 10261 را در سطح سیستم تنظیم کرده و پروسیجر prc2 مجددا را اجرا می کنیم:

SQL> alter system set events = ‘10261 trace name context forever, level 100000’;

System altered

SQL> select sid from v$mystat where rownum<=1;

       SID

———-

      2837

SQL> exec prc2();

ORA-00600: internal error code, arguments: [723], [16384], [pga heap], [], [], [], [], [], [], [], [], []

SQL> SELECT TRUNC(PGA_MAX_MEM/ 1024 / 1024) PGA_MAX_MEM_MB FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR and sid = 2837;

PGA_MAX_MEM_MB

————–

            97