همانطور که می دانید PGA متشکل از قسمتهای مختلفی می باشد که میزان استفاده هر پروسس از هر کدام از این قسمتها را می توان با کمک پارامترهای نظیر sort_area_size، hash_area_size، bitmap_merge_area_size و… کنترل کرد.

با این حال از اوراکل 9i، پارامتر دیگری به نام PGA_AGGREGATE_TARGET اضافه شد که با مقداردهی آن، اندازه هر یک از قسمتهای PGA توسط خود اوراکل مدیریت می شود.

اوراکل تلاش می کند مجموع فضای PGA تخصیص داده شده(pga1+pga2+pga3+…) به پروسسها را به مقدار در نظر گرفته شده برای پارامتر PGA_AGGREGATE_TARGET محدود کند اما در مواقعی، به خصوص در زمان بالا رفتن بارکاری سیستم و یا پایین بودن مقدار پارامتر PGA_AGGREGATE_TARGET، ممکن است مجموع فضای PGA تخصیص داده شده، از مقدار تنظیم شده برای این پارامتر بیشتر شود.

از این رو، مقدار تعیین شده برای پارامتر PGA_AGGREGATE_TARGET صرفا به عنوان یک soft limit در نظر گرفته خواهد شد و حتی در شرایطی ممکن است فضای PGA تخصیص داده شده به یک پروسس، از مقدار تنظیم شده برای این پارامتر تجاوز کند!(مطلب “نگاهی به استثنائاتی در مورد پارامترهای PGA” را مطالعه کنید)

زمانی که پارامتر PGA_AGGREGATE_TARGET در دیتابیسی تنظیم می شود، پارامتر مخفی PGA_MAX_SIZE_ حداکثر سایز PGA را برای هر پروسس به مقداری محدود خواهد کرد(البته موارد استثنا هم در این زمینه وجود دارد که در مطلب “نگاهی به استثنائاتی در مورد پارامترهای PGA” توضیح داده شده است) و پارامتر مخفی SMM_MAX_SIZE_، حداکثر سایز work area(SORT, HASH JOIN, GROUP BY,  BITMAP MERGE) را برای هر پروسس مشخص می کند.

SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

from sys.x$ksppi a, sys.x$ksppcv b

where a.indx = b.indx

and a.ksppinm in( ‘_pga_max_size’,’_smm_max_size’);

تغییر مقدار پارامترهای مخفی ذکر شده، در محیط DWH می تواند اثر مثبتی را ایجاد کند.

 

PGA و SWAPPING

زمانی را در نظر بگیرید که sessionهای زیادی به صورت همزمان مشغول انجام عملیاتی نظیر join، sort، direct path read و دیگر عملیات PGA خور هستند در چنین شرایطی چندان دور از ذهن نیست که مجموع فضای PGA مصرف شده از مقدار در نظر گرفته شده برای پارامتر PGA_AGGREGATE_TARGET بیشتر شود و حتی ممکن است PGA پس از استفاده کامل فضای آزاد RAM، به ناچار سراغ swap برود و اصطلاحا swapping رخ دهد که در این صورت باید به فکر راهکارهای چون tune کردن پرس و جو ها، افزایش فضای RAM و … بود.

در این قسمت قصد داریم با کمک شل اسکریپتی، sessionهای متعددی را به دیتابیس متصل کرده و عملیاتی که نیاز به مصرف نسبتا بالای PGA دارند را توسط این sessionها اجرا کنیم.

ابتدا مشخصات محیط تست را مرور می کنیم:

RAM=15792MB

Swap size= 15999MB

Database Version= 11.2.0.3.0

memory_max_target=0

SGA_MAX_SIZE= 8192M

SGA_TARGET= 8192M      (Huge_page =TRUE)

PGA_AGGREGATE_TARGET= 2048MB

قصد داریم کوئری select * from mytbl order by 3,2,1 را به صورت همزمان توسط دو هزار session اجرا کنیم:

[oracle@myhost ~]$ cat myscript.sh

for ((i=1;i<=2000;i++))

do

nohup sh query.sh &

done

[oracle@myhost ~]$ cat query.sh

sqlplus -s usef/a <<EOF

select * from mytbl;

exit

EOF

قبل از اجرای query، وضعیت مصرفی RAM و همچنین مجموع فضای PGA  تخصیص داده شده به پروسسها را بررسی می کنیم:

[root@myhost ~]# free -m

             total       used       free     shared    buffers     cached

Mem: 15792       9989       5802          1        243        763

-/+ buffers/cache:       8982       6809

Swap: 15999          0      15999

 

SQL> SELECT TRUNC(sum(PGA_USED_MEM)/ 1024 / 1024) PGA_USED_MB,TRUNC(max(PGA_MAX_MEM)/ 1024 / 1024) PGA_MAX_MEM ,count(s.sid) SESSION_COUNT FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR and s.USERNAME is not null;

PGA_USED_MB            PGA_MAX_MEM       SESSION_COUNT

——————                  ——————                ——————-

          1                                1                                               1

در چنین شرایطی، اسکریپت myscript.sh را اجرا می کنیم:

[oracle@myhost ~]$ ./myscript.sh

nohup: appending output to `nohup.out’

nohup: appending output to `nohup.out’

