为什么要采集数据库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里，做成图表的形式，非常直观！！！
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-5-21 10:06:53 |
赞(
操作
