Search This Blog

Total Pageviews

Wednesday, 26 April 2023

Load Profile in AWR reports

Load Profile in AWR reports
=====

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
			--sysdate - interval '5' minute
			and END_TIME > sysdate - interval '5' minute
			)
      group by short_name)
 order by m_rank;


   DB Time              4.6
              DB CPU              2.0
           Redo size        255,839.5           7,734.4
       Logical reads        596,220.2          18,024.6
       Block changes          1,221.1              36.9
      Physical reads          2,155.1              65.2
     Physical writes             23.4                .7
          User calls            754.5              22.8
              Parses            874.5              26.4
         Hard Parses              6.7                .2
              Logons             20.8                .6
            Executes          1,003.1              30.3
           Rollbacks               .0
        Transactions             33.1
        Applied urec               .0                .0

  
  
  ---  ===== 
	   
	   
	   
	   
 
set head off  pages 0  lines 300 echo off  feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;




WITH snaps
     AS (SELECT :DID db_id,
                :INST_NUMBER instance_number,
                :EndSnap e_snap_id,
                :BgnSnap 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')),
     d_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, d_u_val
          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 d_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
/


Redo size:            5,382,951.2      79709.2
Logical reads:          640,660.8       9486.7
Block changes:           23,180.7        343.3
Physical reads           10,727.6        158.9
Physical writes:            586.8          8.7
User calls:                 796.8         11.8
Parses:                     884.7         13.1
Hard Parses:                  6.4           .1
Logons:                      21.0           .3
Executes:                 1,150.6           17
Rollbacks:                     .0            0
Transactions:                67.5



No comments:

Oracle DBA

anuj blog Archive