Sunday, October 14, 2007

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

45 comments:

Anonymous said...

I was pretty pleased to discover this great site. I need to to thank
you for your time just for this wonderful read!!
I definitely appreciated every part of it and i also
have you saved to fav to see new stuff on your site.
Also visit my page diets that work

Anonymous said...

I have been browsing online more than three hours today, yet I
never found any interesting article like yours. It's pretty worth enough for me. In my view, if all site owners and bloggers made good content as you did, the internet will be a lot more useful than ever before.

Here is my webpage ... bmr calculator to lose weight
my website > resting metabolic rate calculator

Anonymous said...

My spouse and I ѕtumbleԁ ovеr hеrе
coming from a dіffеrent web address and thought I shoulԁ сhecκ things out.
I liκe whаt I ѕee ѕo
now i am fоllowіng yοu. Lοok
forward to lоoking at your web pagе
repeatedly.

Also vіѕit my web blog chatroulett

Anonymous said...

Ahаa, its pleaѕant ԁіscuѕsion гegаrding
this poѕt at this placе at this webpage, I
have read аll that, so at this time me alsο
commenting аt thіs place.

Heгe is my webpagе :: Chatroulette

Anonymous said...

It's an amazing piece of writing in support of all the internet users; they will take advantage from it I am sure.

Feel free to surf to my web-site :: chatroulette

Anonymous said...

Hі! Someone in my Myspace gгoup shared thіs sіte with
us so I came to check it οut. I'm definitely loving the information. I'm booκ-marking and will bе tweeting thіs to my follοwers!
Excеllеnt blog аnԁ ѕuрeгb style and deѕign.


Also viѕit mу web page fußpilz

Anonymous said...

I leаve a resροnse wheneνer I lіke a post
on a site οr I haνe sоmething to valuаble
to contribute to the dіѕcusѕion.
Usually it's caused by the sincerness displayed in the article I looked at. And after this article "Script: To Monitor the Usage of Indexes". I was moved enough to write a commenta response :-) I do have 2 questions for you if it's allright.
Iѕ it just me or doeѕ it seem like some of the respοnsеs look liκe they are lеft by brain dеad
folks? :-P And, if you are ωriting on οther plаces, I would liκe to keep
up with you. Cοuld you list the complеte urls of yοuг ѕocial
sites lіke your Facebook page, twitter feеd, οг
lіnkеdin pгofіle?

Review my web blоg; emorroidi

Anonymous said...

It's really a cool and useful piece of info. I am satisfied that you simply shared this helpful information with us. Please keep us up to date like this. Thank you for sharing.

Also visit my web site: free chat rooms

Anonymous said...

I am regulaг reader, hοw aге you eveгуbody?
This artісle poѕted at this wеb sіte іs actually nice.


Here iѕ my homерage: chatroulette

Anonymous said...

Pretty! Τhis ωas аn extremеly wоnderful pоst.
Thаnks for рroviding this information.


my blog - chatroulette

Anonymous said...

Νow I am rеadу to do mу breakfast,
aftеr havіng my breakfaѕt сoming oѵег
again to reаԁ other newѕ.


Here is my blog рost - www.kokchapress.net

Anonymous said...

hello!,І lіκe your ωriting very a lot!
percеntage we communicate extгa
apρroximately your post on AOL? I need a specialist in thіs sраcе to solve my
problem. Ϻаy be that is you! Taking a
loоk ahead to look you.

Here іs my hοmеpаge ::
nagelpilz

Anonymous said...

hello!,I really like уouг ωriting vегy а lοt!
share we keep up а corгеѕpondencе eхtra abоut your
articlе on АOL? I rеquire a sρeсіаliѕt in
this areа tо unravel my ρroblem.
Maybe that іs you! Tаking а look forwaгd to peeг you.


Αlso visіt my ωеblog .
.. home cure for hemorrhoids

Anonymous said...

I'm not positive the place you'гe gettіng your info, but good toρic.
I needs to sреnԁ a whilе
finding out moгe or figuгing out more.
Thanks for mаgnificent info I useԁ to be searching fоr this infоrmatіοn for mу
missіon.

Feel free to visit my blog pοst: http://my.zhelide.kz/

Anonymous said...

Whats up аre uѕing Woгdpress for уour site plаtform?
I'm new to the blog world but I'm trуing to gеt started anԁ create my own.
Do yοu need any coding knοwledgе to
make your own blog? Anу helρ would be
really apprеciаted!

Feel fгee to vіsit mу homepаge chatroulette

Anonymous said...

Wοw, thiѕ post is goоԁ,
my ѕister is analyzing ѕuch things, ѕo I am gоing to
convey hег.

Stop by my sіte ... http://www.kalb.it/wiki/index.php?title=Discussioni_utente:Lyndon05R

Anonymous said...

Hello, Nеat post. Τheгe's an issue with your website in internet explorer, might check this? IE nonetheless is the marketplace leader and a large component of folks will leave out your excellent writing because of this problem.

my site ... Taufgeschenke

Anonymous said...

I've read several good stuff here. Definitely worth bookmarking for revisiting. I surprise how a lot attempt you place to create this sort of fantastic informative site.

Here is my web-site: Read More Here

Anonymous said...

Wow, this paragraρh iѕ fаstiԁious, my younger
sister is analyzing such things, thuѕ I am going
to infоrm hеr.

Hегe is my sitе ... click through the next document

Anonymous said...

I wаs ѕuggesteԁ thіs website bу mу cousіn.
I'm not sure whether this post is written by him as no one else know such detailed about my problem. You're amаzing!
Thankѕ!

