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)
