The Geek Diary

SQL Script to Monitor Usage of Indexes

by

You have an environment that is heavily indexed, and you want to monitor the usage of the indexes. For example, at the end of the week before the batch loads, you would like to check which indexes are being used in queries throughout the week.

You can find the index usage from the explain plan. If you explain all the queries within V$SQLAREA, you can see which indexes are being used. The following is a sample of the type of script you can write to get these results. This script is only a sample and works under certain assumptions.

The script

The Sample Script is as follows:

set echo off 
Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN 
drop table plan_table; 


Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA 
drop table sqltemp; 
create table sqltemp 
  (ADDR VARCHAR2 (16), 
   SQL_TEXT VARCHAR2 (2000), 
   DISK_READS NUMBER, 
   EXECUTIONS NUMBER, 
   PARSE_CALLS NUMBER); 

set echo on 

Rem Create procedure to populate the plan_table by executing 
Rem explain plan...for 'sqltext' dynamically 
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; 
/ 

Rem Start EXPLAINing all S/I/U/D statements in the shared pool 
declare 
-- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS) 
cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS, 
PARSE_CALLS 
from v$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; 
begin 
open c1; 
fetch c1 into addr2,sqltext,dreads,execs,pcalls; 
while (c1%found) loop 
insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls); 
commit; 
fetch c1 into addr2,sqltext,dreads,execs,pcalls; 
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; 
end; 
/ 

Rem Generate a report of index usage based on the number of times 
Rem a SQL statement using that index was executed 
select p.owner, p.name, 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 
group by p.owner, p.name 
order by 2 desc; 

Rem Perform cleanup on exit (optional)
delete	
from	plan_table
where	statement_id in(
	select	addr
	from	sqltemp
	);
drop table sqltemp;

The Output

The output produced is as follows:

SQL> @check_indexes 

Table dropped. 
  

Table created. 
  

Table dropped. 
  

Table created. 

SQL> Rem Create procedure to populate the plan_table by executing 
SQL> Rem explain plan...for 'sqltext' dynamically 
SQL> create or replace procedure do_explain 
  2  (addr IN varchar2, sqltext IN varchar2) 
  3  as 
  4  dummy varchar2 (1100); 
  5  mycursor integer; 
  6  ret integer; 
  7  my_sqlerrm varchar2 (85); 
  8  begin 
  9  dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ; 
 10  dummy:=dummy||''''||addr||''''||' FOR '||sqltext; 
 11  mycursor := dbms_sql.open_cursor; 
 12  dbms_sql.parse(mycursor,dummy,dbms_sql.v7); 
 13  ret := dbms_sql.execute(mycursor); 
 14  dbms_sql.close_cursor(mycursor); 
 15  commit; 
 16  exception -- Insert errors into PLAN_TABLE... 
 17  when others then 
 18  my_sqlerrm := substr(sqlerrm,1,80); 
 19  insert into plan_table(statement_id,remarks) 
 20  values (addr,my_sqlerrm); 
 21  -- close cursor if exception raised on EXPLAIN PLAN 
 22  dbms_sql.close_cursor(mycursor); 
 23  end; 
 24  / 

Procedure created. 

SQL> Rem Start EXPLAINing all S/I/U/D statements in the shared pool 
SQL> declare 
  2  -- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS) 
  3  cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS, 
  4  PARSE_CALLS 
  5  from v$sqlarea where command_type in (2,3,6,7) 
  6  and parsing_schema_id != 0; 
  7  cursor c2 is select addr, sql_text from sqltemp; 
  8  addr2 varchar(16); 
  9  sqltext v$sqlarea.sql_text%type; 
 10  dreads v$sqlarea.disk_reads%type; 
 11  execs v$sqlarea.executions%type; 
 12  pcalls v$sqlarea.parse_calls%type; 
 13  begin 
 14  open c1; 
 15  fetch c1 into addr2,sqltext,dreads,execs,pcalls; 
 16  while (c1%found) loop 
 17  insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls); 
 18  commit; 
 19  fetch c1 into addr2,sqltext,dreads,execs,pcalls; 
 20  end loop; 
 21  close c1; 
 22  open c2; 
 23  fetch c2 into addr2, sqltext; 
 24  while (c2%found) loop 
 25  do_explain(addr2,sqltext); 
 26  fetch c2 into addr2, sqltext; 
 27  end loop; 
 28  close c2; 
 29  end; 
 30  / 

PL/SQL procedure successfully completed. 

SQL> Rem Generate a report of index usage based on the number of times 
SQL> Rem a SQL statement using that index was executed 
SQL> select p.owner, p.name, sum(s.executions) totexec 
  2  from sqltemp s, 
  3  (select distinct statement_id stid, object_owner owner, object_name name 
  4  from plan_table 
  5  where operation = 'INDEX') p 
  6  where s.addr = p.stid 
  7  group by p.owner, p.name 
  8  order by 2 desc; 

OWNER                          NAME                              TOTEXEC 
------------------------------ ------------------------------ ---------- 
TEST                           JUNK_C1                                 1

You May Also Like