Script: To Monitor the Usage of Indexes

Subject: Script: To Monitor the Usage of Indexes
Doc ID: Note:1033478.6 Type: SCRIPT
Last Revision Date: 30-MAY-2007 Status: PUBLISHED

Abstract
This script will monitor the usage of indexes on the database.


Product Name, Product Version
Oracle Server, 7.3 to 10.0
Platform Platform Independent
Date Created 09-Jul-1997

Instructions
Execution Environment:
SQL, SQL*Plus

Access Privileges:
Requires DBA access privileges to be executed.

Usage:
sqlplus sys/

Instructions:
Copy the script to a file and execute it from SQL*Plus.


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.



Description
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.

Miscellaneous requirements and info:
- The user running the script should have all the privileges to explain
everything in v$sqlarea not loaded by SYS.
- plan_table.remarks can be used to determine privilege related errors.
- The parameter OPTIMIZER_GOAL is constant for all SQL in shared pool
ignores v$sqlarea.optimizer_mode.
- The statistics have not been regenerated between snapshots.
- No statements have been truncated.
- All objects are local.
- All referenced tables/views are either owned by the user running the
script or fully qualified names/synonyms were used in the SQL.
- No "popular" statements have aged out of (and for that matter, been
reloaded into) the shared pool since the last snapshot.
- Instance is either bounced or has the shared pool completely flushed
after each snapshot to reset the executions and other statistics to zero.
- For all statements, v$sqlarea.version_count = 1 (children).
- Review Bug:2282891 and Bug:2953935 that may affect performance of this script

NOTE: With 9i, you can use the V$SQL_PLAN instead.




References




Script
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;



==============
Sample Output:
==============

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




Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.



Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU