LoadRunner監控Oracle計數器要點

監控方法：

LoadRunner監控Oracle
http://www.docin.com/p-92077154.html

 

計數器介紹：

1、sorts(disk)(V$SYSSTAT)
If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE. For more information, see "SORT_AREA_SIZE".

sorts (memory) and sorts (disk)：sorts(memory)是在SORT_AREA_SIZE(因此不需要在磁碟進行排序)的排序操作的數量。sorts(disk)則是由於排序所需空間太大，SORT_AREA_SIZE不能滿足而不得不在磁碟進行排序操作的數量。這兩項統計通常用於計算in-memory sort ratio。

2、sort(memory)(V$SYSSTAT)
If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application work load. You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

In-memory sort ratio：該項顯示記憶體中完成的排序所佔比例。最理想狀態下，在OLTP系統中，大部分排序不僅小並且能夠完全在記憶體裡完成排序。
公式：sorts (memory) / ( sorts (memory) + sorts (disk) )
執行：
selecta.value/(b.value+c.value)
 fromv$sysstat a,v$sysstat b,v$sysstat c
 wherea.name='sorts (memory)'and
        b.name='sorts (memory)'andc.name='sorts (disk)';

對於要做大量排序操作的SQL語句的執行（例如select * from tt order by 1,2,3,4;），可監控到sort(disk)和sort(memory)都會有所上升。效能好的話，應該是大部分排序在記憶體中進行。

查詢PGA統計資訊：
SELECT * FROM v$pgastat;
查看bytes processed、extra bytes read/written的增量值和cache hit percentage的值
如果cache hit percentage偏低，則要考慮調整PGA

PGA
（Program Global Area程式全域區）是一塊包含一個服務進程的資料和控制資訊的記憶體地區。它是Oracle在一個服務進程啟動時建立的，是非共用的。一個Oracle進程擁有一個PGA記憶體區。一個PGA也只能被擁有它的那個服務進程所訪問，只有這個進程中的Oracle代碼才能讀寫它。因此，PGA中的結構是不需要Latch保護的。

3、db block gets (V$SYSSTAT)

Number of blocks accessed in buffer cache for INSERT, UPDATE, DELETE, and SELECT FOR UPDATE. Represent block logical reads (from cache).The logical reads ALWAYS include the physical reads. Low number of physical reads is preferable.

在Oracle的文檔中有這樣一段解釋：
db block gets：Number of times a CURRENT block was requested.
consistent gets：Number of times a consistent read was requested for a block.
physical reads：Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
---------------------------------------------------------------
針對以上3個概念進行的說明解釋及關係如下：
1、DB Block Gets（當前請求的塊數目）
當前模式塊意思就是在操作中正好提取的塊數目，而不是在一致性讀的情況下而產生的塊數。正常的情況下，一個查詢提取的塊是在查詢開始的那個時間點上存在的資料區塊，當前塊是在這個時刻存在的資料區塊，而不是在這個時間點之前或者之後的資料區塊數目。

2、Consistent Gets（資料請求總數在復原段Buffer中的資料一致性讀所需要的資料區塊）
這裡的概念是在處理你這個操作的時候需要在一致性讀狀態上處理多少個塊，這些塊產生的主要原因是因為由於在你查詢的過程中，由於其他會話對資料區塊進行操 作，而對所要查詢的塊有了修改，但是由於我們的查詢是在這些修改之前調用的，所以需要對復原段中的資料區塊的前映像進行查詢，以保證資料的一致性。這樣就產 生了一致性讀。

3、Physical reads（物理讀）
就是從磁碟上讀取資料區塊的數量，其產生的主要原因是：
1)、 在資料庫快取中不存在這些塊
2)、 全表掃描
3)、 磁碟排序

它們三者之間的關係大致可概括為：
邏輯讀指的是Oracle從記憶體讀到的資料區塊數量。一般來說是'consistent gets' + 'db block gets'。當在記憶體中找不到所需的資料區塊的話就需要從磁碟中擷取，於是就產生了'physical reads'。

