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;