ایندکس های بی استفاده سبب ایجاد سربار در عملیات DML می شوند و فضای دیتابیس را هدر می دهند. همانطور که در مطلب روشی برای شناسایی ایندکس های تکراری اشاره شد می توان تعداد موارد بکارگیری ایندکس ها توسط OPTIMIZER در بازه های زمانی گذشته را استخراج کرد و ایندکس های بی استفاده را حذف نمود. در این متن سه روش مختلف برای شناسایی ایندکس های بی استفاده را توضیح می دهیم.
دیتابیس اوراکل بدون توجه به کاربرد ایندکس ها، بخش قابل توجه ای از CPU و دسترسی های I/O را برای نگه داری و بروزرسانی آنها اختصاص می دهد. بنابراین تعداد و حجم بالای ایندکس ها می تواند در سیستم های OLTP سربار ایجاد کند. لذا شناسایی و حذف کردن ایندکس های بی استفاده از اهمیت زیادی برخوردار است.
از طرفی دیگر حذف کردن ایندکس هایی که توسط اکثر روش ها به عنوان ایندکس های بی استفاده شناسایی می شوند ممکن است از لحاظ پرفورمنس مناسب نباشد. زیرا معمولا این روش ها ایندکس های زیر را بی استفاده تشخیص می دهند:
1.ایندکس هایی که در یک بازه زمانی خاص بی استفاده هستند ولی در زمان های دیگر کاربرد زیادی دارند.
2.ایندکس هایی که برای ستون های FOREIGN KEY تعریف شده اند و از SHARE-SUBEXCLUSIVE TABLE LOCK در جدول CHILD جلوگیری می کنند.
3.ایندکس هایی که فقط برای بررسی CONSTRAINTها و RECURSIVE SQL در زمان اجرا استفاده می شوند.
بنابراین باید توجه کرد که حذف کردن این دسته از ایندکس ها می تواند باعث ایجاد برخی ازمشکلات دیگر شود.
روش اول: AWR
در نسخه های ENTERPRISE اوراکل نگارش 10g به بعد، برای جمع آوری و گزارش اطلاعات پرفورمنسی دیتابیس می توان از AWR بهره برد. با استفاده از ویووهای AWR و SNAP_IDهای دلخواه، تعداد موارد بکارگیری ایندکس های دیتابیس توسط OPTIMIZER در بازه های زمانی خاص قابل استخراج هستند.
QUERY زیر با استفاده از AWR تعداد موارد SCAN ایندکس (در اینجا ایندکس IND_TBL_01) را گزارش می دهد:
select
p.object_name as “object name”,
p.operation as “operation”,
p.options as ” scan model”,
count(1) as “index usage count”
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner in (‘MILAD’,‘AHMAD’) /* index schemas */
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
and s.snap_id between 85 and 110 /* start snap_id and end snap_id*/
and p.object_name=‘IND_TBL_01‘
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
نکته: در این QUERY تعداد و نوع SCAN برای ایندکس ها در بازه زمانی دلخواه (SNAP_ID) گزارش می شود.
می توان QUERY بالا را برای تمام ایندکس های دیتابیس استفاده کرد:
select
p.object_name as “object name”,
p.operation as “operation”,
p.options as ” scan model”,
count(1) as “index usage count”
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner not in (‘SYS’) /* index schemas */
and
p.operation like ‘%INDEX%’
and
p.sql_id = s.sql_id
and s.snap_id between 85 and 110 /* start snap_id and end snap_id*/
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;
در ادامه یک QUERY دیگر از نوع AWR برای مشاهده تعداد موارد بکارگیری ایندکس IND_TBL_01 در بازه های زمانی مختلف را مشاهده می کنید:
select
to_char(sn.begin_interval_time,‘yy-mm-dd hh24’) as “Begin Interval time”,
count(*) as “Invocation Count”
from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_name = ‘IND_TBL_01‘
group by
begin_interval_time,search_columns;
روش دوم: MONITORING USAGE
هر زمان که ایندکس ها با دستور alter index <index_name> monitoring usage در حالت monitoring قرار گیرند در ادامه ی فعالیت دیتابیس اگر OPTIMIZER از آن ایندکس ها استفاده کند سابقه بکارگیری آنها در ویووی V$object_usage ثبت خواهد شد:
نکته: می توان تمام ایندکس های یک اسکیما را به حالت monitoring برد:
select
‘alter index ‘||owner||’.’||index_name||’ monitoring usage;’
from
dba_indexes
where
owner =’MILAD’
;
بنابراین با گذشت زمان و اجرا شدن QUERYهای مختلف در دیتابیس می توان ویوی V$object_usage را بررسی کرد و ایندکس های بی استفاده را شناسایی نمود:
select
index_name
mon,
used
from
v$object_usage;
نکته: روش غیر فعال کردن Monitoring Usage:
ALTER INDEX index_name NOMONITORING USAGE;
select
‘alter index ‘||owner||’.’||index_name||’ nomonitoring usage;’
from
dba_indexes
where
owner =’MILAD’
;
نکته: در نگارش های قبل از 12C فقط با اسکیمایی که monitoring ایندکس هایش فعال شده است می توان محتویات ویووی V$object_usage را مشاهده نمود. در نگارش های 12C به بعد ویووی V$object_usage از رده خارج شده است و DBA_OBJECT_USAGE جایگزین آن شده است. در این ویوو با اضافه شدن ستون OWNER این مشکل حل شده است.
روش سوم: STATSPACK
پکیج STATSPACK مجموعه ای از اسکریپت هاس SQL و PL/SQL است که با استفاده از آنها می توان اطلاعات پرفورمنسی دیتابیس را جمع آوری و مشاهده نمود. البته برخلاف AWR می بایست پکیج STATSPACK را نصب کرد و سطح جمع آوری اطلاعات را تنظیم کرد.
نکته: SNAP_ID های STATSPACK متفاوت با SNAP_ID های AWR هستند.
QUERY زیر با استفاده از STATSPACK ایندکس هایی که در بازه های زمانی خاص استفاده نشده اند را گزارش می دهد:
select *
from
(select
owner,
index_name
from
dba_indexes di
where
di.index_type != ‘LOB’
and
owner not in (‘SYS’, ‘SYSMAN’, ‘SYSTEM’, ‘MDSYS’, ‘WMSYS’, ‘TSMSYS’, ‘DBSNMP’, ‘OUTLN’)
minus
select
index_owner owner,
index_name
from
dba_constraints dc
where
index_owner not in (‘SYS’, ‘SYSMAN’, ‘SYSTEM’, ‘MDSYS’, ‘WMSYS’, ‘TSMSYS’, ‘DBSNMP’, ‘OUTLN’)
minus
select
p.object_owner owner,
p.object_name index_name
from
stats$snapshot sn,
stats$sql_plan p,
stats$sql_summary st,
stats$sql_plan_usage spu
where
st.sql_id = spu.sql_id
and
spu.plan_hash_value = p.plan_hash_value
and
st.hash_value = p.plan_hash_value
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
sn.snap_id = spu.snap_id
and
sn.dbid = spu.snap_id
and
sn.instance_number = spu.instance_number
and
sn.snap_id between &begin_snap and &end_snap
and
p.object_type = ‘INDEX’
)
where owner not in (‘SYS’, ‘SYSMAN’, ‘SYSTEM’, ‘MDSYS’, ‘WMSYS’, ‘TSMSYS’, ‘DBSNMP’, ‘OUTLN’)
order by 1, 2
/