Buffer Cache Hit Ratio
查看oracle緩衝的命中率（大於90％）

select 1 - ((physical.value - direct.value - lobs.value) / logical.value)  "Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';

該項顯示buffer cache大小是否合適

 

4、parse count (hard)(V$SYSSTAT)
Total number of parse calls (real parses). A hard parse is a very expensive operation in terms of memory use, because it requires Oracle to allocate a
workheap and other memory structures and then build a parse tree.Should be minimized. The ratio of Hard Parse to Total should be less than 20%.

parse count (hard)：在shared pool中解析調用的未叫用次數。當sql語句執行並且該語句不在shared pool或雖然在shared pool但因為兩者存在部分差異而不能被使用時產生硬解析。如果一條sql語句原文與當前存在的相同，但查詢表不同則認為它們是兩條不同語句，則硬解析即會發生。硬解析會帶來cpu和資源使用的高昂開銷，因為它需要oracle在shared pool中重新分配記憶體，然後再確定執行計畫，最終語句才會被執行。

parse count (total)：解析調用總數，包括軟解析和硬解析。當session執行了一條sql語句，該語句已經存在於shared pool並且可以被使用則產生軟解析。當語句被使用(即共用)所有資料相關的現有sql語句(如最佳化的執行計畫)必須同樣適用於當前的聲明。這兩項統計可被用於計算軟解析命中率。

Soft parse ratio：這項將顯示系統是否有太多硬解析。該值將會與原始統計資料對比以確保精確。例如，軟解析率僅為0.2則表示硬解析率太高。不過，如果總解析量(parse count total)偏低，這項值可以被忽略。
公式：1 - ( parse count (hard) / parse count (total) )
執行：
select1-(a.value/b.value)
 fromv$sysstat a,v$sysstat b
 Wherea.name='parse count (hard)'andb.name='parse count (total)';

The parse process includes the following phases:

Checking that the SQL statement is syntactically valid (that is, that the SQL conforms to the rules of the SQL language, and that all keywords and operators are valid and correctly used).

Checking that the SQL is semantically valid. This means that all references to database objects (such as tables and columns) are valid.

Checking security (that is, that the user has permission to perform the specified SQL operations on the objects involved).

Determining an execution plan for the SQL statement. The execution plan describes the series of steps that Oracle performs in order to access and update the data involved.
Parsing can be an expensive operation. Its overhead is often masked by the greater overhead of high I/O requirements. However, eliminating unnecessary parsing is always desirable.

The parse/execute ratio reflects the ratio of parse calls to execute calls. Because parsing is an expensive operation, it is better to parse statements once and then execute them many times. High parse ratios (greater than 20%) can result from the following circumstances:

If literals, rather than bind variables, are used as query parameters, the SQL must be re-parsed on every execution. You should use bind variables whenever possible, unless there is a pressing reason for using column histograms.

Some development tools or techniques result in SQL cursors being discarded after execution. If a cursor is discarded, then the parse is required before the statement can be re-executed.

診斷：
尋找不能被充分共用利用的SQL語句（查詢LibraryCache中執行次數偏低的SQL語句）：
SELECT sql_text FROM v$sqlarea WHERE executions < 5 ORDER BY UPPER(sql_text);

尋找SQL執行次數和SQL解釋次數（hard parse），對比兩個值的差：
SELECT sql_text , parse_calls , executions FROM v$sqlarea ORDER BY parse_calls;

查詢v$librarycache視圖的Reloads值（reparsing）的值，值應該接近0，否則應該考慮調整shared pool size,通過調整Shared Pool來調整Library Cache

invalidations的值也應該接近0

SQL> select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;

SELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';

SELECT sql_text , users_executing , executions , loads FROM v$sqlarea;
進一步查詢該SQL的完整資訊：
SELECT * FOM v$sqltext WHERE sql_text LIKE 'SELECT * FROM hr.employees WHERE %'

