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
Avg wait % DB Event Waits Time(s) (ms) time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- db file sequential read 203,792 1,229 6 18.1 User I/OI 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:
SYS@DEMO1> @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 ---------- ---------- ---------------------------------------- ---------- ----------- 25711 25712 17-APR-13 10.00.23.941 AM 1 1,467.08 25051 25052 03-APR-13 10.25.04.526 AM 2 1,462.38 25711 25712 17-APR-13 12.27.12.332 AM 2 1,286.57 39803 39804 04-FEB-14 03.00.29.313 PM 2 973.53 31252 31253 10-AUG-13 10.00.21.283 PM 1 972.83 39803 39804 04-FEB-14 03.00.29.332 PM 1 937.64 31001 31002 05-AUG-13 04.30.05.879 PM 1 914.14 37620 37621 20-DEC-13 09.30.13.774 AM 1 907.31 27056 27057 14-MAY-13 04.51.55.478 PM 2 832.97 36891 36892 05-DEC-13 05.00.53.452 AM 1 803.05 41244 41245 06-MAR-14 02.00.45.589 PM 1 788.56 41244 41245 06-MAR-14 02.00.45.556 PM 2 765.52 30997 30998 05-AUG-13 02.30.39.894 PM 2 741.66 27350 27351 20-MAY-13 06.06.14.325 PM 2 740.82 41245 41246 06-MAR-14 02.30.49.804 PM 1 735.51 41245 41246 06-MAR-14 02.30.49.783 PM 2 730.33 27350 27351 21-MAY-13 01.30.47.642 PM 1 706.62 24946 24947 01-APR-13 11.30.15.704 AM 1 669.47 27774 27775 29-MAY-13 11.32.33.827 AM 2 657.94 40139 40140 11-FEB-14 03.00.19.397 PM 1 656.12 25616 25617 15-APR-13 10.30.12.308 AM 1 653.37 33817 33818 02-OCT-13 04.00.11.759 PM 1 639.21 25616 25617 15-APR-13 01.31.17.433 AM 2 635.89 32440 32441 04-SEP-13 09.00.12.239 AM 2 615.73 41218 41219 06-MAR-14 01.30.25.003 AM 1 554.16 29839 29840 12-JUL-13 10.00.11.173 AM 1 495.98 32441 32442 04-SEP-13 09.30.17.408 AM 2 485.19 32758 32759 10-SEP-13 04.30.37.159 PM 1 475.09 32440 32441 04-SEP-13 09.00.12.257 AM 1 450.08 38822 38823 14-JAN-14 03.30.14.091 PM 1 447.01 30 rows selected. SYS@DEMO1> / Enter value for instance_number: 1 Enter value for begin_snap_id: Enter value for end_snap_id: BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME ---------- ---------- ---------------------------------------- ---------- ----------- 25711 25712 17-APR-13 10.00.23.941 AM 1 1,467.08 31252 31253 10-AUG-13 10.00.21.283 PM 1 972.83 39803 39804 04-FEB-14 03.00.29.332 PM 1 937.64 31001 31002 05-AUG-13 04.30.05.879 PM 1 914.14 37620 37621 20-DEC-13 09.30.13.774 AM 1 907.31 36891 36892 05-DEC-13 05.00.53.452 AM 1 803.05 41244 41245 06-MAR-14 02.00.45.589 PM 1 788.56 41245 41246 06-MAR-14 02.30.49.804 PM 1 735.51 27350 27351 21-MAY-13 01.30.47.642 PM 1 706.62 24946 24947 01-APR-13 11.30.15.704 AM 1 669.47 40139 40140 11-FEB-14 03.00.19.397 PM 1 656.12 25616 25617 15-APR-13 10.30.12.308 AM 1 653.37 33817 33818 02-OCT-13 04.00.11.759 PM 1 639.21 41218 41219 06-MAR-14 01.30.25.003 AM 1 554.16 29839 29840 12-JUL-13 10.00.11.173 AM 1 495.98 32758 32759 10-SEP-13 04.30.37.159 PM 1 475.09 32440 32441 04-SEP-13 09.00.12.257 AM 1 450.08 38822 38823 14-JAN-14 03.30.14.091 PM 1 447.01 41241 41242 06-MAR-14 01.00.36.625 PM 1 439.31 38665 38666 11-JAN-14 09.00.51.012 AM 1 409.40 40390 40391 16-FEB-14 07.30.31.445 PM 1 407.51 34524 34525 17-OCT-13 07.30.22.438 AM 1 401.27 40391 40392 16-FEB-14 08.00.33.852 PM 1 400.16 24467 24468 22-MAR-13 12.00.50.236 PM 1 399.93 39579 39580 30-JAN-14 11.00.09.432 PM 1 393.69 41217 41218 06-MAR-14 01.00.17.643 AM 1 391.91 41216 41217 06-MAR-14 12.30.13.259 AM 1 388.50 27061 27062 15-MAY-13 01.00.38.871 PM 1 385.93 41246 41247 06-MAR-14 03.00.02.625 PM 1 382.91 37621 37622 20-DEC-13 10.00.17.026 AM 1 378.81 30 rows selected. SYS@DEMO1> / Enter value for instance_number: 2 Enter value for begin_snap_id: Enter value for end_snap_id: BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME ---------- ---------- ---------------------------------------- ---------- ----------- 25051 25052 03-APR-13 10.25.04.526 AM 2 1,462.38 25711 25712 17-APR-13 12.27.12.332 AM 2 1,286.57 39803 39804 04-FEB-14 03.00.29.313 PM 2 973.53 27056 27057 14-MAY-13 04.51.55.478 PM 2 832.97 41244 41245 06-MAR-14 02.00.45.556 PM 2 765.52 30997 30998 05-AUG-13 02.30.39.894 PM 2 741.66 27350 27351 20-MAY-13 06.06.14.325 PM 2 740.82 41245 41246 06-MAR-14 02.30.49.783 PM 2 730.33 27774 27775 29-MAY-13 11.32.33.827 AM 2 657.94 25616 25617 15-APR-13 01.31.17.433 AM 2 635.89 32440 32441 04-SEP-13 09.00.12.239 AM 2 615.73 32441 32442 04-SEP-13 09.30.17.408 AM 2 485.19 41241 41242 06-MAR-14 01.00.36.589 PM 2 441.76 38917 38918 16-JAN-14 03.00.30.290 PM 2 435.25 31997 31998 26-AUG-13 04.30.21.911 AM 2 396.75 41246 41247 06-MAR-14 03.00.02.644 PM 2 382.78 27061 27062 14-MAY-13 07.20.22.034 PM 2 374.80 39579 39580 30-JAN-14 11.00.09.451 PM 2 374.27 39581 39582 31-JAN-14 12.00.14.393 AM 2 374.23 24467 24468 22-MAR-13 09.56.28.738 AM 2 355.12 41031 41032 02-MAR-14 04.00.09.242 AM 2 351.54 28065 28066 04-JUN-13 11.17.54.455 AM 2 342.21 32758 32759 10-SEP-13 04.30.37.185 PM 2 323.50 40481 40482 18-FEB-14 05.00.21.115 PM 2 304.51 30754 30755 31-JUL-13 01.00.01.562 PM 2 301.76 38956 38957 17-JAN-14 10.30.27.946 AM 2 296.97 25024 25025 02-APR-13 09.04.58.097 PM 2 289.19 27054 27055 14-MAY-13 03.52.24.750 PM 2 263.88 24465 24466 22-MAR-13 08.56.55.128 AM 2 255.28 27053 27054 14-MAY-13 03.22.42.301 PM 2 255.20 30 rows selected.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