عملیات I/O به صورت MULTI BLOCK

پارامتر DB_FILE_MULTIBLOCK_READ_COUNT تعداد بلاک هایی که می توانند در هر مرحله از FULL TABLE SCAN با عملیات I/O از دیسک به حافظه منتقل شوند را مشخص می کند البته حداکثر تعداد MULTI BLOCK در زمان اجرا وابسته به پشتیبانی سیستم است. در این متن ویژگی ها و نحوه برخورد اوراکل با این پارامتر را توضیح می دهیم و عملیات خواندن همزمان بلاک ها با یک مثال اجرایی نمایش داده می شود.

همانطور که در مطلب انواع SCANهای OMPTIMIZER عنوان شد در FULL TABLE SCAN (یا FAST FULL INDEX SCAN) عملیات انتقال دیتا از دیسک به BUFFER CACHE به صورت MULTI BLOCK و بر اساس پارامتر DB_FILE_MULTIBLOCK_READ_COUNT انجام می شود. البته اوراکل پیشنهاد می کند که این پارامتر توسط DBA تنظیم نشود تا به صورت تجربی و اتوماتیک با مقدار بهینه تنظیم گردد.

 

 

پارامتر DB_MULTIBLOCK_READ_COUNT دو کاربرد مختلف دارد:

1.در عملیات FULL TABLE SCAN یا FAST FULL INDEX SCAN از مقدار این پارامتر استفاده می شود تا تعداد بلاک هایی که می توانند در هر مرحله از I/O منتقل شوند تعیین گردد.

2.در محاسبه COST عملیات FULL TABLE SCAN از مقدار این پارامتر استفاده می شود.

نکته: OPTIMIZER برای محاسبات COST در عملیات FULL TABLE SCAN معمولا از مقدار پیش فرض 8 برای MULTIBLOCK استفاده می کند.

 

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

 

مقدار MAX I/O CHUNK SIZE به دیسک و سیستم عامل بستگی دارد و اگر برابر با 1MB باشد و سایز بلاک دیتابیس نیز 8KB باشد مقدار پیشنهادی اوراکل طبق این فرمول برابر با 128 خواهد بود.

 

نکته: اوراکل مقدار پارامتر MULTI_BLOCK_READ_COUNT را فقط به بیشترین مقداری که قابل پشتیبانی است تنظیم می کند:

SQL> alter system set db_file_multiblock_read_count=32769;

System altered

 

SQL> alter system set db_file_multiblock_read_count=4000;

 System altered

 

تعداد واقعی بلاک در هر عمل MULTI BLOCK در زمان اجرای دستورات FULL TABLE SCAN متغییر است. این تعداد حداکثر برابر با مقداری است که توسط سیستم قابل انجام است یا در پارامتر DB_MULTIBLOCK_READ_COUNT تنظیم شده است(هر کدام که کوچکتر باشد) و حداقل برابر با یک بلاک  است. برای هر دستور SQL درخواست های خواندن بلاک ها  توسط BUFFER CACHE به دو یا چند درخواست تقسیم می شوند و به صورت ترتیبی ارسال می گردند بنابراین ممکن است:

– بعضی بلاک ها در BUFFER CACHE در حال حاضر PIN شده باشند و نیاز به انتقال آنها از دیسک به حافظه نباشد.

– سایز یک SEGMENT کوچکتر از سایز خواندن چند بلاک همزمان باشد.

 

مثال:  در یک محیط آزمایشی حداکثر تعداد MULTI BLOCK برابر با 126 است.

 

SQL>  alter session set db_file_multiblock_read_count=8000;

 Session altered

 SQL> show parameter multiblock

 

SQL> alter session set events ‘10046 trace name context forever, level 8’;

 Session altered

 

SQL> select /*+ full(t) noparallel(t) nocache(t) */ count(*) from milad.karbaran t;

 

SQL> alter session set events ‘10046 trace name context off’;

 Session altered

 SQL>

