当前位置: 开发笔记 > 数据库 > 正文

查看scnheadroom变化趋势的几种方法-mysql教程

作者：拉扯作乱_991 | 来源：互联网 | 2017-05-12 15:44

查看scnheadroom变化趋势的几种方法scnheadroom问题，本文不做解释。本文为自己的总结，脚本来自于oraclesr技术工程师。转载请注明出处blog.csdn.netmsdnchinaarticledetails38404501第一个方法：查询smon_scn_time表获得。connassysdb

查看scn headroom变化趋势的几种方法 scn headroom问题，本文不做解释。本文为自己的总结，脚本来自于oracle sr技术工程师。转载请注明出处http://blog.csdn.net/msdnchina/article/details/38404501 第一个方法：查询smon_scn_time表获得。conn / as sysdb

查看scn headroom变化趋势的几种方法

scn headroom问题，本文不做解释。

本文为自己的总结，脚本来自于oracle sr技术工程师。

转载请注明出处http://blog.csdn.net/msdnchina/article/details/38404501

第一个方法：查询smon_scn_time表获得。conn / as sysdba
set numwidth 17
set pages 1000
alter session set nls_date_format=&＃39;DD/Mon/YYYY HH24:MI:SS&＃39;;
with t1 as(
select time_dp , 24*60*60*(time_dp - lag(time_dp) over (order by time_dp)) timediff,
scn - lag(scn) over(order by time_dp) scndiff
from smon_scn_time
)
select time_dp , timediff, scndiff,
trunc(scndiff/timediff) rate_per_sec
from t1
order by 1;

第二个方法：查询awr报告的信息：

1. 通过How to extract the historical values of a statistic from AWR Repository (Doc ID 948272.1)
将Script 部分复制到您生成数据库两个实例本地，命名为例如 awr.sql

该脚本为：

set trimspool on
set pages 50000
set lines 132
set tab off
set feedback off

clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 50000 linesize 1000 newpage 1 recsep off;
set trimspool on trimout on;

-- 
-- Request the DB Id and Instance Number, if they are not specified


column instt_num  heading "Inst Num"  format 99999;
column instt_name heading "Instance"  format a12;
column dbb_name   heading "DB Name"   format a12;
column dbbid      heading "DB Id"     format a12 just c;
column host       heading "Host"      format a20;


prompt
prompt
prompt instances IN this workload repository SCHEMA
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT DISTINCT ( CASE
                    WHEN cd.dbid = wr.dbid
                         AND cd.name = wr.db_name
                         AND ci.instance_number = wr.instance_number
                         AND ci.instance_name = wr.instance_name THEN &＃39;* &＃39;
                    ELSE &＃39;  &＃39;
                  END )
                || wr.dbid         dbbid,
                wr.instance_number instt_num,
                wr.db_name         dbb_name,
                wr.instance_name   inst_name,
                wr.host_name       host
FROM   dba_hist_database_instance wr,
       v$database cd,
       v$instance ci;


prompt
prompt USING &&dbid FOR DATABASE id
-- 
--  Set up the binds for dbid and instance_number
variable dbid NUMBER;
BEGIN
    :dbid := &dbid;
END;
/
--  Error reporting
whenever SQLERROR EXIT;
variable max_snap_time CHAR(10);
DECLARE
    CURSOR cidnum IS
      SELECT &＃39;X&＃39;
      FROM   dba_hist_database_instance
      WHERE  dbid = :dbid;
    CURSOR csnapid IS
      SELECT To_char(Max(end_interval_time), &＃39;dd/mm/yyyy&＃39;)
      FROM   dba_hist_snapshot
      WHERE  dbid = :dbid;
    vx CHAR(1);
BEGIN
    -- Check Database Id/Instance Number is a valid pair
    OPEN cidnum;


    FETCH cidnum INTO vx;


    IF cidnum%NOTFOUND THEN
      Raise_application_error(-20200, &＃39;Database/Instance &＃39;
                                      || :dbid
                                      || &＃39;/&＃39;
                                      ||
      &＃39; does not exist in DBA_HIST_DATABASE_INSTANCE&＃39;);
    END IF;


    CLOSE cidnum;


    -- Check Snapshots exist for Database Id/Instance Number
    OPEN csnapid;


    FETCH csnapid INTO :max_snap_time;


    IF csnapid%NOTFOUND THEN
      Raise_application_error(-20200,
      &＃39;No snapshots exist for Database/Instance &＃39;
      ||:dbid
      ||&＃39;/&＃39;);
    END IF;


    CLOSE csnapid;
