ایندکس سازی خودکار در پایگاه داده اوراکل ۱۹C

۱٫ مقدمه
ویژگی ایندکس سازی خودکار در پایگاه‌داده اوراکل ۱۹c معرفی شده، که شما را قادر می سازد تا برخی از تصمیمات مربوط به مدیریت ایندکس ها را به پایگاه‌داده منتقل کنید.

۲٫ چه کاری انجام می دهد
ویژگی ها ایندکس سازی خودکار به شرح زیر است:
–شناسایی پتانسیل ایندکس های خودکار بر اساس استفاده از ستون های جدول که با نام کاندید ایندکس (candidate indexes) ثبت می شوند.
–ایجاد ایندکس های خودکار به عنوان شاخص‌های غیرقابل مشاهده، بطوری که آن ها در execution plans استفاده نمی شود. نام های ایندکس شامل پیشوند SYS_AI است.
-تست ایندکس های خودکار غیرقابل مشاهده در دستورات sql برای اطمینان از تاثیر در بهبود عملکرد آن ها. اگر عمکرد بهبود پیدا کند ایندکس قابل مشاهده می شود، و اگر تاثیری در بهبود عملکرد نداشته باشد برجسب غیرقابل استفاده می‌خورد و سپس حذف می شود. دستورات sql که شکست میخورند در لیست سیاه قرار می گیرند و برای ساخت ایندکس های خودکار در آینده مورد توجه نیستند. ایندکس های خودکار برای اجرای اولین بار یک دستور sql توسط optimizer مورد استفاده قرار نمی گیرد.
–حذف ایندکس های بلا استفاده

۳٫ پیشنیازها
در حال حاضر این ویژگی به نسخه اینترپرایز محدود شده است. یک راه برای تست آن این است که پارامتر _exadata_feature_on را فعال کنید.

export ORACLE_SID=cdb1

export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

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

shutdown immediate;

startup;

exit;

EOF

عملیات فوق را در محیط غیر عملیاتی امتحان شود.

۴٫ پیکربندی
پکیج DBMS_AUTO_INDEX برای مدیریت ایندکس سازی خودکار استفاده می شود. در ادامه نحوه مدیریت پایه‌ای آن توضیح داده شده است.

۵٫ نمایش تنظیمات
ویو CDB_AUTO_INDEX_CONFIG وضعیت فعلی تنظیمات ایندکس سازی خودکار را نمایش می دهد.

COLUMN parameter_name FORMAT A40

COLUMN parameter_value FORMAT A15

SELECT con_id, parameter_name, parameter_value

FROM cdb_auto_index_config

ORDER BY 1, 2;

CON_ID PARAMETER_NAME PARAMETER_VALUE

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

۱ AUTO_INDEX_COMPRESSION OFF

۱ AUTO_INDEX_DEFAULT_TABLESPACE

۱ AUTO_INDEX_MODE OFF

۱ AUTO_INDEX_REPORT_RETENTION 31

۱ AUTO_INDEX_RETENTION_FOR_AUTO 373

۱ AUTO_INDEX_RETENTION_FOR_MANUAL

۱ AUTO_INDEX_SCHEMA

۱ AUTO_INDEX_SPACE_BUDGET 50

۳ AUTO_INDEX_COMPRESSION OFF

۳ AUTO_INDEX_DEFAULT_TABLESPACE

۳ AUTO_INDEX_MODE OFF

۳ AUTO_INDEX_REPORT_RETENTION 31

۳ AUTO_INDEX_RETENTION_FOR_AUTO 373

۳ AUTO_INDEX_RETENTION_FOR_MANUAL

۳ AUTO_INDEX_SCHEMA

۳ AUTO_INDEX_SPACE_BUDGET 50

SQL>

اگر ما به یک pdb سوییچ کنیم فقط می توانیم مقادیر همان container را ببینیم.

ALTER SESSION SET CONTAINER = pdb1;

SQL> SELECT con_id, parameter_name, parameter_value

FROM cdb_auto_index_config

ORDER BY 1, 2;

CON_ID PARAMETER_NAME PARAMETER_VALUE

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

۳ AUTO_INDEX_COMPRESSION OFF

۳ AUTO_INDEX_DEFAULT_TABLESPACE

۳ AUTO_INDEX_MODE OFF

۳ AUTO_INDEX_REPORT_RETENTION 31

۳ AUTO_INDEX_RETENTION_FOR_AUTO 373

