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

悠悠 2022-07-16 14:50 142阅读 0赞

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不是很熟，正在熟悉中，如果有知道的朋友请留言，谢谢）