5、CPU used by this session(V$SYSSTAT)
Amount of CPU time (in tens of milliseconds) used by a session from the time a user call starts until it ends. If a user call completes within 10 milliseconds, the start- and end-user call times are the same for purposes of this statistic, and 0 milliseconds are added

Parse CPU to total CPU ratio：該項顯示總的CPU花費在執行及解析上的比率。如果這項比率較低，說明系統執行了太多的解析。
公式：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';

V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU used by this session" shows the aggregate CPU used by all sessions.

V$SESSTAT shows Oracle CPU usage per session. You can use this view to determine which particular session is using the most CPU.

If you can, determine why the processes use so much CPU time and attempt to tune them. Possible areas to research include, but are not limited to, the following:
Reparsing SQL Statements
Read Consistency
Scalability Limitations Within the Application
Wait Detection
Latch Contention

 

 

---------------------------------------------------------------------------------------------

 

修改sample rate：

To change the length of each monitoring sample (in seconds), edit the dat\monitors\vmon.cfg file in the LoadRunner root folder. The default rate is 10 seconds.

The minimum sampling rate for the Oracle Monitor is 10 seconds. If you set the sampling rate at less than 10 seconds, the Oracle Monitor will continue to monitor at 10 second intervals.

 

添加自訂計數器：

在LoadRunner安裝路徑的\dat\monitors找到vmon.cfg檔案並修改：

 

[V$ Monitor]

Counters=150

CustomCounters=9

;How many seconds for each data sample?

SamplingRate=10

 

[Custom0]

;Name must be unique

Name=庫快存命中率

Description=該計數器返回當前庫快存命中率

Query=SELECT 100*((sum(pins-reloads))/sum(pins)) from v$librarycache

IsRate=0

 

[Custom1]

;Name must be unique

Name=快取區命中率

Description=oracle database shoot straight

Query=SELECT round(1-SUM(PHYSICAL_READS)/(SUM(DB_BLOCK_GETS) + SUM(CONSISTENT_GETS)), 4) * 100 FROM (SELECT CASE WHEN NAME='physical reads' THEN VALUE END PHYSICAL_READS,CASE WHEN NAME = 'db block gets' THEN VALUE END DB_BLOCK_GETS,CASE WHEN NAME = 'consistent gets' THEN VALUE END CONSISTENT_GETS FROM V$SYSSTAT WHERE Name IN ('physical reads','db block gets','consistent gets'))

IsRate=0

 

[Custom2]

;Name must be unique

Name=共用區庫緩衝區命中率

Description=命中率應大於0.99

Query=Select round(sum(pins-reloads)/sum(pins) * 100, 2) from v$librarycache

IsRate=0

 

[Custom3]

;Name must be unique

Name=共用區字典緩衝區命中率

Description=命中率應大於0.85

Query=Select round(sum(gets-getmisses-usage-fixed)/sum(gets) * 100, 2) from v$rowcache

IsRate=0

 

[Custom4]

;Name must be unique

Name=檢測復原段的爭用

Description=應該小於1%

Query=select round(sum(waits)/sum(gets) * 100, 2) from v$rollstat

IsRate=0

 

[Custom5]

;Name must be unique

Name=檢測復原段收縮次數

Description=應該小於1%

Query=select sum(shrinks) from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn

IsRate=0

 

[Custom6]

;Name must be unique

Name=監控資料表空間的I/O讀總數

Description=監控資料表空間的I/O

Query=select sum(f.phyrds) pyr from v$filestat f, dba_data_files df where f.file# = df.file_id

IsRate=0

 

[Custom7]

;Name must be unique

Name=監控資料表空間的I/O塊讀總數

Description=監控資料表空間的I/O

Query=select sum(f.phyblkrd) pbr from v$filestat f, dba_data_files df where f.file# = df.file_id

IsRate=0

 

[Custom8]

;Name must be unique

Name=監控資料表空間的I/O寫總數

Description=監控資料表空間的I/O

Query=select sum(f.phywrts) pyw from v$filestat f, dba_data_files df where f.file# = df.file_id

IsRate=0