همانطور که در مطلب “Direct path vs Conventional insert“ بیان شد، زمانی که با اجرای دستور DMLای، تغییری را در جدولی ایجاد می کنیم، قبل از اجرای دستور commit، اوراکل بلاکهای جدول را به حافظه منتقل کرده و سپس اطلاعات جدید تایید نشده را در این بلاکها درج/حذف/اصلاح می کند(در حالت Conventional) همچنین شکل قبلی رکوردها در undo segment و یا rollback segment ثبت خواهد شد.

با این مکانیزم، اجرای دستور commit، با سرعت بسیار بالایی انجام خواهد شد(حداکثر به اندازه زمان انتقال redo entryهای مربوط به این تغییر به Online Redo Log(با فرض no archive log mode و در پی آن عدم پیکربندی دیتاگارد)).

ولی از سوی دیگر، اجرای کامل دستور rollback، ممکن است ساعتها به طول بینجامد چرا که در زمان اجرای دستور rollback، باید اطلاعات قدیمی(تایید شده) جدول از undo خوانده شده و مجددا در بلاکهای آن جدول ثبت شود.(می دانیم که در این حین، اطلاعات جدول از طریق undo به کاربران نمایش داده می شود).

مقایسه سرعت اجرای دستور commit و rollback:

–commit

SQL> update mytbl set count=count+10 where id =1;

16000000 rows updated.

Elapsed: 00:03:48.35

SQL> commit;

Commit complete.

Elapsed: 00:00:00.17

 

–rollback

SQL> update mytbl set count=count+10 where id =1;

16000000 rows updated.

Elapsed: 00:04:38.15

SQL> rollback;

Elapsed: 00:04:34.80

همانطور که می بینید، اجرای دستور commit کمتر از یک ثانیه و اجرای دستور rollback حدودا چهار دقیقه به طول انجامید که در ادامه این متن قصد داریم به بررسی مخاطرات این مسئله بپردازیم.

 

نظارت بر پیشرفت عملیات rollback

در حین اجرای یک تراکنش، تعداد undo blockهای استفاده شده توسط تراکنش را می توان با کمک ستون USED_UBLK از ویوی v$transaction مانیتور کرد که البته مقدار این فیلد برای تراکنش در حال rollback کاهشی خواهد بود:

SQL> select sid,serial#,USED_UBLK  from v$transaction ,v$session where addr=taddr;

       SID    SERIAL#  USED_UBLK

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

       498       1960     306208

SQL> /

       SID    SERIAL#  USED_UBLK

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

       498       1960     250614

SQL> /

       SID    SERIAL#  USED_UBLK

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

       498       1960      44883

SQL> /

no rows selected

علاوه بر روش فوق، برای نظارت کردن بر میزان پیشرفت عملیات rollback، می توان از ویوی v$session_longops هم کمک گرفت البته با این شرط که اجرای دستور rollback بیش از 6 ثانیه زمان ببرد این قابلیت از اوراکل 10g به وجود آمد:

SQL> select round(sofar/totalwork*100) PCT,opname from v$session_longops where  opname =’Transaction Rollback’;

       PCT OPNAME

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

        83 Transaction Rollback

 

SQL> /

       PCT OPNAME

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

        89 Transaction Rollback

 

SQL> /

       PCT OPNAME

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

        100 Transaction Rollback

Rollback شدن یک تراکنش همیشه با نظر کاربر انجام نمی شود در مواقعی ممکن است این اتفاق به دلایلی چون kill شدن session، کرش کردن instance، پر شدن فضای undo و … اتفاق بیفتد که اثرات هر یک از این موارد را در ادامه شرح خواهیم دید.

 

kill شدن session

اگر sessionی که در حال اجرای تراکنشی است را kill کنیم، تراکنش در حال اجرای آن، به صورت خودکار rollback خواهد شد.

البته در زمان rollback شدن این تراکنش، نمی توان اطلاعات مربوط به عملیات rollback را از طریق v$transaction.used_ublk مشاهده کرد و برای نظارت بر پیشرفت آن باید به سراغ ویوهایی چون v$fast_start_transactions و یا x$ktuxe رفت. مثال زیر را ببینید.

فرض کنید session شماره یک در حال اجرای دستور update می باشد:

–session 1:

SQL> select s.sid,serial#,status from v$session s,v$mystat t where t.sid=s.sid and rownum=1;

       SID    SERIAL# STATUS

———- ———- ——–

872       3119              ACTIVE

SQL>update mytbl set count=count+10 where id =1;

Executing

این دستور update برای مدت زمان زیادی در حالت اجرا باقی می ماند و در همین شرایط، ادمین دیتابیس تصمیم می گیرد که این session را kill کند:

–session 2:

SQL>  select XIDUSN,sid,serial#,USED_UBLK  from v$transaction ,v$session where addr=taddr;

    XIDUSN        SID    SERIAL#  USED_UBLK

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

         20        872       3119     157092

SQL>  alter system kill session ‘872,3119’;

System altered.

SQL> select s.sid,serial#,status from v$session s where sid=869;

no rows selected

همانطور که می بینید، با kill شدن این session، اثری از آن در ویوی v$session باقی نمانده است(البته در مواردی این session برای مدتی در وضعیت KILLED در دیتابیس باقی خواهد ماند). بعد از ناپدید شدن این session، تراکنشی که توسط آن در حال اجرا بوده به صورت Dead transaction در خواهد آمد.

روند پیشرفت rollback شدن این تراکنش را می توان با کمک ویوی v$fast_start_transactions مانیتور کرد:

SQL> alter session set nls_date_format=’YYYY/MM/DD HH24:mi:ss’;

Session altered.

