همانطور که می دانید از طریق پارامتر undo_retention می توان تعیین کرد که undoها برای چه مدت زمانی در وضعیت unExpire قرار داشته باشند و با فرض عدم کمبود فضا برای undo tablespace، وضعیت undo dataهای unExpire بعد از چه مدت زمانی به Expire تغییر کند.

برخلاف انتظار، بسیاری از مواقع در محیطهای عملیاتی شاهد آن هستم که undo dataها بعد از مدت زمان تعیین شده برای undo_retention هم موجود و قابل استفاده هستند که در سناریوی زیر این مسئله را مشاهده می کنید.

***پارامتر undo_retention را به مقدار 30(ثانیه) تنظیم کرده و رکوردی را از جدول tbl حذف می کنیم:

SQL> alter system set undo_retention=30;

System altered.

SQL> delete tbl where id=1;

1 row deleted.

در گام بعدی، با استفاده از دو پرس و جوی زیر، segment_id و extent_id مربوط به اطلاعات حذف شده را در undo tablespace مشخص می کنیم:

SQL>    Select t.XIDUSN     segment_id,

          t.START_UEXT extent_id

          From v$session s, v$transaction t

         Where s.SADDR = t.SES_ADDR and s.SID=(select sid from v$mystat where rownum=1);

SEGMENT_ID  EXTENT_ID

———- ———-

        10         60

با کمک پرس و جوی زیر، وضعیت Extent را مورد بررسی قرار خواهیم داد:

SQL> select status  from dba_undo_extents

 where segment_name =

       (Select segment_name from dba_rollback_segs where segment_id = 10)

   and extent_id = 60;

STATUS

———

ACTIVE

با توجه به آنکه تراکنش، کماکان در حال اجرا است، undo data مربوط به آن تراکنش هم در حالت ACTIVE قرار دارد. در این شرایط، تراکنش را commit کرده و مجددا وضعیت این Extent را بررسی می کنیم:

16:56:01 SQL> commit;

Commit complete.

16:56:02 SQL> select status  from dba_undo_extents

 where segment_name =

       (Select segment_name from dba_rollback_segs where segment_id = 10)

   and extent_id = 60;

STATUS

———

UNEXPIRED

Extent در حالت UNEXPIRED قرار گرفته است. 30 ثانیه(مقدار undo_retention) منتظر می مانیم تا ببینیم این Extent در چه وضعیتی قرار می گیرد:

16:56:34 SQL> /

STATUS

———

UNEXPIRED

16:56:35 SQL> /

STATUS

———

UNEXPIRED

16:59:00 SQL> /

STATUS

———

UNEXPIRED

 همانطور که می بینید، undo بعد از گذشت 180 ثانیه هم هنوز expire نشده و قابل استفاده است:

17:02:18 SQL> select * from tbl;

no rows selected

17:02:45 SQL> select * from tbl AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘7’ MINUTE);

        ID NAME

———- ———-

         1 usef

سوال؟ چرا بعد از گذشت مدت زمان 30 ثانیه(زمان تعیین شده برای پارامتر undo_retention)، دیتای قدیمی هنوز expire نشده است؟

این مسئله به ویژگی Automatic Undo Retention Tuning برمی گردد که در ادامه مختصرا این ویژگی را توضیح خواهیم داد.

 

ویژگی Automatic Undo Retention Tuning

از نسخه 10g، اوراکل مدت زمان UNEXPIRE ماندن undoها(یا همان undo retention) را از طریق ویژگی auto undo tune کنترل می کند و جز در مواردی چون retention guarantee و LOBها توجهی به مقدار پارامتر undo_retention نخواهد کرد.

ویژگی auto undo tune، مقدار undo retention را بر مبنای on/off بودن autoextend دیتافایلهای undo Tbs محاسبه می کند به طوری که اگر autoextend دیتافایلها on باشد، tuned retention بر مبنای آمار اجرای پرس و جوها و MAXQUERYLEN محاسبه خواهد شد و در صورتی که autoextend=off باشد، صرف نظر از آمارها، مقدار tuned retention بر اساس حداکثر فضای undo Tbs تعیین خواهد شد.

tuned retention هر ده دقیقه یکبار توسط اوراکل محاسبه شده و مقدار محاسبه شده از طریق ویوی v$undostat قابل مشاهد است:

17:39:43 SQL> SELECT to_char(BEGIN_TIME,’YYYY/MM/DD HH24:mi:ss’,’NLS_CALENDAR=persian’)  BEGIN_TIME, TUNED_UNDORETENTION FROM V$UNDOSTAT ORDER BY BEGIN_TIME;

BEGIN_TIME          TUNED_UNDORETENTION

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

1400/01/16 16:39:37                 900

1400/01/16 16:49:37                 900

1400/01/16 16:59:37                 900

1400/01/16 17:09:37                 900

1400/01/16 17:19:37                 900

1400/01/16 17:29:37                 900

1400/01/16 17:39:37                 900

7 rows selected.

 

غیر فعال کردن ویژگی auto undo tune

برای غیر فعال کردن این ویژگی می توان از پارامتر مخفی undo_autotune_ استفاده کرد. با غیر فعال کردن این ویژگی، معیار undo retention، پارامتر undo_retention خواهد بود و TUNED_UNDORETENTION هم مقدار undo_retention را نشان خواهد داد.