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)

14 Comments

  1. Gary Eckhardt says:

    Brilliant! Thanks Kerry! :)

  2. osborne says:

    My pleasure.

    Kerry

  3. Jerry Carlisle says:

    Hey that’s a pretty cool picture lol.

  4. maddy says:

    How did you get the work load report which script you used can you please share

  5. osborne says:

    Maddy,

    That’s the very top part of the standard AWR report.

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql

  6. Maddy says:

    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.

  7. Jm says:

    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

      221      222 18-FEB-12 10.00.43.328000000 AM             1      113.44 
    

    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/O
    

    I tried to get the same % from dba_hist_active_sess_history but no where i am near ..

    Select event, round(sum(tm_delta_time)/1000000/60,1) delta_time_mint 
    , round(sum(tm_delta_cpu_time)/1000000/60,1) delta_cpu_mint
    , round(sum(tm_delta_db_time)/1000000/60,1) delta_db_mint
    from dba_hist_active_sess_history 
    where snap_id between 221 and 222 and 
    instance_number = 1 and event = 'db file sequential read'   
    group by event
    
    
    EVENT                                                            DELTA_TIME_MINT DELTA_CPU_MINT DELTA_DB_MINT
    ---------------------------------------------------------------- --------------- -------------- -------------
    db file sequential read                                                    101.9        96226.1           151 
    

    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.

  8. jm says:

    Hi Kerry, it seems you did not like my reply :(

  9. osborne says:

    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).

  10. osborne says:

    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.

  11. Oscar says:

    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.

  12. osborne says:

    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

    • Oscar says:

      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.

  13. osborne says:

    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.
    

Leave a Reply