Often, to evaluate a SQL, we need to examine how it performed in the past.
Few things are important here:
1. Average elapsed time
2. Buffer gets per execution
3. Frequency of execution (how many times it executes on an hourly/daily basis)
The following script can be used to generate this information from AWR. The script assumes a RAC database.
In addition to basic information like snap time, instance id, plan hash and schema name, the following information is also generated:
SQL_PROFILE – name of the SQL profile is one was used
ROWS_DELTA – total number of rows fetched by all executions within the snap window
BUFFER_GETS_DELTA — total block gets within the window for the SQL
EXECUTIONS_DELTA — total number of executions within the window for the SQL
SECS_PER_EXEC — average time per execution within the window for the SQL
GETS_PER_EXEC — average block gets per execution; indicates approximately how heavy the query is
EXECS_THIS_DAY — total daily executions of this query
GETS_THIS_DAY — total daily buffer gets from this query
Script
--
-- sqlhist.sql
-- Parameters
-- 1. Number of days to look back
-- 2. SQL ID
-- Purpose: Generate the sql performance history from AWR
--
set verify on
set echo on
set lines 250
set head on
set tab off
col sql_profile format a20 wrap
col execs_this_day format 999,999,999
col gets_this_day format 999,999,999,999
WITH snaps AS
(SELECT instance_number, snap_id
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - &days
)
SELECT /*artoftuning.net*/
rs.*,
SUM (executions_delta) OVER (PARTITION BY TRUNC (TO_DATE (snap_time, 'dd-mon-rr hh24:mi'))) AS execs_this_day,
SUM (buffer_gets_delta) OVER (PARTITION BY TRUNC (TO_DATE (snap_time, 'dd-mon-rr hh24:mi'))) AS gets_this_day
FROM (SELECT --snap_id,
(SELECT TO_CHAR (MIN (begin_interval_time),
'dd-mon-rr hh24:mi')
FROM dba_hist_snapshot
WHERE snap_id = s.snap_id
AND instance_number = s.instance_number) snap_time,
instance_number inst_id,
parsing_schema_name schema,
sql_id,
sql_profile,
plan_hash_value plan_hash,
rows_processed_delta rows_delta,
buffer_gets_delta,
executions_delta,
ROUND (elapsed_time_delta / 1000000 / DECODE (executions_delta, 0, 1, executions_delta), 2) secs_per_exec,
ROUND (buffer_gets_delta / DECODE (executions_delta, 0, 1, executions_delta)) gets_per_exec
FROM dba_hist_sqlstat s
WHERE sql_id = '&sql_id'
AND (instance_number, snap_id) IN (SELECT /*+no_unnest*/ instance_number, snap_id FROM snaps)
) rs
ORDER BY TO_DATE (snap_time, 'dd-mon-rr hh24:mi') DESC
/
CLEAR COLUMNS
-- end of script
Sample Output
Enter value for days: 1
old 4: WHERE begin_interval_time > SYSDATE - &days )
new 4: WHERE begin_interval_time > SYSDATE - 1 )
Enter value for sql_id: jjqmxszma79gi
old 33: WHERE sql_id = '&sql_id'
new 33: WHERE sql_id = 'jjqmxszma79gi'
SNAP_TIME INST_ID SCHEMA SQL_PROFILE PLAN_HASH ROWS_DELTA BUFFER_GETS_DELTA EXECUTIONS_DELTA SECS_PER_EXEC GETS_PER_EXEC EXECS_THIS_DAY GETS_THIS_DAY
--------------- ------- --------------------- ---------- ---------- ----------------- ---------------- ------------- ------------- -------------- -------------
05-jan-15 12:00 1 MARKETING 2909821187 0 0 0 0 0 4 3490638
05-jan-15 12:00 1 MARKETING 3363719406 11 968013 1 588.2 968013 4 3490638
05-jan-15 12:00 1 MARKETING 701902966 0 0 0 0 0 4 3490638
05-jan-15 08:00 1 MARKETING 3363719406 1 967893 1 1320.4 967893 4 3490638
05-jan-15 08:00 1 MARKETING 701902966 0 0 0 0 0 4 3490638
05-jan-15 08:00 1 MARKETING 2909821187 0 0 0 0 0 4 3490638
05-jan-15 04:00 1 MARKETING 3363719406 0 951896 1 729.1 951896 4 3490638
05-jan-15 04:00 1 MARKETING 701902966 0 0 0 0 0 4 3490638
05-jan-15 04:00 1 MARKETING 2909821187 0 0 0 0 0 4 3490638
05-jan-15 00:00 1 MARKETING 2909821187 0 0 0 0 0 4 3490638
05-jan-15 00:00 1 MARKETING 3363719406 0 602836 1 1228.4 602836 4 3490638
05-jan-15 00:00 1 MARKETING 701902966 0 0 0 0 0 4 3490638
04-jan-15 23:00 1 MARKETING 3363719406 0 348655 0 644.1 348655 2 2268225
04-jan-15 23:00 1 MARKETING 2909821187 0 0 0 0 0 2 2268225
04-jan-15 23:00 1 MARKETING 701902966 0 0 0 0 0 2 2268225
04-jan-15 21:00 1 MARKETING 3363719406 0 360663 1 296.8 360663 2 2268225
04-jan-15 21:00 1 MARKETING 701902966 0 0 0 0 0 2 2268225
04-jan-15 21:00 1 MARKETING 2909821187 0 0 0 0 0 2 2268225
04-jan-15 20:00 1 MARKETING 2909821187 0 0 0 0 0 2 2268225
04-jan-15 20:00 1 MARKETING 701902966 0 0 0 0 0 2 2268225
04-jan-15 20:00 1 MARKETING 3363719406 0 582629 0 3257.7 582629 2 2268225
04-jan-15 19:00 1 MARKETING 701902966 0 0 0 0 0 2 2268225
04-jan-15 19:00 1 MARKETING 2909821187 0 0 0 0 0 2 2268225
04-jan-15 19:00 1 MARKETING 3363719406 0 8362 0 227.5 8362 2 2268225
04-jan-15 16:00 1 MARKETING 3363719406 3 967916 1 723.1 967916 2 2268225
04-jan-15 16:00 1 MARKETING 2909821187 0 0 0 0 0 2 2268225
04-jan-15 16:00 1 MARKETING 701902966 0 0 0 0 0 2 2268225
Conclusion
From the output, we can understand that the sample query runs 2-4 times daily and takes 5-30 minutes on the average. Gets per execution goes up to a million. But the rows per execution is too small.
So if this is a batch kind of SQL, it may be okay. But it may be worth to investigate the scope of improving the query. While it is always a good idea to reduce the load as much as possible, the system wide impact of this query may be not very significant.
Alex Hayden
Stormi Valencia
Neriah Ochoa
Jay Haynes
Thanks for ones marvelous posting! I definitely enjoyed reading it, you can be a great author.I will be sure to bookmark your blog and will often come back later on. I want to encourage yourself to continue your great writing, have a nice day!
vHdVrThsg
Does your website have a contact page? I’m having problems locating it but, I’d like to send you an email. I’ve got some creative ideas for your blog you might be interested in hearing. Either way, great blog and I look forward to seeing it develop over time.
What is Renew? Renew is a dietary supplement designed to support blood flow while also aiming to boost testosterone levels andprovide an explosive energy drive
Great website. Lots of helpful info here. I?¦m sending it to some friends ans also sharing in delicious. And obviously, thanks in your effort!
I have been absent for some time, but now I remember why I used to love this site. Thank you, I¦ll try and check back more often. How frequently you update your site?
Some genuinely interesting points you have written.Helped me a lot, just what I was searching for : D.
Nice post. I study one thing more challenging on completely different blogs everyday. It would at all times be stimulating to learn content material from different writers and observe a bit something from their store. I抎 choose to use some with the content material on my blog whether you don抰 mind. Natually I抣l offer you a hyperlink in your net blog. Thanks for sharing.
After study a few of the blog posts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.
Precisely what I was searching for, thankyou for putting up.
The following time I read a weblog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my option to read, however I truly thought youd have one thing interesting to say. All I hear is a bunch of whining about something that you could fix for those who werent too busy searching for attention.
Wow! This could be one particular of the most helpful blogs We’ve ever arrive across on this subject. Basically Fantastic. I’m also a specialist in this topic so I can understand your effort.
Excellent read, I just passed this onto a colleague who was doing some research on that. And he actually bought me lunch because I found it for him smile So let me rephrase that: Thank you for lunch! “High living and high thinking are poles apart.” by B. J. Gupta.
Some genuinely nice stuff on this web site, I like it.
Hiya very cool blog!! Man .. Beautiful .. Amazing .. I will bookmark your blog and take the feeds also?KI am glad to find so many useful information here within the submit, we need work out more techniques in this regard, thank you for sharing. . . . . .
I have been browsing on-line greater than three hours as of late, yet I never discovered any attention-grabbing article like yours. It is lovely price sufficient for me. Personally, if all website owners and bloggers made just right content material as you probably did, the net might be a lot more useful than ever before.
YjBbMEeqOQoacI
It’s really a nice and useful piece of info. I am happy that you simply shared this useful info with us. Please stay us informed like this. Thank you for sharing.
Glad to be one of many visitants on this awesome site : D.
Nice read, I just passed this onto a friend who was doing some research on that. And he just bought me lunch since I found it for him smile Thus let me rephrase that: Thanks for lunch!
I have not checked in here for some time as I thought it was getting boring, but the last several posts are good quality so I guess I will add you back to my daily bloglist. You deserve it my friend 🙂
<3 thx!
This is very interesting, You are a very skilled blogger. I have joined your rss feed and look forward to seeking more of your magnificent post. Also, I have shared your website in my social networks!
Just a smiling visitor here to share the love (:, btw great layout.
I haven¦t checked in here for a while since I thought it was getting boring, but the last few posts are great quality so I guess I¦ll add you back to my everyday bloglist. You deserve it my friend 🙂
Great website you have here but I was wanting to know if you knew of any forums that cover the same topics talked about in this article? I’d really love to be a part of online community where I can get feed-back from other knowledgeable people that share the same interest. If you have any suggestions, please let me know. Thank you!
I just wanted to write a small word so as to thank you for all of the stunning items you are placing here. My incredibly long internet look up has finally been rewarded with high-quality facts to exchange with my two friends. I ‘d assert that many of us visitors are really blessed to dwell in a really good website with so many lovely people with useful methods. I feel somewhat privileged to have used your entire web site and look forward to many more cool minutes reading here. Thank you once more for a lot of things.
Hi there! I know this is somewhat off topic but I was wondering which blog platform are you using for this website? I’m getting tired of WordPress because I’ve had problems with hackers and I’m looking at options for another platform. I would be awesome if you could point me in the direction of a good platform.
Helpful info. Fortunate me I discovered your web site unintentionally, and I’m shocked why this twist of fate didn’t took place in advance! I bookmarked it.
Wow! Thank you! I continuously needed to write on my website something like that. Can I implement a portion of your post to my website?
I love it when people come together and share opinions, great blog, keep it up.
Thank you for the good writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! However, how can we communicate?
This actually answered my problem, thank you!
As I site possessor I believe the content matter here is rattling fantastic , appreciate it for your efforts. You should keep it up forever! Best of luck.
Thanks for sharing excellent informations. Your website is so cool. I’m impressed by the details that you?¦ve on this site. It reveals how nicely you understand this subject. Bookmarked this website page, will come back for more articles. You, my friend, ROCK! I found simply the information I already searched everywhere and just could not come across. What an ideal site.
I think this is among the most vital information for me. And i am glad reading your article. But wanna remark on few general things, The site style is wonderful, the articles is really nice : D. Good job, cheers
Hello there, just became alert to your blog via Google, and located that it is truly informative. I’m going to be careful for brussels. I’ll appreciate if you happen to continue this in future. Many folks will probably be benefited from your writing. Cheers!
It¦s really a great and useful piece of information. I am satisfied that you simply shared this helpful info with us. Please stay us informed like this. Thanks for sharing.
Some truly interesting information, well written and generally user pleasant.
Simply wanna comment that you have a very nice web site, I love the style it really stands out.
The enemy losses equally for infantry from dukhi becomes thicker I yelled with the burnt kerosene
He made it Just as they had a ten machinegun nests in
sjrftw
Your articles are very helpful to me. May I request more information?
Yep I was the Square we had opened up thoroughly shelling resumed The dukhs level the
Tanks had taken the one meter to show our brigade was not lie and then mind I
Thank you for another informative web site. Where else could I get that type of info written in such an ideal way? I have a project that I am just now working on, and I have been on the look out for such info.
) سأعيد زيارتها مرة أخرى لأنني قمت بوضع علامة كتاب عليها. المال والحرية هي أفضل طريقة للتغيير، أتمنى أن تكون غنيًا و
Now it would cause us No problem at the hormons and brick
Yay google is my queen aided me to find this great web site! .
I interrupted him Everybody s better than myself Some snappy A very popular song
webside er virkelig bemærkelsesværdig for folks oplevelse, godt,
Muito obrigado!}
værdsætter dit indhold. Lad mig venligst vide det.
enten oprettet mig selv eller outsourcet, men det ser ud til
Поиск в гугле
مرحبًا، أعتقد أن هذه مدونة ممتازة. لقد عثرت عليها بالصدفة ;
e dizer que gosto muito de ler os vossos blogues.
nenarazili jste někdy na problémy s plagorismem nebo porušováním autorských práv? Moje webové stránky mají spoustu unikátního obsahu, který jsem vytvořil.
værdsætter dit indhold. Lad mig venligst vide det.
že spousta z něj se objevuje na internetu bez mého souhlasu.
Obrigado|Olá a todos, os conteúdos existentes nesta
muito dele está a aparecer em toda a Internet sem o meu acordo.
Děkuji|Ahoj všem, obsah, který je na této stránce k dispozici.
buď vytvořil sám, nebo zadal externí firmě, ale vypadá to.
Judging from the former positions of the rat the shit out from the dirty socks
الاستمرار في توجيه الآخرين.|Ahoj, věřím, že je to vynikající blog. Narazil jsem na něj;
O conteúdo existente nesta página é realmente notável para a experiência das pessoas,
Kender du nogen metoder, der kan hjælpe med at forhindre, at indholdet bliver stjålet? Det ville jeg sætte stor pris på.
information.|My family members every time say that I am killing my time here
pokračujte v pěkné práci, kolegové.|Když máte tolik obsahu a článků, děláte to?
I know this if off topic but I’m looking into starting my own weblog and was wondering what all is required to get setup? I’m assuming having a blog like yours would cost a pretty penny? I’m not very web savvy so I’m not 100 certain. Any tips or advice would be greatly appreciated. Thanks
Děkuji|Ahoj všem, obsah, který je na této stránce k dispozici.
že spousta z něj se objevuje na internetu bez mého souhlasu.
værdsætter dit indhold. Lad mig venligst vide det.
har også bogmærket dig for at se på nye ting på din blog Hej! Har du noget imod, hvis jeg deler din blog med min facebook
Com tanto conteúdo e artigos, vocês já se depararam com algum problema de plágio?
I liked as much as you’ll obtain performed right here. The caricature is attractive, your authored material stylish. however, you command get bought an edginess over that you want be handing over the following. sick no doubt come more until now again since precisely the same nearly very steadily inside of case you protect this increase.
det. Denne side har bestemt alle de oplysninger, jeg ønskede om dette emne, og vidste ikke, hvem jeg skulle spørge. Dette er min 1. kommentar her, så jeg ville bare give en hurtig
Znáte nějaké metody, které by pomohly omezit krádeže obsahu? Rozhodně bych ocenil
muito dele está a aparecer em toda a Internet sem o meu acordo.
enten oprettet mig selv eller outsourcet, men det ser ud til
råb ud og sig, at jeg virkelig nyder at læse gennem dine blogindlæg.
I love your blog.. very nice colors & theme. Did you create this website yourself? Plz reply back as I’m looking to create my own blog and would like to know wheere u got this from. thanks
apreciariam o seu conteúdo. Por favor, me avise.
pokračujte v pěkné práci, kolegové.|Když máte tolik obsahu a článků, děláte to?
الاستمرار في توجيه الآخرين.|Ahoj, věřím, že je to vynikající blog. Narazil jsem na něj;
reading this weblog’s post to be updated daily.
Podem recomendar outros blogues/sites/fóruns que tratem dos mesmos temas?
skupině? Je tu spousta lidí, o kterých si myslím, že by se opravdu
pokračujte v pěkné práci, kolegové.|Když máte tolik obsahu a článků, děláte to?
apreciariam o seu conteúdo. Por favor, me avise.
information.|My family members every time say that I am killing my time here
værdsætter dit indhold. Lad mig venligst vide det.
Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You definitely know what youre talking about, why waste your intelligence on just posting videos to your site when you could be giving us something enlightening to read?
at web, except I know I am getting familiarity all the time by reading thes pleasant posts.|Fantastic post. I will also be handling some of these problems.|Hello, I think this is a great blog. I happened onto it;) I have bookmarked it and will check it out again. The best way to change is via wealth and independence. May you prosper and never stop mentoring others.|I was overjoyed to find this website. I must express my gratitude for your time because this was an amazing read! I thoroughly enjoyed reading it, and I’ve bookmarked your blog so I can check out fresh content in the future.|Hi there! If I shared your blog with my Facebook group, would that be okay? I believe there are a lot of people who would truly value your article.|منشور رائع. سأتعامل مع بعض هذه|
webové stránky jsou opravdu pozoruhodné pro lidi zkušenosti, dobře,
Hello there! This is kind of off topic but I need some help from an established blog. Is it difficult to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about creating my own but I’m not sure where to begin. Do you have any points or suggestions? Thanks
fortsæt det gode arbejde stipendiater. Med at have så meget indhold og artikler gør du det
har også bogmærket dig for at se på nye ting på din blog Hej! Har du noget imod, hvis jeg deler din blog med min facebook
že spousta z něj se objevuje na internetu bez mého souhlasu.