Oracle动态性能视图学习笔记(1)_v$sysstat

Oracle动态性能视图学习笔记(1)_v$sysstat

目录
1 Overview
2 Uses for V$SYSSTAT Data 一般用途
3 Usefule Statistics for v$SYSSTAT 常用的统计数据
4 Instance Efficiency Rations from V$SYSSTAT Statistics:
5 Load Profile Data from v$sysstat
6 问题

参考文档<>Chapter 24

##################################################################
1 Overview
##################################################################

v$sysstata stores instance-wide statistics on resource usage, cumulative since the instacne was started.

统计自系统启动以来的数据。系统级的统计

##################################################################
2 Uses for V$SYSSTAT Data 一般用途
##################################################################

1) Monitoring system performance. Such as the buffer cache hit ration and soft parse ration.

2) Monitoring system resource usage and how the system's resource usage changes over tims.

3) 可以灵活地用来比较两个SQL语句间的差异

##################################################################
3 Usefule Statistics for v$SYSSTAT 常用的统计数据
##################################################################

1) CPU used by this session. The total amount of CPU used by all sessions excluding background processes.
所有会话使用的CPU资源，不包括后台进程，单位为百分之一秒。

2) db block changes. The number of changes made to database blocks in the SGA that were part of an insert, update,or delete operation.
(SGA中数据发生变化(dirty)的数据块数目?)

3) execute count: The total number of SQL statement executions(including recursive SQL)
(执行的SQL语句总数）

4) logons current: Session currently connected to the instance.
(当前Logon的会话数)

5) logons cumulative: The total number of logons since the instance started.
(自系统启动以来的Logon会话数)

6) Parese count(hard): The number of parse calls that resulted in a miss in the shared pool.
(硬解析的次数)

7) parse count(total): The total number fo parese calls, both hard and soft.
(语句解析的总次数，包括，硬，软两种)

8) parse time cpu: Total CPU time spent parsing in hundredths of a second.This includes both hard and soft parses.
(语句解析花费的总CPU时间，包括，硬，软两种))

9)Parse time elapsed: The total elapsed time for the parse call to complete.
(语句解析流逝的总CPU时间，包括，硬，软两种。 肯定大于Parse time cpu. 有时要竞争CPU)

10)Physical reads: The number of blocks read from the operating system.It includes physical reads into the SGA buffer cache(a buffer cache miss)
and direct physical reads into the PGA(during direct sort operations). This statistics is not the number of I/O requests.
(物理读的次数，从操作系统读，并不是直接从Disk读.也不是I/O的请求次数)

11) Physical writes: The number of database blocks written from the SGA buffer cache to disk by DBWR and from the PGA by processes performing direct writes.
(物理写的次数。写到操作系统)

12) Redo log space request:The number of times a server process waited for space in the redo logs,typically because a log switch is needed.
(请求redo 空间的次数，是否可以理解为写Redo 的次数？)

13) Redo size: The total amount of redo generated(and hence written to the log buffer), in bytes.
(redo的产生量,为什么没有undo的产生量呢?)

14) Session logical reads.The number of logical read requests that can be satisfied in the buffer cache or by a physical read.
(会话逻辑读，包括物理读，从SGA的读)

15) sorts(memory) and sorts(disk): sorts(memory) is the number of sort operations that fit inside the SORT_AREA_SIZE(and henece did not require an on disk sort).
sort(disk) is the number of sort operations that were larger than SORT_AREA_SIZE and had to use space on disk to complete the sort.
(内存排序，Disk排序的次数)

16) sorts(rows): The total number of rows sorted.this statistics can be divided by the sorts(total) statitics to determine rows for each sort.
It is an indicator of data volumes and application characteristics.
(排序的行数)

17) table fetch by rowid. The number of rows returned using ROWID(index access or rowid=&rowid).
(通过ROWID返回的数据量)

18) table scans rows gotten: The total number fo rows processed during full table scans.
(全表扫描的行数)

19) table scans blocks gotten: The total number of blocks scanned during full table scans, excluding those for split rows.
(全表扫描的块数)

