OS level statistic and TPS

Below are SQL used to give report like Linux Sar.
========================================

DBA_HIST_OSSTAT
  select BEGIN_INTERVAL_TIME, os.* from
  (select
  SNAP_ID,
   cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
  round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
  round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
  round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
  from
  (SELECT SNAP_ID,
  sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
  sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
  sum(decode(stat_name,'USER_TIME',value,0))   user1,
  sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
  sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
  sum(decode(stat_name,'NICE_TIME',value,0))   nice1
  FROM DBA_HIST_OSSTAT group by SNAP_ID))os , DBA_HIST_snapshot sn
  where os.SNAP_ID=sn.SNAP_ID
order by 1;


col BEGIN_INTERVAL_TIME for a30
set lin 1000 pages 1000
select count(1), to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice from
(
select
SNAP_ID,
 cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
 round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
 round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
 round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
from
(SELECT SNAP_ID,
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
sum(decode(stat_name,'USER_TIME',value,0))   user1,
sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
sum(decode(stat_name,'NICE_TIME',value,0))   nice1
FROM   DBA_HIST_OSSTAT group by SNAP_ID))os
     , DBA_HIST_snapshot sn
     , dba_hist_active_sess_history ash
where os.SNAP_ID=sn.SNAP_ID
and sn.SNAP_ID=ash.SNAP_ID
and ash.snap_id=os.snap_id
group by to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice
order by 1;


col BEGIN_INTERVAL_TIME for a22
col idle for 999
col sys for 999
col user1 for 999
col nice for 999
set lin 1000 pages 1000
col db_name for a22
col cpus1 for 9999
select (select instance_name||'@'||substr(host_name,-(length(host_name))) from v$instance)db_name ,cpus1,sess,date1, idle,sys,user1,nice,sql.cnt ,sql.CPU,sql.ELAPSED,sql.iowait,sql.exec
from
(
select count(1)sess,os.snap_ID ,cpus1, to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice
 from
(
select
SNAP_ID,
 cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
 round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
 round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
 round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
from
(SELECT SNAP_ID,
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
sum(decode(stat_name,'USER_TIME',value,0))   user1,
sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
sum(decode(stat_name,'NICE_TIME',value,0))   nice1
FROM   DBA_HIST_OSSTAT group by SNAP_ID))os
     , DBA_HIST_snapshot sn
     , (select * from dba_hist_active_sess_history where user_id in (select user_id from dba_users where username in ('SYS','SYSTEMS'))) ash  
where os.SNAP_ID=sn.SNAP_ID
and sn.SNAP_ID=ash.SNAP_ID
and ash.snap_id=os.snap_id
group by os.SNAP_ID,cpus1,to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice
)aa, (select snap_id,count(1)cnt,round(sum(CPU_TIME_TOTAL/1000000),0)CPU,round(sum(ELAPSED_TIME_TOTAL/1000000),0)ELAPSED
,round(sum(IOWAIT_TOTAL/1000000),0)IOWAIT,round(sum(EXECUTIONS_TOTAL),0)exec from DBA_HIST_SQLSTAT group by snap_id) sql
where sql.snap_id=aa.snap_id order by 3;


---@dsession
set lin 180
select
   rpad(c.name||':',11)||rpad(' current logons='||
   (to_number(b.sessions_current)),20)||'cumulative logons='||
   rpad(substr(a.value,1,10),10)||'highwater mark= '||
   b.sessions_highwater ||'   avg logon per_day= ' ||round(to_number(rpad(substr(a.value,1,10),10))/(sysdate-d.STARTUP_TIME))
   ||' Startup_date= '||d.STARTUP_TIME Information
from
   v$sysstat a,
   v$license b,
   v$database c,
   v$instance d
where
   a.name = 'logons cumulative'
/
set lin 180

WITH hist_snaps

     AS (SELECT instance_number,

                snap_id,

                round(begin_interval_time,'MI') datetime,

                  (  begin_interval_time

                   + 0

                   - LAG (

                        begin_interval_time + 0)

                     OVER (PARTITION BY dbid, instance_number

                           ORDER BY snap_id))

                * 86400      -- seconds per day

                   diff_time -- calculate difference in time from 

                             -- previous snapshot

                   FROM dba_hist_snapshot,

     hist_stats

     AS (SELECT dbid,

                instance_number,

                snap_id,

                stat_name,

                  VALUE

                - LAG (

                     VALUE)

                  OVER (PARTITION BY dbid,instance_number,stat_name

                        ORDER BY snap_id)

                  delta_value -- difference in value from previous 

                              -- snapshot for each stat_name

           FROM dba_hist_sysstat

          WHERE stat_name IN ('user commits', 'user rollbacks'))

  SELECT datetime,

         ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"

     FROM hist_snaps sn, hist_stats st

   WHERE     st.instance_number = sn.instance_number

         AND st.snap_id = sn.snap_id

         AND diff_time IS NOT NULL

GROUP BY datetime

ORDER BY 1 desc;

TPS Related SQL:
=====================================

set pages 9999; 

column c1 heading "start|time" format a20; 

column c2 heading "end|time" format a20; 

column c3 heading "total|undo|blocks|used" format 9,999,999; 

column c4 heading "total|number of|transactions|executed" format 999,999; 

column c5 heading "longest|query|(sec)" format 999,999; 

column c6 heading "highest|concurrent|transaction|count" format 9,999; 

break on report 

compute sum of c3 on report 

compute sum of c4 on report 

set linesize 120 

select 

to_char(begin_time,'dd-mon-yy hh:mi pm') c1, 

to_char(end_time ,'dd-mon-yy hh:mi pm') c2, 

sum(undoblks) c3, 

sum(txncount) c4, 

max(maxquerylen) c5, 

max(maxconcurrency) c6 

from v$undostat 

group by to_char(begin_time,'dd-mon-yy hh:mi pm'), to_char(end_time ,'dd-mon-yy hh:mi pm') 

order by 1 asc; 

select min(begin_time), max(end_time),

sum(case metric_name when 'User Commits Per Sec' then average end) User_Commits_Per_Sec,

sum(case metric_name when 'User Rollbacks Per Sec' then average end) User_Rollbacks_Per_Sec,

sum(case metric_name when 'User Transaction Per Sec' then average end) User_Transactions_Per_Sec,

snap_id

from dba_hist_sysmetric_summary

where trunc(begin_time) > sysdate-7

group by snap_id

order by snap_id;