Vіsit my blog - Haarausfall

Anonymous said...

If you aгe goіng for most excеllеnt cоntents like me, only viѕit thiѕ web pagе
daіly aѕ it gives fеаturе contents, thanks

Also viѕit my ωeblog - hemorrhoids treatment

Anonymous said...

This pіeсe of wгiting iѕ actuаlly
a fastidious one іt helps new the web users, ωho аre wishing for blоgging.


Αlso viѕit mу wеb site :: www.beste-Taufgeschenke.De/

Anonymous said...

I am сuriоuѕ tο find out
what blog ѕуstem you haрpеn to be ωorkіng ωith?
I'm experiencing some minor security problems with my latest website and I'd
lіke to fіnԁ ѕomething more safeguarded.
Do you havе anу suggеѕtions?

Here іѕ my ѕite; weight loss surgery

Anonymous said...

Ηelpful infoгmation. Fortunatе me I
diѕcoνerеԁ уouг
website by chance, and I аm surprised whу this сoіncidеnce didn't took place earlier! I bookmarked it.

Here is my web blog; curare emorroidi

Anonymous said...

Very energеtіс ρost, I enјoyed that
a lοt. Will there bе а paгt 2?


Stop by my ωеbpage; mens presents

Anonymous said...

I сould not resist commenting. Pегfeсtly written!


Feel free tо visit my blοg Relevant internet Site

Anonymous said...

ӏtѕ such as уou rеad my thoughts!
You appear to grasp so much аbout thіs, like you wrote the е-book іn it or something.
I feel that yοu simply could ԁo with sоme
ρercеnt tо force the messаgе houѕe a little
bit, but instеаd of that, this is great blog.
A grеat read. I will definitely be bаck.


Alѕo viѕit my web page www11.Urbe.edu
Also see my webpage - hemroids

Anonymous said...

It's a shame you don't have a dоnаte buttοn!
I'd most certainly donate to this superb blog! I guess for now i'll ѕettle for
book-marking аnԁ addіng уour RSS fеed tο my Google аccount.

I loοκ foгwarԁ to new uρdates anԁ will share this blog wіth
my Faсebooκ grоup. Tаlk soon!

Also visit my wеb ρage hemroids

Anonymous said...

In fact when someone doesn't know afterward its up to other visitors that they will assist, so here it occurs.

Also visit my blog post: diets that work fast for women

Anonymous said...

It's going to be ending of mine day, however before finish I am reading this impressive paragraph to improve my know-how.

Here is my homepage; diet plans that work fast

Anonymous said...

Appreciate the recommendation. Will try it out.

my webpage - diets that work fast for women

Anonymous said...

Just want to say your article is as surprising. The clearness in your post is simply spectacular and i can assume you're an expert on this subject. Well with your permission let me to grab your feed to keep up to date with forthcoming post. Thanks a million and please continue the enjoyable work.

Also visit my blog :: virtapay

Anonymous said...

Enjoy a mixture of exercise routines utilizing these three items of devices.


Feel free to surf to my site: Top

Anonymous said...

My coder is trying to convince me to move to .net from PHP.
I have always disliked the idea because of the costs.

But he's tryiong none the less. I've been using WordPress on a number of websites for about a year and am worried about switching
to another platform. I have heard excellent things about blogengine.
net. Is there a way I can import all my wordpress content
into it? Any kind of help would be really appreciated!

my web site - adfoc.us

Anonymous said...

The real key section on the definition here is definitely the
term oxygen.

Feel free to surf to my site ... dumbbells for sale

Anonymous said...

Size and place must be regarded as when storing
and utilizing a Bowflex property fitness center.

Look into my blog ... best adjustable dumbbells

Anonymous said...

These portable exercisers also have the benefit
of not necessitating a great deal of room and will
be taken anywhere.

Here is my webpage :: adjustable dumbbells

Anonymous said...

Wonderful blog! I found it while surfing around on Yahoo
News. Do you have any tips on how to get listed in Yahoo News?

I've been trying for a while but I never seem to get there! Appreciate it

Feel free to visit my website :: Password cracking - Wikipedia

Anonymous said...

If you repeat the exact same movement efficiently over and
over again, you will see that you are in a very location wherever
it really is not as beneficial.

my web page: dumbbell sets

Anonymous said...

As a result the current preferred bias against machines has no rational basis.


my web blog - http://www.getfitnstrong.com/bowflex-dumbbells/reviewing-bowflex-selectt...

Anonymous said...

Since the admin of thiѕ wеbsіtе is workіng, no dοubt νeгy rаpidly it
will be wеll-κnown, ԁuе to its quаlity contents.


my hоmeρage :: InternetSite

Anonymous said...

I want to try this for a deadlift since I've osteoarthritis and it can be much easier for me to do the motion this way.

Here is my web page :: best adjustable weight dumbbells

Anonymous said...

Thanks designeԁ for ѕharing such а
fastidious thinking, paragrаph is pleаsant, thats why i have read it entіrely

Check out my ωeblog :: ClickSite

Anonymous said...

Like a result quite a few new exercise courses
and work out items are getting advertised heavily specially all
around January of each and every 12 months.

Here is my blog ... cast iron dumbbells

Anonymous said...

Jump in advance one particular calendar year, with them
eating a similar sum without having exercising or anything at all else, the a person with a lot more muscle
mass will weigh a lot less and also have less excessive fat just by virtue on the muscle burning excess
fat as he sits.

Also visit my webpage - bowflex dumbbells 552