How to Monitor the Usage of Indexes

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/<password>

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.

 

Anúncios

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em INDEX TUNING e marcado . Guardar link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s