در دنیای واقعی کارهای بسیاری وجود دارند که اساسا امکان انجام آنها به صورت سریالی به سختی قابل انجام است و به ناچار نیاز است که این کارها به صورت موازی و همروند اجرا شوند به صورت مثال، فرض کنید نیاز است تا در کشوری همانند چین، سرشماری به صورت دستی انجام بگیرد انجام این عمل توسط یک فرد شاید مضحک و غیرعملی بنظر برسد و ممکن است فرد تا پایان سرشماری، از دنیا برود!!! به همین دلیل نیاز است تا سازمانی برای انجام این کار ایجاد شود و نیروهایی را به استخدام خود در بیاورد تا بتواند با تقسیم کار بین آنها، این عمل را با سرعت بیشتری به انجام برساند.
در دنیای IT و علی الخصوص در بانکهای اطلاعاتی هم نیاز به همروندی فراوان دیده می شود و از همروندی به کررات استفاده می شود تا عملیاتی که انجام سریالی آن بسیار پرهزینه و وقت گیر است، با کمک چند پروسس انجام شود و با تقسیم کار بین این چند پروسس، تسریع در اجرای عملیات صورت پذیرد. بدیهیست که تقسیم یک کار کوچک به چند قسمت نه تنها باعث تسریع اجرای ان نخواهد شد، بلکه ان عمل را به مراتب کندتر خواهد کرد.
همروندی در بانک اطلاعاتی اوراکل، با تصمیم optimizer(بهینه کننده) انجام می شود و زمانی که بهینه کننده تصمیم به اجرای همروند یک دستور می گیرد، ابتدا پروسس مجری دستور، نقش یک هماهنگ کننده یا Query Coordinator(QC) را به خود خواهد گرفت که باید شی ای که منبع داده محسوب می شود و خروجی دستور از ان بدست می اید را به قسمتهای کوچکتری تقسیم و هر کدام از این قسمتها را به پروسسی که Parallel Excution Server(PX) نام دارد، تخصیص دهد در صورتی که کار هر کدام از pxها به پایان برسد، آن px مشغول انجام عملیاتهای باقیمانده خواهد شد تا در نهایت همه قسمتها مورد دسترسی قرار بگیرند. به هر کدام از این قسمتهای کوچک، granule گفته می شود که هر px در یک زمان تنها با یک granule کار می کند. تعیین اندازه granuleها بر اساس مکانیزم داخلی اوراکل انجام می شود و امکان مداخله در اندازه ان از بیرون وجود ندارد.
با کمی اغماض، شاید بشود مثال سرشماری را با عملیاتی که در اوراکل برای پیاده سازی همروندی انجام می شود مشابه دانست و اجزاهای هر دو را با هم مطابقت داد که در این صورت، مدیر سازمانی که مسئولیت سرشماری را بر عهده دارد و نیروهایش را فرامی خواند و کارها را بین انها تقسم می کند، در اوراکل همان هماهنگ کننده یا QC خوانده می شود و پروسسهای PX هم مانند نیروهایی هستند که به دل شهر و روستا سفر می کنند تا سرشماری ان منطقه را انجام دهند و همچنین می توان هر منطقه را با یک granule مشابهت داد که مدیر سازمان(QC) وظیفه دارد تا مشخص کند که کدام نیروها(PX) مسئولیت سرشماری کدام منطقه(granule) را دارند.
معمولا granuleها به دو شیوه مشخص می شوند: Block range granule و Partition granule که Block range granule در بیشتر عملیات همروندی استفاده می شود و اندازه granule در آن بر اساس بلاکهای فیزیکی شی مشخص می شود همچنین در این شیوه سعی بر آن است تا حتی المقدور هر پروسس مشغول انجام کار بر روی یک دیسک شود تا کارایی خواندن افزایش یاد. در روش Partition granule، هر پروسس مشغول کار با یک پارتیشن از جدول و یا ایندکس خواهد شد که در این صورت حداکثر درجه همروندی برابر با تعداد پارتیشنها خواهد بود و اگر اندازه پارتیشنها یکسان نباشد، ممکن است تعداد پروسسها از تعداد پارتیشنها کمتر باشد.
بعد از تعیین اندازه granule و نیز تقسیم کار بین PXها، زمانی که PXها کارهای محوله را به تمامه انجام داده اند، خروجی هر قسمت را به QC ارسال می کنند و QC با جمع بندی خروجی ها، اطلاعات را به کاربر نشان می دهد. به عبارت دیگر، QC نقش مسئول و هماهنگ کننده را ایفا خواهد کرد.
توضیحی مختصر در مورد Producer و Consumer
زمانی که در اجرای یک دستور نیاز به عملیات پیوند(join)، مرتب سازی(sort) و … وجود داشته باشد(البته در صورت اجرای دستور به صورت همروند!)، مفهومی به نام تولیدکننده(Producer) و مصرف کننده(Consumer) مطرح می شود طوری که دسته ای از pxها نقش تولیدکننده و دسته ای دیگر نقش مصرف کننده را ایفا خواهند کرد و تولیدکننده ها با اتمام کاری که به انها محول می شود(مثل برگرداندن رکوردهای یک جدول)، خروجی را به مصرف کننده خواهند فرستاد. معمولا دسته تولید کننده ها، وظیفه برگرداندن رکوردها را دارند و عملیاتی همچون JOIN، SORT، DML و DDL بر عهده دسته مصرف کننده می باشد همچنین هر پروسسی که در دسته تولید کننده وجود دارد با پروسسی که در دسته مصرف کننده وجود دارد، ارتباط خواهد داشت. برای مشاهده دسته هایی که برای اجرای یک دستور اجرا می شوند، می توان از نقشه اجرایی(execution plan) کمک گرفت که ستون TQ این اطلاعات را به ما خواهد داد(در ادامه در این مورد مطالبی را خواهیم اورد).
حتی می توان با دستور زیر تعیین کرد که کدام پروسس در کدام server set ایفای نقش می کند:
select decode(px.qcinst_id,NULL,username, ‘ – ‘||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”, decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” , to_char( px.server_set) server_set, to_char(s.sid) “SID”, decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”, px.req_degree “Requested DOP”, px.degree “Actual DOP” from v$px_session px, v$session s where px.sid=s.sid (+) and px.serial#=s.serial# order by 5 , 1 desc;
مثال:
select /*+shared(4) */* from usef_tbl l , usef_tbl m where l.file#=m.file#;
| Username | QC/Slave | SERVER_SET | SID | QC SID | Requested DOP | Actual DOP |
| USEF | QC | 1344 | 1344 | |||
| – p007 | (Slave) | 2 | 1157 | 1344 | 4 | 4 |
| – p006 | (Slave) | 2 | 964 | 1344 | 4 | 4 |
| – p005 | (Slave) | 2 | 584 | 1344 | 4 | 4 |
| – p004 | (Slave) | 2 | 393 | 1344 | 4 | 4 |
| – p003 | (Slave) | 1 | 11 | 1344 | 4 | 4 |
| – p002 | (Slave) | 1 | 968 | 1344 | 4 | 4 |
| – p001 | (Slave) | 1 | 774 | 1344 | 4 | 4 |
| – p000 | (Slave) | 1 | 6 | 1344 | 4 | 4 |
نکته: همروندی در سیستمهایData Warehouse کاربرد قابل توجهی دارد ولی در سیستمهای OLTP بیشترین در ساخت جدول، ایندکس و اجرای batch و اقداماتی از این قبیل می باشد و بر روی دستورات DMLای یا SELECT ساده اثر چندانی ندارد.
اجرای عملیات به صورت همروند
عملیات زیر، تنها نمونه هایی از عملیاتی هستند که قابلیت اجرا به صورت همروند را پشتیبانی می کنند:
| Access method | table scans – index full scans – partitioned index range scans |
| Join method | nested loop – sort merge – hash – star transformation |
| DML | INSERT AS SELECT – updates – deletes – MERGE |
| DDL | CREATE TABLE AS SELECT – CREATE INDEX – REBUILD INDEX – REBUILD INDEX PARTITION – MOVE/SPLIT/COALESCE PARTITION |
برای اجرای این عملیات به صورت همروند، روشهای مختلفی وجود دارد، که در ادامه هر یک را به اختصار مورد بررسی قرار خواهیم داد.
همروندی در سطح session
برای اجرای همروند دستورات در یک session، می توان از دستور ALTER SESSION استفاده کرد. ساختار کلی این دستور به صورت زیر می باشد که نشان می دهد هر کدام از عملیاتهای DDL، DML و SELECT نیاز به دستور جداگانه ای دارند:
ALTER SESSION {ENABLE | DISABLE | FORCE} PARALLEL {DML|DDL|QUERY} [PARALLEL int]
{ENABLE | DISABLE | FORCE}
همانطور که در ساختار کلی دستور دیده می شود، برای استفاده از این دستور(ALTER SESSION…PARALLEL)، می توان از سه عبارت ENABLE ، FORCE و یا DISABLE استفاده کرد که عبارت ENABLE تنها امکان استفاده از همروندی را برای دستور فعال می کند و در صورتی که شی مرجع، درجه ایی بیشتر از یک داشته باشد، دستور به صورت همروند و با درجه همروندی ان شی اجرا خواهد شد اما عبارت FORCE سبب می شود تا دستور با درجه همروندی پیش فرض(DEFAULT) اجرا شود البته می توان با استفاده از عبارت PARALLEL، در مورد درجه همروندی اعمال نظر کرد. بی تردید استفاده از hint در دستور، بر هر دو قانون قبلی غلبه خواهد کرد و دستور با درجه تعیین شده آن hint اجرا خواهد شد.
| session | degree(table) | hint | Servers In Use |
| ENABLE PARALLEL | 10 | – | 10 |
| FORCE PARALLEL | 10 | – | 32 |
همچنین استفاده از عبارت DISABLE در این دستور سبب می شود تا در session مربوطه، عملیاتی به صورت همروند قابل اجرا نباشد البته استفاده از parallel hint این قائده را نسخ خواهد کرد:
ALTER SESSION DISABLE PARALLEL QUERY;
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL DML;
[PARALLEL int]
در دستور ALTER SESSION، مقدار int، درجه همروندی را مشخص می کند که اگر این مقدار مشخص نشود، درجه همروندی پیش فرض، برای این دستور لحاظ خواهد شد(در مورد چگونگی تعیین درجه پیش فرض، در ادامه مطالبی اورده خواهد شد).
{DML|DDL|QUERY}
در زمان اجرای دستور ALTER SESSION..PARALLEL، می توان جنس دستوراتی که امکان اجرای همروند را دارند، مشخص کرد که این کار با استفاده از سه عبارت DML|DDL|QUERY قابل انجام می باشد که اثر هر یک را در ادامه با مثالی نشان خواهیم داد.
استفاده از عبارت QUERY برای اجرای همروند پرس و جو و نیز به صورت کلی برای خواندن استفاده می شود. مثال زیر را ببینید:
دستور زیر مشخص می کند که چه تعداد از PXها در حال استفاده می باشند:
select * from v$px_process_sysstat where statistic like ‘%Servers In Use%’
| STATISTIC | VALUE |
| Servers In Use | 0 |
همانطور که می بینید، هیچ پروسس همروندی در حال اجرا نمی باشد حال همروندی را در سطح session فعال کرده و دستوری را اجرا می کنیم:
–session 1:
ALTER SESSION FORCE PARALLEL QUERY PARALLEL;
select * from usef_tbl;
در همین حال، در session دیگری دستور زیر را دوباره اجرا می کنیم که نشان می دهد 16 پروسس در حال استفاده می باشند:
session 2:
select * from v$px_process_sysstat where statistic like ‘%Servers In Use%’
| STATISTIC | VALUE |
| Servers In Use | 16 |
با مشاهده نقشه اجرایی دستور، درخواهیم یافت که دسترسی به ایندکسها هم در صورت امکان به شکل همروند امکان پذیر خواهد بود:
set autotrace traceonly explain
SQL> select id from usef_tbl;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
—————————————————————————————————————–
| 0 | SELECT STATEMENT | | 797M| 5325M| 29479 (3)| 00:00:09 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 797M|5325M| 29479 (3)| 00:00:09 | Q1,00 | P->S | QC (RAND)|
| 3 | PX BLOCK ITERATOR | | 797M| 5325M| 29479 (3)| 00:00:09 | Q1,00 | PCWC | |
| 4 | INDEX FAST FULL SCAN| id_pk | 797M| 5325M| 29479 (3)| 00:00:09 | Q1,00 | PCWP | |
همچنین برای اجرای همروند دستورات DDLای در یک session باید از عبارت DDL در این دستور بهره گرفت:
ALTER SESSION FORCE PARALLEL DDL;
create table usef_t as select * from usef_tbl;
این روش برای ایجاد و بازسازی همروند ایندکسها هم قابل استفاده می باشد:
ALTER SESSION FORCE PARALLEL DDL;
alter index usef_ind1 rebuild;
عبارت DML در این دستور برای اجرای همروند دستورات insert، delete، update و … کاربرد دارد:
ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
ALTER SESSION FORCE PARALLEL DDL;
delete usef_tbl;
البته دستورات DMLای محدودیتهای قابل توجهی هم دارند از قبیل اینکه نمی توان عملیات همروند را بر روی جداول کلاستر شده، IOT، و نیز جداولی که شامل LOB segment می باشند، انجام داد. همچنین در یک تراکنش نمی توان بر روی یک جدول به صورت همروند دو عمل را انجام داد در این صورت، با خطای زیر مواجه خواهیم شد:
ALTER SESSION ENABLE PARALLEL DML;
insert /*+parallel(m,5) */into usef_tbl3 m select * from usef_tbl;
delete usef_tbl3;
ORA-12838: cannot read/modify an object after modifying it in parallel
نکته 1: امکان همروندی و وضیعت فعلی یک session بخصوص را می توان با استفاده از دستور زیر تعیین نمود:
SELECT username, pq_status, pdml_status, pddl_status FROM v$session WHERE sid = sys_context(‘userenv’,’sid’);
در خروجی این دستور خواهیم دید که فیلدهای PDDL_STATUS و PQ_STATUS فعال می باشند ولی PDML_STATUS به صورت پیش فرض غیرفعال می باشد و باید با دستور زیر فعال شود:
ALTER SESSION ENABLE PARALLEL DML;
البته غیرفعال بودن پیش فرض PDML سبب نمی شود تا الزاما دستورات DMLای از همروندی استفاده نکنند بلکه باید توجه کرد که اجرای همروندی برای دستورات DMLای در دو سطح قابل بررسی می باشد که در حالت اول ان، می توان هم در مرحله خواندن و هم در مرحله تغییر(نوشتن) عملیات همروند داشت ولی در حالت دوم، تنها عملیات خواندن به صورت همروند امکان پذیر است و عملیات تغییر و نوشتن به صورت سریالی توسط QC انجام می شود. مثال زیر نشان می دهد که در عین غیرفعال بودن PDML، کماکان برای اجرای دستور DMLای(صرفا برای خواندن)، از همروندی استفاده می شود:
SELECT username, pq_status, pdml_status, pddl_status FROM v$session WHERE sid = sys_context(‘userenv’,’sid’);
| USERNAME | PQ_STATUS | PDML_STATUS | PDDL_STATUS |
| USEF | DISABLED | ENABLED | ENABLED |
alter table usef.USEF_TBL parallel 10;
–session 1
ALTER SESSION DISABLE PARALLEL DML;
delete usef.USEF_TBL;
–session 2
select value from v$px_process_sysstat where statistic like ‘%Servers In Use%’;
10
نکته 2: منظور از همروندی دستور INSERT، دستور INSERT . . . SELECT . . . FROM می باشد نه دستور به شکل ساده آن.
نکته 3: اگر در sessionای عملیات DMLایی انجام شده باشد که هنوز commit برای ان صادر نشده است، فعال سازی همروندی به صورت DML در سطح session با خطا متوقف می شود:
SQL> delete usef.usef_tbl;
206 rows deleted.
SQL> ALTER SESSION ENABLE PARALLEL DML;
ORA-12841: Cannot alter the session parallel DML state within a transaction
نکته 4: با فعال سازی همروندی DMLای برای یک session، جدولی که بر روی آن عملیات DMLای انجام می شود، تا قبل از انجام commit یا rollback، امکان ارائه گزارش را در این session نخواهد داد:
ALTER SESSION enable PARALLEL DML;
insert /*+parallel(m,5) */into usef_tbl3 m select * from usef_tbl;
select * from usef_tbl3;
ORA-12838: cannot read/modify an object after modifying it in parallel
نکته 5: برای استفاده از این دستورات در محیط پروسیجر و یا فانکشن، می توان از چنین دستوری استفاده کرد:
EXECUTE IMMEDIATE ‘alter session force parallel DML parallel 10’;
همروندی در سطح شی
اگر شی ایی در بیشتر عملیات به صورت همروند مورد دسترسی قرار می گیرد و نیاز به اجرای همروند برای آن ضروری باشد، می توان در هنگام ساخت شی و یا بعد از ایجاد آن، درجه همروندی ثابتی را برای ان شی لحاظ کرد تا عملیاتی که همروندی را پشتیبانی می کنند، برای ان شی به صورت همروند انجام شوند.
دستور زیر، جدول usef_tbl2 را به صورت همروند ایجاد خواهد کرد و این صفت همروندی با این شی خواهد ماند و هر دسترسی که به این جدول صورت پذیرد، در صورت امکان همروندی را در پی خواهد داشت:
create table usef_tbl2 parallel 8 as select * from usef_uss;
برای خارج کردن جدول از حالت همروندی از دستور زیر استفاده می شود:
ALTER TABLE usef_tbl2 NOPARALLEL;
البته با دستور ALTER TABLE می توان برای جداول درجه همروندی تعیین کرد:
ALTER TABLE usef_tbl2 PARALLEL 5;
دستور زیر مشابه دستور قبلی می باشد:
alter table usef_tbl2 parallel (degree 5);
ساخت ایندکس هم می تواند به صورت همروند انجام شود که البته این صفت همروندی بعد از ایجاد ایندکس هم با ان خواهد ماند مگر اینکه به صورت صریح از طریق ALTER INDEX غیرفعال شود:
create index us_ind2 on usef_uss(owner) parallel 5;
برای غیرفعالسازی درجه همروندی یک ایندکس می توان از دستور زیر استفاده کرد:
ALTER INDEX us_ind1 NOPARALLEL;
همانند جدول، امکان تغییر درجه همروندی برای ایندکسها هم ممکن می باشد:
ALTER INDEX us_ind1 PARALLEL 8;
در زمان بازسازی ایندکس هم می توان از همروندی استفاده کرد:
ALTER INDEX us_ind1 PARALLEL 4;
همروندی با استفاده از hint
طریق دیگری که امکان اجرای همروند یک دستور را فراهم می کند، استفاده از hint می باشد. در این روش، برخلاف روشهای دیگر، تنها در مورد اجرای همروند یک دستور اعمال نظر می شود که می تواند در محیطهای OLTP بسیار مفید باشد.
در زمینه همروندی، hintهای مختلفی وجود دارد که از جمله می توان به موارد زیر اشاره کرد:
PARALLEL – NOPARALLEL – NO_PARALLEL – PARALLEL_INDEX – NO_PARALLEL_INDEX –
NOPARALLEL_INDEX – PQ_DISTRIBUTE
در این بین، PARALLEL از پرکاربردترین هینتها می باشد و ساختاری به صورت زیر دارد:
/*+ PARALLEL (table_name[, degree[, instances]]) */
عبارت table_name در PARALLEL hint به جدولی اشاره دارد که قرار است به صورت همروند و با درجه ای به اندازه degree مورد دستیابی قرار بگیرد و نیز عبارت instance مشخص می کند که دستور در محیط RAC، می تواند با کمک چند نود اجرا شود.
در ادامه برای هر یک از عملیاتهای SELECT، DML و DDL مثالی از همروندی به طریق hint اورده شده است.
دستور زیر جدول usef_tbl را به صورت همروند و با درجه 5 خواهد خواهند:
select /*+parallel(a,5) */ * from usef_tbl a;
البته استفاده از کاما ضرورتی ندارد:
select /*+parallel(a 5) */ * from usef_uss a;
همچنین در صورتی که شی ای ویژگی همروندی را دارا باشد می توان در سطح دستور این ویژگی را غیرفعال کرد:
select /*+NO_PARALLEL(a) */* from usef_tbl a
همروندی برای یک ایندکس خاص، به وسیله PARALLEL_INDEX قابل انجام می باشد که ساختار آن به صورت زیر می باشد:
/*+ PARALLEL_INDEX (table_name[, index_name [, degree[, instances]]]) */
دستور زیر سبب می شود تا در صورت لزوم، ایندکس id_pk به صورت همروند و همراه با دو پروسس و با کمک دو نود(در محیط RAC) مورد دسترسی قرار بگیرد(دو پروسس در هر نود):
select /*+PARALLEL_INDEX(a,id_PK,2,2) */ id from usef_tbl a;
البته برای جلوگیری از دسترسی همروند به یک ایندکس، می توانیم از NOPARALLEL_INDEX استفاده کنیم:
alter index id_PK parallel 10;
select /*+NO_PARALLEL_INDEX(a,id_PK) */ id from usef_tbl a;
همانطور که قبلا بیان شد، برای دستورات DMLای و DDLای هم می توان از هینت استفاده کرد که در ادامه مثالهایی را خواهیم اورد.
برای حذف اطلاعات جدول به صورت همروند هم می توانیم از hint استفاده کنیم. البته هینت دیگری هم در این زمینه وجود دارد که enable_parallel_dml نام دارد که قابلیت انجام DML همروند را ممکن می سازد که هینت disable_parallel_dml عکس ان عمل خواهد کرد:
delete /*+ enable_parallel_dml PARALLEL(a,5) */ usef_tbl a;
برای درج همروند هم دستور به صورت زیر خواهد بود:
insert /*+PARALLEL*/ into usef_tbl2 a select * from usef_tbl;
البته می توان همروندی را در قسمت پرس و جو هم اعمال کرد:
insert /*+ parallel(t1) */ into t1 select /*+ parallel(t2) */ * from t2;
مثال بعدی، بروزرسانی به صورت همروند را نشان می دهد:
update /*+PARALLEL(a,5) */ usef_tbl a set a.last_updated_by=’usef’;
ایجاد جدول هم با کمک همروندی قابل انجام است:
create table usef_tb as select /*+PARALLEL(a,5) */ * from usef_tbl a;
نکته 1: از اوراکل 11gR2 این قابلیت به وجود امد تا با استفاده از هینت parallel(degree) برای یک پرس وجو، قابلیت دسترسی همروند به اشیاهای مورد رجوع در دستور فراهم شود به بیانی دقیق تر، بهینه کننده(optimizer) می تواند برای اجرای این دستور، از پروسسهای موازی استفاده کند. البته می توان از مقدار degree هم صرف نظر کرد و در صورتی که عددی برای ان مشخص نشود، این عدد توسط اوراکل قابل تعیین است که معمولا مقدار پیش فرض خواهد بود(نحوه محاسبه درجه همروندی پیش فرض، در ادامه خواهد امد).
مثال: در مثال زیر، در ابتدا خواهیم دید که هیچ پروسس همروندی در حال استفاده نیست و سپس با اجرای پرس و جو با استفاده از این نوع هینت، خواهیم دید 9 پروسس برای اجرای ان، بسیج شده اند:
select count(*) from v$px_process l where l.STATUS=’IN USE’;
0
–session1:
set autotrace traceonly explain
SQL> select /*+parallel(9) */ * from usef.usef_uss;
–session2
select count(*) from v$px_process l where l.STATUS=’IN USE’;
9
نکته 2: زمانی که در یک دستور از اسم مستعار برای یک جدول استفاده می شود، در هنگام استفاده از هینت هم باید از اسم مستعار جدول استفاده شود در غیر این صورت، این جدول به صورت همروند مورد دستیابی قرار نمی گیرد. مثال زیر را ببینید:
استفاده از اسم مستعار:
select /*+parallel(m) */ * from usef.usef_uss m;
| 3 | PX BLOCK ITERATOR | | 408K| 56M| 106 (0)| 00:00:01| Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| USEF_USS | 408K| 56M| 106 (0)| 00:00:01| Q1,00 | PCWP ||
استفاده از نام کامل جدول:
select /*+parallel(usef_uss) */ * from usef.usef_uss m;
| 0 | SELECT STATEMENT | | 408K| 56M| 3054 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| USEF_USS | 408K| 56M| 3054 (1)| 00:00:01 |
نکته 3: امکان استفاده از چند hint parallel در یک پرس و جو ممکن می باشد:
select /*+parallel(m,60) parallel(u,50)*/ * from usef.usef_uss m,usef_tbl u;
نکته4: استفاده از عبارت DEFAULT به جای degree و instance سبب می شود تا رفتار پیش فرض اوراکل برای این گزینه ها لحاظ شود.
نکته 5: در صورتی که برای یک دستور، از هر سه روش همروندی به صورت همزمان استفاده شود، hint بر دو مورد دیگر ارجحیت دارد و به اصطلاح بر روی آنها override می شود.
نکته 6: برای مشاهده اسامی hintها، می توان از v$sql_hint استفاده کرد. البته این ویو شامل همه hintها نخواهد بود.
نکته 7: علاوه بر parallel hint، همروندی از طریقhint SHARED هم ممکن می باشد.
select /*+shared(l,4) */* from usef_tbl l;
جدول زیر مروری تیتروار از انچه که بیان شد را در خود دارد:
| Parallel Operation | PARALLEL Hint | PARALLEL Clause | ALTER SESSION | Parallel Declaration |
| Parallel query table scan (partitioned or nonpartitioned table) | 1) PARALLEL | 2) FORCE PARALLEL QUERY | 3) of table | |
| Parallel query index range scan (partitioned index) | 1) PARALLEL_INDEX | 2) FORCE PARALLEL QUERY | 2) of index | |
| Parallel UPDATE or DELETE (partitioned table only) | 1) PARALLEL | 2) FORCE PARALLEL DML | 3) of table being updated or deleted from | |
| INSERT operation of parallel INSERT… SELECT (partitioned or nonpartitioned table) | 1) PARALLEL of insert | 2) FORCE PARALLEL DML | 3) of table being inserted into | |
| SELECT operation of INSERT … SELECT when INSERT is parallel | Takes degree from INSERT statement | Takes degree from INSERT statement | Takes degree from INSERT statement | Takes degree from INSERT statement |
| SELECT operation of INSERT … SELECT when INSERT is serial | 1) PARALLEL | 2) of table being selected from | ||
| CREATE operation of parallel CREATE TABLE … AS SELECT (partitioned or nonpartitioned table) | Note: Hint in the SELECT clause does not affect the CREATE operation | 2) | 1) FORCE PARALLEL DDL | |
| SELECT operation of CREATE TABLE … AS SELECT when CREATE is parallel | Takes degree from CREATE statement | Takes degree from CREATE statement | Takes degree from CREATE statement | Takes degree from CREATE statement |
| SELECT operation of CREATE TABLE … AS SELECT when CREATE is serial | 1) PARALLEL or PARALLEL_INDEX | 2) of querying tables or partitioned indexes | ||
| Parallel CREATE INDEX (partitioned or nonpartitioned index) | 2) | 1) FORCE PARALLEL DDL | ||
| Parallel REBUILD INDEX (nonpartitioned index) | 2) | 1) FORCE PARALLEL DDL | ||
| REBUILD INDEX (partitioned index)—never parallelized | ||||
| Parallel REBUILD INDEX partition | 2) | 1) FORCE PARALLEL DDL | ||
| Parallel MOVE or SPLIT partition | 2) | 1) FORCE PARALLEL DDL |
نقشه اجرایی و همروندی
همروندی دستورات بر روی نقشه اجرایی(execution plan) انها اثر گذار خواهد بود به همین جهت عبارتها و یا ستونهایی به این جهت در نقشه اجرایی اضافه خواهند شد. در ادامه با اوردن نمونه ای از نقشه اجرایی یک دستور همروند، در مورد نقش هر یک از ستونها، مختصر مطالبی اورده خواهد شد:
SQL> set autotrace traceonly explain
SQL> select /*+ parallel(p)*/ * from oc_personnels p;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 2255K| 673M| 650 (1)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2255K| 673M| 650 (1)| Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2255K| 673M| 650 (1) | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| OC_PERSONNELS | 2255K| 673M| 650 (1)| Q1,00 | PCWP ||
ستون operation: همانطور که می دانید این ستون در همه نقشه های اجرایی وجود دارد و به استفاده و یا عدم استفاده از همروندی مربوط نمی شود و تنها در صورت استفاده از همروندی، عبارتهای جدیدی در این ستون دیده خواهد شد از قبیل:
PX COORDINATOR: به QC اشاره دارد که در مورد نقش ان قبلا مطالبی اورده شد نتیجه انکه هر دستوری که به صورت همروند اجرا می شود، این عبارت در نقشه اجرایی ان موجود خواهد بود.
PX BLOCK ITERATOR : در صورت استفاده از روش block range granule(که قبلا توضیح داده شد)، این عبارت در نقشه اجرایی دیده خواهد شد.
PX PARTITION RANGE: در صورت استفاده از روش Partition granule(که قبلا توضیح داده شد)، این عبارت در نقشه اجرایی دیده خواهد شد.
PX SEND QC (RANDOM) : این عبارت بیانگر ارسال تصادفی(بدون رعایت ترتیب) نتیجه محاسبات انجام شده توسط pxها به QC می باشد.
PX SEND QC (ORDER): با این عبارت، ارسال اطلاعات به QC به صورت ترتیبی انجام خواهد شد.
نکته: در نقشه اجرایی، عبارت PX SEND به تولید کننده و ارسال داده توسط ان اشاره دارد و عبارت PX RECEIVE هم مصرف کننده را نشان خواهد داد و PX SEND QC نشان می دهد که چگونه اطلاعات به سمت QC ارسال می شود.
ستون NAME: هر دسته از پروسسها در هنگام اجرای همروند یک دستور، به طور مجزا از table queue استفاده می کند تا به صورت موقت اطلاعات را در آن قرار دهند این ظرف اسامی به شکل TQ1000n دارد و در صورتی که فضای کافی را برای نگهداری اطلاعات نداشته باشد، ممکن است از temporary tablespace هم استفاده کند. فضای TQها می تواند از shared pool و یا large pool بدست اید.
ستون TQ: برای تعیین تعداد دسته های پروسسهای همروند، می توان از این ستون استفاده کرد که این ستون ساختاری به صورت Qn,nn دارد.
ستون IN-OUT: از طریق این ستون می توان فهمید که اطلاعات از چه نوع پروسسی به چه نوع پروسس دیگر ارسال می شود برای مثال، این ستون نشان خواهد داد که دسته ای از PX به QC داده ای را ارسال کرده است و یا یک دسته از PX به دسته دیگر اطلاعاتی را فرستاده است. نمونه ای از مقادیری که این ستون می تواند به خود بگیرد، در ادامه اورده شده است:
P->S به معنی parallel -> serial می باشد و معمولا برای نمایش ارسال خروجی از دسته PXها به QC به کار می رود.
S->P به معنی serial->parallel می باشد که نشان می دهد خروجی یک جدول که به صورت غیرموازی خوانده شده است، قرار است در همروندی مورد استفاده قرار بگیرد.
P>P بیانگر parallel-> parallel می باشد و به ارتباط بین PXها و ارسال شدن داده ها از یک پروسس همروند به پروسس همروند دیگر اشاره دارد. ارسال داده بین تولید کننده و مصرف کننده می تواند جزو مصادیق این مقدار باشد.
ستون PQ Distrib: این ستون نشان می دهد که ارسال داده بین دسته تولید کننده، دسته مصرف کننده و QC با چه روش و الگوریتمی انجام خواهد شد. این ستون می تواند مقادیر مختلفی را به خود بگیرد که نمونه ای از این مقادیر در ادامه اورده شده است:
BROADCAST: هر تولید کننده اطلاعاتش را به هر مصرف کننده می فرستد.
ROUND-ROBIN: رکوردها به صورت ترتیبی و بر اساس الگوریتم round robin به مصرف کننده ها فرستاده می شوند.
RANGE: تولیدکننده تصمیم دارد تا محدوده ای از داده ها را به یک مصرف کننده بفرستد و محدوده های دیگر را برای مصرف کننده های دیگر ارسال کند.
HASH: با استفاده از الگوریتم hash مشخص می شود که کدام یک از مصرف کننده ها باید داده را از تولید کننده دریافت کند. این روش بسیار رایج می باشد.
QC (RAND): تولید کننده اطلاعاتش را بدون ملاحضه هیچ ترتیبی به QC ارسال می کند.
QC (ORDER): تولید کننده اطلاعاتش را با ترتیب مشخصی به QC خواهد فرستاد.
پارامترها
تعدادی از پارامترها در اجرای همروند دستورات نقش اساسی دارند که در این قسمت در مورد بعضی از انها، مطالبی اورده شده است و مابقی در قسمت “همروندی خودکار” مورد بررسی قرار می گیرند.
PARALLEL_MIN_SERVERS
با استفاده از این پارامتر می توان تعدادی از پروسس ها را برای اجرای عملیات همروند، در زمان راه اندازی بانک، استارت کرد و در حالت اماده باش قرار داد. بدون تردید اگر مقدار این پارامتر، از تعداد پروسسهایی مورد استفاده توسط دستورات همروند بیشتر باشد، سربار اضافه ای را بر روی بانک ایجاد خواهد کرد. مقدار پیش فرض ان برابر با صفر می باشد و همچنین می توان با استفاده از دستور زیر، این پروسسها را در سطح سیستم عامل مشاهده کرد:
ps -eaf|grep ora_p
oracle 31382 1 0 16:02 ? 00:00:00 ora_p000_usef11g
oracle 31384 1 0 16:02 ? 00:00:00 ora_p001_usef11g
oracle 31386 1 0 16:02 ? 00:00:00 ora_p002_usef11g
parallel_max_servers
حداکثر تعداد پروسس همروندی که در بانک قابل اجرا و استفاده هستند، توسط این پارامتر مشخص می شود. مقدار پیش فرض این پارامتر با فرمول زیر محاسبه می شود:
parallel_max_servers= CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5
البته اندازه cpu_count به صورت خودکار تعیین می شود و مقدار پارامتر parallel_threads_per_cpu به صورت پیش فرض برابر با 2 می باشد.
parallel_min_percent
همانطور که در مورد پارامتر parallel_max_servers اورده شد، تعداد پروسسهایی که برای همروندی قابل استفاده می باشند، محدود خواهند بود حال اگر این پروسسهای همروند در حال اجرا به مقدار تعیین شده برای parallel_max_servers برسند، دستور به صورت سریال اجرا خواهد شد مگر انکه پارامتر parallel_min_percent
به مقدار غیر صفر تنظیم شده باشد که سبب می شود دستور در صورت نبود پروسس به تعداد کافی، به جای اجرای سریالی، با خطا متوقف شود. پس این پارامتر تعیین می کند که حداقل چند درصد از پروسسهای همروند مربوط به یک دستور باید قابل دستیابی باشند تا دستور به صورت همروند اجرا شود(مبحث تنزل درجه همروندی در ادمه خواهد امد).
مثال: مثال زیر نشان می دهد که دستور زیر به دلیل انکه نخواهد توانست به اندازه حداقل درصد تعیین شده، پروسس همروند بدست اورد، با خطا متوقف می شود:
parallel_min_percent=100
parallel_max_servers=40
session1:
select /*+parallel(m,30) */* from usef_uss m;
session2:
select /*+parallel(m,30) */* from usef_uss m;
ORA-12827: insufficient parallel query slaves (requested 30, available 10, parallel_min_percent 100)
PARALLEL_ADAPTIVE_MULTI_USER
با تنظیم این پارامتر، در صورتی که بار کاری سرور زیاد باشد، درجه همروندی دستور برای اجرا کاهش خواهد یافت.
PARALLEL_EXECUTION_MESSAGE_SIZE
اندازه بافر مورد استفاده توسط PXها، برای ارتباط با یکدیگر و نیز ارتباط با QC توسط این پارامتر تعیین می شود. معمولا این فضا از shared pool اخذ می شود و البته می توان از فضای large pool هم برای این بافر استفاده کرد اندازه این پارامتر در نسخه های مختلف، متفاوت می باشد که مقدار آن در نسخه 12.2 برابر با 16384 می باشد.
parallel_automatic_tuning
این پارامتر مربوط به نسخه های قدیمی می باشد و فی الحال در اوراکل 12.2 منسوخ شده است. از کارهایی که از طریق این پارامتر قابل انجام است، انتقال بافر از shared pool به large pool می باشد. در صورت استفاده از این پارامتردر نسخه 12c، با خطای زیر مواجه خواهیم شد:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> alter system set parallel_automatic_tuning=true;
ORA-25138: PARALLEL_AUTOMATIC_TUNING initialization parameter has been made obsolete
نکته: برای مشاهده همه پارامترهای منسوخ، می توان از ویوی v$obsolete_parameter استفاده کرد.
همروندی خودکار
از اوراکل 11g این قابلیت به وجود امد تا با تنظیم پارامتری، اوراکل به صورت خودکار و بر اساس مقادیر پارامترها و حجم جداول، درجه همروندی را برای اجرای دستورات تنظیم و اعمال کند.
برای فعال سازی این ویژگی نیاز است تا پارامتر parallel_degree_policy را به مقدار مطلوب تنظیم کرد. این پارامتر، می تواند مقادیر AUTO، MANUAL و یا LIMITED به خود بگیرد که هر کدام در ادامه توضیح داده خواهند شد.
MANUAL
به صورت پیش فرض مقدار پارامتر parallel_degree_policy برابر با manual می باشد که ویژگی خودکارسازی را غیرفعال خواهد کرد در این صورت، اجرای همروند تنها برای مواردی که به صورت صریح تعیین شده اند، قابل انجام می باشد یعنی به سه طریق سطح session، سطح شی و یا از طریق hint می توان یک دستور را به صورت همروند اجرا کرد.
نکته: در صورتی که پارامتر parallel_degree_policy به مقداری غیر از manual تنظیم شده باشد، می توان با استفاده از هینتی با نام parallel(manual) این پارامتر را در سطح دستور به manual تغییر داد.
LIMITED
همانند manual، مانع اجرای همروند به صورت خودکار خواهد شد مگر اینکه درجه موازی سازی شی به صورت default تعیین شده باشد که در این حالت، متناسب با اندازه جدول و پارامتری که در ادامه خواهد امد، درجه همروندی را تعیین می کند و حتی ممکن است از همروندی برای اجرای دستور استفاده نکند.
مثال: ابتدا پارامتر parallel_degree_policy را به limited تنظیم می کنیم:
alter system set parallel_degree_policy=limited;
درجه جدول usef_tbl را به default تغییر می دهیم:
alter table usef_tbl parallel;
با استفاده از ویوی dba_tables، اندازه و درجه این جدول را مورد بازبینی قرار می دهیم:
select table_name,num_rows,degree from dba_tables where table_name=’USEF_TBL’;
| TABLE_NAME | NUM_ROWS | DEGREE |
| USEF_TBL | 206 | DEFAULT |
جدول را به صورت full scan می خوانیم:
select * from usef_tbl;
در همین حال، در Sessionای دیگر، تعداد پروسسهای موازی در حال استفاده را می بینیم:
select * from v$px_process_sysstat where statistic like ‘%Servers In Use%’;
0
همانطور که دیده شد، به دلیل حجم پایین جدول usef_tbl و نیز default بودن درجه همروندی آن، از هیچ پروسس همروندی استفاده نشده است، البته در صورت قابل توجه بودن حجم جدول، ممکن است تعداد این پروسسها افزایش پیدا کند پس با این روش، به صورت محدود، خودکارسازی انجام شده است.
AUTO
در صورتی که مقدار پارامتر parallel_degree_policy برابر با AUTO باشد، امکان استفاده خودکار از همروندی برای اجرای پرس و جوها مهیا خواهد شد.
حال باید به این سوال پاسخ داده شود که چه دستوراتی امکان اجرای خودکار را خواهند داشت و فاکتورهایی که برای تعیین درجه همروندی موثر خواهند بود، کدامند؟ امکان اجرا با استفاده از پارامتر PARALLEL_MIN_TIME_THRESHOLD قابل تعیین است و هر دستور که طول زمان اجرای ان از مقدار این پارامتر بیشتر باشد، به صورت همروند اجرا خواهد شد.
مقدار تعیین شده برای پارامتر PARALLEL_MIN_TIME_THRESHOLD، بر اساس ثانیه می باشد و به صورت پیش فرض برابر با 10 ثانیه تنظیم شده است.
همچنین خودکارسازی درجه همروندی، توسط اوراکل و بر اساس بار کاری سرور و نیز با کمک پارامتر PARALLEL_DEGREE_LIMIT انجام می شود به طوری که درجه همروندی از مقدار تعیین شده برای این پارامتر بیشتر نخواهد شد. به صورت پیش فرض مقدار پارامتر PARALLEL_DEGREE_LIMIT برابر با cpu می باشد که ترجمه عددی آن، با ضرب مقدار سه پارامتر دیگر قابل محاسبه می باشد:
PARALLEL_DEGREE_LIMIT =PARALLEL_THREADS_PER_CPU * CPU_COUNT * instances
البته برای تغییر این مقدار، می توان عددی را به این پارامتر تخصیص داد:
PARALLEL_DEGREE_LIMIT=16
یا مقدار این پارامتر(PARALLEL_DEGREE_LIMIT) را برابر با IO قرار داد، تا اوراکل در هنگام تعیین درجه همروندی، محدودیتها و قابلیتهای I/O را لحاظ کند. البته اگر i/o به خوبی اندازه گیری نشده باشد، با تنظیم این پارامتر به IO، به خطای زیر برخواهیم خورد:automatic DOP: skipped because of IO calibrate statistics are missing برای رفع این خطای، ابتدا باید مقدار پارامترهای زیر بررسی شود تا از صحت و درستی تنظیم انها، مطمئن شد:
disk_asynch_io=TRUE
filesystemio_options =ASYNCH
و سپس بلاک زیر را اجرا کرد تا در فیلد status مربوط به ویوی v$IO_CALIBRATION_STATUS، مقدار ready دیده شود:
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
— DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ || iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ || lat);
DBMS_OUTPUT.PUT_LINE (‘max_mbps = ‘ || mbps);
end;
/
در ادامه مثالی در مورد خودکارسازی درجه همروندی اورده شده است.
مثال: در ابتدا درجه جدول مورد نظر را تعیین می کنیم:
select l.degree from dba_tables l where l.table_name=’USEF_TBL’;
1
همچنین پارامترهایی که به این مسئله مربوط هستند، به صورت زیر تعریف شده اند:
parallel_degree_policy=auto
parallel_max_servers=80
parallel_min_time_threshold=2
parallel_degree_limit=CPU
parallel_threads_per_cpu=2
cpu_count=8
حال با مشاهده نقشه اجرایی مربوط به دستور زیر، خواهیم دید که اوراکل برای اجرای دستور، از درجه همروندی 16 استفاده خواهد کرد:
SQL> select * from usef.usef_tbl;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
————————————————————————————————————————–
| 0 | SELECT STATEMENT || 797M| 201G| 490K (2)| 00:02:22 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000|797M| 201G| 490K (2)| 00:02:22 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR || 797M| 201G| 490K (2)| 00:02:22 | Q1,00 | PCWC ||
| 4 | TABLE ACCESS FULL| usef_tbl| 797M| 201G| 490K (2)| 00:02:22 | Q1,00 | PCWP | |
————————————————————————————————————————–
– automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
همانطور که عبارت بالا نشان می دهد، این دستور با 16 پروسس و به صورت همروند قابل اجرا می باشد. البته بعد از اجرای این دستور، می توان تعداد پروسسهای همروند را با دستور زیر تعیین کرد:
select statistic, value from v$pq_sysstat where statistic like ‘Servers Busy%’;
16
نکته 1: با استفاده از هینتی به نام parallel(AUTO) می توان پارامتر parallel_degree_policy را در سطح دستور به auto تنظیم کرد. البته این کار سبب فعال سازی statement queuing(که در ادامه خواهد امد) نمی شود.
نکته 2: پارامتر PARALLEL_THREADS_PER_CPU مشخص می کند که هر cpu چه تعداد پروسس همروند را پشتیبانی می کند.
نکته 3: از دیگر کاربردهای پارامتر parallel_degree_limit زمانی است که از parallel hint بدون هیچ پرانتزی استفاده کرده باشیم:
Select /*+ parallel */ * from usef.usef_tbl; –> Servers In Use=16
نکته 4: معمولا توصیه می شود که از این مقدار(parallel_degree_policy=AUTO) در محیط DW استفاده شود و در محیط OLTP، تنظیم این مقدار ممکن است سبب عدم استفاده از ایندکسها شود و موضوع همروندی در OLTP بهتر است در سطح hint و در سطح دستور پیاده سازی شود.
STATEMENT QUEUEING
تعیین مقدار auto برای پارامتر parallel_degree_policy سبب می شود تا در صورت ازاد نبودن پروسسهای همروند، دستور به صورت سریالی و یا با تعداد پروسسهای کمتر اجرا نشود بلکه در یک صف قرار بگیرد و منتظر بماند تا پروسس به اندازه کافی ازاد گردد به این اتفاق، STATEMENT QUEUEING می گویند. می توان گفت که این ویژگی تکامل یافته پارامتر parallel_min_percent می باشد که به جای توقف اجرای دستور، منتظر خواهد ماند تا پروسسهای دیگر به قدر ضرورت ازاد شوند.
به مثال زیر توجه کنید:
ابتدا پارامترهای مورد نیاز برای پیاده سازی STATEMENT QUEUEING را تنظیم می کنیم:
alter system set parallel_degree_policy=auto;
alter system set parallel_max_servers=10;
کاربر اول با دستور زیر، همه 10 پروسس را از آن خود خواهد کرد:
–session 1:
select /*+parallel(10) session1 */ * from usef.usef_uss m;
کاربر دوم هم دستوری را اجرا می کند که نیاز به 10 پروسس دارد.
–session 2:
select /*+parallel(10) session2 */ * from usef.usef_uss m;
با توجه به اجرای دستور توسط کاربر اول، پروسس همروند ازادی در سیستم وجود نخواهد داشت و در نهایت دستور کاربر دوم در صف قرار می گیرد و منتظر خواهد ماند تا پروسس به قدر ضرورت ازاد شود. دستور زیر وضیعت دو دستور اجرا شده را نشان می دهد:
select status, sql_text from v$sql_monitor where sql_text like ‘%sess%’;
| STATUS | SQL_TEXT |
| QUEUED | select /*+parallel(10) session2 */ * from usef.usef_uss m |
| EXECUTING | select /*+parallel(10) session1 */ * from usef.usef_uss m |
حال اگر کاربر اول را از حالت اجرا خارج کنیم، خروجی دستور قبلی به صورت زیر تغییر خواهد کرد و کاربر دوم 10 پروسس را در اختیار خواهد گرفت:
| STATUS | SQL_TEXT |
| EXECUTING | select /*+parallel(10) session2 */ * from usef.usef_uss m |
| DONE (FIRST N ROWS) | select /*+parallel(10) session1 */ * from usef.usef_uss m |
نکته: در صورتی که اجرای یک پرس وجو به صورت همروند با وقفه مواجه شود، می توان دستور را دوباره اجرا کرد و با تریس زیر، همه رخدادها را به دقت مشاهده کرد:
alter session set “_px_trace”=high,all;
در این صورت اگر دستور در صف انتظار قرار گرفته باشد، پیامی شبیه به پیام زیر در فایل تریس دیده می شود:
2017-04-24 05:31:23.540601*:PX_Queuing:kxfxq.c@769:kxfxqStmtQueuable():
Statement is queuable? YES
استفاده از پارامتر Parallel_servers_target در مدیریت STATEMENT QUEUEING موثر خواهد بود این پارامتر حد یقفی را برای درجه همروندی خودکار و همزمان ایجاد خواهد کرد به طور مثال، اگر مقدار این پارامتر برابر با 31 باشد وانگهی دو دستور که هر کدام برای اجرای همروندشان نیاز به 16 پروسس دارند، امکان اجرای همزمان نخواهند داشت و باید دستوری در ابتدا اجرا شود و دستور دوم در صف منتظر بماند مگر آنکه مقدار این پارامتر، به 32 تغییر کند که در این صورت، دستور دوم در صف قرار نمی گیرد و همزمان با دستور اول اجرا خواهد شد. البته نباید این پارامتر را با parallel_max_servers اشتباه گرفت. برای روشن تر شدن مطلب، فرض کنید پارامترهای مربوطه به صورت زیر تنظیم شده اند:
parallel_servers_target=31
parallel_max_servers=80
parallel_degree_policy=auto
parallel_degree_limit=16
فرض کنید قصد دسترسی به جدول usef_tbl را داریم:
–Session 1:
select * from usef.USEF_TBL;
select value from v$px_process_sysstat where statistic like ‘%Servers In Use%’;
16
حال همین دستور را در session دیگری اجرا می کنیم که به دلیل سقف تعیین شده برای پارامتر parallel_servers_target، این دستور در صف اجرا خواهد ماند تا پروسسهای همروندی که برای همروندی خودکار در حال استفاده هستند، به اندازه کافی آزاد شوند:
–Session 1:
select * from usef.USEF_TBL;
wait…
پس افزایش مقدار پارامتر parallel_servers_target، سبب کاهش رخ دادن statement queuing می شود در مثال مذکور، افزایش مقدار این پارامتر از 31 به 32، مانع از در صف ماندن دستور دوم خواهد شد.
مقدار پیش فرض پارامتر parallel_servers_target، به صورت زیر محاسبه می شود:
parallel_servers_target = 4 X PARALLEL_THREADS_PER_CPU X CPU_COUNT
نکته 1: برای فعال و غیرفعالسازی ویژگی statement queuing به صورت مستقل و بدون توجه به مقدار پارامتر parallel_degree_policy، می توان از پارامتر _parallel_statement_queuing استفاده کرد.
نکته 2: همچنین برای فعال و غیرفعالسازی این ویژگی در سطح یک دستور می توان از دو hint زیر استفاده کرد:
/*+ NO_STMT_QUEUING */
/*+ STMT_QUEUING */
نکته 3: برای نمایش همه دستوراتی که به جهت استفاده از ویژگی statement queuing در صف قرار گرفته اند، می توان از دستور زیر استفاده کرد:
SELECT s.sql_id, s.sql_text FROM v$SQL_MONITOR m, v$SQL s WHERE m.status=’QUEUED’ AND m.sql_id = s.sql_id;
تنزل درجه همروندی (Downgrade)
فرض کنید دستوری با درجه همروندی 100 قصد اجرا دارد بالطبع برای اجرای این دستور باید این تعداد پروسس همروند در بانک اطلاعاتی ازاد باشند و در صورت کافی نبودن تعداد پروسسهای ازاد، با تنظیمات پیش فرض بانک(PARALLEL_MIN_PERCENT=0 و _parallel_statement_queuing=false)، دستور با تعداد پروسس کمتری اجرا خواهد شد که اصطلاحا به این رخداد، downgrade می گویند.
در صورتی که پارامتر PARALLEL_MIN_PERCENT به مقدار غیر صفر تنظیم شده باشد و تعداد پروسس های همروند آزاد کمتر از درجه همروند دستور اجرا شده باشد، دستور مورد نظر تنها زمانی اجرا خواهد شد که حداقل به اندازه درصد تعیین شده برای پارامتر PARALLEL_MIN_PERCENT ارضا ده باشد و در غیر این صورت، با خطا متوقف خواهد شد.
درصد تنزل درجه همروندی در بانک قابل تعیین است و برای مشاهده تعداد دستورات همروند تنزل یافته به تناسب درصد آنها، می توان از پرس و جوی زیر استفاده کرد:
SELECT l.INST_ID,l.NAME,l.VALUE,l.STAT_ID FROM GV$SYSSTAT l WHERE name LIKE ‘Parallel operation%’;
| INST_ID | NAME | VALUE |
| 1 | Parallel operations not downgraded | 73 |
| 1 | Parallel operations downgraded to serial | 1 |
| 1 | Parallel operations downgraded 75 to 99 pct | 3 |
| 1 | Parallel operations downgraded 50 to 75 pct | 0 |
| 1 | Parallel operations downgraded 25 to 50 pct | 0 |
| 1 | Parallel operations downgraded 1 to 25 pct | 3 |
همروندی در محیط RAC
همروندی در محیط rac همانند همروندی در محیط single می باشد با این تفاوت که در این محیط می توان همروندی را با کمک چند نود انجام داد به این صورت که تعدادی از پروسس در هر نود اجرا شوند و گوشه ای از کار را برعهده بگیرند و در نهایت خروجی را به QC بسپارند. همچنین تولید کننده و مصرف کننده ها در این محیط همانند محیط single با هم در ارتباط می باشند.
برای داشتن همروندی در محیط rac، می توان از همان روشهای قبلی استفاده نمود به طور مثال، می توان از hint استفاده کرد که ساختار ان قبلا اورده شده است.
مثال: دستور زیر به صورت همروند و با کمک دو نود اجرا خواهد شد(البته در صورت امکان و با تشخیص optimizer):
select /*+PARALLEL_INDEX(a,id_PK,2,2) */ id from usef_tbl a;
همچنین می توان از فعال سازی همروندی در سطح شی نیز استفاده نمود:
ALTER TABLE NODETEST1 PARALLEL(DEGREE 4 INSTANCES 2)
البته در این محیط، پارامترها هم نقش بسیار مهمی را ایفا می کنند که در ادامه هر یک از پارامترهای اثرگذار را به طور مجزا مورد بحث قرار خواهیم داد.
از پارامترهایی که در این زمینه مفید می باشند، می توان به دو پارامتر INSTANCE_GROUPS و PARALLEL_INSTANCE_GROUP اشاره کرد که پارامتر INSTANCE_GROUPS امکان ایجاد گروه هایی را فراهم می کند تا هر یک از نودها، در یکی از این گروه ها قرار بگیرند اینکه چه نود و حتی چه sessionایی در چه گروهی قرار می گیرد از طریق پارامتر PARALLEL_INSTANCE_GROUP قابل کنترل می باشد پس مقدار این پارامتر تنها می تواند یکی از مقادیر تعیین شده در پارامتر INSTANCE_GROUPS باشد(البته می توان از اسم سرویس هم برای این پارامتر استفاده کرد). به مثال زیر توجه کنید.
ابتدا سه گروه برای پارامتر instance_groups تعریف می کنیم و هر کدام از نودها را به دو گروه تخصیص می دهیم البته برای تست، یکی از گروه ها دوبار تکرار شدند تا در صورت استفاده از این گروه، از هر دو نود استفاده شود:
SQL> alter system set instance_groups=’grp1′,’grp2′ sid=’rac1′ scope=spfile;
SQL> alter system set instance_groups=’grp2′,’grp3′ sid=’rac2′ scope=spfile;
حال برای انجام تست، یکی از این گروه ها را به پارامتر PARALLEL_INSTANCE_GROUP تخصیص می دهیم:
–node 1:
SQL> alter system set parallel_instance_group=’grp3′;
با تخصیص این گروه به نود اول، تمامی دستورات همروندی که در این نود اجرا می شوند، پروسسهای همروندشان تنها از نود دوم تامین خواهد شد:
–node 1:
select /*+parallel(a,5) */ * from usef_tbl a;
–node1:
select value from v$px_process_sysstat where statistic like ‘%Servers In Use%’;
0
–node2:
select value from v$px_process_sysstat where statistic like ‘%Servers In Use%’;
5
نکته: PARALLEL_FORCE_LOCAL: در صورتی که قصد اجرای همروند دستورات تنها با کمک نود جاری را داشته باشیم، می توانیم این پارامتر را به مقدار true تنظیم کنیم.
حال فرض کنید دستور زیر را در نود اول اجرا می کنیم، جدول زیر خروجی را با در نظر گرفتن حالتهای مختلف برای پارامترهای موثر، نشان خواهد داد:
select /*+parallel(a,40) */* from usef_tbl a;
rac1.INSTANCE_GROUPS=grp1,grp2
rac2.INSTANCE_GROUPS=grp2,grp3
| statment | PARALLEL_FORCE_LOCAL | PARALLEL_INSTANCE_GROUP | inst1 | inst2 | |
| test1 | /*+parallel(a,40)*/ | true | all | 40 | 0 |
| test2 | /*+parallel(a,40,2)*/ | true | all | 80 | 0 |
| test3 | /*+parallel(a,40)*/ | flase | – | 20 | 20 |
| test4 | /*+parallel(a,40,3)*/ | false | – | 60 | 60 |
| test5 | /*+parallel(a,40)*/ | false | grp1 | 40 | 0 |
| test6 | /*+parallel(a,40)*/ | false | grp2 | 20 | 20 |
| test7 | /*+parallel(a,40)*/ | false | grp3 | 0 | 40 |
در نسخه 11g، همروندی متناسب با سرویسها انجام می شود و پارامترهای مذکور(INSTANCE_GROUPS و PARALLEL_INSTANCE_GROUP) هر چند که قابل استفاده هستند ولی منسوخ و یا به عبارت دقیق تر، deprecate شده اند و در صورت استفاده از این پارامترها، بعد از هر راه اندازی مجدد بانک، پیامی شبیه به پیام زیر را دیده خواهد شد:
ORA-32006: INSTANCE_GROUPS initialization parameter has been deprecated
ORA-32006: PARALLEL_SERVER_INSTANCES initialization parameter has been deprecated
مدیریت همروندی با کمک سرویس، سبب می شود تا اگر فردی با سرویسی به بانک وصل شود که ان سرویس تنها حق دسترسی به 2 نود از 6 نود را در محیط rac دارا می باشد و یا به عبارت فنی تر، درprefer instance آن سرویس تنها اسامی دو نود نوشته شده باشد، آن فرد می تواند دستورات همروند را تنها با کمک آن دو نود اجرا کند. پس نحوه اتصال به بانک می تواند نقش اصلی را در نحوه اجرای همروند دستورات ایفا کند مگر انکه پارامترهای مذکور، تعریف شده باشند.
بر همین منوال، می توان به جای تعریف گروه برای پارامتر PARALLEL_SERVER_INSTANCES موجود در نودها، از سرویس استفاده کرد و این پارامتر را با سرویسهایی که در سطح بانک ایجاد شده اند، مقدار دهی کرد. به طور مثال، سه سرویس ایجاد می کنیم و رفتار همروندی را با سوییچ بین این سه سرویس، دنبال خواهیم کرد.
[oracle@rac1 ~]$ srvctl add service -d rac -s srv1 -r rac1
[oracle@rac1 ~]$ srvctl add service -d rac -s srv2 -r rac2
[oracle@rac1 ~]$ srvctl add service -d rac -s srv3 -r rac1,rac2
سرویسهای ایجاد شده را استارت می کنیم:
[oracle@rac1 ~]$ srvctl start service -d rac
حال فرض کنید دستور زیر را بر روی نود اول اجرا می کنیم و با تغییر مقدار برای این پارامتر، تغییر رفتار اجرای همروندی را دنبال خواهیم کرد:
select /*+parallel(a,20) */ * from usef_tbl a;
| PARALLEL_INSTANCE_GROUP | inst1 | inst2 | |
| test1 | srv1 | 20 | 0 |
| test2 | srv2 | 0 | 20 |
| test3 | srv3 | 20 | 20 |
نکته 1: در صورتی که پارامتر PARALLEL_INSTANCE_GROUP با مقداری نامعتبری تنظیم شود، سبب غیرفعال شدن همروندی با کمک نودهای دیگر خواهد شد. البته اگر این پارامتر مقداری نداشته باشد، به طور پیش فرض همروندی با کمک نودهای دیگر انجام می شود(به فرض ورود به سیستم با سرویس پیش فرض بانک).
نکته 2: در محیط RAC قبل از اجرای همروند دستورات، باید از وجود Private Interconnect قوی برای ارتباط بین نودها مطمئن بود، در غیر این صورت، کمک گرفتن از نودهای دیگر برای اجرای یک دستور، چندان مفید نخواهد بود. دستور زیر حجم رد و بدل شده بین نودها برای پاسخ دهی به اجرای همروندی دستورات را نشان می دهد:
SELECT name,TRUNC (bytes_sent/1024/1024,2) bytes_sent_MB, TRUNC(bytes_rcv /1024/1024,2) bytes_recv_MB FROM x$ksxpclient where name=’ipq’;
ipq 21.71 20.63
همچنین برای ساخت ایندکس هم باید موضوع interconnect را لحاظ کرد و در صورتی که یک نود منابع ازاد کافی برای انجام این کار را دارد، استفاده از نودهای دیگر شاید چندان به صلاح نباشد و سبب ترافیک بین نودها شود.
مانیتورینگ همروندی
در قسمت اخر این مقاله، متن پرس و جوهایی که می توانند برای بررسی و نظارت بر روی همروندی مفید باشند، اورده شده است.
*پرس و جوی زیر نشان می دهد که تعداد پروسسهای همروند در حال استفاده به چه تعداد می باشد:
select * from v$px_process_sysstat where statistic like ‘%Servers In Use%’;
+برای نمایش وضیعت(ازاد یا مشغول بودن) و مشخصات دقیق هر یک از PXها، می توان از ویوی v$px_process استفاده کرد.
*پرس و جوی زیر، اطلاعاتی را در مورد پروسسهای در حال استفاده می دهد:
select px.inst_id INT, decode(px.qcinst_id,NULL,username,’ – ‘||lower(substr(s.program,length(s.program)-4,4)) ) “Username”, decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” , to_char(px.server_set) “Slave Set”, to_char(s.sid) “SID”, decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) “QC SID”, px.req_degree “Requested DOP”, px.degree “Actual DOP”, px.qcinst_id “QC Inst” from gv$px_session px, gv$session s where px.inst_id = s.inst_id and px.sid=s.sid (+) and px.serial#=s.serial# and username not in (‘SYSTEM’)order by 6,2 desc;
| INT | Username | QC/Slave | Slave Set | SID | QC SID | Requested DOP | Actual DOP | QC Inst |
| 1 | USEF | QC | 11996 | 11996 | ||||
| 1 | – p002 | (Slave) | 1 | 8400 | 11996 | 3 | 3 | 1 |
| 1 | – p001 | (Slave) | 1 | 7198 | 11996 | 3 | 3 | 1 |
| 1 | – p000 | (Slave) | 1 | 6001 | 11996 | 3 | 3 | 1 |
*هر پروسس در یک server set چه میزان PHYSICAL READ داشته است:
SELECT a.qcsid, a.sid, a.server_group, a.server_set, substr(b.name,1,20) operation,a.value FROM v$px_sesstat a, v$statname b WHERE a.statistic# = b.statistic# AND UPPER(b.name) = ‘PHYSICAL READS’ ORDER BY a.qcsid, a.server_group, a.server_set;
| QCSID | SID | SERVER_GROUP | SERVER_SET | OPERATION | VALUE | |
| 11996 | 6001 | 1 | 1 | physical reads | 26 | |
| 11996 | 7198 | 1 | 1 | physical reads | 34 | |
| 11996 | 8400 | 1 | 1 | physical reads | 24 | |
| 11996 | 11996 | physical reads | 690 | |||
دستور زیر، وضیعت پروسسها را متناسب با نوع wait ای که دارند نشان می دهد:
SELECT px.SID “SID”, p.PID, p.SPID “SPID”, px.INST_ID “Inst”, px.SERVER_GROUP “Group”, px.SERVER_SET “Set”,px.DEGREE “Degree”, px.REQ_DEGREE “Req Degree”, w.event “Wait Event” FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;
| SID | PID | SPID | Inst | Group | Set | Degree | Req Degree | Wait Event |
| ##### | 58 | 10455 | 1 | SQL*Net message from client | ||||
| 8400 | 39 | 9186 | 1 | 1 | 1 | 3 | 3 | PX Deq Credit: send blkd |
| 6001 | 37 | 9182 | 1 | 1 | 1 | 3 | 3 | PX Deq Credit: send blkd |
| 7198 | 38 | 9184 | 1 | 1 | 1 | 3 | 3 | PX Deq Credit: send blkd |
دستور زیر، اطلاعاتی را در مورد دستورات همروندی که اجرا شده اند خواهد داد:
select name,value from v$sysstat where upper(name) like ‘%PARALLEL OPERATIONS%’ or upper(name) like ‘%PARALLELIZED%’ or upper(name) like ‘%PX%’;
| NAME | VALUE |
| queries parallelized | 11 |
| DML statements parallelized | 0 |
| DDL statements parallelized | 0 |
| DFO trees parallelized | 11 |
| Parallel operations not downgraded | 11 |
| Parallel operations downgraded to serial | 0 |
| Parallel operations downgraded 75 to 99 pct | 0 |
| Parallel operations downgraded 50 to 75 pct | 0 |
| Parallel operations downgraded 25 to 50 pct | 0 |
| Parallel operations downgraded 1 to 25 pct | 0 |
| PX local messages sent | 806 |
| PX local messages recv’d | 638 |
| PX remote messages sent | 0 |
| PX remote messages recv’d | 0 |
نکته پایانی
برای محاسبه آمارها هم می توان ار همروندی بهره گرفت این کار با تنظیم پارامتر degree در پکیج dbms_stat قابل انجام خواهد بود. برای مثال، دستور زیر با استفاده از 5 پروسس همروند، عملیات جمع اوری امار را انجام می دهد:
SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘USEF_TBL’,degree => 5);
وحید یوسف زاده
ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com