فرض کنید بلاکی توسط session شماره یک مورد دستیابی قرار گرفته و توسط آن در بافر pin شده است(هر بافر در هر لحظه تنها توسط یک session می تواند pin شود) و در همین حال session دیگری قصد دسترسی به آن بلاک را دارد در این صورت session دوم باید در انتظار بماند به این مدل از انتظار Buffer Busy Wait گفته می شود.

آن چیزی که Buffer Busy Wait را از بعضی رویدادهای انتظار دیگر متمایز می کند، تنازع دو session در سطح بلاک می باشد یعنی ممکن است این دو session قصد دسترسی به دو رکورد مختلف از رکوردهای ذخیره شده در این بلاک را داشته باشند، ولی رقابت آنها در سطح بلاک صورت می گیرد.

همانطور که می دانید بلاکها انواع مختلفی از داده ها را در خود ذخیره می کنند و ممکن است جنس محتویات دو بلاک یکسان نباشد به طور مثال ممکن است بلاکی اطلاعات کاربر را در خود ذخیره کند و بلاک دیگر حاوی اطلاعات کنترلی باشد به همین دلیل تنازع بین sessionها برای دسترسی به بلاکها را می توان به سطوح مختلفی تقسیم کرد:

data block – Data Segment Header – Undo Header – Undo Block

نقش هر کدام از این انواع بلاک، در این مدل انتظار در ادامه به طور مختصر مورد بررسی قرار می گیرد.

1) data block: منظور محتویات اشیاها می باشد که ایجاد Buffer Busy Wait در این سطح می تواند دلایل مختلفی از جمله موارد زیر داشته باشد:

1. بهینه نبودن پرس و جوها: تا جایی که ممکن است باید تعداد بلاک کمتری توسط هر Session خوانده شود.
2. وجود hot block: بلاکی که حاوی رکوردهایی باشد که زیاد مورد استفاده کاربران قرار می گیرند، باید بهینه شود(با روشهایی که بعدا ذکر می شود).
3. missهای مکرر برای دسترسی به hot block: در صورتی که بلاکی در buffer Cache موجود نباشد و دو session بخواهند به آن دسترسی پیدا کنند، Session دوم برای انتقال بلاک به buffer cache باید منتظر session اولی بماند این انتظار قبلا به اسم Buffer Busy Wait شناخته می شد ولی بعد از نسخه 10.1 به read by other session متمایز شده است. نتیجه اینکه بهتر است جدول حاوی hot block در buffer cache نگهداری شود.
4. بزرگ بودن اندازه بلاکها: اگر اندازه بلاکها بزرگ باشد، رکوردهای زیادی در یک بلاک قرار خواهند گرفت که شانس رجوع همزمان دو session به یک بلاک را بالا می برند برای حل این مشکل می توان object را باPCTFREE بالاتر بازسازی کرد و یا اندازه بلاک را کوچکتر در نظر گرفت تا تعداد رکوردها در یک بلاک کاهش یابد.
5. در صورتی که ایندکس مسبب این قضیه شده باشد، می توان از Hash partition و یا ایندکس معکوس برای حل آن استفاده کرد.

همانطور که می دانید، اطلاعات درون ایندکسها مرتب هستند و اگر ایندکس معکوس شود، اطلاعات رکوردها معکوس خواهند شد و رکوردهایی که فعلا در بلاک هستند ممکن است بین بلاکهای مختلف توزیع شوند و این موضوع کمک می کند تا sessionها کمتر به یک بلاک رجوع کنند.

با دستور زیر می توانیم ایندکسها و جداول درگیر این نوع از انتظار را شناسایی کنیم و در صورت لزوم ایندکسها را به صورت reverse بازسازی کنیم:

SELECT owner, object_name,l.OBJECT_TYPE, SUM(VALUE) buffer_busy_count , round(sum(value) * 100/sum(sum(value)) over(),2) pct FROM v$segment_statistics l WHERE statistic_name IN ( ‘buffer busy waits’)   AND VALUE > 0GROUP BY owner, object_name,l.OBJECT_TYPE ORDER BY SUM(VALUE) DESC;

مثالی برای بازسازی ایندکس به صورت reverse:

alter index SALTEST.SPPA_INDX4 rebuild reverse;

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

SELECT  ‘alter index ‘ || owner||’.’||object_name||’  rebuild reverse;’  FROM v$segment_statistics l WHERE statistic_name IN ( ‘buffer busy waits’)   AND VALUE > 0 and OBJECT_TYPE=’INDEX’ GROUP BY owner, object_name,l.OBJECT_TYPE ORDER BY SUM(VALUE) DESC;

2) Data Segment Header : هر سگمنت بلاکی دارد که اطلاعات کلی آن سگمنت از قبیل high watermark، free block و … را در خود ذخیره می کند این بلاک، هدر نام دارد حتی ممکن است برای این بلاک هدر هم بین sessionها رقابت ایجاد شود. دلیل اصلی تنازع در جایی است که sessionای بخشی از اطلاعلات این سگمنت را حذف کرده و بالطبع باید ساختار جدید بلاکها درfreelist  اعمال شود به همین شکل در زمان درج اطلاعات در سگمنت.

هدر بلاک ممکن است به دلایل زیر اصلاح شود:

1. INSERT برای اصلاح و افزایش high watermark.
2. INSERT برای افزودن extent جدید.
3. UPDATE، INSERT و DELETE برای اصلاح freelist.

برای کاهش این نوع از انتظار، رعایت نکات زیر مفید می باشد:

1. پارتیشن بندی سگمنت: سبب می شود بر تعداد هدرها اضافه شود.
2. استفاده از extent بزرگتر: سبب می شود تا نیاز کمتری به افزودن سگمنت داشته باشیم.
3. استفاده از tablespaceهایی با نوع مدیریتASSM  به جای freelist segment space management.

3) Undo Header، Undo Block: زمانی که undo segmentهای اندکی موجود باشند ولی تراکنشهای زیادی انجام بگیرد امکان تنازع بر روی header block زیاد خواهد بود. البته این اتفاق زمانی می افتد که مدیریت undoها به صورت manual باشد.

همچنین زمانی که یک undo block توسط دو session در حال اصلاح باشد و به عبارت دیگر hot block در سطح undo block رخ دهد، بیشتر شاهد این نوع انتظار خواهیم بود.

معمولا با افزایش اندازه undo tablespace و یا بهینه کردن APP، انتظار در این سطح کمتر خواهد شد.

 

برای تشخیص اینکه این رویداد انتظار کدام بلاکها را درگیر کرده است، می توانیم از پرس و جوی زیر کمک بگیریم که شماره datafile، شماره بلاک و نوع بلاک را مشخص می کند:

select p1 “File #”,p2 “Block #”,p3 “Reason Code” from v$session_wait where event = ‘buffer busy waits’;

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

select owner,segment_name,segment_type from dba_extents where file_id = &P1 and  &P2 between block_id and block_id + blocks -1;

 

read by other session

همانطور که قبلا آورده شد، در صورتی که بلاکی در buffer Cache موجود نباشد و دو session بخواهند به آن دسترسی پیدا کنند، Session دوم برای انتقال بلاک به buffer cache باید منتظر session اولی بماند این انتظار قبلا به اسم Buffer Busy Wait شناخته می شد ولی بعد از نسخه 10.1 به read by other session متمایز شده است. اگر session دوم از درخواستش منصرف شود، تنها با wait، db file sequential read روبرو خواهیم شد.

در صورتی که پرس و جوها به خوبی بهینه نشوند و یا خواندن از دیسک به کندی انجام شود، ممکن است این نوع انتظار زمان بیشتری را به خود اختصاص دهد.

 

یک مثال: در ادامه نقش اندازه بلاکها بر میزان رخ دادن Buffer Busy Wait  مورد بررسی قرار خواهد گرفت:

