怎么定位那些SQL产生了很多的redo日志ITeye - 众发娱乐

怎么定位那些SQL产生了很多的redo日志ITeye

2019-01-10 11:07:57 | 作者: 新之 | 标签: | 浏览: 1732

<pre>------------------------------------------------------------------------------------------------</pre> <pre>REM Author: Riyaj Shamsudeen @OraInternals, LLC</pre> <pre>REM         www.orainternals.com</pre> <pre>REM</pre> <pre>REM Functionality: This script is to print redo size rates in a RAC claster</pre> <pre>REM **************</pre> <pre>REM</pre> <pre>REM Source  : AWR tables</pre> <pre>REM</pre> <pre>REM Exectution type: Execute from sqlplus or any other tool.</pre> <pre>REM</pre> <pre>REM Parameters: No parameters. Uses Last snapshot and the one prior snap</pre> <pre>REM No implied or explicit warranty</pre> <pre>REM</pre> <pre>REM Please send me an email to rshamsud@orainternals.com, if you enhance this script :-)</pre> <pre>REM  This is a open Source code and it is free to use and modify.</pre> <pre>REM Version 1.20</pre> <pre>REM</pre> <pre>------------------------------------------------------------------------------------------------</pre> <pre> </pre> <pre>set colsep |</pre> <pre>set lines 220</pre> <pre>alter session set nls_date_format=YYYY-MM-DD HH24:MI;</pre> <pre>set pagesize 10000</pre> <pre>with redo_data as (</pre> <pre>SELECT instance_number,</pre> <pre>       to_date(to_char(redo_date,DD-MON-YY-HH24:MI), DD-MON-YY-HH24:MI) redo_dt,</pre> <pre>       trunc(redo_size/(1024 * 1024),2) redo_size_mb</pre> <pre> FROM  (</pre> <pre>  SELECT dbid, instance_number, redo_date, redo_size , startup_time  FROM  (</pre> <pre>    SELECT  sysst.dbid,sysst.instance_number, begin_interval_time redo_date, startup_time,</pre> <pre>  VALUE -</pre> <pre>    lag (VALUE) OVER</pre> <pre>    ( PARTITION BY  sysst.dbid, sysst.instance_number, startup_time</pre> <pre>      ORDER BY begin_interval_time ,sysst.instance_number</pre> <pre>     ) redo_size</pre> <pre>  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps</pre> <pre>WHERE sysst.stat_id =</pre> <pre>       ( SELECT stat_id FROM sys.wrh$_stat_name WHERE  stat_name=redo size )</pre> <pre>  AND snaps.snap_id = sysst.snap_id</pre> <pre>  AND snaps.dbid =sysst.dbid</pre> <pre>  AND sysst.instance_number  = snaps.instance_number</pre> <pre>  AND snaps.begin_interval_time sysdate-30</pre> <pre>   ORDER BY snaps.snap_id )</pre> <pre>  )</pre> <pre>)</pre> <pre>select  instance_number,  redo_dt, redo_size_mb,</pre> <pre>    sum (redo_size_mb) over (partition by  trunc(redo_dt)) total_daily,</pre> <pre>    trunc(sum (redo_size_mb) over (partition by  trunc(redo_dt))/24,2) hourly_rate</pre> <pre>   from redo_Data</pre> <pre>order by redo_dt, instance_number</pre> <pre>/</pre>
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表众发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2
  • 3

    MySQL 1064 过错ITeye

    关键字,单引号,呈现
  • 4

    用批处理文件履行SQL脚本ITeye

    履行,文件,批处理
  • 5
  • 6
  • 7

    oracle搜集ITeye

    假如,查询,成果
  • 8

    db2move 指令无法导出表ITeye

    导出,数据,字符
  • 9
  • 10

    Memcached的定位ITeye

    定位,数据,一个