۳ AUTO_INDEX_RETENTION_FOR_MANUAL

۳ AUTO_INDEX_SCHEMA

۳ AUTO_INDEX_SPACE_BUDGET 50

SQL>

۶٫ فعال/غیرفعال کردن ایندکس سازی خودکار
ایندکس سازی خودکار با استفاده از رویه CONّFIGURE از پکیج DBMS_AUTO_INDEX تنظیم می شود.
سوییچ ON/OFF برای ایندکس سازی خودکار توسط ویژگی AUTO_INDEX_MODE کنترل می شود، که مقادیر آن به شرح زیر است:
– IMPLEMENT: ایندکس سازی خودکار فعال می‌شود. ایندکس های جدید که عملکرد را بهبود می بخشند قابل رویت می شوند و برای استفاده optimizer در دسترس قرار می گیرد.
-REPORT ONLY: ایندکس سازی خودکار فعال می‌شود. اما ایندکس های جدید غیرقابل مشاهده باقی می مانند.
-OFF: ایندکس سازی خودکار غیر فعال میشود.
مثال هایی از سوییچ بین مدهای مختلف در زیر نمایش داده شده است:

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

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

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

۷٫ TABLESPACE برای ایندکس های خودکار
بطور پیشفرض ایندکس های خودکار در TableSpaceهای Permanent ایجاد می شود.اگر این مورد برای شما قابل قبول نیست می‌توانید با استفاده از ویژگی AUTO_INDEX_DEFAULT_TABLESPACE یک tablespace را مشخص کنید. در زیر یک Tablespace را برای نگهداری برای ایندکس های خودکار ایجاد می کنیم و براساس همان این ویژگی را تنظیم می کنیم.

ALTER SESSION SET CONTAINER = pdb1;

CREATE TABLESPACE AUTO_INDEXES_TS DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE’,’AUTO_INDEXES_TS’);

برای بازگشت به حالت پیشفرض، مقدار NULL را تنظیم می کنیم.

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_DEFAULT_TABLESPACE’,NULL);

۸٫ Schema-Level Control
هنگامی که ایندکس سازی خودکار فعال می شود، همه اسکیماهای معرفی شده برای شناسایی کاندیدای ایندکس تلاش می کنند . شما می توانید رفتار پیشفرض را با استفاده از ویژگی AUTO_INDEX_SCHEMA تغییر دهید، که به شما اجازه می دهد به لیست موردنظر محرومین و مشمولین را اضافه کنید.
اگر پارامتر ALLOW برروی TRUE تنظیم شود، اسکیمای موردنظر به لیست مشمولین اضافه می شود. توجه داشته باشید که یک پیشفرض از اسکیماها ایجاد می کند.

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘TEST’, allow => TRUE);

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘TEST2’, allow => TRUE);

SQL> SELECT con_id, parameter_name, parameter_value

FROM cdb_auto_index_config

ORDER BY 1, 2;

CON_ID PARAMETER_NAME PARAMETER_VALUE

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

۳ AUTO_INDEX_COMPRESSION OFF

۳ AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS

۳ AUTO_INDEX_MODE IMPLEMENT

۳ AUTO_INDEX_REPORT_RETENTION 31

۳ AUTO_INDEX_RETENTION_FOR_AUTO 373

۳ AUTO_INDEX_RETENTION_FOR_MANUAL

۳ AUTO_INDEX_SCHEMA schema IN (TEST, TEST2)

۳ AUTO_INDEX_SPACE_BUDGET 50

SQL>

لیست ورودی را می توان با استفاده از پارامتر NULL خالی کرد.

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

SQL> SELECT con_id, parameter_name, parameter_value

FROM cdb_auto_index_config

ORDER BY 1, 2;

CON_ID PARAMETER_NAME PARAMETER_VALUE

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

۳ AUTO_INDEX_COMPRESSION OFF

۳ AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS

۳ AUTO_INDEX_MODE IMPLEMENT

۳ AUTO_INDEX_REPORT_RETENTION 31

۳ AUTO_INDEX_RETENTION_FOR_AUTO 373

۳ AUTO_INDEX_RETENTION_FOR_MANUAL

۳ AUTO_INDEX_SCHEMA

۳ AUTO_INDEX_SPACE_BUDGET 50

SQL>

اگر پارامتر ALLOW را روی FALSE تنظیم کنیم، اسکیمای مشخص شده به لیست محرومین اضافه می شود.

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘TEST’, allow => FALSE);

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, ‘TEST2’, allow => FALSE);