1) 2K: در این سناریو اندازه بلاک برابر با 2K خواهد بود همچنین نسخه اوراکل 11.2.0.4 می باشد.

با توجه به اینکه اندازه پیش فرض بلاکها در بانک مورد نظر ما برابر با 8K می باشد، لازم است تا برای بلاکهای با اندازه 2K هم امکان استفاده از بافرکش ممکن باشد به همین دلیل، پارامتر db_2k_cache_size را تنظیم می کنیم.

alter system set db_2k_cache_size=500m  scope=spfile;

tablespaceای با بلاک سایز 2K ایجاد می کنیم:

CREATE TABLESPACE SIZE2k   DATAFILE SIZE 300M blocksize 2K;

جدولی را بر روی این tablespace می سازیم:

create table usef_tbl2k(a number,b number) tablespace SIZE2k   ;

مقداری اطلاعات در درون این جدول درج می کنیم:

begin

for I in 1..5000 loop

insert into usef_tbl2k  values(I,i+10);

commit;

end loop;

end;

در صورت صلاح دید، از جدول آمار می گیریم تا آمار های بانک از این جدول تازه باشد.

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’,’USEF_TBL2K’);

دستور زیر نشان می دهد که این جدول در کدام فایل قرار گرفته و شماره اولین و آخرین بلاک مصرفی این جدول چه عددی می باشد.

select a, dbms_rowid.rowid_relative_fno(owed), dbms_rowid.rowid_block_number(owed) from SYS.USEF_TBL2K where a in (1, 5000);

A	file#	block#
1	6	1087
5000	6	1114

با یک جمع تفریق ساده می توانیم تعداد بلاکهای مصرفی جدول بالا(با بلاک 2K) را بیابیم:

تعداد بلاک مورد استفاده=1114 – 1087+1=28

در ادامه برای اینکه نتیجه تست را در محیط AWR شاهد باشیم، در ابتدا و انتهای تست، AWR را اجرا می کنیم تا از وضیعت جاری snapshot تهیه کند:

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

تست به این صورت است که دو session به صورت همزمان(البته با  اندکی تلرانس) قصد اصلاح رکوردهای جدول مورد نظر را دارند همچنین هیچ کاربر دیگری در سیستم موجود نیست و هیچ عملیات دیگری در بانک انجام نمی شود مگر عملیات سیستمی:

–session 1

begin

for i in 1..1000 loop

update usef_tbl32k   set a=i;

commit;

end loop;

end;

 

–session 2

begin

for i in 1..1000 loop

update usef_tbl32k   set a=i;

commit;

end loop;

end;

 

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); 

در نهایت تعداد ثانیه و درصدی که DB time با این نوع از wait روبرو بوده است را مشاهده خواهیم کرد:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
enq: TX – row lock contention	1,979	125.1	63	43.3	Application
DB CPU		82.4		28.5
log file switch (checkpoint incomplete)	44	36.9	838	12.8	Configuration
log buffer space	97	14.5	149	5.0	Configuration
log file switch completion	18	7.6	423	2.6	Configuration
row cache lock	2,522	5.5	2	1.9	Concurrency
buffer busy waits	2,372	3.4	1	1.2	Concurrency
db file sequential read	486	2.5	5	.8	User I/O
buffer exterminate	137	1.5	11	.5	Other
gc current block 2-way	635	1.2	2	.4	Cluster

همانطور که می بینید، تعداد کل buffer busy waitای که AWR نشان می دهد برابر با 2372 می باشد که جمعا 3.4 ثانیه و 1.2 درصد DB time را به خود اختصاص داده است ولی نکته مهم اینجاست که همه این انتظارها مربوط به تست ما نمی شود و ممکن است sessionهای دیگر(البته سیستمی) هم در حین انجام تست، با این نوع از انتظار مواجه شده باشند به همین دلیل از دستور زیر استفاده می کنیم تا مقدار دقیق این نوع انتظار از تست انجام شده، مشخص شود:

