AWR DBtime script
Here’s a handy little script for showing when the database has been most busy. I wrote this one because I wanted to be able to identify the workload characteristics of an instance when it was at its “busiest”, so we could model our testing strategy “correctly”. So the first part of that problem was to identify when the instance was “busiest”. The DBtime in the AWR reports seemed like a good indicator of “busiest”, so there you go. The script calculates the DBtime for each snapshot using the lag function and then reports the top 30. The script could easily be modified to calculate across multiple snapshots if desired to give the busiest day for example, but I’ll leave that as an exercise for the reader. By the way, the calculation came from 11g statspack report. The AWR report is wrapped, but the statspack report is not. So I just modified the query to use the AWR tables instead of the Statspack tables and added the lag function. This script works on 10gR2 as well.
Here’s the script: dbtime.sql
And an example of its use:
SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 5 10:08:58 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @dbtime
Enter value for instance_number:
Enter value for begin_snap_id:
Enter value for end_snap_id:
BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME
---------- ---------- ---------------------------------------- ---------- -----------
8205 8206 27-APR-09 09.21.22.022 AM 1 32.87
8207 8208 27-APR-09 09.40.50.404 AM 1 32.63
8245 8246 28-APR-09 10.00.58.734 PM 1 5.65
8389 8390 04-MAY-09 10.00.26.563 PM 1 5.62
8325 8326 02-MAY-09 06.00.06.252 AM 1 4.81
8220 8221 27-APR-09 10.00.06.667 PM 1 4.28
8317 8318 01-MAY-09 10.00.18.928 PM 1 3.14
8230 8231 28-APR-09 08.00.17.951 AM 1 2.80
8293 8294 30-APR-09 10.00.56.784 PM 1 2.37
8349 8350 03-MAY-09 06.00.28.355 AM 1 2.35
8244 8245 28-APR-09 09.00.51.632 PM 1 2.26
8232 8233 28-APR-09 10.00.32.610 AM 1 1.97
8305 8306 01-MAY-09 10.00.07.398 AM 1 1.93
8292 8293 30-APR-09 09.00.50.806 PM 1 1.86
8268 8269 29-APR-09 09.00.29.463 PM 1 1.82
8269 8270 29-APR-09 10.00.35.137 PM 1 1.80
8388 8389 04-MAY-09 09.00.20.561 PM 1 1.80
8353 8354 03-MAY-09 10.00.51.838 AM 1 1.32
8316 8317 01-MAY-09 09.00.12.973 PM 1 1.31
8329 8330 02-MAY-09 10.00.29.899 AM 1 1.22
8348 8349 03-MAY-09 05.00.22.374 AM 1 1.18
8213 8214 27-APR-09 03.00.16.481 PM 1 1.17
8365 8366 03-MAY-09 10.00.02.746 PM 1 1.17
8357 8358 03-MAY-09 02.00.15.346 PM 1 1.08
8341 8342 02-MAY-09 10.00.40.654 PM 1 1.04
8337 8338 02-MAY-09 06.00.17.054 PM 1 1.03
8204 8205 27-APR-09 09.20.32.516 AM 1 1.00
8361 8362 03-MAY-09 06.00.39.298 PM 1 1.00
8202 8203 27-APR-09 09.17.02.145 AM 1 0.97
8333 8334 02-MAY-09 02.00.53.480 PM 1 0.97
30 rows selected.
And here’s the output of the AWR report for the top period.
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
LAB111 1385947011 LAB111 1 22-Apr-09 14:19 11.1.0.7.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
homer Linux IA (32-bit) 16 4 4 31.73
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 8205 27-Apr-09 09:21:22 25 1.8
End Snap: 8206 27-Apr-09 09:27:33 23 1.8
Elapsed: 6.19 (mins)
DB Time: 32.87 (mins)
Brilliant! Thanks Kerry! 🙂
My pleasure.
Kerry
Hey that’s a pretty cool picture lol.
How did you get the work load report which script you used can you please share
Maddy,
That’s the very top part of the standard AWR report.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Hey Kerry,
Can you please zip/point with an URL regarding all your sql scripts. I always follow up your scripts what ever I get. Please post an URL with all the scripts or a zip file. This will help us a lot.
Hi Kerry- Thanks for the wonderful tip.
I have another question, do you know how to calculate top wait time for a given snapshot using DB Time.
As per script below is DB Time
and AWR report for same time tells "db file sequential read" was 18.1% of DB Time
I tried to get the same % from dba_hist_active_sess_history but no where i am near ..
But I could not match how given values can be of 18% of DB Time : 113 minute.. Or I am looking at wrong place at all..
Thanks in advance.
Hi Kerry, it seems you did not like my reply 🙁
Nah, I’m just a little slow getting to comments. 😉 I’ll post a little code in a bit (hopefully in the next hour or two).
jm
Looks like I am not going to get any scripts up for a few days. I think you want DBA_HIST_SYSTEM_EVENT though not dba_active_session_history. The Top 5 part of AWR displays non-idle event (wait_class != ‘Idle’) and unions in the DBA_HIST_SYS_TIME_MODEL stuff I think. I fooled around a little with reproducing the top 5 profile but don’t have time to mess with it anymore until next week (or maybe later than that). Hopefully that will get you heading in the right direction though. The dbtime value can be pulled straight from my dbtime.sql script.
Hi,
First of all thanks for share with all us.
I try tu run on test system and returns negative values for dbtime, How is possible?
Best Regards.
Hi Oscar,
Interesting question. I “borrowed” that query directly from AWR. AWR runs it for one instance at a time though, so if you leave the instance_number blank (meaning it will run across all snaps for all instances in a RAC environment), the order by snap_id is not sufficient to to get the data in the correct order. Anyway, I have added the instance_number to the order by which appears to fix the issue. Re-download the script and give it another shot with the new version and let me know if it works better.
Kerry
Hi Osborne,
Thanks for your fast response. I try to execute again the query but I’m not sure if I downloaded the new version.
I downloaded from link:
Here’s the script: dbtime.sql
I tryed to again execute without parameters and I get a high value for dbtime. This values is not the same giving intance number to script.
Giving the instance number to script run fine and is awsome.
Sorry for the inconvenience.
Thx.
Hi Oscar,
If you leave the instance number blank on a RAC db, it should show snaps with the highest dbtime regardless of which node the snap occurred on. So the snaps would tend to bounce around between instances. If you put in an instance, all the snaps should be from that single instance. If it’s not behaving as expected feel free to send me the output.
Here’s an example of what I mean:
Kerry:
This is a really nice script, I wish I had found this one earlier.
I am using a similar (much messier) script to do this.
However to find a server busiest snap, I use “DB CPU” + “background cpu time” stats
select snap_id, sum(value) value, ‘DB CPU’ stat_name from DBA_HIST_SYS_TIME_MODEL where stat_name in (‘DB CPU’,’background cpu time’) group by snap_id ;
Regards
Zhen
[…] duration for further analysis. So i was searching for DB Time Calculations and i found the logic on Kerry Osborne’s blog. This post will give demonstration of the awr dbtime script. I have tested this script on 11.2.0.1 […]
Hi All,
Can anyone tell me, how dba_hist_service_wait_class being queried by AWR for section Service stat by wait class.
Regards,
VInodh kumar