automatic indexing یکی از مهمترین قابلیتهایی است که در اوراکل 19c ارائه شد، این قابلیت، متناسب با دستورات sqlای که در دیتابیس اجرا می شوند، به صورت خودکار، برای ستونهای پراستفاده ایندکسی را ایجاد خواهد کرد البته علاوه بر امکان ایجاد خودکار ایندکسها، قابلیت حذف ایندکسهایی که در درازمدت از انها استفاده نشده است را هم فراهم می کند.

auto indexها در ابتدا به صورت invisible ایجاد خواهند شد و در صورتی که بهبودی را در سرعت اجرای دستورات ایجاد کنند، به حالت visible در خواهند امد همچنین اگر auto index، توسط دستور sqlای کپچر شده، مورد استفاده قرار نگیرد، در حالت unusable قرار خواهد گرفت و بعد از طی شدن بازه زمانی معینی، پاک خواهد شد همچنین دستور مورد نظر هم در black list قرار می گیرد.

فعالسازی ویژگی auto indexing

این قابلیت را می توان به دو شکل فعال کرد:

IMPLEMENT: ایندکسها به صورت خودکار ایجاد خواهند شد و در صورت استفاده توسط دستورات sqlای کپچر شده، در وضعیت visible قرار خواهند گرفت.

REPORT ONLY: ایندکسها به صورت خودکار و در حالت invisible ایجاد می شوند و در همین وضعیت باقی خواهند ماند و در زمان اجرای دستورات، optimizer از انها استفاده نمی کند.

برای فعال و یا غیرفعال کردن این قابلیت، باید از پروسیجر DBMS_AUTO_INDEX.CONFIGURE استفاده کرد:

–فعالسازی در سطح IMPLEMENT:

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE‘,’IMPLEMENT‘);

–فعالسازی در سطح REPORT ONLY:

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE‘,’REPORT ONLY‘);

همچنین برای غیرفعال کردن قابلیت auto indexing می توان دستور زیر را اجرا نمود:

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE‘,’OFF‘);

دستور زیر، وضعیت فعلی این قابلیت را نمایش خواهد داد:

select parameter_value from   DBA_AUTO_INDEX_CONFIG where parameter_name=’AUTO_INDEX_MODE’;

بصورت پیش فرض، قابلیت auto indexing در دو حالت IMPLEMENT و REPORT ONLY، برای همه schemaها فعال خواهد شد و استثنا کردن بعضی از این schemaها، در هنگام استفاده از این قابلیت، هم امکان پذیر می باشد، برای مثال، با دستور زیر، کاربر ali اسثتنا شده و اصطلاحا در exclusion list قرار می گیرد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘ALI’, FALSE);

PL/SQL procedure successfully completed

با دستور زیر، لیست schemaهایی که در exclusion list قرار دارند را مشاهده خواهیم کرد:

select * from   DBA_AUTO_INDEX_CONFIG where parameter_name=’AUTO_INDEX_SCHEMA’;

در صورت منصرف شدن از این تصمیم، می توان دستور زیر را اجرا نمود:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘ALI’, NULL);

PL/SQL procedure successfully completed

همچنین دستور زیر، همه schemaها را از exclusion list خارج خواهد کرد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, NULL, TRUE);

PL/SQL procedure successfully completed

استفاده از قابلیت auto indexing در محیط enterprise edition

تا نسخه 19cR3، هنوز امکان استفاده از قابلیت auto indexing در محیط enterprise edition وجود ندارد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);

ORA-40216: feature not supported

برای استفاده از این قابلیت در محیط enterprise edition، به ناچار باید پارامتر مخفی exadata_feature_on_ را فعال نمود:

SQL> alter system set “_exadata_feature_on”=true scope=spfile;

System altered.

SQL> startup force;

با تنظیم این پارامتر، امکان فعالسازی این قابلیت فراهم خواهد شد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,’IMPLEMENT’);

PL/SQL procedure successfully completed.

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

select * from   DBA_AUTO_INDEX_CONFIG;

بعد از فعالسازی قابلیت auto indexing در سطح implement، قصد داریم با ارائه مثالی، نحوه عملکرد auto indexing را مورد بررسی قرار دهیم.

مثال: جدولی را با نام mytbl ایجاد کرده و اطلاعاتی را به ان اضافه می کنیم:

SQL> create table mytbl as select * from sys.source$;

Table created

SQL> insert into mytbl select * from mytbl;

295545 rows inserted

SQL> insert into mytbl select * from mytbl;

591090 rows inserted

SQL> insert into mytbl select * from mytbl;

1182180 rows inserted

SQL> commit;

Commit complete

برای جدول mytbl ایندکسی ایجاد نشده است پس در صورتی که بخواهیم با دستور زیر به جدول mytbl دسترسی پیدا کنیم، به ناچار یک عمل TABLE ACCESS FULL رخ خواهد داد:

select count(*) from mytbl where obj#=1767;

