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?