SQL> SELECT usn, state, undoblockstotal “Total”, undoblocksdone “Done”,undoblockstotal – undoblocksdone “ToDo”,round(undoblocksdone/undoblockstotal*100) PROGRES_PCT,DECODE(cputime,0,’unknown’,SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Finish at” ,sysdate FROM v$fast_start_transactions  where undoblockstotal – undoblocksdone>0;

 همچنین ویوی x$ktuxe هم می تواند در این زمینه به ما کمک کند ستون ktuxesiz تعداد undo blockهای تراکنشی که در حال rollback شدن است  را نمایش می دهد:

SQL>   select ktuxeusn , ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = ‘DEAD’ and ktuxesta=’ACTIVE’;

  KTUXEUSN   KTUXESIZ KTUXESTA

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

        20     42080    ACTIVE

SQL> /

  KTUXEUSN   KTUXESIZ KTUXESTA

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

        20     40712    ACTIVE

خروجی این دستور مشخص می کند که 40712 بلاک(undo block) دیگر باید اعمال شوند تا عملیات rollback کامل شود.

 

کرش کردن instance

با کرش کردن instance، تراکنشهای در حال اجرا بعد از open شدن دیتابیس، توسط بک گراند پروسس SMON،ه rollback خواهند شد(ROLL FORWARD). این عملیات می تواند مصرف بالای منابع و در پی آن، کندی دیتابیس را به همراه داشته باشد. مثال زیر را ببیند.

Session شماره 1 به مدت بیش از یک ساعت است که در حال اجرای دستور update زیر می باشد:

–session 1:

SQL> update mytbl set count=count+10 where id =1;

Executing…

ادمین دیتابیس تصمیم می گیرد تا دیتابیس را restart کند:

–session 2:

SQL> startup force

ORACLE instance started.

Total System Global Area 7516189792 bytes

Fixed Size                  8914016 bytes

Variable Size             503316480 bytes

Database Buffers         6459228160 bytes

Redo Buffers              544731136 bytes

Database mounted.

Database opened.

بعد از open شدن دیتابیس، می توانیم از طریق ویوی v$fast_start_transactions میزان پیشرفت عملیات roll backward را مشاهده کنیم و به صورت تخمینی، زمان پایان عملیات rollback را مشخص کنیم:

SQL> SELECT usn, state, undoblockstotal “Total”, undoblocksdone “Done”,undoblockstotal – undoblocksdone “ToDo”,round(undoblocksdone/undoblockstotal*100) PROGRES_PCT,DECODE(cputime,0,’unknown’,SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Finish at” ,sysdate FROM v$fast_start_transactions  where undoblockstotal – undoblocksdone>0;

SQL> select ktuxeusn , ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = ‘DEAD’ and ktuxesta=’ACTIVE’;

  KTUXEUSN   KTUXESIZ KTUXESTA

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

        31     111440 ACTIVE

عملیات ریکاوری به صورت parallel و با 16 پروسس در حال انجام است:

SQL> select count(*) from V$FAST_START_SERVERS where state=’RECOVERING’ ;

  COUNT(*)

———-

        16

این پروسسها منابع نسبتا زیادی را از سرور به خود اختصاص داده اند: 

top eventها هم این مسئله را ثابت می کنند:

همچنین بیشترین I/O دیتافایلها، مربوط به undo tablespace می باشد:

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

 

راهکار اول

به عنوان راهکار اول(و البته یک مسکن)، می توانیم عملیات ریکاوری را متوقف کرده و انجام ان را به زمان دیگری موکول کنیم. دستور زیر، SMON را از انجام عملیات transaction recovery منصرف خواهد کرد:

SQL> select pid from v$process where PNAME like ‘%SMON%’;

       PID

———-

        23

SQL> oradebug setorapid 23

Oracle pid: 23, Unix process pid: 23251, image: oracle@ol6 (SMON)

SQL> oradebug event 10513 trace name context forever, level 2

Statement processed.

SQL> select count(*) from V$FAST_START_SERVERS where state=’RECOVERING’;

  COUNT(*)

———-

0

SQL> select ktuxeusn , ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = ‘DEAD’ and ktuxesta=’ACTIVE’;

  KTUXEUSN   KTUXESIZ KTUXESTA

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

         2     731342 ACTIVE

SQL> /

  KTUXEUSN   KTUXESIZ KTUXESTA

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

         2     731342 ACTIVE

با این کار، مصرف منابع سرور به حداقل خواهد رسید:

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

SQL> oradebug setorapid 23

Oracle pid: 23, Unix process pid: 27794, image: oracle@ol6 (SMON)

SQL> oradebug event 10513 trace name context off

Statement processed.

SQL> oradebug event 10513 trace name context off

Statement processed.

SQL> select ktuxeusn , ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = ‘DEAD’ and ktuxesta=’ACTIVE’;

  KTUXEUSN   KTUXESIZ KTUXESTA

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

         2     729701 ACTIVE

SQL> /

  KTUXEUSN   KTUXESIZ KTUXESTA

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

         2     729581 ACTIVE

در alert log هم اطلاعاتی از استارت مجدد و یا توقف شدن SMON ثبت خواهد شد:

SMON: parallel recovery restart with degree=0 (!=32)

2020-06-23T12:30:30.150747+00:00

SMON: parallel recovery restart with degree=0 (!=32)

  Current log# 6 seq# 1792 mem# 1: /19c/fra/DB19C/onlinelog/o1_mf_6_hg93d490_.log

2020-06-23T12:30:30.188946+00:00

SMON: parallel recovery restart with degree=0 (!=32)

SMON: parallel recovery restart with degree=0 (!=32)

SMON: Restarting fast_start parallel rollback

2020-06-23T12:30:30.249347+00:00

 

راهکار دوم

به عنوان راهکار دوم، می توان عملیات ریکاوری را به صورت سریال و با تعداد پروسسهای کمتری انجام داد:

SQL> select count(*) from V$FAST_START_SERVERS where state=’RECOVERING’;

  COUNT(*)

———-

        16

SQL> ALTER SYSTEM SET fast_start_parallel_rollback=’FALSE’;

System altered.

 

پارامتر FAST_START_PARALLEL_ROLLBACK

برای سرعت بخشیدن به انجام عملیات SMON، می توان پارامتر FAST_START_PARALLEL_ROLLBACK را به مقدار LOW و یا HIGH تنظیم کرد تا عملیات  ROLLBACK به صورت parallel و با چند پروسس انجام شود.

سه مقدار زیر را می توان برای این پارامتر تنظیم کرد:

FALSE: عملیات Parallel Rollback را غیرفعال خواهد کرد.

LOW: عملیات Parallel Rollback حداکثر می تواند با درجه 2 * CPU_COUNT انجام شود.

HIGH: عملیات Parallel Rollback حداکثر می تواند با درجه 4 * CPU_COUNT انجام شود.