همانطور که می دانید، از طریق قابلیت (Automatic Data Optimization (ADO می توان سیاستهایی را بر اساس آمارهای دستیابی و اصلاح segmentها، برای کاهش فضای مصرفی و بهبود کارایی اعمال کرد برای مثال با فشرده سازی جداولی که کمتر مورد اصلاح و یا دستیابی قرار می گیرند و نیز انتقال segmentهای کم طرفدار به دیسک با هزینه کمتر، می توان قدمهایی را در زمینه بهینه سازی برداشت.

ADO در اوراکل نسخه 12c ارائه شد و بعدا در هر کدام از نسخه ها، قابلیتهای جدیدی به آن اضافه شد که در این مطلب، به قابلیت جدید اوراکل نسخه 21c در این زمینه خواهیم پرداخت.

در این نسخه می توان با کمک ADO برای ایندکسها پالیسیهایی را وضع کرد مثلا می توان بر اساس تاریخ آخرین زمان دستیابی و اصلاح، در سه سطح فشرده سازی(compressing)، بازسازی(rebuilding) و کاهش حجم(shrinking) قوانینی را اعمال نمود.

برای استفاده از این فیچر، باید قابلیت HEAT_MAP را فعال کنیم تا اوراکل با ثبت data access و data modification آمار فعالیتهای مربوط به ایندکس را(همانند جداول و بقیه segmentها) جمع آوری کرده و زیر نظر داشته باشد.

در ادامه پالیسی ای را با طی چند مرحله ایجاد خواهیم کرد که بر اساس آن، اگر به ایندکسی به مدت 4 روز رجوع نشود(no access)، ایندکس optimize شود. optimize می تواند یکی از عملیات فوق(compressing، rebuilding و shrinking) باشد که تصمیم گیری آن با خود اوراکل است.

1.در قدم اول، برای استفاده از قابلیت ADO، باید پارامتر HEAT_MAP را به مقدار ON تنظیم کنیم.

SQL> alter system set heat_map=ON;
System altered

توجه: برای مشاهده آمارهای جمع آوری شده توسط قابلیت HEAT MAP، می توان از ویوی V$HEAT_MAP_SEGMENT استفاده کرد(مطالعه بیشتر).

 

2.جدول و ایندکسی را برای پیش بردن این سناریو ایجاد می کنیم:

SQL> create table tbl_IR_21c
as
select * from dba_objects
union all
select * from dba_objects
union all
select * from dba_objects;
SQL> create index ind1_tbl21c on tbl_IR_21c(object_name);
Index created

حجم ایندکس ind1_tbl21c حدودا 12 مگابایت است:

SQL> select SEGMENT_NAME,bytes/1024/1024 size_MB from user_segments p where p.segment_name=upper('IND1_TBL21C');
SEGMENT_NAME    SIZE_MB
------------ ----------
IND1_TBL21C          12

و برای کوئری زیر، از این ایندکس استفاده می شود:

SQL> select * from tbl_IR_21c i where i.object_name='TBL_IR_21C';

 

3.با دستور زیر پالیسی ای را ایجاد می کنیم که بر اساس آن، با چهار روز عدم دسترسی به ایندکس، یکی از عملیات OPTIMIZE انجام شود:

SQL> ALTER INDEX ind1_tbl21c ILM ADD POLICY OPTIMIZE AFTER 4 DAYS OF NO ACCESS;
Index altered

مشخصات policy ایجاد شده را با می توان با ویوی user_ilmdatamovementpolicies مشاهده کرد:

SELECT p.policy_name,p.action_type,p.scope,p.condition_type,p.condition_days FROM user_ilmdatamovementpolicies p;

 

4.برای تست کردن ADO Policy ایجاد شده، کوئری زیر را یکبار اجرا می کنیم:

SQL> select * from tbl_IR_21c i where i.object_name='TBL_IR_21C';

با اجرای این کوئری، به ایندکس IND1_TBL21C رجوع می شود با توجه به اینکه قابلیت heat map را در مرحله اول فعال کرده ایم، زمان دستیابی به ایندکس در ویوی V$HEAT_MAP_SEGMENT قابل مشاهده است:

SQL> SELECT object_name,segment_write,segment_read,track_time,lookup_scan FROM V$HEAT_MAP_SEGMENT where object_name=upper('ind1_tbl21c');
OBJECT_NAME  SEGMENT_WRITE SEGMENT_READ TRACK_TIME  LOOKUP_SCAN
------------ ------------- ------------ ----------- -----------
IND1_TBL21C  NO            YES          01/12/2022  YES

قصد داریم تغییراتی را ایجاد کنیم تا ایندکس صلاحیت shrink و یا rebuild شدن را پیدا کند. یک راهکار، حذف همه  اطلاعات جدول است:

SQL> delete tbl_IR_21c;
226392 rows deleted
SQL> commit;
Commit complete

با حذف این اطلاعات، تغییری در حجم ایندکس ایجاد نمی شود:

SQL> select SEGMENT_NAME,bytes/1024/1024 size_MB from user_segments p where p.segment_name=upper('IND1_TBL21C');
SEGMENT_NAME    SIZE_MB
------------ ----------
IND1_TBL21C          12

البته اگر از زمان دسترسی به ایندکس 4 روز بگذرد، ADO Policy فعال می شود و سایز ایندکس را کاهش می دهد. با توجه به آنکه نمی توانیم چهار روز منتظر بمانیم، تاریخ سرور را تغییر می دهیم:

[root@oLinux7 ~]# date
Wed Jan 12 08:03:16 EST 2022
[root@oLinux7 ~]# date -s "Wed Jan 22 08:03:16 EST 2022"
Sat Jan 22 08:03:16 EST 2022

با اجرای پروسیجر dbms_ilm.preview_ilm، سیاستهای وضع شده را به صورت دستی مورد بررسی قرار خواهیم داد تا در صورت فراهم شدن شرایط تعیین شده، taskای را ایجاد کند(ین پروسیجر در بازه زمانی مشخصی به صورت خودکار انجام خواهد شد):

declare
v_task_id number:=0;
BEGIN
dbms_ilm.preview_ilm(task_id => v_task_id,ilm_scope => dbms_ilm.SCOPE_SCHEMA);
dbms_output.put_line('task id = ' || v_task_id);
if v_task_id is null then
dbms_output.put_line('task id is null');
end if;
END;
/

Task شماره 323 و جاب ILMJOB3530 ایجاد شد و آماده اجرا است:

SELECT p.task_id,p.policy_name,p.object_name,p.object_type,p.selected_for_execution,p.job_name FROM DBA_ILMEVALUATIONDETAILS p where task_id=323;

از طریق پروسیجر dbms_ilm.execute_ilm_task، این task را اجرا می کنیم(البته پروسیجر هم در بازه زمانی مشخصی به صورت خودکار اجرا خواهد شد):

begin
  dbms_ilm.execute_ilm_task(task_id            => 323,
                            execution_mode     => dbms_ilm.ILM_EXECUTION_ONLINE,
                            execution_schedule => dbms_ilm.SCHEDULE_IMMEDIATE);
end;
/

جاب ILMJOB3530 با موفقیت اجرا شد:

SELECT task_id, job_name, job_state FROM user_ilmresults;

با اجرای این جاب، حجم ایندکس از 12 مگابایت به 65536 بایت کاهش یافته است:

SQL>  select SEGMENT_NAME,bytes/1024/1024 size_MB from user_segments p where p.segment_name=upper('IND1_TBL21C');
SEGMENT_NAME       SIZE_MB
--------------- ----------
IND1_TBL21C         0.0625