Oracle index usage count

Bazen özellikle büyük tabloların büyük boyutlardaki indexleri eğer kullanılmıyorsa önce invisible a çekip belli bir zaman izlendikten sonra drop index yöntemine gidilebilir. Indexler eğer 1 kez bile kullanılıyorsa index monitoring yaptığımızda kullanılıyor olarak gösterir bize ancak biz indexlerin tarih bazında kullanım sayısını öğrenmek istediğimizde aşağıdaki gibi bir yolu izleyebiliriz.
index kullanım sayılarını tarih bazında sorgulamak üzere iki geçici tablo oluşturuyorum:

CREATE TABLE idx_TABLE1
(
 OWNER VARCHAR2(30 BYTE),
 INDEX_NAME VARCHAR2(30 BYTE),
 FIRST_LOAD_TIME VARCHAR2(20 BYTE),
 LAST_LOAD_TIME VARCHAR2(10 BYTE),
 LAST_ACTIVE_TIME VARCHAR2(10 BYTE),
 TOTEXEC NUMBER,
 RUN_DATE DATE DEFAULT sysdate
);

CREATE TABLE idx_TABLE2
(
 OWNER VARCHAR2(30 BYTE),
 INDEX_NAME VARCHAR2(30 BYTE),
 FIRST_LOAD_TIME VARCHAR2(20 BYTE),
 LAST_LOAD_TIME VARCHAR2(10 BYTE),
 LAST_ACTIVE_TIME VARCHAR2(10 BYTE),
 TOTEXEC NUMBER,
 RUN_DATE DATE DEFAULT sysdate
);

Oracle support un Doc ID 1033478.6 sindeki do_explain procedure ünü create ediyorum:

create or replace procedure do_explain
  (addr IN varchar2, sqltext IN varchar2) as
  dummy varchar2 (1100);
  mycursor integer;
  ret integer;
  my_sqlerrm varchar2 (85);
  begin
  dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ;
  dummy:=dummy||''''||addr||''''||' FOR '||sqltext;
  mycursor := dbms_sql.open_cursor;
  dbms_sql.parse(mycursor,dummy,dbms_sql.v7);
  ret := dbms_sql.execute(mycursor);
  dbms_sql.close_cursor(mycursor);
  commit;
  exception -- Insert errors into PLAN_TABLE...
  when others then
  my_sqlerrm := substr(sqlerrm,1,80);
  insert into plan_table(statement_id,remarks)
  values (addr,my_sqlerrm);
  -- close cursor if exception raised on EXPLAIN PLAN
  dbms_sql.close_cursor(mycursor);
  end;
  /

Oracle support un Doc ID 1033478.6 sindeki scrpitleri customize ederek tarih bilgilerini çekiyorum ve toplanan bilgileri geçici tablolarıma aktarıyorum:

create or replace procedure idx_big_tables_prc as
  -- declare
  cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS,
  PARSE_CALLS,FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME
  from gv$sqlarea where command_type in (2,3,6,7)
  and parsing_schema_id != 0;
  cursor c2 is select addr, sql_text from sqltemp;
  addr2 varchar(16);
  sqltext v$sqlarea.sql_text%type;
  dreads v$sqlarea.disk_reads%type;
  execs v$sqlarea.executions%type;
  pcalls v$sqlarea.parse_calls%type;
  f_load_time v$sqlarea.FIRST_LOAD_TIME%type;
  l_load_time v$sqlarea.LAST_LOAD_TIME%type;
  l_active_time v$sqlarea.LAST_ACTIVE_TIME%type;

begin
  execute immediate 'drop table sqltemp';

execute immediate 'create table sqltemp
  (ADDR VARCHAR2 (16),
  SQL_TEXT VARCHAR2 (2000),
  DISK_READS NUMBER,
  EXECUTIONS NUMBER,
  PARSE_CALLS NUMBER,
  FIRST_LOAD_TIME VARCHAR2(20),
  LAST_LOAD_TIME DATE,
  LAST_ACTIVE_TIME DATE)';
  open c1;
  fetch c1 into addr2,sqltext,dreads,execs,pcalls,f_load_time,l_load_time,l_active_time;
  while (c1%found) loop
  insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls,f_load_time,l_load_time,l_active_time);
  commit;
  fetch c1 into addr2,sqltext,dreads,execs,pcalls,f_load_time,l_load_time,l_active_time;
  end loop;
  close c1;
  open c2;
  fetch c2 into addr2, sqltext;
  while (c2%found) loop
  do_explain(addr2,sqltext);
  fetch c2 into addr2, sqltext;
  end loop;
  close c2;