END;
/


whenever SQLERROR CONTINUE;
-- 
--  Ask how many days of snapshots to display
set termout ON;
column instart_fmt noprint;
column inst_name format a12 heading &＃39;Instance&＃39;;
column db_name format a12 heading &＃39;DB Name&＃39;;
column snap_id format 99999990 heading &＃39;Snap Id&＃39;;
column snapdat format a18 heading &＃39;Snap Started&＃39; just c;
column lvl format 99 heading &＃39;Snap|Level&＃39;;
prompt
prompt
prompt specify the NUMBER OF days OF snapshots TO choose FROM
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt entering the NUMBER OF days (n) will result IN the most recent
prompt (n) days OF snapshots being listed. pressing  without
prompt specifying a NUMBER LISTS ALL completed snapshots.
prompt
prompt
set heading OFF;
column num_days new_value num_days noprint;
SELECT &＃39;Listing &＃39;
       || Decode(Nvl(&＃39;&&num_days&＃39;, 3.14), 0, &＃39;no snapshots&＃39;,
                                          3.14, &＃39;all Completed Snapshots&＃39;,
                                          1,
          &＃39;the last day&＃39;&＃39;s Completed Snapshots&＃39;,
          &＃39;the last &num_days days of Completed Snapshots&＃39;
          ),
       Nvl(&＃39;&&num_days&＃39;, 3.14) num_days
FROM   sys.dual;


set heading ON;
-- 
-- List available snapshots
break ON inst_name ON db_name ON host ON instart_fmt skip 1;
ttitle OFF;
SELECT To_char(s.startup_time, &＃39;dd Mon "at" HH24:mi:ss&＃39;)   instart_fmt,
       di.instance_name                                    inst_name,
       di.db_name                                          db_name,
       s.snap_id                                           snap_id,
       To_char(s.end_interval_time, &＃39;dd Mon YYYY HH24:mi&＃39;) snapdat,
       s.snap_level                                        lvl
FROM   dba_hist_snapshot s,
       dba_hist_database_instance di
WHERE  s.dbid = :dbid
       AND di.dbid = :dbid
       AND di.dbid = s.dbid
       AND di.instance_number = s.instance_number
       AND di.startup_time = s.startup_time
       AND s.end_interval_time >= Decode(&num_days, 0, To_date(&＃39;31-JAN-9999&＃39;,
                                                       &＃39;DD-MON-YYYY&＃39;
                                                       ),
                                                    3.14, s.end_interval_time,
                                                    To_date(:max_snap_time,
                                                    &＃39;dd/mm/yyyy&＃39;)
                                                    - ( &num_days - 1 ))
ORDER  BY db_name,
          instance_name,
          snap_id;


clear break;
ttitle OFF;
-- 
--  Ask for the snapshots Id&＃39;s which are to be compared
prompt
prompt
prompt specify the BEGIN AND END SNAPSHOT ids
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt BEGIN SNAPSHOT id specified: &&begin_snap
prompt
prompt END SNAPSHOT id specified: &&end_snap
prompt
-- 
--  Set up the snapshot-related binds
-- 
variable bid NUMBER;
variable eid NUMBER;
BEGIN
    :bid := &begin_snap;


    :eid := &end_snap;
END;
/


prompt
-- 
--  Ask for Statistics Name Filter
-- 
prompt
prompt
prompt search statistic
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt search BY STATISTICS name. pressing  without
prompt specifying anything show ALL STATISTICS.
set heading OFF;
column stat_search new_value stat_search noprint;
SELECT &＃39;Statistic Name Filter: &＃39;
       || Nvl(&＃39;&&stat_search&＃39;, &＃39;%&＃39;),
       Nvl(&＃39;&&stat_search&＃39;, &＃39;%&＃39;) stat_search
FROM   sys.dual;


