Load profile

I like Load profile section of Statspack or AWR reports (who doesn’t). It’s short and gives a brief understanding of what kind of work a database does. But what if you don’t have an access to Statspack or AWR but still want to see something similar? It’s possible to use V$SYSMETRIC to get this numbers for last 60 or 15 seconds. I wanted to write a script to do this for a long time. Here it is.

col short_name  format a20              heading 'Load Profile'
col per_sec     format 999,999,999.9    heading 'Per Second'
col per_tx      format 999,999,999.9    heading 'Per Transaction'
set colsep '   '

select lpad(short_name, 20, ' ') short_name
     , per_sec
     , per_tx from
    (select short_name
          , max(decode(typ, 1, value)) per_sec
          , max(decode(typ, 2, value)) per_tx
          , max(m_rank) m_rank 
       from
        (select /*+ use_hash(s) */ 
                m.short_name
              , s.value * coeff value
              , typ
              , m_rank
           from v$sysmetric s,
               (select 'Database Time Per Sec'                      metric_name, 'DB Time' short_name, .01 coeff, 1 typ, 1 m_rank from dual union all
                select 'CPU Usage Per Sec'                          metric_name, 'DB CPU' short_name, .01 coeff, 1 typ, 2 m_rank from dual union all
                select 'Redo Generated Per Sec'                     metric_name, 'Redo size' short_name, 1 coeff, 1 typ, 3 m_rank from dual union all
                select 'Logical Reads Per Sec'                      metric_name, 'Logical reads' short_name, 1 coeff, 1 typ, 4 m_rank from dual union all
                select 'DB Block Changes Per Sec'                   metric_name, 'Block changes' short_name, 1 coeff, 1 typ, 5 m_rank from dual union all
                select 'Physical Reads Per Sec'                     metric_name, 'Physical reads' short_name, 1 coeff, 1 typ, 6 m_rank from dual union all
                select 'Physical Writes Per Sec'                    metric_name, 'Physical writes' short_name, 1 coeff, 1 typ, 7 m_rank from dual union all
                select 'User Calls Per Sec'                         metric_name, 'User calls' short_name, 1 coeff, 1 typ, 8 m_rank from dual union all
                select 'Total Parse Count Per Sec'                  metric_name, 'Parses' short_name, 1 coeff, 1 typ, 9 m_rank from dual union all
                select 'Hard Parse Count Per Sec'                   metric_name, 'Hard Parses' short_name, 1 coeff, 1 typ, 10 m_rank from dual union all
                select 'Logons Per Sec'                             metric_name, 'Logons' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all
                select 'Executions Per Sec'                         metric_name, 'Executes' short_name, 1 coeff, 1 typ, 12 m_rank from dual union all
                select 'User Rollbacks Per Sec'                     metric_name, 'Rollbacks' short_name, 1 coeff, 1 typ, 13 m_rank from dual union all
                select 'User Transaction Per Sec'                   metric_name, 'Transactions' short_name, 1 coeff, 1 typ, 14 m_rank from dual union all
                select 'User Rollback UndoRec Applied Per Sec'      metric_name, 'Applied urec' short_name, 1 coeff, 1 typ, 15 m_rank from dual union all
                select 'Redo Generated Per Txn'                     metric_name, 'Redo size' short_name, 1 coeff, 2 typ, 3 m_rank from dual union all
                select 'Logical Reads Per Txn'                      metric_name, 'Logical reads' short_name, 1 coeff, 2 typ, 4 m_rank from dual union all
                select 'DB Block Changes Per Txn'                   metric_name, 'Block changes' short_name, 1 coeff, 2 typ, 5 m_rank from dual union all
                select 'Physical Reads Per Txn'                     metric_name, 'Physical reads' short_name, 1 coeff, 2 typ, 6 m_rank from dual union all
                select 'Physical Writes Per Txn'                    metric_name, 'Physical writes' short_name, 1 coeff, 2 typ, 7 m_rank from dual union all
                select 'User Calls Per Txn'                         metric_name, 'User calls' short_name, 1 coeff, 2 typ, 8 m_rank from dual union all
                select 'Total Parse Count Per Txn'                  metric_name, 'Parses' short_name, 1 coeff, 2 typ, 9 m_rank from dual union all
                select 'Hard Parse Count Per Txn'                   metric_name, 'Hard Parses' short_name, 1 coeff, 2 typ, 10 m_rank from dual union all
                select 'Logons Per Txn'                             metric_name, 'Logons' short_name, 1 coeff, 2 typ, 11 m_rank from dual union all
                select 'Executions Per Txn'                         metric_name, 'Executes' short_name, 1 coeff, 2 typ, 12 m_rank from dual union all
                select 'User Rollbacks Per Txn'                     metric_name, 'Rollbacks' short_name, 1 coeff, 2 typ, 13 m_rank from dual union all
                select 'User Transaction Per Txn'                   metric_name, 'Transactions' short_name, 1 coeff, 2 typ, 14 m_rank from dual union all
                select 'User Rollback Undo Records Applied Per Txn' metric_name, 'Applied urec' short_name, 1 coeff, 2 typ, 15 m_rank from dual) m
          where m.metric_name = s.metric_name
            and s.intsize_csec > 5000
            and s.intsize_csec < 7000)
      group by short_name)
 order by m_rank;

Couple of notes:

  • It’s a simple piece of code with most of it being copy-paste to select data that is only required and to sort it the same way as AWR does
  • I’ve deliberately supplied a USE_HASH hint to the query because on one DB without a fixed objects statistics (or wrong – not sure) Oracle decided to do a NESTED LOOP with a FULL SCAN of a huge in-memory structure so the query took around a second to execute. Don’t wanna that happen
  • It’s possible to utilize PIVOT in the query, but it’ll limit applicability to 11g only – and I want this SQL to be able to run on 10g too

And here is a sample output. Looks familiar :-)

Load Profile               Per Second   Per Transaction
--------------------   --------------   ---------------
             DB Time               .7
              DB CPU               .7
           Redo size        217,570.3       4,283,958.7
       Logical reads          4,177.1          82,248.0
       Block changes          1,475.5          29,053.0
      Physical reads               .1               2.0
     Physical writes               .4               8.3
          User calls               .3               6.0
              Parses              8.2             161.3
         Hard Parses              7.3             144.0
              Logons               .1               1.3
            Executes            722.3          14,222.3
           Rollbacks               .0
        Transactions               .1
        Applied urec               .1               1.0

7 Comments

  1. Posted February 22, 2012 at 13:39 | Permalink | Reply

    Hi Timur,
    seems very familiar….maybe I’ve seen it in somewhere… :)
    Ciao

  2. amy
    Posted February 23, 2012 at 01:40 | Permalink | Reply

    awesome query…one question i had was.. you mentioned this query can be used to get data for last 15 or 60 seconds…
    first of all…the current query you have…dose that show data for last 15 or last 60 seconds…
    second…if i want to get the data for lets say last 5 or 10 mins…how do i change the query ?

    • Posted February 23, 2012 at 12:19 | Permalink | Reply

      Hi,
      plz check the doc
      V$SYSMETRIC displays the system metric values captured for the most current time interval for both the long duration (60-second) and short duration (15-second) system metrics.
      Regards
      GregG

  3. Nomura
    Posted August 13, 2012 at 23:49 | Permalink | Reply

    Thank you this is extremely useful!
    Thank you a lot!

    • Posted August 14, 2012 at 10:29 | Permalink | Reply

      You’re welcome.

      • Ganesh
        Posted August 10, 2015 at 06:09 | Permalink | Reply

        With your above query inspiration,

        I tired for the AWR load profile.

        WITH snaps
             AS (SELECT 3063374685 AS db_id,
                        1 instance_number,
                        237164 e_snap_id,
                        237163 b_snap_id
                   FROM DUAL),
             e_u_val
             AS (SELECT SUM (VALUE) end_val
                   FROM dba_hist_sysstat e, snaps sn
                  WHERE     1 = 1
                        AND e.snap_id = sn.e_snap_id
                        AND e.dbid = sn.db_id
                        AND e.instance_number = sn.instance_number
                        AND e.stat_name IN ('user rollbacks', 'user commits')),
             b_u_val
             AS (SELECT SUM (VALUE) bgn_val
                   FROM dba_hist_sysstat b, snaps sn
                  WHERE     1 = 1
                        AND b.snap_id = sn.b_snap_id
                        AND b.dbid = sn.db_id
                        AND b.instance_number = sn.instance_number
                        AND b.stat_name IN ('user rollbacks', 'user commits')),
             u_val
             AS (SELECT end_val - bgn_val usr_val
                   FROM e_u_val, b_u_val
                  WHERE 1 = 1),
             db_tme
             AS (SELECT     EXTRACT (
                               DAY FROM e.end_interval_time - b.end_interval_time)
                          * 86400
                        +   EXTRACT (
                               HOUR FROM e.end_interval_time - b.end_interval_time)
                          * 3600
                        +   EXTRACT (
                               MINUTE FROM e.end_interval_time - b.end_interval_time)
                          * 60
                        + EXTRACT (
                             SECOND FROM e.end_interval_time - b.end_interval_time)
                           d_db_tme
                   FROM dba_hist_snapshot b, dba_hist_snapshot e, snaps sn
                  WHERE     e.snap_id = sn.e_snap_id
                        AND b.snap_id = sn.b_snap_id
                        AND b.dbid = sn.db_id
                        AND b.instance_number = sn.instance_number
                        AND e.dbid = sn.db_id
                        AND e.instance_number = sn.instance_number),
             trn_val
             AS (SELECT 'Transactions:' st_name,
                        ROUND (usr_val / d_db_tme, 2) per_sec,
                        NULL per_txn,
                        12 m_rank
                   FROM db_tme, uval
                  WHERE 1 = 1),
             bgn_val
             AS (SELECT /*+ use_hash(s) */
                       m.st_name, b.VALUE VALUE, m_rank
                   FROM dba_hist_sysstat b,
                        (SELECT 'redo size' stat_name, 'Redo size:' st_name, 1 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'session logical reads' stat_name,
                                'Logical reads:' st_name,
                                2 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'db block changes' metric_name,
                                'Block changes:' st_name,
                                3 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'physical reads' metric_name,
                                'Physical reads' st_name,
                                4 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'physical writes' metric_name,
                                'Physical writes:' st_name,
                                5 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'user calls' metric_name,
                                'User calls:' st_name,
                                6 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'parse count (total)' metric_name,
                                'Parses:' st_name,
                                7 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'parse count (hard)' metric_name,
                                'Hard Parses:' st_name,
                                8 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'logons cumulative' metric_name,
                                'Logons:' st_name,
                                9 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'execute count' metric_name,
                                'Executes:' st_name,
                                10 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'user rollbacks' metric_name,
                                'Rollbacks:' st_name,
                                11 m_rank
                           FROM DUAL) m,
                        snaps sn
                  WHERE     1 = 1
                        AND m.stat_name = b.stat_name
                        AND b.snap_id = sn.b_snap_id
                        AND b.dbid = sn.db_id
                        AND b.instance_number = sn.instance_number),
             end_val
             AS (SELECT /*+ use_hash(s) */
                       m.st_name, b.VALUE VALUE, m_rank
                   FROM dba_hist_sysstat b,
                        (SELECT 'redo size' stat_name, 'Redo size:' st_name, 1 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'session logical reads' stat_name,
                                'Logical reads:' st_name,
                                2 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'db block changes' metric_name,
                                'Block changes:' st_name,
                                3 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'physical reads' metric_name,
                                'Physical reads' st_name,
                                4 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'physical writes' metric_name,
                                'Physical writes:' st_name,
                                5 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'user calls' metric_name,
                                'User calls:' st_name,
                                6 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'parse count (total)' metric_name,
                                'Parses:' st_name,
                                7 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'parse count (hard)' metric_name,
                                'Hard Parses:' st_name,
                                8 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'logons cumulative' metric_name,
                                'Logons:' st_name,
                                9 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'execute count' metric_name,
                                'Executes:' st_name,
                                10 m_rank
                           FROM DUAL
                         UNION ALL
                         SELECT 'user rollbacks' metric_name,
                                'Rollbacks:' st_name,
                                11 m_rank
                           FROM DUAL) m,
                        snaps sn
                  WHERE     1 = 1
                        AND m.stat_name = b.stat_name
                        AND b.snap_id = sn.e_snap_id
                        AND b.dbid = sn.db_id
                        AND b.instance_number = sn.instance_number)
          SELECT st_name, per_sec, per_txn
            FROM (SELECT e.st_name,
                         ROUND ( (e.VALUE - b.VALUE) / (SELECT d_db_tme FROM db_tme),
                                1)
                            per_sec,
                         ROUND ( (e.VALUE - b.VALUE) / (SELECT usr_val FROM u_val), 1)
                            per_txn,
                         e.m_rank
                    FROM end_val e, bgn_val b
                   WHERE e.st_name = b.st_name AND e.m_rank = b.m_rank
                  UNION ALL
                  SELECT st_name, round(per_sec,1), per_txn, m_rank FROM trn_val)
        ORDER BY m_rank
        /
        

2 Trackbacks

  1. By Stats note « Oracle Scratchpad on February 23, 2012 at 00:20

    […] here’s a link to a really smart piece of SQL from Timur Ahkmadeev which uses v$sysmetric to emulate the output of the statspack/AWR profile over […]

  2. […] a query from a v$ view.. luckily there’s a script created by Timur Akhmadeev a few months ago https://timurakhmadeev.wordpress.com/2012/02/21/load-profile/ that outputs the same Load Profile section of the AWR report but pulls data from the v$sysmetric […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 295 other followers