SQL> select d.value||’/’||lower(rtrim(i.instance, chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_nam from ( select p.spid from sys.v_$mystat m, sys.v_$session s, sys.v_$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p, ( select t.instance from sys.v_$thread t, sys.v_$parameter v where v.name = ‘thread’ and ( v.value = 0 or t.thread# = to_number(v.value) ) ) i,  ( select value from sys.v_$parameter where name = ‘user_dump_dest’) d;

SQL> exit

 

خروجی فایل TRACE:

.

.

.

.

=====================

PARSING IN CURSOR #2 len=79 dep=0 uid=0 oct=3 lid=0 tim=1627629294345008 hv=3018171972 ad=’15e5333f8′ sqlid=’ggt78tatyb9k4′

select /*+ full(t) noparallel(t) nocache(t) */ count(*) from milad.karbaran t

END OF STMT

PARSE #2:c=11998,e=12537,p=2,cr=67,cu=0,mis=1,r=0,dep=0,og=2,plh=2700161694,tim=1627629294345007

EXEC #2:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,plh=2700161694,tim=1627629294345041

WAIT #2: nam=’SQL*Net message to client’ ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1627629294345053

WAIT #2: nam=’SQL*Net message from client’ ela= 290 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1627629294345365

WAIT #2: nam=’Disk file operations I/O’ ela= 22 FileOperation=2 fileno=6 filetype=2 obj#=74879 tim=1627629294345439

WAIT #2: nam=’db file sequential read’ ela= 10 file#=6 block#=42474 blocks=1 obj#=74879 tim=1627629294345463

WAIT #2: nam=’db file scattered read’ ela= 23 file#=6 block#=42475 blocks=5 obj#=74879 tim=1627629294345562

WAIT #2: nam=’db file scattered read’ ela= 31 file#=6 block#=42480 blocks=8 obj#=74879 tim=1627629294345758

WAIT #2: nam=’db file scattered read’ ela= 27 file#=6 block#=49521 blocks=7 obj#=74879 tim=1627629294346004

WAIT #2: nam=’db file scattered read’ ela= 26 file#=6 block#=49528 blocks=8 obj#=74879 tim=1627629294346190

WAIT #2: nam=’db file scattered read’ ela= 24 file#=6 block#=50369 blocks=7 obj#=74879 tim=1627629294346371

WAIT #2: nam=’db file scattered read’ ela= 27 file#=6 block#=50392 blocks=8 obj#=74879 tim=1627629294346551

WAIT #2: nam=’db file scattered read’ ela= 26 file#=6 block#=61625 blocks=7 obj#=74879 tim=1627629294346754

WAIT #2: nam=’db file scattered read’ ela= 28 file#=6 block#=61640 blocks=8 obj#=74879 tim=1627629294346932

WAIT #2: nam=’db file scattered read’ ela= 26 file#=6 block#=61657 blocks=7 obj#=74879 tim=1627629294347114

WAIT #2: nam=’db file scattered read’ ela= 30 file#=6 block#=65584 blocks=8 obj#=74879 tim=1627629294347285

WAIT #2: nam=’db file scattered read’ ela= 25 file#=6 block#=65593 blocks=7 obj#=74879 tim=1627629294347456

WAIT #2: nam=’db file scattered read’ ela= 31 file#=6 block#=65608 blocks=8 obj#=74879 tim=1627629294347623

WAIT #2: nam=’db file scattered read’ ela= 26 file#=6 block#=65617 blocks=7 obj#=74879 tim=1627629294347813

WAIT #2: nam=’db file scattered read’ ela= 28 file#=6 block#=65632 blocks=8 obj#=74879 tim=1627629294347990

WAIT #2: nam=’db file scattered read’ ela= 24 file#=6 block#=65641 blocks=7 obj#=74879 tim=1627629294348171

WAIT #2: nam=’db file scattered read’ ela= 29 file#=6 block#=69440 blocks=8 obj#=74879 tim=1627629294348337

WAIT #2: nam=’db file scattered read’ ela= 707 file#=6 block#=36226 blocks=126 obj#=74879 tim=1627629294349732

WAIT #2: nam=’db file scattered read’ ela= 540 file#=6 block#=42498 blocks=126 obj#=74879 tim=1627629294352454

WAIT #2: nam=’db file scattered read’ ela= 550 file#=6 block#=43266 blocks=126 obj#=74879 tim=1627629294355407

WAIT #2: nam=’db file scattered read’ ela= 537 file#=6 block#=43906 blocks=126 obj#=74879 tim=1627629294358194

WAIT #2: nam=’db file sequential read’ ela= 6 file#=6 block#=48641 blocks=1 obj#=74879 tim=1627629294360037

WAIT #2: nam=’db file sequential read’ ela= 4 file#=6 block#=42473 blocks=1 obj#=74879 tim=1627629294360058

WAIT #2: nam=’db file scattered read’ ela= 13 file#=6 block#=63872 blocks=2 obj#=74879 tim=1627629294360095

WAIT #2: nam=’db file scattered read’ ela= 541 file#=6 block#=44034 blocks=126 obj#=74879 tim=1627629294361056

WAIT #2: nam=’db file scattered read’ ela= 570 file#=6 block#=45058 blocks=126 obj#=74879 tim=1627629294363867

WAIT #2: nam=’db file scattered read’ ela= 515 file#=6 block#=45314 blocks=126 obj#=74879 tim=1627629294366585

WAIT #2: nam=’db file scattered read’ ela= 534 file#=6 block#=45826 blocks=126 obj#=74879 tim=1627629294369348

WAIT #2: nam=’db file scattered read’ ela= 579 file#=6 block#=46594 blocks=126 obj#=74879 tim=1627629294372181

WAIT #2: nam=’db file scattered read’ ela= 535 file#=6 block#=46722 blocks=126 obj#=74879 tim=1627629294374804

WAIT #2: nam=’db file scattered read’ ela= 502 file#=6 block#=47234 blocks=126 obj#=74879 tim=1627629294377398

WAIT #2: nam=’db file scattered read’ ela= 552 file#=6 block#=48002 blocks=126 obj#=74879 tim=1627629294380085

WAIT #2: nam=’db file scattered read’ ela= 532 file#=6 block#=48514 blocks=126 obj#=74879 tim=1627629294382732

WAIT #2: nam=’db file scattered read’ ela= 724 file#=6 block#=48642 blocks=126 obj#=74879 tim=1627629294385561

WAIT #2: nam=’db file scattered read’ ela= 325 file#=6 block#=63936 blocks=64 obj#=74879 tim=1627629294387923

WAIT #2: nam=’SQL*Net message to client’ ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=74879 tim=1627629294388164

FETCH #2:c=41993,e=42786,p=1951,cr=1955,cu=0,mis=0,r=1,dep=0,og=2,plh=2700161694,tim=1627629294388173

STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1955 pr=1951 pw=0 time=0 us)’

STAT #2 id=2 cnt=1000000 pid=1 pos=1 obj=74879 op=’TABLE ACCESS FULL KARBARAN (cr=1955 pr=1951 pw=0 time=66811 us cost=342 size=0 card=100

0000)’

WAIT #2: nam=’SQL*Net message from client’ ela= 6103 driver id=1413697536 #bytes=1 p3=0 obj#=74879 tim=1627629294394337

CLOSE #2:c=0,e=13,dep=0,type=0,tim=1627629294394399

=====================

.

.

.

 

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.