SQL> SELECT con_id, parameter_name, parameter_value

FROM cdb_auto_index_config

ORDER BY 1, 2;

CON_ID PARAMETER_NAME PARAMETER_VALUE

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

۳ AUTO_INDEX_COMPRESSION OFF

۳ AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS

۳ AUTO_INDEX_MODE IMPLEMENT

۳ AUTO_INDEX_REPORT_RETENTION 31

۳ AUTO_INDEX_RETENTION_FOR_AUTO 373

۳ AUTO_INDEX_RETENTION_FOR_MANUAL

۳ AUTO_INDEX_SCHEMA schema NOT IN (TEST, TEST2)

۳ AUTO_INDEX_SPACE_BUDGET 50

SQL>

لیست خروجی را می توانید با پارامتر NULL خالی کنید.

EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_SCHEMA’, NULL, allow => FALSE);

SQL> SELECT con_id, parameter_name, parameter_value

FROM cdb_auto_index_config

ORDER BY 1, 2;

CON_ID PARAMETER_NAME PARAMETER_VALUE

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

۳ AUTO_INDEX_COMPRESSION OFF

۳ AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS

۳ AUTO_INDEX_MODE IMPLEMENT

۳ AUTO_INDEX_REPORT_RETENTION 31

۳ AUTO_INDEX_RETENTION_FOR_AUTO 373

۳ AUTO_INDEX_RETENTION_FOR_MANUAL

۳ AUTO_INDEX_SCHEMA

۳ AUTO_INDEX_SPACE_BUDGET 50

SQL>

۹٫ دیگر تنظیمات
پارامترهای دیگری وجود دارد که ممکن است مورد استفاده قرار گیرد، که در اینجا توضیح داده شده است:
AUTO_INDEX_COMPRESSION: برای کنترل سطح فشرده سازی استفاده می شود که مقدار پیشفرض OFF می باشد.
AUTO_INDEX_REPORT_RETENTION: مدت زمان نگهداری لاگ های ایندکس سازی خودکار، که گزارش‌ها بر اساس این لاگ ها می باشد و مقدار پیشفرض ۳۱ روز است.
AUTO_INDEX_RETENTION_FOR_AUTO: مدت زمان نگهداری، برای ایندکس های خودکار استفاده نشده است. مقدار پیشفرض ۳۷۳ روز است.
AUTO_INDEX_RETENTION_FOR_MANUAL: مدت زمان برای نگهداری ایندکس هایی که بصورت دستی ایجاد شده‌اند. زمانی که به مقدار NULL تنظیم شده باشد، ایندکس هایی که بصورت دستی ایجاد شده باشد برای حذف درنظر نمی گیرد. مقدار پیشفرض آن NULL است.
AUTO_INDEX_SPACE_BUDGET: درصدی از فضای tablespace پیشفرض که جهت ذخیره سازی ایندکس های خودکار استفاده می کند. این پارامتر زمانی که با استفاده از AUTO_INDEX_DEFAULT_TABLESPACE یک Tablespace مشخص کنیم نادیده گرفته می شود.

۱۰٫ حذف ایندکس های ثانویه
با رویه DROP_SECONDARY_INDEXES همه ایندکس ها جز ایندکس هایی که برای محدودیت ها استفاده شده‌اند، حذف می کند. این رویه می تواند در سطح دیتابیس، اسکیما و جدول انجام شود.

— Table-level

EXEC DBMS_AUTO_INDEX.drop_secondary_indexes(‘MY_SCHEMA’, ‘MY_TABLE’);

— Schema-level

EXEC DBMS_AUTO_INDEX.drop_secondary_indexes(‘MY_SCHEMA’);

— Database-level

EXEC DBMS_AUTO_INDEX.drop_secondary_indexes;

۱۱٫ ویوها
همانطور که در زیر مشاهده می‌کنید چندین ویو وجود دارد که با ویژگی های ایندکس سازی خودکار مرتبط است.

SELECT view_name

FROM dba_views

WHERE view_name LIKE ‘DBA_AUTO_INDEX%’

ORDER BY 1;

VIEW_NAME

——————————————————————————–

DBA_AUTO_INDEX_CONFIG

DBA_AUTO_INDEX_EXECUTIONS

DBA_AUTO_INDEX_IND_ACTIONS

DBA_AUTO_INDEX_SQL_ACTIONS

DBA_AUTO_INDEX_STATISTICS

