Oracle query dbtime, and query scripts for each indicator
Set linesize 1000
Set pagesize 1000
Col snap_date for a10
Col "TIME" for A6
Col "elapse (min)" for A6
Col redo for 9999999999
Col "DB time (min)" for 99999.99
Select s.snap_date
Decode (s.redosize, null,'--shutdown or end--', s.currtime) "TIME"
To_char (round (s.secondsplink 60 min 2)) "elapse (min)"
Round (t.db_time / 1000000 / 60,2) "DB time (min)"
S.redosize redo
Round (s.redosize / s.seconds, 2) "redo/s"
S.logicalreads logical
Round (s.logicalreads / s.seconds, 2) "logical/s"
Physicalreads physical
Round (s.physicalreads / s.seconds, 2) "phy/s"
S.executes execs
Round (s.executes / s.seconds, 2) "execs/s"
S.parse
Round (s.parse / s.seconds, 2) "parse/s"
S.hardparse
Round (s.hardparse / s.seconds, 2) "hardparse/s"
S.transactions trans
Round (s.transactions / s.seconds, 2) "trans/s"
From (select curr_redo-last_redo redosize
Curr_logicalreads-last_logicalreads logicalreads
Curr_physicalreads-last_physicalreads physicalreads
Curr_executes-last_executes executes
Curr_parse-last_parse parse
Curr_hardparse-last_hardparse hardparse
Curr_transactions-last_transactions transactions
Round ((currtime + 0)-(lasttime + 0)) 3600 24,0) seconds
To_char (currtime, 'yy/mm/dd') snap_date
To_char (currtime, 'hh34:mi') currtime
Currsnap_id endsnap_id
To_char (startup_time, 'yyyy-mm-dd hh34:mi:ss') startup_time
From (select a.redo last_redo
A.logicalreads last_logicalreads
A.physicalreads last_physicalreads
A.executes last_executes
A.parse last_parse
A.hardparse last_hardparse
A.transactions last_transactions
Lead (a.redo, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_redo
Lead (a.logicalreads, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_logicalreads
Lead (a.physicalreads, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_physicalreads
Lead (a.executes, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_executes
Lead (a.parse, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_parse
Lead (a.hardparse, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_hardparse
Lead (a.transactions, 1, null) over (partition by b.startup_time order by b.end_interval_time) curr_transactions
B.end_interval_time lasttime
Lead (b.end_interval_time, 1, null) over (partition by b.startup_time order by b.end_interval_time) currtime
Lead (b.snap_id, 1, null) over (partition by b.startup_time order by b.end_interval_time) currsnap_id
B.startup_time
From (select snap_id
Dbid
Instance_number
Sum (decode (stat_name, 'redo size', value, 0)) redo
Sum (decode (stat_name)
'session logical reads'
Value
0) logicalreads
Sum (decode (stat_name)
'physical reads'
Value
0) physicalreads
Sum (decode (stat_name, 'execute count', value, 0)) executes
Sum (decode (stat_name)
'parse count (total)'
Value
0) parse
Sum (decode (stat_name)
'parse count (hard)'
Value
0) hardparse
Sum (decode (stat_name)
'user rollbacks'
Value
'user commits'
Value
0) transactions
From dba_hist_sysstat
Where stat_name in
('redo size'
'session logical reads'
'physical reads'
'execute count'
'user rollbacks'
'user commits'
'parse count (hard)'
'parse count (total)')
Group by snap_id, dbid, instance_number) a
Dba_hist_snapshot b
Where a.snap_id = b.snap_id
And trunc (b.begin_interval_time) > = sysdate-7
And a.instanceproof number = (select instance_number from v$instance)
And a.dbid = b.dbid
And a.instance_number = b.instance_number
And a.dbid = (select dbid from v$database)
Order by end_interval_time)) s
(select lead (a.value, 1, null) over (partition by b.startup_time order by b.end_interval_time)-a.value db_time
Lead (b.snap_id, 1, null) over (partition by b.startup_time order by b.end_interval_time) endsnap_id
From dba_hist_sys_time_model a, dba_hist_snapshot b
Where a.snap_id = b.snap_id
And trunc (b.begin_interval_time) > = sysdate-7
And a.dbid = b.dbid
And a.instance_number = b.instance_number
And a.instanceproof number = (select instance_number from v$instance)
And a.stat_name ='DB time'
And a.dbid = (select dbid from v$database)) t
Where s.endsnap_id = t.endsnap_id
Order by s.snap_date,time