set heading ON;
column stat_id heading "Statistic ID" format 9999999999999;
column name heading "Statistic Name" format a64;
column class_name heading "Statistic Class" format a10;
SELECT stat_id,
       ( CASE
           WHEN class = 1 THEN &＃39;USER&＃39;
           WHEN class = 2 THEN &＃39;REDO&＃39;
           WHEN class = 4 THEN &＃39;ENQUEUE&＃39;
           WHEN class = 8 THEN &＃39;CACHE&＃39;
           WHEN class = 16 THEN &＃39;OS&＃39;
           WHEN class = 32 THEN &＃39;RAC&＃39;
           WHEN class = 40 THEN &＃39;RAC-CACHE&＃39;
           WHEN class = 64 THEN &＃39;SQL&＃39;
           WHEN class = 72 THEN &＃39;SQL-CACHE&＃39;
           WHEN class = 128 THEN &＃39;DEBUG&＃39;
           ELSE To_char(class)
         END ) CLASS_NAME,
       name
FROM   v$sysstat
WHERE  Upper(name) LIKE Trim(Upper(&＃39;%&stat_search%&＃39;))
ORDER  BY class,
          name
/


-- 
--  Ask for the statistics
variable stat_filter_id NUMBER
variable stat_filter_name VARCHAR2(64)
prompt
prompt
prompt specify the STATISTICS
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt enter STATISTICS id OR STATISTICS name.
prompt
BEGIN
    SELECT To_number(&＃39;&&stat_input&＃39;)
    INTO   :stat_filter_id
    FROM   dual;
EXCEPTION
    WHEN invalid_number THEN
      :stat_filter_name := &＃39;&stat_input&＃39;;
END;
/


prompt STATISTICS specified : &&stat_input
column end_interval_time heading &＃39;Snap Started&＃39; format a18 just c;
column dbid heading &＃39;DB Id&＃39; format a12 just c;
column instance_number heading &＃39;Inst|Num&＃39; format 99999;
column elapsed heading &＃39;Elapsed&＃39; format 999999;
column stat_value heading &＃39;Stat Value&＃39; format 999999999999
column stat_name heading &＃39;Stat Name&＃39; format a64 just l;
SELECT snap_id,
       To_char(dbid)                                     DBID,
       instance_number,
       elapsed,
       To_char(end_interval_time, &＃39;dd Mon YYYY HH24:mi&＃39;) END_INTERVAL_TIME,
       --stat_name,
       ( CASE
           WHEN stat_value > 0 THEN stat_value
           ELSE 0
         END )                                           STAT_VALUE
FROM   (SELECT snap_id,
               dbid,
               instance_number,
               elapsed,
               end_interval_time,
               stat_name,
               ( stat_value - Lag (stat_value, 1, stat_value)
                                over (
                                  PARTITION BY dbid, instance_number
                                  ORDER BY snap_id) ) AS STAT_VALUE
        FROM   (SELECT snap_id,
                       dbid,
                       instance_number,
                       elapsed,
                       end_interval_time,
                       stat_name,
                       SUM(stat_value) AS STAT_VALUE
                FROM   (SELECT X.snap_id,
                               X.dbid,
                               X.instance_number,
                               Trunc(SN.end_interval_time, &＃39;mi&＃39;)
                               END_INTERVAL_TIME,
                               X.stat_name,
                               Trunc(( Cast(SN.end_interval_time AS DATE) -
                                       Cast(SN.begin_interval_time AS DATE) ) *
                                     86400)                      ELAPSED,
                               ( CASE
                                   WHEN ( X.stat_name = :stat_filter_name
                                           OR X.stat_id = :stat_filter_id ) THEN
                                   X.value
                                   ELSE 0
                                 END )                           AS STAT_VALUE
                        FROM   dba_hist_sysstat X,
                               dba_hist_snapshot SN,
                               (SELECT instance_number,
                                       Min(startup_time) STARTUP_TIME
                                FROM   dba_hist_snapshot
                                WHERE  snap_id BETWEEN :bid AND :eid
                                GROUP  BY instance_number) MS
                        WHERE  X.snap_id = sn.snap_id
                               AND X.dbid = sn.dbid
                               AND x.dbid = :dbid
                               AND x.snap_id BETWEEN :bid AND :eid
                               AND SN.startup_time = MS.startup_time
                               AND SN.instance_number = MS.instance_number
                               AND X.instance_number = sn.instance_number
                               AND ( X.stat_name = :stat_filter_name
                                      OR X.stat_id = :stat_filter_id ))
                GROUP  BY snap_id,
                          dbid,
                          instance_number,
                          elapsed,
                          end_interval_time,
                          stat_name));




undefine dbid
undefine num_days
undefine begin_snap
undefine end_snap
undefine stat_id
undefine stat_search
undefine stat_filter_name
undefine stat_filter_id
undefine stat_input 


---该脚本结束。

2. 在SQLPLUS中运行该脚本，并根据您系统实际情况输入

instances IN this workload repository SCHEMA
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


DB Id Inst Num DB Name INST_NAME Host
------------ -------- ------------ ---------------- --------------------
* 1163866261 1 RBIG5 RBIG5 xxx
m


Enter value for dbid: 1163866261 《=====输入实例ID
USING 1163866261 FOR DATABASE id

specify the NUMBER OF days OF snapshots TO choose FROM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
entering the NUMBER OF days (n) will result IN the most recent
(n) days OF snapshots being listed. pressing without
specifying a NUMBER LISTS ALL completed snapshots.

Enter value for num_days: 2 <===输入AWR采样天数

specify the BEGIN AND END SNAPSHOT ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1605
BEGIN SNAPSHOT id specified: 1605 《===根据对话输入起始snapshot 序号


Enter value for end_snap: 1639
END SNAPSHOT id specified: 1639《===根据对话输入结束snapshot 序号

search statistic
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
search BY STATISTICS name. pressing without
specifying anything show ALL STATISTICS.
Enter value for stat_search: calls to kcmgas 《======输入需要显示的统计项： calls to kcmgas


Statistic Name Filter: calls to kcmgas


Statistic ID Statistic Statistic Name
-------------- ---------- ----------------------------------------------------------------
4072914524 DEBUG calls to kcmgas

specify the STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
enter STATISTICS id OR STATISTICS name.

Enter value for stat_input: 4072914524 《======输入统计项返回的ID

最后，将返回一个列表，例如

Inst
Snap Id DB Id Num Elapsed Snap Started Stat Value
--------- ------------ ------ ------- ------------------ -------------
1605 1163866261 1 3600 08 Sep 2013 00:00 0
1606 1163866261 1 3601 08 Sep 2013 01:00 170
1607 1163866261 1 3600 08 Sep 2013 02:00 164
。。。
1626 1163866261 1 3600 08 Sep 2013 21:00 155
1627 1163866261 1 3600 08 Sep 2013 22:00 165
1628 1163866261 1 3600 08 Sep 2013 23:00 2065《===如果有类似跳变发生,则表示数据库内部交易产生的剧烈变化，非DBLINK造成。
。。。
1636 1163866261 1 3600 09 Sep 2013 07:00 145
1637 1163866261 1 3601 09 Sep 2013 08:00 174
1638 1163866261 1 3600 09 Sep 2013 09:00 156
1639 1163866261 1 3600 09 Sep 2013 10:00 142

请提供您的输出结果来作为SCN 非外部数据库DBLINK造成跳变的调查结果。

第三个方法：查询v$archived_log视图（前提是数据库开启归档模式）

