Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.