پارامتر 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
=====================
.
.
.