SELECT owner,object_name,tablespace_name,object_type,statistic_name,value FROM v$segment_statistics WHERE statistic_name like ‘buffer busy waits’ AND value > 1 ORDER BY value desc;

OWNER	OBJECT_NAME	TBS	OBJECT_TYPE	STATISTIC_NAME	VALUE
SYS	USEF_TBL2K	SIZE2K	TABLE	buffer busy waits	1978

 

همچنین گزارش زیر هم که از AWR گرفته شده است، این مطلب را ثابت می کند:

Segments by Buffer Busy Waits

 

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Buffer Busy Waits	% of Capture
SYS	SIZE2K	USEF_TBL2K		TABLE	1,978	100.00

 

 

2) 32K: همانطور که گفته شد، این رویداد انتظار در بلاکهایی با اندازه بزرگتر، استعداد بیشتری برای رخ دادن دارد به همین دلیل سناریوی بالا را برای همان جدول با بلاک 32K هم انجام داده ایم که تنها قسمتهای مهم آن را در ادامه خواهیم دید:

ابتدا برای اینکه همه آمارهای انتظار از صفر شروع شود، بانک را برای یک بار shutdown و start می کنیم.

همچنین به همان تعداد رکوردی که به جدول با بلاک 2K داده بودیم، به جدول با بلاک 32K هم اضافه می کنیم:

begin

for I in 1..500 loop

insert into usef_tbl32k  values(I,i+500);

commit;

end loop;

end;

نکته مهم در تعداد بلاکهای مصرفی این جدول می باشد که نسبت با بلاک 2K بسیار کمتر می باشد:

select a, dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from SYS.USEF_TBL32K where a in(1,5000) –between 1 and 1000;

A	file#	block#
5000	7	56
1	7	57

 

تنها دو بلاک در این حالت مصرف شده است.

همان دو اسکریپت سناریوی قبل را تنها با تغییر نام جدول به طور همزمان در دو session اجرا کرده ایم(البته با  اندکی تلرانس) که گزارش AWR مربوط به این تست نشان می دهد اگر بلاک به 32K تغییر کند، تعداد دفعات این نوع از انتظار هم بالا خواهد رفت.

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
enq: TX – row lock contention	1,881	92.1	49	27.7	Application
DB CPU		90		27.0
log file switch (checkpoint incomplete)	44	53.9	1226	16.2	Configuration
buffer busy waits	2,429	49.8	21	15.0	Concurrency
log file switch completion	20	9.7	485	2.9	Configuration
log buffer space	69	8.6	125	2.6	Configuration
row cache lock	1,986	4.2	2	1.3	Concurrency
buffer exterminate	301	3.7	12	1.1	Other
db file sequential read	743	3	4	.9	User I/O
gc current block busy	12	.4	32	.1	Cluster

 

همانطور که قبلا گفته شد، دستور زیر تعداد دقیق را برای جدول مورد نظر مشخص می کند:

SELECT owner,object_name,tablespace_name,object_type,statistic_name,value FROM v$segment_statistics WHERE statistic_name like ‘buffer busy waits’ AND value > 1 ORDER BY value desc;

OWNER	OBJECT_NAME	TBS	OBJECT_TYPE	STATISTIC_NAME	VALUE
SYS	USEF_TBL2K	SIZE32K	TABLE	buffer busy waits	2204

 

گزارش AWR هم این نکته را تایید می کند:

Segments by Buffer Busy Waits

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Buffer Busy Waits	% of Capture
SYS	SIZE32K	USEF_TBL32K		TABLE	2,204	100.00

 

 

نتیجه این که در محیطی با بلاک 2K تعداد رویداد انتظار مشاهده شده برای یک تست ساده و با محیط تقریبا یکسان برابر با 1978 و مقدار تقریبی 3.4 ثانیه بوده است ولی همین تست در همان محیط با بلاکی به اندازه 32K برابر با 2204 و مقدار ثانیه 49.8 زمان گرفته است.