با بلاک زیر، این دستور را 100 بار اجرا می کنیم و انتظار داریم با فرا رسیدن interval پانزده دقیقه ای قابلیت auto indexing، ایندکسی برای این دستور ایجاد شود.

declare

id number;

begin

  for i in 1..100 loop

     select count(*) into id  from mytbl where obj#=1767;

   end loop;

end;

پس از گذشت interval پانزده دقیقه ای، اطلاعات ویوی DBA_AUTO_INDEX_EXECUTIONS را مورد بازبینی قرار می دهیم:

select * from   DBA_AUTO_INDEX_EXECUTIONS;

همانطور که می بینید، auto indexing یکبار اجرا شده و exection_name آن برابر SYS_AI_2019-05-26/06:36:51 می باشد.

برای مشاهده اطلاعات جزیی تر در مورد این اجرا، به ویوی DBA_AUTO_INDEX_STATISTICS سری می زنیم:

select * from  DBA_AUTO_INDEX_STATISTICS where  EXECUTION_NAME=’SYS_AI_2019-05-26/06:36:51′;

قابل مشاهده است که auto indexای ایجاد شده و در وضعیت visible قرار دارد.

همچنین ویوی DBA_AUTO_INDEX_IND_ACTIONS مشخص می کند که ایندکس با چه نامی و در طی چه مراحلی ایجاد شده است:

select * from   DBA_AUTO_INDEX_IND_ACTIONS where EXECUTION_NAME=’SYS_AI_2019-05-26/06:36:51′;

همانطور که می بینید، برای ستون #obj، ایندکسی به نام SYS_AI_f0p99q0yv3sdr ایجاد شده است این ایندکس در ابتدا به صورت unusable و invisible ایجاد شده و پس از بازسازی، در وضعیت visible قرار گرفته است.

با این تغییرات، مجددا execution plan دستور sqlای را بازبینی می کنیم:

select count(*) from mytbl where obj#=1767;

همانطور که قابل مشاهده است، در زمان اجرای این دستور، از auto index ایجاد شده، استفاده می شود.

نکته 1: برای مشاهده لیست auto indexهای ایجاد شده، می توان از دستور زیر استفاده کرد:

select owner,index_name,table_name,index_type,auto from dba_indexes where AUTO=’YES’;

نکته 2: به جهت ممانعت از استفاده auto indexها برای یک دستور sqlای مشخص، می توان از هینت NO_USE_AUTO_INDEXES استفاده کرد.

نگهداری ایندکسها

auto index به صورت پیش فرض در tablespace، سیستم ایجاد خواهند شد. با کمک خصیصه AUTO_INDEX_DEFAULT_TABLESPACE، امکان تغییر tablespace پیش فرض این نوع از ایندکسها هم وجود دارد برای مثال، با دستور زیر، tablespace پیش فرض auto indexها، به MTBS تغییر خواهد کرد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE’,’MTBS’);

PL/SQL procedure successfully completed

SQL> select parameter_value from DBA_AUTO_INDEX_CONFIG where parameter_name=’AUTO_INDEX_DEFAULT_TABLESPACE’;

PARAMETER_VALUE

—————–

MTBS

در صورتی که auto indexهای ایجاد شده،  به مدت 373 روز مورد استفاده قرار نگیرند، به صورت خودکار حذف خواهند شد:

select parameter_value from DBA_AUTO_INDEX_CONFIG where parameter_name=’AUTO_INDEX_RETENTION_FOR_AUTO‘;

373

برای تغییر این زمان، می توان از گزینه AUTO_INDEX_RETENTION_FOR_AUTO استفاده کرد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_AUTO’, ’10’);

PL/SQL procedure successfully completed

ویژگی auto indexing در کنار حذف خودکار auto indexهای استفاده نشده، قابلیت حذف non-auto indexها(ایندکسهایی که به صورت دستی ایجاد شده اند) را هم فراهم می کند:

select parameter_value from DBA_AUTO_INDEX_CONFIG where parameter_name=’AUTO_INDEX_RETENTION_FOR_MANUAL’;

برای مثال، با دستور زیر، ایندکسهایی manual اگر به مدت 20 روز مورد استفاده قرار نگیرند، حذف خواهند شد:

SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_RETENTION_FOR_MANUAL’, ’20’);

PL/SQL procedure successfully completed

تهیه گزارش از عملیات auto indexing

با کمک دو تابع REPORT_ACTIVITY و REPORT_LAST_ACTIVITY از پکیج DBMS_AUTO_INDEX می توان گزارشی را از عملیات انجام شده توسط قابلیت automatic indexing ایجاد نمود. این گزارش می تواند در یکی از قالبهای TEXT، HTML و یا XML ایجاد شود.

–تهیه گزارش برای بازه زمانی مشخص با فرمت HTML:

select DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP-1,SYSTIMESTAMP,’HTML’,’ALL’,’ALL’) from dual;

–ایجاد گزارشی از اخرین اجرا:

select DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(‘TEXT’,’ALL’,’ALL’) from dual;