bn/qbU0Connected.
&cj.`.VJ| y~0SQL> ITPUB个人空间d2W Ys:|3N
SQL>
.u G2u-V D0SQL> insert into ttt values('1','2','3','4','5');
,M`v4K4RQy0 ---------- ---------- ---------- ---------------- ITPUB个人空间9@&?;h.re&hV2`
10 17 22579 ACTIVE
Transactions: | 916.9 |
user commits | 827,280 | 916.90 | 1.00 |
SQL> select instance_number,
[RZ}8I)V7tg!J#MD0 2 metric_unit, ITPUB个人空间"e Q3O^7B$~.X
3 trunc(begin_time) time,
ds t^u,`/X)Q0 4 round(avg(average), 2) average ITPUB个人空间S#aX |/{|$I
5 from DBA_HIST_SYSMETRIC_SUMMARY
:A5f'T&L&e4A1M RXV0 6 where metric_unit = 'Transactions Per Second'
6A+@!mp,^e;~~5p0 7 and begin_time >=
yJ D1DZS0 8 to_date('2013-01-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ITPUB个人空间 ozS@pe jvk
9 and begin_time < to_date('2013-01-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ITPUB个人空间ypa4wfXX.^7]
10 group by instance_number, metric_unit, trunc(begin_time)
)~%]So.M0 11 order by instance_number;
@ dN;|9q#ie5l/eV0--------------- ------------------------------ ------------ ----------
9{x3O{'no0c(p H0 1 Transactions Per Second 21-JAN-13 .17
2 metric_unit,
-E/o4W:JW3oNw7y8K0 3 trunc(begin_time) time, ITPUB个人空间7awsJ,ogGR~
4 avg(average)*60*60*24 "Transactions Per Day" ITPUB个人空间\)lQ3L`2De p
5 from DBA_HIST_SYSMETRIC_SUMMARY ITPUB个人空间`!B\ck;U
6 where metric_unit = 'Transactions Per Second' ITPUB个人空间 C|"e@KR#@
7 and begin_time >=
.h}8I$wO{e9rw0 8 to_date('2013-01-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ITPUB个人空间XH1_~UCEb
9 and begin_time < to_date('2013-01-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
f:w5^%^ w0 10 group by instance_number, metric_unit, trunc(begin_time)
)fd{|'d4t_q'C0 11 order by instance_number;
;R+XK3]_ `0--------------- ------------------------------ ------------ -------------------- ITPUB个人空间+i U0RY.KK)q~3]
1 Transactions Per Second 21-JAN-13 14727.5308
DeltaCommits + DeltaRollbacks where:
-
DeltaCommits: difference of 'select value from v$sysstat where name='user commits'' between sample end and start
-
DeltaRollbacks: difference of 'select value from v$sysstat where name='user rollbacks'' between sample end and start
在2个时间段分别执行上面语句,把2个结果相减即可得出这段时间内的事务数。
根据这个方法,我写出查询某个用户的时间段内事务数的方法:
select s.USERNAME,sum(se.VALUE) "session transaction number",sum(sy.VALUE) " database transaction number" from v$session s,v$sesstat se,v$sysstat syITPUB个人空间)^yt]tCa!v
where s.sid=se.SID and se.STATISTIC#=sy.STATISTIC#ITPUB个人空间9b ]J P)pb9RA.I
and sy.NAME='user commits'ITPUB个人空间T/nWH:?Q{;r2H
and s.USERNAME=upper('&username')ITPUB个人空间;~0q,g2fa }2M
group by s.USERNAME;
使用方法和上面的一样,需要在2个时间段分别运行改脚本,把执行结果相减,即可得出该时间段内的事务数
文章来自:http://blog.163.com/yanenshun@126/blog/static/128388169201302793943909/
联系邮箱:qrcg92@foxmail.com