DBA_AUTO_INDEX_VERIFICATIONS

SQL>

علاوه براین ویوهای {CDB|DBA|ALL|USER}_INDEXES شامل ستون AUTO هستند، که نشان می دهد ایندکس توسط ویژگی ایندکس سازی خودکار ایجاد شده است یا خیر.

COLUMN owner FORMAT A30

COLUMN index_name FORMAT A30

COLUMN table_owner FORMAT A30

COLUMN table_name FORMAT A30

SELECT owner,

index_name,

index_type,

table_owner,

table_name

table_type

FROM dba_indexes

WHERE auto = ‘YES’

ORDER BY owner, index_name;

۱۲٫ گزارش های فعالیت
پکیج DBMS_AUTO_INDEX شامل دوتابع ارائه گزارش است.

DBMS_AUTO_INDEX.REPORT_ACTIVITY (

activity_start IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP – 1,

activity_end IN TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,

type IN VARCHAR2 DEFAULT ‘TEXT’,

section IN VARCHAR2 DEFAULT ‘ALL’,

level IN VARCHAR2 DEFAULT ‘TYPICAL’)

RETURN CLOB;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY (

type IN VARCHAR2 DEFAULT ‘TEXT’,

section IN VARCHAR2 DEFAULT ‘ALL’,

level IN VARCHAR2 DEFAULT ‘TYPICAL’)

RETURN CLOB;

تابع REPORT_ACTIVITY شمارا قادر می‌سازد تا فعالیت های یک دوره ی خاص را نمایش دهید، پیشفرض آن برای روز گذشته است. تابع REPORT_LAST_ACTIVITY گزارشی از آخرین عملیات ایندکس سازی خودکار می دهد. برای خروجی هردو تابع می توان پارامترهای زیر را تنظیم کرد:
– TYPE: مقادیر مجاز (TEXT, HTML, XML)
– SECTION: مقادیر مجاز (SUMMARY, INDEX_DETAILS, VERIFICATION_DETAILS, ERRORS, ALL) . شما همچنین می توانید ترکیبی با کارکترهای + و – برای نشان دادن اینکه شامل بشود یا نشود مانند SUMMARY +ERRORS و ALL –ERRORS استفاده کنید.
– LEVEL:مقادیر مجاز (BASIC, TYPICAL, ALL)

چند نمونه استفاده از تابع ها در زیر نمایش داده شده است:

SET LONG 1000000 PAGESIZE 0

— Default TEXT report for the last 24 hours.

SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

— Default TEXT report for the latest activity.

SELECT DBMS_AUTO_INDEX.report_last_activity() FROM dual;

— HTML Report for the day before yesterday.

SELECT DBMS_AUTO_INDEX.report_activity(

activity_start => SYSTIMESTAMP-2,

activity_end => SYSTIMESTAMP-1,

type => ‘HTML’)

FROM dual;

— HTML report for the latest activity.

SELECT DBMS_AUTO_INDEX.report_last_activity(

type => ‘HTML’)

FROM dual;

— XML Report for the day before yesterday with all information.

SELECT DBMS_AUTO_INDEX.report_activity(

activity_start => SYSTIMESTAMP-2,

activity_end => SYSTIMESTAMP-1,

type => ‘XML’,

section => ‘ALL’,

“LEVEL” => ‘ALL’)

FROM dual;

— XML report for the latest activity with all information.

SELECT DBMS_AUTO_INDEX.report_last_activity(

type => ‘HTML’,

section => ‘ALL’,

“LEVEL” => ‘ALL’)

FROM dual;

SET PAGESIZE 14

در اینجا مثالی است از خروجی گزارش فعالیت پیشفرض قبل از آن که ایندکسی ایجاد شود.

SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;

GENERAL INFORMATION

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

Activity start : 03-JUN-2019 21:59:21

Activity end : 04-JUN-2019 21:59:21

Executions completed : 2

Executions interrupted : 0

Executions with fatal error : 0

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

SUMMARY (AUTO INDEXES)

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

Index candidates : 0

Indexes created : 0

Space used : 0 B

Indexes dropped : 0

SQL statements verified : 0

SQL statements improved : 0

SQL plan baselines created : 0

Overall improvement factor : 0x

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

SUMMARY (MANUAL INDEXES)

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

Unused indexes : 0

Space used : 0 B

Unusable indexes : 0

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

ERRORS

——————————————————————————–

No errors found.

——————————————————————————–

SQL>