nohup: appending output to `nohup.out’

……

در حین اجرای این اسکریپت، وضیعت PGA را مانیتور می کنیم.

“مجموع حافظه تخصیص داده شده برای PGA”، “حداکثر میزان فضای PGA پروسسها دیتابیس از زمان استارت instance “ و “تعداد sessionهای غیر سیستمی” را در زمانی که تعداد sessionها به دو هزار می رسد، مشاهده می کنید:

SQL> /

PGA_USED_MB      PGA_MAX_MEM    COUNT(S.SID)

———–                    ———–                     ————

6830                           277                              2001

 

همانطور که می بینید، با رسیدن تعدادsessionها به عدد 2000، نزدیک به 7GB از حافظه برای PGA در حال مصرف است و تقریبا همه فضای swap در حال استفاده می باشد:

[root@myhost ~]# free -m

             total       used       free     shared    buffers     cached

Mem: 15792       15706         86          0          2        197

-/+ buffers/cache: 15506        286

Swap: 15999        15234        765

 

از خروجی دستور free هم خواهیم دید که عدم ایجاد محدودیت دیتابیس برای مجموع فضای استفاده شده، ممکن است سبب swapping و متعاقب آن کندی دیتابیس شود پس باید به فکر راهکاری بود تا از این مسئله در دیتابیس جلوگیری شود.

البته تا قبل از ارائه اوراکل نسخه 12c راهکار رسمی در این زمینه وجود ندارد و در نسخه هایی چون 10g و 11g، امکان تعیین حداکثر فضای کلی قابل استفاده برای PGA در سطح یک instance وجود ندارد.

 

پارامتر PGA_AGGREGATE_LIMIT

اوراکل در نسخه 12c با اضافه کردن پارامتر PGA_AGGREGATE_LIMIT، مجموع فضای مصرف شده PGA را در سطح یک instance محدود می کند.

برخلاف آنچه که در مورد پارامتر PGA_AGGREGATE_TARGET بیان شد، عددی که برای پارامتر PGA_AGGREGATE_LIMIT تنظیم می شود، برای اوراکل به عنوان hard limit شناخته خواهد شد و جز در موارد استثنا که در مطلب “نگاهی به استثنائاتی در مورد پارامترهای PGA” توضیح داده شد، اوراکل به این محدودیت وفادار خواهد ماند.

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

1.دو برابر کردن مقدار پارامتر PGA_AGGREGATE_TARGET

SQL> select value/1024/1024 MB from v$parameter  where name =‘pga_aggregate_target’;

        MB

———-

        80

80MB * 2=160MB

نکته 1: در صورتی که مقدار پارامتر PGA_AGGREGATE_TARGET  از 90% خروجی physical memory – SGA بزرگتر باشد، باید پارامتر PGA_AGGREGATE_LIMIT به کمتر از 200 درصد PGA_AGGREGATE_TARGET تنظیم شود البته این مقدار نمی تواند به زیر 100% از PGA_AGGREGATE_TARGET برسد.

البته نکته فوق با این شرط بیان شد که پارامتر مخفی pga_limit_target_perc_، در حالت پیش فرض تنظیم شده باشد.

نکته 2: مقدار پارامتر مخفی pga_limit_target_perc_ در حالت پیش فرض برابر با 200 می باشد و در صورت تغییر مقدار این پارامتر، فرمول فوق به صورت زیر تغییر خواهد کرد:

PGA_AGGREGATE_LIMIT=PGA_AGGREGATE_TARGET * _pga_limit_target_perc

 

2.حاصلضرب مقدار پارامتر processes در 3MB

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

VALUE

——-

640

640 * 3MB= 1920MB

 

3.دو گیگابایت: در صورتی که دو روش قبلی عددی کمتر از 2GB را برگردانند، مقدار 2GB برای پارامتر PGA_AGGREGATE_LIMIT تنظیم خواهد شد.

بعد از محاسبه فرمولهای فوق، از بین سه عدد بدست آمده، 2GB ،1920MB و 160MB هر کدام از آنها که بزرگتر باشند، برای پارامتر PGA_AGGREGATE_LIMIT تنظیم خواهند شد.

تا زمانی که مقدار پارامتر PGA_AGGREGATE_LIMIT به صورت دستی تنظیم نشود، تغییر هرکدام از متغیر فرمولهای گفته شده، منجر به تغییر مقدار پارامتر PGA_AGGREGATE_LIMIT خواهد شد همچنین در صورتی که مقدار پارامتر PGA_AGGREGATE_LIMIT، همان مقدار پیش فرض باشد، در زمان استارت دیتابیس، پیغامی مشابه پیغام زیر، در alert log مشاهده خواهید کرد:

ALTER DATABASE   MOUNT

2020-05-03T15:46:23.840801+04:30

Using default pga_aggregate_limit of 2048 MB

 

بعد از تنظیم پارامتر PGA_AGGREGATE_LIMIT به مقداری بزرگتر از صفر، اگر مجموع فضای PGA در سطح instance به عدد تنظیم شده برای پارامتر PGA_AGGREGATE_LIMIT برسد، اوراکل در ابتدا دستور در حال اجرای پروسسی که بیشتر از پروسسهای دیگر فضای PGA را مصرف کرده، متوقف می کند اما session را kill نخواهد کرد:

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

       SID

———-

       876

SQL>  exec prc1();

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

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

       SID

———-

       876

SQL>

در صورتی که با توقف اجرای دستور این session توسط اوراکل، باز هم مجموع فضای PGA از PGA_AGGREGATE_LIMIT کمتر نشود، این بار اوراکل sessionای که بیشترین استفاده از PGA را دارد، kill خواهد کرد(دستورش متوقف نمی شود بلکه session به صورت کامل kill می شود):

PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB

KILL SESSION for sid=(506, 18499):

  Reason = pga_aggregate_limit

  Mode = KILL HARD SAFE -/-/-

  Requestor = DBRM (orapid = 14, ospid = 23982, inst = 1)

  Owner = Process: USER (orapid = 100, ospid = 8726)

  Result = ORA-31