ITPub博客

首页 > Linux操作系统 > Linux操作系统 > v$sesstat and v$statname usage

v$sesstat and v$statname usage

原创 Linux操作系统 作者:yanggq 时间:2019-07-21 11:27:02 0 删除 编辑
From the performance of Oracle

To determine the STATISTIC#’s for the session logical reads and physical
reads statistics:
SELECT name, statistic#
FROM V$STATNAME
WHERE name IN ('session logical reads','physical reads') ;
NAME STATISTIC#
------------------------------ ----------
session logical reads 9
physical reads 40
Use these values in the following query, which orders the sessions by resource
usage:
SELECT ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0))
/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, 60*24*(sysdate-ses.logon_time) "Minutes"
Table 24–21 Join Columns for V$SESSTAT
Column View Joined Column(s)
STATISTIC# V$STATNAME STATISTIC#
SID V$SESSION SID

FROM V$SESSION ses
, V$SESSTAT sta
WHERE ses.status = 'ACTIVE'
AND sta.sid = ses.sid
AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
/ greatest(3600*24*(sysdate-ses.logon_time),1) DESC;
SID User Log IO/s Phy IO/s Minutes
----- ------ -------- -------- -------
1951 batch 291 257.3 1
470 online 6,161 62.9 0
730 batch 7,568 43.2 197
2153 online 1,482 98.9 10
2386 batch 7,620 35.6 35
1815 batch 7,503 35.5 26
1965 online 4,879 42.9 19
1668 online 4,318 44.5 1
1142 online 955 69.2 35
1855 batch 573 70.5 8
1971 online 1,138 56.6 1
1323 online 3,263 32.4 5
1479 batch 2,857 35.1 3
421 online 1,322 46.8 15
2405 online 258 50.4 8
To better show the impact of each individual session on the system, the results were
ordered by the total resource usage each second. The resource usage was calculated
by adding session logical reads and (a weighted) physical reads.
Physical reads was weighted by multiplying the raw value by a factor of 100, to
indicate that a physical I/O is significantly more expensive than reading a buffer
already in the cache.
To calculate the physical I/O weighting factor, the following assumptions were
made:
n Average wait for a physical I/O (PIO) was 10 ms (queried from V$SYSTEM_
EVENT.AVERAGE_WAIT for the events db file sequential read and db
file scattered read).
n Average logical I/O rate (LIO) was 13000/second/CPU (queried from
V$SYSSTAT for the statistic name session logical reads. This statistic was
divided by the elapsed time in seconds and the number of CPUs on the system).

n This provides a ratio of 130 logical reads for each 10 ms, and 1 physical read for each 10 ms for this configuration. This ratio was rounded to the ballpark number of 100.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26706/viewspace-64586/,如需转载,请注明出处,否则将追究法律责任。

下一篇: DBA三准则
请登录后发表评论 登录
全部评论

注册时间:2002-10-29

  • 博文量
    78
  • 访问量
    50523