#پرسش: کدام یک از ایندکسها در بانک اطلاعاتی بلاستفاده و قابل حذف هستند؟ تعداد رجوع به یک ایندکس را چگونه می توان تعیین کرد؟

پاسخ: دست یافتن به پاسخ قسمت اول این سوال، در اوراکل نسخه 10g و 11g نسبتا کار ساده ای است این کار با اجرای یک دستور و همچنین طول کشیدن مدت زمانی از اجرای ان(گاها بیش از چند ماه)، قابل انجام خواهد بود.

برای این کار ابتدا باید با دستور زیر، مانیتورینگ را بر روی ایندکسهای مورد نظر فعال کرد:

ALTER INDEX <index_name> MONITORING USAGE;

و با طی شدن یک بازه زمانی از اجرای این دستور، می توان با کمک ویوی V$OBJECT_USAGE بررسی کرد که این ایندکس مورد استفاده قرار گرفته است یا خیر؟

SELECT index_name, used, monitoring FROM V$OBJECT_USAGE WHERE index_name = ‘<index_name>’;

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

نکته 1: در اوراکل نسخه 10g، این روش معایب دیگری هم دارد! در این نسخه، با جمع اوری امار و به عبارتی دقیق تر، اجرای بسته dbms_stat برای ایندکس مورد نظر، مقدار فیلد monitoring برای ان ایندکس، برابر با YES خواهد شد.

نکته 2: در هنگام استفاده از روشهای مختلف مانیتورینگ ایندکسها، باید مواظب ایندکسهایی که بر روی FKها ایجاد شده اند، باشیم و در صورت عدم رجوع به ان ایندکسها، انها را حذف نکنیم.

این شیوه از مانیتورینگ در نسخه 12c و 18c هم وجود دارد و می توان با کمک آن، بر روی دسترسی یا عدم دسترسی به یک ایندکس؛ در یک بازه زمانی مشخص نظارت داشت با این وجود، در نسخه 12cR2، قابلیت جدیدی هم عرضه شد که با ارائه دو ویوی DBA_INDEX_USAGE و V$INDEX_USAGE_INFO، تا حدودی نقطه ضعف روش قبلی را برطرف می کند و با کمک این دو ویو، دقیقا مشخص خواهد شد که چند بار به یک ایندکس رجوع شده است.

این قابلیت در اوراکل 12c و 18c به طور پیش فرض در سطح SAMPLE فعال می باشد و برای انجام مانیتورینگ در سطح ALL، می توان از پارامتر مخفی _iut_stat_collection_type در سطح session و یا system استفاده کرد در این صورت، فیلد INDEX_STATS_COLLECTION_TYPE از ویوی V$INDEX_USAGE_INFO برابر با صفر خواهد شد.

در این روش از مانیتورینگ، اطلاعات مربوطه ابتدا در حافظه قرار میگیرد و هر 15 دقیقه یکبار به دیسک منتقل خواهد شد(به اصطلاح flush رخ خواهد داد).

برای مشاهده جزییات میزان استفاده از ایندکسها، باید از ویوی DBA_INDEX_USAGE استفاده کرد که البته این ویو، اطلاعات بعد از flush آخر(که در حافظه قرار دارند) را نشان نمی دهد و قرار است در هر 15 دقیقه، خروجی آن بروزرسانی شود.

همچنین برای مشاهده جزییات اطلاعات موجود در حافظه، نظیر  count_FLUSH و یا LAST_FLUSH_TIME می توان از ویوی V$INDEX_USAGE_INFO استفاده کرد.

در ادامه با ارائه یک سناریو، به بررسی بیشتر این قابلیت خواهیم پرداخت.

*** در ابتدا جدولی را به همراه یک ایندکس ایجاد می کنیم:

create table tbl as select * from sys.SOURCE$;

create index ind_monitoring on tbl(line);

با دستور زیر، مانیتورینگ را در سطح ALL فعال کرده:

alter system set “_iut_stat_collection_type”=ALL;

و پرس و جویی را اجرا می کنیم که با اجرای ان، به ایندکس ind_monitoring هم رجوع خواهد شد:

select count(*) from tbl where line =12;

بلافاصله بعد از اجرای این پرس و جو، از ویوی DBA_INDEX_USAGE استفاده می کنیم تا تعداد رجوع به ایندکس ind_monitoring را مشاهده کنیم:

SELECT total_access_count FROM  dba_index_usage where name=’IND_MONITORING’;

no rows selected

همانطور که می بینید، اطلاعاتی در مورد این ایندکس وجود ندارد شاید این فقدان، ناشی از عدم گذشتن زمان 15 دقیقه برای انتقال اطلاعات از حافظه به دیسک باشد، پس زمان last_flush_time را در ویوی v$index_usage_info را با ساعت جاری مقایسه می کنیم:

select LAST_FLUSH_TIME,ACTIVE_ELEM_COUNT,sysdate from V$INDEX_USAGE_INFO;

بعد از گذشت 15 دقیقه از زمان last_flush_time، ویوی dba_index_usage را بازبینی می کنیم:

SELECT total_access_count,last_used FROM  dba_index_usage where name =’IND_MONITORING’;

همانطور که می بینید، تعداد رجوع به ایندکس IND_MONITORING برابر با یک می باشد این عدد با دسترسی های مجدد به این ایندکس، افزایش خواهد یافت.

نکته پایانی: قابلیت مانیتورینگ ایندکسها در نسخه 12cR2، معایبی را هم به همراه دارد به طور نمونه، استفاده از بسته dbms_stat برای جمع آوری امار ایندکسها، سبب خواهد شد تا به تعداد رجوع به ایندکسها افزوده شود.

وحید یوسف زاده

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com