insert into idx_TABLE1
  (OWNER, INDEX_NAME, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME,TOTEXEC)
  select owner,name index_name,FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME, sum(totexec) totexec from (
  select p.owner, p.name,
  FIRST_LOAD_TIME,
  to_char(s.LAST_LOAD_TIME,'dd.mm.yyyy') LAST_LOAD_TIME,
  to_char(s.LAST_ACTIVE_TIME,'dd.mm.yyyy') LAST_ACTIVE_TIME,
  sum(s.executions) totexec
  from sqltemp s, (select distinct statement_id stid, object_owner owner, object_name name from plan_table where operation = 'INDEX') p
  where s.addr = p.stid
  and p.name in (select distinct index_name from dba_indexes where table_name='TABLE1')
  group by p.owner, p.name,s.FIRST_LOAD_TIME,s.LAST_LOAD_TIME,s.LAST_ACTIVE_TIME
  order by 2 desc)
  group by owner,name,FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME;

insert into idx_TABLE2
  (OWNER, INDEX_NAME, FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME,TOTEXEC)
  select owner,name index_name,FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME, sum(totexec) totexec from (
  select p.owner, p.name,
  FIRST_LOAD_TIME,
  to_char(s.LAST_LOAD_TIME,'dd.mm.yyyy') LAST_LOAD_TIME,
  to_char(s.LAST_ACTIVE_TIME,'dd.mm.yyyy') LAST_ACTIVE_TIME,
  sum(s.executions) totexec
  from sqltemp s, (select distinct statement_id stid, object_owner owner, object_name name from plan_table where operation = 'INDEX') p
  where s.addr = p.stid
  and p.name in (select distinct index_name from dba_indexes where table_name='TABLE2')
  group by p.owner, p.name,s.FIRST_LOAD_TIME,s.LAST_LOAD_TIME,s.LAST_ACTIVE_TIME
  order by 2 desc)
  group by owner,name,FIRST_LOAD_TIME,LAST_LOAD_TIME,LAST_ACTIVE_TIME;

commit;

delete
  from plan_table
  where statement_id in(
  select addr
  from sqltemp
  );

commit;

end;

Oluşturduğum idx_big_tables_prc procedure u sch. job ile 6 saatte bir çalıştırarak geçici tablolarımı besliyorum:

begin
  SYS.DBMS_SCHEDULER.CREATE_JOB
  (
  job_name => 'SYS.IDX_BIG_TABLES'
  ,start_date => TO_TIMESTAMP_TZ('2017/02/13 15:17:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
  ,repeat_interval => 'FREQ=HOURLY; INTERVAL=6; BYMINUTE=00; BYSECOND=0'
  ,end_date => NULL
  ,job_class => 'DEFAULT_JOB_CLASS'
  ,job_type => 'STORED_PROCEDURE'
  ,job_action => 'SYS.IDX_BIG_TABLES_PRC'
  ,comments => NULL
  );
  end;

Belli bir süre sonra aşağıdaki sorgularımla index kullanım sayısı düşük indexleri belirliyorum, önce invisible a çekip bir müddet izledikten sonra indexleri drop ederek hem ASM de yer kazanıyorum hem de günlük backupların IO sunu düşürmüş oluyorum:

select index_name, run_date, sum(totexec) from idx_TABLE1
  group by index_name, run_date
  order by 2;

select index_name, run_date, sum(totexec) from idx_TABLE1 where index_name ='IX_TEST_ID'
  group by index_name, run_date
  order by 2;
  select index_name, run_date, sum(totexec) from idx_TABLE1
  having sum(totexec)< 50
  group by index_name, run_date
  order by 2;

Aşağıdaki gibi geçici index create ederek testlerinizde kullanabilirsiniz:

create table table_1 as select * from dba_objects;

select * from table_1 where object_id between 10 and 1000;

CREATE INDEX indx_table_1_01 ON table_1 (object_id);

select * from table_1 where object_id between :t1 and :t2;

select * from v$sql where sql_text like '%table_1%';

select min (LAST_LOAD_TIME) from v$sql;

select * from v$sql_plan where sql_id='awhh3a963v05u';

select * from v$sql where sql_id='awhh3a963v05u';

select ADDRESS from v$sqlarea where sql_id='awhh3a963v05u';

select * from v$sqlarea where sql_id='awhh3a963v05u';

select * from plan_table where STATEMENT_ID='07000000A2341B00';

select * from sys.sqltemp where addr='07000000A2341B00';