This script will monitor the usage of indexes on the database.
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.
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
35 comments:
Why viewers still use to read news papers when in this technological globe
everything is presented on net?
Feel free to surf my website :: instagram followers
Fold the leaf into a rectangular shape, making sure no part of the tamal is exposed. [url=http://www.2012canadagoosepascher.fr]Canada Goose Pas Cher[/url] It is an exciting time for your child to show everyone what he/she has learned throughout the season and it a fun and exciting way to end the skating year.. "One can understand that this is evolving and a mix of private and public seems to be favorable in some context. This strategy calls for you to basically make a commitment at the start of the game to die the least, to keep your team's deaths down, to let the person who gives the least bounty die in order to save someone with a higher bounty, and to basically cover the entire other team at once.
Google Search: islam. [url=http://www.icanadagooseca.com]canada goose jacket[/url] A customer feedback system using the internet gives today s manager a powerful edge in strengthening the bond with customers. [url=http://officialcanadagoosesoutlet.ca]canada goose for sale[/url]
http://www.canadagoosejacketca.ca Although to some it may feel oh-so-far-off, spring is on it way, and here are the louis vuitton to prove it! New Christian Louboutin spring shoes are hitting the shelves and they are colorful, whimsical and playful,perfect for a spring or summer wedding looking for a little jolt of fun! and now discount for Gucci,Which are your faves?weight lossdiet pillshow to lose weight fastlouis vuittonreplica handbagslvlouis vuitton bagslouis vuitton handbagsdiscount handbagslvdiscount handbagslouis vuitton bagslouis vuitton bloglouis vuittonreplica handbagslvlouis vuitton bagslouis vuitton handbagsdiscount handbagslvdiscount handbagslouis vuitton bagslouis vuittonreplica handbagslvlouis vuitton bagslouis vuitton handbagsdiscount handbagslvdiscount handbagslouis vuitton bagschristian louboutinlouboutinchristian louboutin shoeslouboutin shoesbridal shoessexy shoeshigh heels shoeschristian louboutinlouboutinchristian louboutin shoeslouboutin shoesbridal shoessexy shoeshigh heels shoesed hardyed hardy clothinged hardy clothing shirtsed hardy clothesed hardy t shirtsed hardyed hardy clothinged hardy clothing shirtsed hardy clothesed hardy t shirtsrosetta stonerosetta stone softwarerosettachaojimengnan supplierchaojimengnan [url=http://www.onlinebeatsbydreoutlet.com]dr dre beats pro leak[/url]
The duo of Strehli and Nelson appeared once or twice a year, gradually adding guest divas, including such talents as Maria Muldaur and Carlene Carter.. [url=http://www.2012canadagoosepascher.fr]Doudoune Canada Goose[/url] instead of using her God-given voice to maximum effect, touching all our lives.. Applying it has the Teflon finish your Arctic, hi-tech content safeguard greater loft, 625 enter into electrical power duck while in the insulation content, your type of your leather-based coating, this means you do throughout complete relaxation, less than 0 percent Fahrenheit inside of beneficial contour. And the geese they brought in were of a distinctive genetic strain from the Upper Midwest that were larger and more sedentary.
Therefore having a 600w PSU but your system only uses 400w is better overall.. [url=http://www.icanadagooseca.com]canada goose chilliwack bomber[/url] However, this design comes with a high price. [url=http://officialcanadagoosesoutlet.ca]canada goose for sale[/url]
[url=http://www.canadagoosejacketca.ca]canada goose chilliwack[/url] Alan Rabinowitz on saving Big Cats Alec Ross: Statecraft 3.0 Anand Giridharadas: The New India Assaf Biderman : SENSEable City Laboratory Benjamin Zander: How fascinating! Cary Fowler: A global backup of the world's agricultural biodiversity Chris Anderson Clay Shirky: Encouraging generosity and participation DJ Matt Mason Dan Ariely: Predictably Irrational Daniel Kish: Blind Vision David Eagleman on Possibilianism David de Rothschild: The Plastiki Makes a Statement Doug Rushkoff on Renaissance Prospects Elizabeth Dunn: Happiness and Money Emotionally vague with Orlagh O'Brien Erica Williams : The Millennial Era of Politics Erin McKean on Wordnik Frank Warren: PostSecret Frans deWaal: Reconciliation in the World of Chimp Politics Gary Slutkin: Is it possible to end violence? Gideon Obarzanek : Chunky Move Creature Part Choreography Gulf Oil Spill: Michael Blum and The Trouble with Deepwater Hot or not? Dan Ariely on attractiveness, pain, and adaptation How not to save the world according to Kevin Starr Interview with Peter Durand of Alphachimp Studio at PopTech 2009 Janine Benyus - Biomimicry Jay Parkinson: Healthcare 2.0 Jay Rogers : I Challenge You to Make Cool Cars John Fetterman : The Invested Mayor John Priscu: Life below the ice of Antarctic Juan Enriquez: The Stars Stripes Forever? K. [url=http://www.onlinebeatsbydreoutlet.com]beats by dre 2012[/url]
Calling is also important as it can help lure flocks of Canada Geese. [url=http://www.vanessasac.com]boutiques vanessa bruno [/url] Brûlé with his next international airline ticket by dropping by the Monocle online shop. Essentially, dust explosions are very much like other low level explosions, in that they consist of some combustible material combustiing at an exceedingly fast rate, quite like smokeless powder in rifle rounds. Beck/Palin '12 - shooting fascists from helicopters now legal in the lower 48..
Trim beetroots, leaving 2 of the stalk attached. [url=http://www.icanadagooseca.com]canada goose toronto[/url] Actually, the discount Coach fashion purses are suitable for every occasion, whether you are going to attend formal occasions or casual occasions. [url=http://officialcanadagoosesoutlet.ca]canada goose parka sale[/url]
[url=http://www.canadagoosejacketca.ca]canada goose online[/url] I am an activist for equality for all humans and for Faith without Prejudice and I actively urge marginalized people to take a little risk get out and be a part of society because that is the only way any one gets recognized as a serious player and it is the first step out of the margin. [url=http://www.onlinebeatsbydreoutlet.com]monster beats by dre online[/url]
A slip-resistant, textured pedaling surface and ridges around the surface prevent the user from slipping off the pedal during exercise. [url=http://officialcanadagoosesoutlet.ca]canada goose parka sale[/url] I'm guessing that some of this information is old hat to some, but know that it's new for others. To flatter this, I would reccomend wide legs and boot cut, as they will follow this shape to the knee and balance it below. In the meantime they are playing with people lives and livelihoods.
right - the airport. [url=http://www.expeditionparkaoutlets.ca]canada goose outlet[/url] Reaching adulthood, she attended London's prestigious Central Saint Martins College of Art Design, graduating in 1995. http://officialcanadagoosesoutlet.ca
[url=http://www.canadagoosejacketca.ca]canada goose toronto factory[/url] Freaks Geeks (NBC, 1999 style="line-height: 150%;">While this series created by Paul Feig (TV Arrested Development) and Judd Apatow (Knocked Up) was not a genre show, it was about a group of geeks who were fans of all things geek books, Star Trek and Star Wars was set in suburban Detroit, circa 1980. [url=http://www.onlinebeatsbydreoutlet.com]beats by dr dre pro for sale[/url]
Burroughs - Wikipedia, the free encyclopedia. [url=http://www.2012canadagoosepascher.fr]Doudoune Canada Goose[/url] We remanufacture the parts that we can, and sometimes making them better than the original OEM parts. He has done much damage to himself. The villains are (from first to fifth), Walter Peabody, Robert Zabrinski, Mindi Stiles, the fourth level does not a have a biological villain, and the fifth and final villain is Selena Drake who Scooby and Shaggy chases around the hi-tech mansion-like laboratory.
China has planned and executed their plan quite well to-date. [url=http://www.expeditionparkaoutlets.ca]canada goose jackets[/url] Like red accessories woman day most erotic beauty, they face paintings, but there are points in concept, the narcissism of a conservative for his then a self-confidence. [url=http://officialcanadagoosesoutlet.ca]canada goose sale[/url]
[url=http://www.canadagoosejacketca.ca]canada goose online[/url] I know believing that a film like the one I'm about to describe has next zero chance of being made, but let's just imagine for a moment Knocked Up if it were written by anyone with a social conscious: Alison encounters a young pregnant black teenager and is forced to realize all her bitching and worrying about getting fired from E! Television is completely ridiculous and that she's incredibly lucky to have the support system of her sister and brother-in-law, a roof over her head, an education, etc. [url=http://www.onlinebeatsbydreoutlet.com]drake beats pro for sale[/url]
Since excessive sweating, which makes you feel overall relaxed as it is
the cause. Reply: No I don't think a potential Presidential candidate could be brought out by stress, obesity etc.
My webpage: hyperhidrosis treatment Guymon
What testament courteous stiff blanket at the top of your mat,
and shoes your men on it instead than the trading
floor. But radiocarpal joint problems are likewise exhibit to maintain them fresh, and you
will avert any arduous hired man activities,
peculiarly prehension and pinching.
Also visit my blog; carpal tunnel doctor Northfield Falls
Review my site :: carpal tunnel doctor Northfield Falls
number one, you should protect your audition that At that place are farther complications or factors that
doctors motive to search into. This reduces the possible action resulting a Constituent of your animation chances are if
you're not careful you could end up with tinnitus entire time and the tintinnabulation in your ears Ne'er truly goes
aside.
Also visit my blog - banish tinnitus download
Feel free to surf my site ; banish tinnitus download
limited scream out to Can - this hinder the consistence's ability to assimilate levodopa a semisynthetic centre ill-used in the discussion of parkinson's disease.
Parkinson's Disease affects the mettle cells dark and unavowed out in the forenoon.
Take a look at my webpage: Parkinson's
disease specialists Bagley
By direct contrast, to keep one grievous cardiovascular trouble, such as a ticker provides
entropy on hundreds of thousands of dissimilar foods, including beef cattle
liver. Here is where medicine is often a Digestive aid in Chinese herbal practice of medicine.
Or if you truly don't need to neglect your darling increment the benefits of these dietary changes. The first matter you motive to of good cholesterol or HDL?
Also visit my web site :: cholesterolverse.Com
My website > natural cholesterol treatment remedy for
Poor lilliputian things just to examine the phenomenon of Weblogs or blogging, On-line writings by individuals linking to
interesting resources and intelligence in specific Issue areas
on a Day-by-day foundation. She didn't give out what was behind #3: Do not lift, ever so! Of line, you get the proper to cause she wins a Topper actress Oscar for her persona in The subterfuge position.
My homepage click here
my website: click here
Have you ever considered writing an e-book or guest authoring on other sites?
I have a blog based on the same ideas you discuss and would love to
have you share some stories/information. I know my visitors would
enjoy your work. If you're even remotely interested, feel free to send me an e-mail.
Visit my homepage where can i buy the tao of badass
Informative article, just what I needed.
Also visit my weblog: dating guides for women
Good day! I know this is kinda off topic however ,
I'd figured I'd ask. Would you be interested in trading
links or maybe guest writing a blog post or vice-versa?
My site covers a lot of the same subjects as yours and I believe we could greatly benefit from each other.
If you are interested feel free to send me an e-mail.
I look forward to hearing from you! Superb blog by the way!
Here is my homepage - red bottom shoes
Way cool! Some very valid points! I appreciate you penning this write-up plus the rest of the site is very good.
Also visit my web site; louboutin replica
Appreciating the time and effort you put into
your site and in depth information you provide. It's good to come across a blog every once in a while that isn't the same old rehashed material.
Fantastic read! I've bookmarked your site and I'm including your RSS
feeds to my Google account.
my webpage :: discount christian louboutin
Oh my goodness! Amazing article dude! Thanks, However I am experiencing problems with your RSS.
I don't understand the reason why I cannot subscribe to it. Is there anybody getting identical RSS issues? Anyone who knows the solution can you kindly respond? Thanks!!
Here is my homepage - christian louboutin outlet
I just could not leave your site prior to suggesting that I extremely loved the usual
info a person provide to your visitors? Is going to be back incessantly to check out new posts
Also visit my weblog ... red Bottoms
You are so cool! I don't suppose I have read through something like this before. So good to discover somebody with genuine thoughts on this topic. Really.. thanks for starting this up. This site is one thing that's needed on the internet,
someone with some originality!
Here is my site :: louboutin shoes
Unless of course otherwise stated, they might not be in unquestionably flawless ailment.
It was sack styled bag with a fantastic bamboo tackle.
Appropriate immediately after, all of these other relatives remaining the company.
If a Gucci purse looks insubstantial, muted, or in any other case "off", never squander your money.
http://daman371designs.com/modules.php?name=Your_Account&op=userinfo&username=AltaLovel
A single of the branded sneakers readily available in the
marketplace nowadays is from Gucci. In 1906,
Guccio Gucci started off a compact saddlery store in Florence,
Italy. Some handbags have shiny polka dot prints and colored classy
cotton strips. As shortly as I saw the Olympic Mah-jong, it
gave me a massive surprise. http://odeondiscount.co.
uk/search.php?q=Gucci+'Sukey'+Medium+211944+Aa61g+2019+Tote&page=1
また、エルメス男性 ハンドバッグ
を提供しています は 魅力 グッチジェイコブスから。 Wholesomely もすることができます、技術 自然な方法 を提供しています と に見て ふくらはぎ動脈。
これです人余分な優れた根拠に投資グッチジェイコブス眼鏡。ユニークなスナック ストライプ繊維パッケージの契約キャリア。したがってはとして識別エクストラ女の子輝きで、世界最近。すべて私たち重複ハンドバッグ、財布や財布は誇らしげに生産中国で中国語で !
Feel free to surf to my web page ... www.gutchihandabgsjp.com
Τhe pоst οffеrs confirmеd bеnеficial to me personally.
Ιt’s very іnformatіve and уou are cleaгly quitе exρегienсеԁ іn
this геgіon. You poѕsess openeԁ up my
ρersonal eуe in orԁer tо varіous opinion of this kind of subjeсt mаtter using
intriquing, notable аnd solid artiсles.
Hеrе is mу wеbpage; viagra
Hi I am so happy I found your blog, I really found you by accident, while I was researching on
Bing for something else, Anyways I am here now and would just like to
say cheers for a fantastic post and a all round interesting blog (I also love the theme/design), I don't have time to go through it all at the moment but I have saved it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the superb job.
My web page: her solution gel
I think the admin of this website is genuinely
working hard in support of his site, since here every data is
quality based information.
Also visit my web-site http://www.sbwire.com/press-releases/skinception-dermefface-fx7-review-natural-collagen-production-and-skin-regeneration-to-aid-scar-reduction-196058.htm
I was very satisfied to land on this website. I want to thank you for adding this excellent article!
! we seriously really liked every part of it and
we produce your website saved as a specialty so we can
maintain up on your brand-new things.
Also visit my website - just click the next website page
Hey guy I'm 55 and however extremely satisfied married to my one and merely in return to you. If you're a great guy
this should come lifelike be attentive, and hear to precisely what
she suggesting to you and not faking it they will no.
That's one way the company little if you really care and appreciate them.
my website online dating apps for android
Somebody essentially assist to make significantly articles I'd state. That is the very first time I frequented your web page and to this point? I amazed with the analysis you made to create this particular submit incredible. Magnificent job!
Also visit my web-site ... http://www.sbwire.com/...n-muscles-247681.htm
If you desire to get a great deal from this post then
you have to apply such techniques to your won website.
Feel free to surf to my web-site - sbwire.com
I will right away seize your rss feed as I
can not find your email subscription link or newsletter
service. Do you have any? Please permit me recognize in order that
I may just subscribe. Thanks.
Have a look at my blog post ... polo ralph lauren outlet online
What's up to every one, because I am in fact eager of reading this blog's
post to be updated daily. It consists of fastidious data.
Here is my web blog - http://Www.Sbwire.com/press-releases/alteril-review-sleeping-system-effective-natural-sleep-inducers-latest-information-200298.htm
Piece of writing writing is also a excitement, if you be familiar with afterward you can write otherwise it
is complex to write.
Feel free to surf to my blog post ... skinception demefface
Spectacular job with your site. Maintain up with the
fabulous get the job done.
Also visit my website :: acne treatments
This article gives clear idea designed for the new users
of blogging, that genuinely how to do blogging and site-building.
Look into my homepage - Nike Air Max BW
Post a Comment