20) user commits + user rollbacks. This provides the total number of transactions on the system.

##################################################################
4 Instance Efficiency Rations from V$SYSSTAT Statistics:
实例级的一些命中率
##################################################################

1) Buffer cache hit rations: This is a good indicator of whether the buffer cache is too small
缓存全中率

1-((physical reads - physical reads direct - physical reads direct(lob)/session logical reads)

select 1-((a.value-b.value-c.value)/d.value)
  from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d
  where a.name='physical reads' and
         b.name='physical reads direct' and
         c.name='physical reads direct (lob)' and
         d.name='session logical reads';

 

2) Soft parse ration: This shows whether there are many hard parses on the system.
软解析率

1-(parse count (hard) / parse count(total))

select 1-(a.value/b.value)
  from v$sysstat a,v$sysstat b
  Where a.name='parse count (hard)' and b.name='parse count (total)';

 

3) In-memory sort ration:
内存排序率
sorts(memory) / (sorts(memory) + sorts(disk))

select a.value/(b.value+c.value)
  from v$sysstat a,v$sysstat b,v$sysstat c
  where a.name='sorts (memory)' and
         b.name='sorts (memory)' and c.name='sorts (disk)';

4) Parse to execute ration.Optimally a SQL statement should be parsed once and executed many times.
解析语句的执行率.
1 - (parse count / execute count)

select 1-(a.value/b.value)
  from v$sysstat a,v$sysstat b
  where a.name='parse count (total)' and b.name='execute count';

 

5) Parse CPU to total CPU ration. This shows how much of the total CPU time used was spent on activities other than parsing.
(用在语句其它动作的时间，除了Parse)
1 - (parse time cpu / cpu used by this session)

select 1-(a.value/b.value)
  from v$sysstat a,v$sysstat b
  where a.name='parse time cpu' and
         b.name='CPU used by this session';

 

6) Parse time CPU to parse time elapsed. time spend on CPU cycles.
(用在CPU 自旋的时间)
parse time cpu /parse time elapsed

select a.value/b.value
  from v$sysstat a,v$sysstat b
  where a.name='parse time cpu' and b.name='parse time elapsed';

##################################################################
5 Load Profile Data from v$sysstat
##################################################################
Load Profile是监控系统负载和吞吐量的重要部分。

To determine the load profile of the system.normalize the following statistics over seconds and over transactions:
"logons cumulative, parse count(total), parse count(hard), executes, physical reads, physical writes, block changes, and redo size."

系统负载的指标：
1) Block changes for each transaction

计算每个事务中block changes可以如下公式：
db blocks changes/(user commits + user rollbacks)

select a.value/(b.value+c.vaule)
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name='db block changes' and
         b.name='user commits' and c.name='user rollbacks';

2) Blocks changed for each read
This shows the proportions of block changes to block reads.
评估系统主要是做Read还是DML(insets/updates/deletes)

select a.value/b.value
  from v$sysstat a,v$sysstat b
  where a.name='db block changes' and
         b.name='session logical reads' ;

3) Rows for each sort
sorts (rows) / (sorts(memory) + sorts(disk));
每次排序的平均行数

select a.value/(b.value+c.value)
  from v$sysstat a,v$sysstat b,v$sysstat c
  where a.name='sorts (rows)' and
         b.name='sorts (memory)' and c.name='sorts (disk)';

 

##################################################################
6 问题
##################################################################
1 可不可以清空这些视图的数据呢？
不行，这是视图不是表。

2 完整的统计name的解释参考文档<>附录 Statistics Descriptions

3 The STATISTIC# for a statistic can change between releases. Do not rely on STATISTIC# to remain constant.
Instead,use the statistic NAME column to query the VALUE.不要依赖于STATISTIC#来查询，在不同版本中不保证一致。

4 查询过程中name用小写，不用大写
col name format a30
select statistic# ,name, value from v$sysstat where name like '%&name%' order by value desc;

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

转载于:http://blog.itpub.net/10248702/viewspace-669511/