Oracle RAC Monitoring Key Metrics and SQLs

Sometime, you need compare this month database change with last month to show new release impact or some key feature difference on database, in order to list changes, you must give the key metrics to express the impact on RAC, based on my experience and give below metrics and SQLs.

1.List Perk Time Work Day SQL Total Executions.

2.List Perk Time Work Day SQL Total Buffer Get.

3.List Perk Time Work Day DB Block Changes.

4.List Perk Time Work Day DB Row Lock Waits.

5.List Perk Time Work Day DB OS CPU Usage(%).

I use webchart(free to use) to show my montor metrics, the graph list as following.

The tool is very good and it's only need write some SQLs, then it can work well, but the SQL are very complex, so let me show the detail SQLs.

#List SQL Execution change

WITH lastMonth as ( 

select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 

from(  

SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 

       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 

       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS Executions, 

       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS LogicIO, 

       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS PhysicalIO, 

           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 

       to_number(TO_CHAR (a.begin_interval_time, 'iw'))+4 AS weeklyNum, 

       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 

  FROM dba_hist_snapshot a, dba_hist_sqlstat b 

 WHERE     b.dbid = a.dbid   

       AND b.dbid = ********

       AND b.instance_number = a.instance_number 

       AND b.snap_id = a.snap_id 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 

       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 

       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  

       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where RN=1 order by StartTime), 

curMonth as  

( 

select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 

from( 

SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 

       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 

       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS Executions, 

       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS LogicIO, 

       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS PhysicalIO, 

           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 

       to_number(TO_CHAR (a.begin_interval_time, 'iw')) AS weeklyNum, 

       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 

  FROM dba_hist_snapshot a, dba_hist_sqlstat b 

 WHERE     b.dbid = a.dbid 

       AND b.dbid = ******** 

       AND b.instance_number = a.instance_number 

       AND b.snap_id = a.snap_id 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 

       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 

       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday')  

       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where RN=1 order by StartTime) 

select to_date(a.StartTime,'mmdd') as StartTime,b.StartTime as lastStartTime,a.Executions as CurrentMonth,b.Executions as LastMonth, trunc((a.Executions-b.Executions)/b.Executions,7) as "Increment" 

from curMonth a ,lastMonth b 

where a.dayname=b.dayname 

and a.weeklyNum=b.weeklyNum 

order by 1 

#List Logic IO change

WITH lastMonth as ( 

select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 

from(  

SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 

       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 

       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS Executions, 

       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS LogicIO, 

       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS PhysicalIO, 

           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 

       to_number(TO_CHAR (a.begin_interval_time, 'iw'))+4 AS weeklyNum, 

       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 

  FROM dba_hist_snapshot a, dba_hist_sqlstat b 

 WHERE     b.dbid = a.dbid   

       AND b.dbid = ****** 

       AND b.instance_number = a.instance_number 

       AND b.snap_id = a.snap_id 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 

       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 

       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  

       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where RN=1 order by StartTime), 

curMonth as  

( 

select StartTime,weeklyNum,dayName,Executions,LogicIO,PhysicalIO,(LogicIO+PhysicalIO) as TotalIO 

from( 

SELECT TO_CHAR (a.begin_interval_time, 'dd') AS Day, 

       TO_CHAR (a.begin_interval_time, 'mmdd') AS StartTime, 

       TRUNC (SUM (b.executions_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS Executions, 

       TRUNC (SUM (b.buffer_gets_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS LogicIO, 

       TRUNC (SUM (b.disk_reads_delta) OVER (PARTITION BY a.snap_id), 2) 

          AS PhysicalIO, 

           UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) as dayName, 

       to_number(TO_CHAR (a.begin_interval_time, 'iw')) AS weeklyNum, 

       ROW_NUMBER () OVER (PARTITION BY a.snap_id order by a.snap_id) AS RN 

  FROM dba_hist_snapshot a, dba_hist_sqlstat b 

 WHERE     b.dbid = a.dbid 

       AND b.dbid = ****** 

       AND b.instance_number = a.instance_number 

       AND b.snap_id = a.snap_id 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (a.begin_interval_time, 'hh24mi') <= '1605' 

       AND a.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 

       AND a.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday')  

       AND UPPER (SUBSTR (TO_CHAR (a.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where RN=1 order by StartTime) 

select to_date(a.StartTime,'mmdd') as StartTime,b.StartTime as lastStartTime,a.LogicIO as CurrentMonth,b.LogicIO as LastMonth, trunc((a.LogicIO-b.LogicIO)/b.LogicIO,7) as "Increment" 

from curMonth a ,lastMonth b 

where a.dayname=b.dayname 

and a.weeklyNum=b.weeklyNum 

order by 1

#List DB block changes

With LasMonth as  

( 

select starttime,to_number(weeklyNum)+4 as weeklyNum,day,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 

from( 

SELECT a.obj#, 

       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 

       TO_CHAR (b.begin_interval_time, 'dd') AS day, 

       TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum,

       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1,3)) as dayName,

       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS,

       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS,

       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,

       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,

       A.snap_id 

  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 

 WHERE     a.dbid = b.dbid 

       AND a.snap_id = b.snap_id 

       AND a.dbid = ******  

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') < '1605'   

       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 

       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  

       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN  

              ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where rn=1 

), 

currentMonth as  

( 

select starttime,day,weeklyNum,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 

from( 

SELECT a.obj#, 

       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 

       TO_CHAR (b.begin_interval_time, 'dd') AS day,  

        TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum, 

       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) as dayName, 

       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS, 

       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS, 

       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,

       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,

       A.snap_id 

  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 

 WHERE     a.dbid = b.dbid 

       AND a.snap_id = b.snap_id 

       AND a.dbid = ****** 

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') <'1605'   

       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 

       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday') 

       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN 

              ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where rn=1 

) 

select to_date(A.starttime,'yyyymmdd') as starttime ,b.starttime as LastStartTime, 

 b.DB_BLOCK_CHANGES as lasMonth,a.DB_BLOCK_CHANGES as currentMonth, 

 trunc((a.DB_BLOCK_CHANGES-b.DB_BLOCK_CHANGES)/b.DB_BLOCK_CHANGES,7) as "Increment" 

 from currentMonth a,LasMonth b  

 where a.weeklyNum=b.weeklyNum and a.dayname=b.dayname 

Order by 1

#For Row lock Waits

With LasMonth as  

( 

select starttime,to_number(weeklyNum)+4 as weeklyNum,day,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 

from( 

SELECT a.obj#, 

       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 

       TO_CHAR (b.begin_interval_time, 'dd') AS day, 

       TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum,

       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1,3)) as dayName,

       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS,

       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS,

       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,

       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,

       A.snap_id 

  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 

 WHERE     a.dbid = b.dbid 

       AND a.snap_id = b.snap_id 

       AND a.dbid =******  

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') < '1605'   

       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 

       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday')  

       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN  

              ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where rn=1 

), 

currentMonth as  

( 

select starttime,day,weeklyNum,dayname,ITL_WAITS,ROW_LOCK_WAITS,DB_BLOCK_CHANGES,snap_id 

from( 

SELECT a.obj#, 

       TO_CHAR (b.begin_interval_time, 'yyyymmdd') AS starttime, 

       TO_CHAR (b.begin_interval_time, 'dd') AS day,  

        TO_CHAR (b.begin_interval_time, 'iw') AS weeklyNum, 

       UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) as dayName, 

       TRUNC (SUM (A.ITL_WAITS_DELTA) OVER (PARTITION BY a.snap_id),2) AS ITL_WAITS, 

       TRUNC (SUM (A.ROW_LOCK_WAITS_DELTA) OVER (PARTITION BY  a.snap_id),2) AS ROW_LOCK_WAITS, 

       TRUNC (SUM (A.DB_BLOCK_CHANGES_DELTA)OVER (PARTITION BY  a.snap_id),2) AS DB_BLOCK_CHANGES,

       ROW_NUMBER () OVER (PARTITION BY A.snap_id ORDER BY  A.snap_id)AS RN,

       A.snap_id 

  FROM DBA_HIST_SEG_STAT a, DBA_HIST_SNAPSHOT b 

 WHERE     a.dbid = b.dbid 

       AND a.snap_id = b.snap_id 

       AND a.dbid =****** 

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') >= '1555' 

       AND TO_CHAR (b.begin_interval_time, 'hh24mi') <'1605'   

       AND b.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 

       AND b.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday') 

       AND UPPER (SUBSTR (TO_CHAR (b.begin_interval_time, 'Day'), 1, 3)) IN 

              ('MON', 'TUE', 'WED', 'THU', 'FRI') 

) where rn=1 

) 

select to_date(A.starttime,'yyyymmdd') as starttime ,b.starttime as LastStartTime, 

 b.ROW_LOCK_WAITS as lasMonth,a.ROW_LOCK_WAITS as currentMonth, 

 trunc((a.ROW_LOCK_WAITS-b.ROW_LOCK_WAITS)/b.ROW_LOCK_WAITS,7) as "Increment" 

 from currentMonth a,LasMonth b  

 where a.weeklyNum=b.weeklyNum and a.dayname=b.dayname 

Order by 1

#For OS CPU Usage, must have two snap shot

WEBCHART.QUERY_5=with lastMonth as 

(

select dayName,weeklyNum,StartTime, 

round(((UserTimeDiff+SysTimeDiff)/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) BusyTimePct, 

round((UserTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) UserTimePct,    

round((NiceTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  NiceTimePct,   

round((SysTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)     SystemTimePct,

round((IOWaitTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  IOWaitTimePct, 

VmInDiff as VmIn,

VmOutDiff as VmOut 

From( 

select StartTime,dayName,weeklyNum,                                                                                       

      (UserTime-UserTimeBefore)       as UserTimeDiff,                                                  

      (IdleTime-IdleTimeBefore)       as IdleTimeDiff,                                                  

      (SysTime-SysTimeBefore)         as SysTimeDiff,                                                   

      (IOWaitTime-IOWaitTimeBefore)   as IOWaitTimeDiff,                                                

      (NiceTime-NiceTimeBefore)       as NiceTimeDiff,                                                  

      (VmIn-VmInBefore)               as VmInDiff,                                                      

      (VmOut-VmOutBefore)             as VmOutDiff                                                      

From(                                                                                                   

select StartTime,dayName,weeklyNum,snap_id,                                                                               

      UserTime,lag(UserTime) over(partition by StartTime order by StartTime,snap_id) as UserTimeBefore, 

      IdleTime,lag(IdleTime) over(partition by StartTime order by StartTime,snap_id) as IdleTimeBefore, 

      SysTime,lag(SysTime) over(partition by StartTime order by StartTime,snap_id)   as SysTimeBefore,   

      IOWaitTime,lag(IOWaitTime) over(partition by StartTime order by StartTime,snap_id) as IOWaitTimeBefore, 

      NiceTime,lag(NiceTime) over(partition by StartTime order by StartTime,snap_id) as NiceTimeBefore, 

      VmIn,lag(VmIn) over(partition by StartTime order by StartTime,snap_id) as VmInBefore,   

      VmOut,lag(VmOut) over(partition by StartTime order by StartTime,snap_id) as VmOutBefore 

from(   

SELECT  TO_CHAR (sn.begin_interval_time, 'mmdd') AS StartTime,    

                       TRUNC (SUM (DECODE (oss.stat_name, 'USER_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as UserTime,

                       TRUNC (SUM (DECODE (oss.stat_name, 'BUSY_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as BusyTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'IDLE_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IdleTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'SYS_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as SysTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'NICE_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as NiceTime,

                       TRUNC (SUM (DECODE (oss.stat_name, 'IOWAIT_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IOWaitTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_IN_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmIn,   

                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_OUT_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmOut, 

                       UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) as dayName, 

                       to_number(TO_CHAR (sn.begin_interval_time, 'iw'))+4 AS weeklyNum, 

                                            sn.snap_id, 

                        ROW_NUMBER () OVER (PARTITION BY sn.snap_id order by sn.snap_id) AS RN 

FROM dba_hist_osstat oss, dba_hist_snapshot sn                           

WHERE            oss.dbid = ******                                   

                 AND   oss.dbid = sn.dbid                                

                 AND   oss.instance_number =  sn.instance_number         

                 AND   oss.snap_id = sn.snap_id                          

                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') >= '1555' 

                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') <= '1615'  

                 AND sn.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -2), 'Monday') 

                 AND sn.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, -1), 'Monday') 

                 AND UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 

                 AND   oss.stat_name IN ('USER_TIME','BUSY_TIME','IDLE_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME','VM_IN_BYTES','VM_OUT_BYTES')

                 order by 1            

) where RN=1                           

) where UserTimeBefore is not null     

       or IdleTimeBefore is not null   

       or SysTimeBefore is not null    

       or SysTimeBefore is not null    

       or IOWaitTimeBefore is not null 

       or VmInBefore is not null  

       or VmOutBefore is not null  

       or NiceTimeBefore is not null 

) order by StartTime), 

curMonth as  

(

select weeklyNum,dayname,StartTime, 

round(((UserTimeDiff+SysTimeDiff)/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) BusyTimePct, 

round((UserTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100) UserTimePct, 

round((NiceTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  NiceTimePct, 

round((SysTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)     SystemTimePct,

round((IOWaitTimeDiff/(UserTimeDiff+IdleTimeDiff+SysTimeDiff+IOWaitTimeDiff+NiceTimeDiff))*100)  IOWaitTimePct,

VmInDiff as VmIn, 

VmOutDiff as VmOut 

From( 

select StartTime,weeklyNum,dayname,  

      (UserTime-UserTimeBefore)       as UserTimeDiff,

      (IdleTime-IdleTimeBefore)       as IdleTimeDiff,

      (SysTime-SysTimeBefore)         as SysTimeDiff,

      (IOWaitTime-IOWaitTimeBefore)   as IOWaitTimeDiff,

      (NiceTime-NiceTimeBefore)       as NiceTimeDiff, 

      (VmIn-VmInBefore)               as VmInDiff, 

      (VmOut-VmOutBefore)             as VmOutDiff 

From( 

select StartTime,weeklyNum,dayname,snap_id, 

      UserTime,lag(UserTime) over(partition by StartTime order by StartTime,snap_id) as UserTimeBefore,

      IdleTime,lag(IdleTime) over(partition by StartTime order by StartTime,snap_id) as IdleTimeBefore,

      SysTime,lag(SysTime) over(partition by StartTime order by StartTime,snap_id)   as SysTimeBefore, 

      IOWaitTime,lag(IOWaitTime) over(partition by StartTime order by StartTime,snap_id) as IOWaitTimeBefore,

      NiceTime,lag(NiceTime) over(partition by StartTime order by StartTime,snap_id) as NiceTimeBefore, 

      VmIn,lag(VmIn) over(partition by StartTime order by StartTime,snap_id) as VmInBefore,    

      VmOut,lag(VmOut) over(partition by StartTime order by StartTime,snap_id) as VmOutBefore   

from(  

SELECT  TO_CHAR (sn.begin_interval_time, 'mmdd') AS StartTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'USER_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as UserTime,

                       TRUNC (SUM (DECODE (oss.stat_name, 'BUSY_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as BusyTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'IDLE_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IdleTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'SYS_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as SysTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'NICE_TIME', VALUE, 0)/100)  OVER (PARTITION BY oss.snap_id), 2) as NiceTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'IOWAIT_TIME', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as IOWaitTime, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_IN_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmIn, 

                       TRUNC (SUM (DECODE (oss.stat_name, 'VM_OUT_BYTES', VALUE, 0)/100) OVER (PARTITION BY oss.snap_id), 2) as VmOut,

                       UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) as dayName, 

                       to_number(TO_CHAR (sn.begin_interval_time, 'iw')) AS weeklyNum, 

                                           sn.snap_id, 

                       ROW_NUMBER () OVER (PARTITION BY sn.snap_id order by sn.snap_id) AS RN 

FROM dba_hist_osstat oss, dba_hist_snapshot sn 

WHERE            oss.dbid = ****** 

                 AND   oss.dbid = sn.dbid 

                 AND   oss.instance_number =  sn.instance_number 

                 AND   oss.snap_id = sn.snap_id 

                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') >= '1555' 

                 AND TO_CHAR (sn.begin_interval_time, 'hh24mi') <= '1615' 

                 AND sn.BEGIN_INTERVAL_TIME >= next_day(ADD_MONTHS(sysdate, -1), 'Monday') 

                 AND sn.BEGIN_INTERVAL_TIME < next_day(ADD_MONTHS(sysdate, 0), 'Monday') 

                                 AND UPPER (SUBSTR (TO_CHAR (sn.begin_interval_time, 'Day'), 1, 3)) IN ('MON', 'TUE', 'WED', 'THU', 'FRI') 

                 AND   oss.stat_name IN ('USER_TIME','BUSY_TIME','IDLE_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME','VM_IN_BYTES','VM_OUT_BYTES')

                 order by 1 

) where RN=1  

) where UserTimeBefore is not null 

       or IdleTimeBefore is not null  

       or SysTimeBefore is not null  

       or SysTimeBefore is not null  

       or IOWaitTimeBefore is not null 

       or VmInBefore is not null  

       or VmOutBefore is not null 

       or NiceTimeBefore is not null 

) order by StartTime) 

select to_date(a.starttime,'mmdd') as starttime, b.BusyTimePct as LastMonth,a.BusyTimePct as CurrentMonth,trunc((a.BusyTimePct-b.BusyTimePct)/b.BusyTimePct,7) as "Increment" 

from curMonth a,lastMonth b 

where a.weeklyNum=b.weeklyNum 

and   a.dayname=b.dayname 

order by 1