شناسایی ایندکس های بی استفاده

ایندکس های بی استفاده سبب ایجاد سربار در عملیات 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

/

 

 

 

 

 

 

 

 

 

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.