Can You Vacuum Up Leaves And Acorns

Hard vs. Soft Parse Parse Parse vs. Non

The sentence I copied below allows a historical report of the general activity or workload of an Oracle database. As the query gets information from the AWR history the amount available will depend on the retention defined in the repository (default is 7 days but I always advisable to change to 30 days). The information provided is what is shown in a report AWR RENTABILIDAD section, which is the principal of the report. Those who have analyzed performance reports looking awr (awrrpt.sql running the script or using a graphical tool like TOAD), know that the section on "Load Profile" along with the 5 events tops are the first "snapshot" of "health" general of the base. But that snapshot does not do much if not known well in advance of activity and type of load or if you are not a large well-valued metric. To

analysis Cash must be removed awr and compare various reports to differentiate. In those cases, I prefer to have all the available history of patallazo to more easily compare and see if some metric is in non-traditional values, to export the query output to a spreadsheet and make a historical figure to make a report.

Before running the query is important to note that the values \u200b\u200bare in units per second (first column of the load profile of awr), operating in 10g or more bases.




with intervals as
(select snap,
extract (second from int) +
extract (minute from int) * 60 + extract(hour from int)*60*60 int_sec
from
(select end_interval_time snap,

           end_interval_time-lead(end_interval_time)  
   over (partition by startup_time order by snap_id desc) int 
    from dba_hist_snapshot)) 
 select to_char(snap_time,'YYYY/MM/DD HH24') snap_time, 
        round((redo_size-lead(redo_size)  
   over (partition by startup_time order by snap_time desc))/ 
        int.int_sec,2) "Redo Size", 
        round((logical_reads-lead(logical_reads)  
   over (partition by startup_time order by snap_time desc))/ 
        int.int_sec,2) "Logical Reads", 
        round((db_block_changes-lead(db_block_changes)   
   over (partition by startup_time order by snap_time desc))/ 
        int.int_sec,2) "Block Changes", 
        round((physical_reads-lead(physical_reads)  
   over (partition by startup_time order by snap_time desc))/ 
        int.int_sec,2) "Physical Reads", 
        round((physical_writes-lead(physical_writes)  
   over (partition by startup_time order by snap_time desc))/ 
        int.int_sec,2) "Physical Writes", 

round((user_calls-lead(user_calls) over (partition by startup_time order by snap_time desc))/ int.int_sec,2) "User Calls", round((parses-lead(parses)
over (partition by startup_time order by snap_time desc))/
int.int_sec,2) "Parses",
round((parses_hard-lead(parses_hard)

over (partition by startup_time order by snap_time desc))/ int.int_sec,2) "Parses Hard",

round((sorts-lead(sorts)
over (partition by startup_time order by snap_time desc))/
int.int_sec,2) "Sorts",
round((logons-lead(logons)
over (partition by startup_time order by snap_time desc))/
int.int_sec,2) "Logons",
round((executes-lead(executes)
over (partition by startup_time order by snap_time desc))/
int.int_sec,2) "Exectutes",
round((user_rollbacks-lead(user_rollbacks) over (partition by startup_time order by snap_time desc)+ user_commits-lead(user_commits) over (partition by startup_time order by snap_time desc))/
int.int_sec,2) "Transactions"
from
(select s.end_interval_time snap_time,
s.startup_time startup_time, max(decode(ss.stat_name,'redo size',value,null)) redo_size, max(decode(ss.stat_name,'user rollbacks',value,null)) user_rollbacks, max(decode(ss.stat_name,'user commits',value,null)) user_commits,
max(decode(ss.stat_name,'session logical reads',value,null)) logical_reads,
max(decode(ss.stat_name,'db block changes',value,null)) db_block_changes,
max(decode(ss.stat_name,'physical reads',value,null)) physical_reads,
max(decode(ss.stat_name,'physical writes',value,null)) physical_writes, max(decode(ss.stat_name,'user calls',value,null)) user_calls, max(decode(ss.stat_name,'parse count (total)',value,null)) parses, max(decode(ss.stat_name,'parse count (hard)',value,null)) parses_hard,
max(decode(ss.stat_name,'sorts (memory)',value,null)) sorts,
max(decode(ss.stat_name,'logons cumulative',value,null)) logons,
max(decode(ss.stat_name,'execute count',value,null)) executes
from dba_hist_sysstat ss, dba_hist_snapshot s where s.snap_id = ss.snap_id and ss.stat_name in ('user rollbacks','user commits','session logical reads',
'db block changes','physical reads','physical writes','user calls',
'parse count (total)','parse count (hard)','sorts (memory)','logons cumulative',
'execute count','redo size')
group by s.end_interval_time,s.startup_time) t, intervals int where t.snap_time = Int.snap snap_time order by desc