--============================================================================
通过几个简单的性能参数(DB CPU | DB time | session logical reads | physical read IO requests | physical write IO requests)统计操作的性能指标
--获取当前会话的编号 v$mystat
SELECT
SID
FROM
v$mystat
WHERE
ROWNUM
<=
1
;
--统计某个会话的CPU消耗 v$sess_time_model
SELECT
stat_name,
VALUE
FROM
v$sess_time_model
WHERE
SID
=
159
AND
stat_name
IN
(
'DB time'
,
'DB CPU'
);
--统计IO性能消耗 物理IO|逻辑IO v$sesstat
SELECT
NAME
,
VALUE
FROM
v$sesstat a,v$statname b
WHERE
a.STATISTIC#=b.STATISTIC#
AND
SID
=
138
AND
NAME
IN
(
'session logical reads'
,
'physical read IO requests'
,
'physical write IO requests'
);
--============================================================================
--性能分析小脚本V1.0
CREATE
TABLE
midinfo(
NAME
VARCHAR2
(
50
),
VALUE
NUMBER
(
12
));
declare
tempname
varchar2
(
50
);
tempvalue
number
(
12
);
tempsum
number
(
3
);
begin
SELECT
COUNT
(*)
INTO
tempsum
FROM
midinfo;
COMMIT
;
IF
(tempsum>
0
)
THEN
dbms_output.put_line(
'即将第二次信息采集并计算输出'
);
--取新值,计算
FOR
cur1
IN
(
SELECT
stat_name,
VALUE
FROM
v$sess_time_model
WHERE
SID
=
142
AND
stat_name
IN
(
'DB time'
,
'DB CPU'
))
LOOP
FOR
cur2
IN
(
SELECT
name
,
VALUE
from
midinfo
WHERE
NAME
=cur1.stat_name)
LOOP
dbms_output.put_line(cur2.name||
' : '
||(cur1.value-cur2.value));
END
LOOP
;
END
LOOP
;
FOR
cur1
IN
(
select
name
,
value
from
v$sesstat a,v$statname b
where
a.STATISTIC#=b.STATISTIC#
and
sid
=
142
and
name
in
(
'session logical reads'
,
'physical read IO requests'
,
'physical write IO requests'
))
LOOP
FOR
cur2
IN
(
SELECT
name
,
VALUE
from
midinfo
WHERE
NAME
=cur1.name)
LOOP
dbms_output.put_line(cur2.name||
' : '
||(cur1.value-cur2.value));
END
LOOP
;
END
LOOP
;
--清空表数据
delete
midinfo;
COMMIT
;
ELSE
--插入第一次统计数据
dbms_output.put_line(
'即将进行第一次信息采集'
);
insert
into
midinfo
select
stat_name,
value
from
V$SESS_TIME_MODEL
where
sid
=
142
and
stat_name
in
(
'DB time'
,
'DB CPU'
);
insert
into
midinfo
select
name
,
value
from
v$sesstat a,v$statname b
where
a.STATISTIC#=b.STATISTIC#
and
sid
=
142
and
name
in
(
'session logical reads'
,
'physical read IO requests'
,
'physical write IO requests'
);
COMMIT
;
dbms_output.put_line(
'信息采集完毕,再次运行将输出性能具体情况'
);
END
IF
;
END
;
/
--============================================================================
create
or
replace
PROCEDURE
calTime
IS
tempname
varchar2
(
50
);
tempvalue
number
(
12
);
tempsum
number
(
3
);
begin
SELECT
COUNT
(*)
INTO
tempsum
FROM
midinfo;
COMMIT
;
IF
(tempsum>
0
)
THEN
dbms_output.put_line(
'即将第二次信息采集并计算输出'
);
--取新值,计算
FOR
cur1
IN
(
SELECT
stat_name,
VALUE
FROM
v$sess_time_model
WHERE
SID
=
142
AND
stat_name
IN
(
'DB time'
,
'DB CPU'
))
LOOP
FOR
cur2
IN
(
SELECT
name
,
VALUE
from
midinfo
WHERE
NAME
=cur1.stat_name)
LOOP
dbms_output.put_line(cur2.name||
' : '
||(cur1.value-cur2.value));
END
LOOP
;
END
LOOP
;
FOR
cur1
IN
(
select
name
,
value
from
v$sesstat a,v$statname b
where
a.STATISTIC#=b.STATISTIC#
and
sid
=
142
and
name
in
(
'session logical reads'
,
'physical read IO requests'
,
'physical write IO requests'
))
LOOP
FOR
cur2
IN
(
SELECT
name
,
VALUE
from
midinfo
WHERE
NAME
=cur1.name)
LOOP
dbms_output.put_line(cur2.name||
' : '
||(cur1.value-cur2.value));
END
LOOP
;
END
LOOP
;
--清空表数据
delete
midinfo;
COMMIT
;
ELSE
--插入第一次统计数据
dbms_output.put_line(
'即将进行第一次信息采集'
);
insert
into
midinfo
select
stat_name,
value
from
V$SESS_TIME_MODEL
where
sid
=
142
and
stat_name
in
(
'DB time'
,
'DB CPU'
);
insert
into
midinfo
select
name
,
value
from
v$sesstat a,v$statname b
where
a.STATISTIC#=b.STATISTIC#
and
sid
=
142
and
name
in
(
'session logical reads'
,
'physical read IO requests'
,
'physical write IO requests'
);
COMMIT
;
dbms_output.put_line(
'信息采集完毕,再次运行将输出性能具体情况'
);
END
IF
;
END
calTime;