Oracle查询dbtime,和各个目标的查询脚本

2020-05-19 万国名师 阅读

  标签:last ges cal red over comm number size tran

  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.seconds/60,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, ‘hh24:mi‘) currtime,

  currsnap_id endsnap_id,

  to_char(startup_time, ‘yyyy-mm-dd hh24: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,

标签: