数据库系统io相关数据采集

最新推荐文章于 2024-07-26 09:40:31 发布

skate

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量2k

点赞数

分类专栏： Oracle Management 文章标签：数据库 io transactions oracle table user

本文链接：https://blog.csdn.net/wyzxg/article/details/6001001

版权

Oracle Management 专栏收录该内容

193 篇文章 2 订阅

订阅专栏

author:skate

time:2010-11-10

数据库系统io相关数据采集

为什么要采集数据库io相关的数据呢？因为现在系统大部分的性能问题大都是因为存储io，所以把系统的io数据采集下来,可以让我们
大概了解数据库io的使用情况，比如系统新增某个模块，数据库的io相关性能指标突变，有助于分析；再比如系统使用很正常，但是
io相关指标数据变化，也可以判断业务的发展情况；从这些io指标数据变化趋势，也可以预知什么时候需要优化或升级等。

功能：统计数据库每秒的事物数,逻辑读,物理读,物理写,磁盘排序比,硬分析比

1. 首先创建表 DBA_DBINFO，TMP_DB_INFO

A. -- Create table DBA_DBINFO

create table DBA_DBINFO
(
TRACE_TIME    DATE,
LOGICREADS    NUMBER(20),
PHYSICREADS   NUMBER(20),
PHYSICWRITES NUMBER(20),
DISKSORTRATE NUMBER(20,6),
HARDPARSERATE NUMBER(20,6),
TRANSACTIONS NUMBER(20,6)
)
tablespace TBS_SKATE;

-- Add comments to the columns
comment on column DBA_DBINFO.TRACE_TIME
is 'db系统跟踪时间，即当前记录时间';
comment on column DBA_DBINFO.LOGICREADS
is '当前时刻数据库逻辑读总数';
comment on column DBA_DBINFO.PHYSICREADS
is '当前时刻数据库物理读总数';
comment on column DBA_DBINFO.PHYSICWRITES
is '当前时刻数据库物理写总数';
comment on column DBA_DBINFO.DISKSORTRATE
is '当前时刻数据库磁盘排序比';
comment on column DBA_DBINFO.HARDPARSERATE
is '当前时刻数据库硬分析比';
comment on column DBA_DBINFO.TRANSACTIONS
is '平均每秒事物数';

---------------------------------------------------------

B. -- Create table TMP_DB_INFO

create table TMP_DB_INFO
(
EVENT_NAME VARCHAR2(100),
DB_VALUES NUMBER(20),
GMT_CREATE DATE
)
tablespace TBS_SKATE;

-- Add comments to the table
comment on table TMP_DB_INFO
is '查询数据库事务的临时表';
-- Add comments to the columns
comment on column TMP_DB_INFO.EVENT_NAME
is '事件名称';
comment on column TMP_DB_INFO.DB_VALUES
is '事务数量';
comment on column TMP_DB_INFO.GMT_CREATE
is '时间点';

2. 创建存储过程，用来采集数据信息（用户要有读取v$sysstat权限）

------------------------------------------------------------------------------------------

create or replace procedure pro_getdb_info
( p_tran_nums out number,
p_elps_time out number)

/*******************************************************
author：skate
time ：2009/09/15
功能：统计数据库每秒的事物数,逻辑读,物理读,物理写,磁盘排序比,硬分析比
说明：可以用oracle定时或os定时，以不同的频率执行
********************************************************/

is
m_count number;
m_old_tran_nums number :=0;
m_old_time date;
m_new_tran_nums number :=0;
m_new_time date;

v_lvalue number(20);
v_prvalue number(20);
v_pwvalue NUMBER (20);
v_sortdiskrate number(20,6);
v_pd_rate number(20,6);

begin
--注意，这里把原来的老数据记录在临时表中，如果临时表还没有初始化
--则需要先初始化临时表

select count(*) into m_count from tmp_db_info;
if m_count = 0 then
    insert into tmp_db_info
      select s.NAME,s.VALUE,sysdate from v$sysstat s
      where s.NAME in
      ('user commits',
      'user rollbacks');
    commit;
    --如果初始化完成，则直接返回
return;
end if;

--正常情况下，先获得上次执行时的时间值
    select gmt_create into m_old_time from tmp_db_info
        where rownum <= 1;
--然后得到上次的事务数
    for c_tmp in (select * from tmp_db_info) loop
       if c_tmp.event_name='user commits' then
          m_old_tran_nums := m_old_tran_nums + c_tmp.db_values;
        elsif c_tmp.event_name='user rollbacks' then
          m_old_tran_nums := m_old_tran_nums + c_tmp.db_values;
        else
          null;
        end if;
    end loop;
--然后，删除临时表
   execute immediate 'truncate table tmp_db_info ';
--在临时表中写入新的值
   insert into tmp_db_info
      select s.NAME,s.VALUE,sysdate from v$sysstat s
      where s.NAME in
      ('user commits',
      'user rollbacks');
     commit;
--得到现在的新值
   select gmt_create into m_new_time from tmp_db_info
        where rownum <= 1;
   for c_tmp in (select * from tmp_db_info) loop
     if c_tmp.event_name='user commits' then
        m_new_tran_nums := m_new_tran_nums + c_tmp.db_values;
      elsif c_tmp.event_name='user rollbacks' then
        m_new_tran_nums := m_new_tran_nums + c_tmp.db_values;
      else
        null;
      end if;
   end loop;
--得到两次调用之间的时间差
   p_elps_time := round((m_new_time - m_old_time)*24*3600,2);
--得到两次调用之间的每秒事务数
   p_tran_nums := round((m_new_tran_nums - m_old_tran_nums)/p_elps_time,2);

----和io相关的信息统计

SELECT T1.LVALUE       "逻辑读",
        T2.PRVALUE      "物理读",
        T3.PWVALUE      "物理写",
        T4.SORTDISKRATE "磁盘排序比",
        T5.PD_RATE      "硬分析比"
   INTO V_LVALUE, V_PRVALUE, V_PWVALUE, V_SORTDISKRATE, V_PD_RATE
   FROM (SELECT 'logic reads', SUM(VALUE) LVALUE
           FROM V$SYSSTAT S
          WHERE S.NAME IN ('consistent gets', 'db block gets')) T1,
        (SELECT 'physic reads', SUM(VALUE) PRVALUE
           FROM V$SYSSTAT S
          WHERE S.NAME LIKE 'physical reads%') T2,
        (SELECT 'physic writes', SUM(VALUE) PWVALUE
           FROM V$SYSSTAT S
          WHERE S.NAME LIKE 'physical writes%'
            AND S.NAME <> 'physical writes non checkpoint') T3,
        (SELECT 'sort(disk) rate', S1.VALUE / TOTALVALUE SORTDISKRATE
           FROM (SELECT SUM(S.VALUE) TOTALVALUE
                   FROM V$SYSSTAT S
                  WHERE S.NAME IN ('sorts (disk)', 'sorts (memory)')) A,
                V$SYSSTAT S1
          WHERE S1.NAME = 'sorts (disk)') T4,
        (SELECT 'sql parse count(hard) rate', S.VALUE / S1.VALUE PD_RATE
           FROM V$SYSSTAT S, V$SYSSTAT S1
          WHERE S.NAME = 'parse count (hard)'
            AND S1.NAME = 'parse count (total)') T5;

---把变量信息插入表中
INSERT INTO DBA_DBINFO
   (TRACE_TIME,
    LOGICREADS,
    PHYSICREADS,
    PHYSICWRITES,
    DISKSORTRATE,
    HARDPARSERATE,
    TRANSACTIONS)
VALUES
   (SYSDATE,
    V_LVALUE,
    V_PRVALUE,
    V_PWVALUE,
    V_SORTDISKRATE,
    V_PD_RATE,
    P_TRAN_NUMS);

end;

-----------------------------------------------------------------------------------

3. 功能已经实现了，剩下的就是要做定时任务了，可以用oracle定时或os定时

用oracle定时，直接在job里创建个定时任务就可以；但有的时候job总是莫名其妙的stop，我更倾向用os的crontab

A。
------------------------------------------------------------
[oracle@host-A ~]$ vi /home/oracle/sh/dba_getdbinfo.sql
connect skate/passwd
set timing on

variable p_tran_nums number;
variable p_elps_time number;

exec pro_getdb_info(p_tran_nums => :p_tran_nums, p_elps_time => :p_elps_time);

exit
------------------------------------------------------------

B。
-------------------------------------------------------------
[oracle@host-A ~]$ vi /home/oracle/sh/dba_getdbinfo.sh
. /home/oracle/.bash_profile
cd /home/oracle/sh
date
sqlplus -s "skate/passwd" @dba_getdbinfo.sql
date

---------------------------------------------------------------

C。
[oracle@host-A ~]$ crontab -l
#get dbinfo
*/5 * * * * sh /home/oracle/sh/dba_getdbinfo.sh >> /home/oracle/sh/dba_getdbinfo.log

---------------------------------------------------------------

这样的话，每5分钟就可以采集一次信息，可以查看表dba_dbinfo，这样可以看到每小时或每天的io情况

SELECT * FROM dba_dbinfo

可以把这些信息放到excel里，做成图表的形式，非常直观！！！

我最近正在考虑如何把采集的io数据自动生成excel图表并email给收件人，其他的都好说，主要如何把txt文件自动生excel成图表麻烦点（偶对VBA不是很熟，正在熟悉中，如果有知道的朋友请留言，谢谢）