set numwidth 17 
set pages 1000 
alter session set nls_date_format=&＃39;DD/Mon/YYYY HH24:MI:SS&＃39;; 
SELECT tim, gscn, 
round(rate), 
round((chk16kscn - gscn)/24/3600/16/1024,1) "Headroom" 
FROM 
( 
select tim, gscn, rate, 
(( 
((to_number(to_char(tim,&＃39;YYYY&＃39;))-1988)*12*31*24*60*60) + 
((to_number(to_char(tim,&＃39;MM&＃39;))-1)*31*24*60*60) + 
(((to_number(to_char(tim,&＃39;DD&＃39;))-1))*24*60*60) + 
(to_number(to_char(tim,&＃39;HH24&＃39;))*60*60) + 
(to_number(to_char(tim,&＃39;MI&＃39;))*60) + 
(to_number(to_char(tim,&＃39;SS&＃39;))) 
) * (16*1024)) chk16kscn 
from 
( 
select FIRST_TIME tim , FIRST_CHANGE# gscn, 
((NEXT_CHANGE#-FIRST_CHANGE#)/ 
((NEXT_TIME-FIRST_TIME)*24*60*60)) rate 
from v$archived_log 
where (next_time > first_time) 
) 
) 
order by 1,2 
;

sql
hive

推荐阅读

数据库
SQL技巧：批量删除用户自定义表和存储过程

当面临数据库清理任务时，若无删除或重建数据库的权限，可以通过编写SQL脚本来实现批量删除用户自定义的数据表和存储过程。本文将详细介绍如何构造这样的SQL脚本。 ... [详细]

蜡笔小新 2024-12-17 13:24:06
database
深入理解SQL Server中的聚集与非聚集索引

本文探讨了SQL Server数据库中两种主要的索引类型——聚集索引和非聚集索引，通过对比分析它们的特点及应用场景，旨在帮助读者更好地理解和利用这两种索引以优化查询性能。 ... [详细]

蜡笔小新 2024-12-16 20:18:16
oracle
迎接云数据库新时代：程序员如何应对变革？

在数据无处不在的时代，数据库成为了管理和处理数据的核心工具。从早期的信息记录方式到现代的云数据库，数据库技术经历了巨大的变革。本文将探讨云数据库的特点及其对程序员的影响。 ... [详细]

蜡笔小新 2024-12-17 14:42:46
oracle
在DELL Inspiron 14R上部署CentOS X64 6.4的详细步骤

本文详细记录了在DELL Inspiron 14R笔记本电脑上安装CentOS X64 6.4操作系统的过程，包括遇到的问题及解决方法。 ... [详细]

蜡笔小新 2024-12-15 18:10:54
oracle
关于考研与就业的选择思考

探讨了一个关于是否应该继续深造或直接步入职场的讨论帖，虽然时间久远，但其内容依然具有参考价值。 ... [详细]

蜡笔小新 2024-12-15 17:41:30
json
深入解析多种端口扫描技术

本文探讨了在渗透测试中信息收集阶段使用的几种端口扫描技术，包括nmap、masscan、socket、telnet及nc等工具的应用与比较。 ... [详细]

蜡笔小新 2024-12-15 13:14:17
数据库
深入理解ASP.NET缓存机制

本文详细介绍了ASP.NET缓存的基本概念和使用方法，包括输出缓存、数据缓存及其高级特性，如缓存依赖、自定义缓存和缓存配置文件等。通过合理利用这些缓存技术，可以显著提升Web应用程序的性能。 ... [详细]

蜡笔小新 2024-12-14 20:37:22
json
深入探讨ASP.NET中的OAuth、JWT与OpenID Connect

本文作为前文关于OAuth2.0和使用.NET实现OAuth身份验证的补充，详细阐述了OAuth与JWT及OpenID Connect之间的关系和差异，旨在提供更全面的理解。 ... [详细]

蜡笔小新 2024-12-14 18:34:43
json
回顾这些年，分享我的人生与创业历程

在这段时间里，虽然与朋友们共同创立的企业没有取得预期的成果，但我却意外地收获了美满的爱情，并且迎来了一个可爱的小生命。对于那些对创业经历感兴趣的朋友，接下来的故事将会陆续分享，敬请期待。 ... [详细]

蜡笔小新 2024-12-14 13:42:37
json
Maven项目中打包JAR与LIB的实践指南

本文详细介绍了如何在Maven项目中配置POM文件以实现JAR和LIB的打包，包括指定主类、跳过单元测试等关键步骤。 ... [详细]

蜡笔小新 2024-12-14 10:43:53
json
解决AppCenter iOS构建失败问题：CopyPlistFile命令出错

本文探讨了使用React Native框架开发的应用，在通过AppCenter构建iOS版本时遇到的‘CopyPlistFile’命令失败的问题，并提供了详细的解决方案。 ... [详细]

蜡笔小新 2024-12-14 10:40:06
json
Direct3D API 获取纹理尺寸信息的方法

本文介绍了如何使用Direct3D API中的D3DSURFACE_DESC结构来获取纹理的具体尺寸信息，包括宽度和高度。通过调用GetLevelDesc方法，可以轻松获取这些关键参数。 ... [详细]

蜡笔小新 2024-12-14 09:52:53
json
探索地图应用的新方式

本文介绍了一种新的地图使用方法，通过实例展示如何更好地利用地图服务来提升用户体验。 ... [详细]

蜡笔小新 2024-12-13 19:31:08
数据库
Linux 文件系统中的特殊权限详解：SUID、SGID 和 Sticky Bit

在 Linux 系统中，除了基本的读取、写入和执行权限外，还存在三种特殊权限：Set User ID (SUID)、Set Group ID (SGID) 和 Sticky Bit。这些特殊权限用于增强系统的安全性和功能性。 ... [详细]

蜡笔小新 2024-12-13 14:55:42
mariadb
Linux虚拟机中MySQL安装指南

本文详细介绍了如何在Linux虚拟机上安装MySQL，包括解决常见问题的方法和步骤。 ... [详细]

蜡笔小新 2024-12-12 19:19:20

拉扯作乱_991

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章