2012年2月6日 星期一

LR中監控ORACLE資料庫常用計數器(如何自訂Oracle計數器)


一、添加自訂計數器的方法

      要創建自訂查詢,請執行以下操作:
1.        在安裝路徑的Mercury LoadRunner\dat\monitors找到vmon.cfg文件,打開。
2.        vmon.cfg檔的第三行中,CustomCounters=指出要創建的自訂計數器個數。
3.        vmon.cfg檔中為新計數器新建一節,每節都有以下格式:
     [Custom0]
     Name=Five Hundred
Descrīption=This counter always returns 500.
Query=SELECT 500 FROM DUAL
IsRate=0
4.        [Custom]行,將計數器順序中的下一個數位分配給新的自訂計數器。
注意:自訂計數器必須是以數位0開始的聯繫順序。
5.        Name行,輸入新計數器的名稱(可以輸入中文)。
6.        Descrīption行,輸入對該計數器的描述或解釋(可以輸入中文)。
7.        Query行,輸入恰好返回資料庫一行的SQL查詢的文本,該行必須包含一列數值。
注意:自訂查詢文本不能夠超過512字元。
8.        IsRate行,如果希望資料庫將計數器報告為一個絕對值,請輸入0;如果希望數
據庫報告每單位時間計數器的更改,請輸入1
注意:自訂查詢無法返回負值。
 
例:
[Custom0]
;Name must be unique
Name=庫快存命中率
Descrīption=該計數器返回當前庫快存命中率
Query=SELECT 100*((sum(pins-reloads))/sum(pins)) from v$librarycache
IsRate=0


3        設定檔示例對象
安裝路徑的Mercury LoadRunner\dat\monitors找到vmon.cfg文件:

V$ Monitor]
Counters=150
CustomCounters=12
;How many seconds for each data sample?
SamplingRate=10

[Custom0]
;Name must be unique
Name=庫快存命中率
Descrīption=該計數器返回當前庫快存命中率
Query=SELECT 100*((sum(pins-reloads))/sum(pins)) from v$librarycache
IsRate=0

[Custom1]
;Name must be unique
Name=快取記憶體區命中率
Descrīption=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=共用區庫緩存區命中率
Descrīption=命中率應大於0.99
Query=Select round(sum(pins-reloads)/sum(pins) * 100, 2) from v$librarycache
IsRate=0

[Custom3]
;Name must be unique
Name=共用區字典緩存區命中率
Descrīption=命中率應大於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=檢測回滾段的爭用
Descrīption=應該小於1%
Query=select round(sum(waits)/sum(gets) * 100, 2) from v$rollstat
IsRate=0

[Custom5]
;Name must be unique
Name=檢測回滾段收縮次數
Descrīption=應該小於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讀總數
Descrīption=監控表空間的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塊讀總數
Descrīption=監控表空間的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寫總數
Descrīption=監控表空間的I/O
Query=select sum(f.phywrts) pyw from v$filestat f, dba_data_files df where f.file# = df.file_id
IsRate=0
.
.
.
.
.
(以上為12個自訂的計數器,以下為LR工具自帶的計數器)

[0]
Name=CPU used by this session
Descrīption=This is the amount of CPU time (in 10s of milliseconds) used by a session between when a user call started and ended. Some user calls can complete within 10 milliseconds and as a result, the start and end user-call time can be the same. In this case, 0 milliseconds are added to the statistic. A similar problem can exist in the reporting by the operating system, especially on systems that suffer from many context switches.
IsRate=0

[1]
Name=CPU used when call started
Descrīption=The CPU time used when the call is started.
IsRate=0
.
.
.
.


二、常用自訂計數器列表

序號        監控名稱        SQL演算法        說明
1   資料快取記憶體區命中率        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'))        (監控 SGA 的命中率)命中率應大於0.90最好

2   庫快存命中率        SELECT 100*((sum(pins-reloads))/sum(pins)) from v$librarycache        該計數器返回當前庫快存命中率

3   共用區庫緩存區命中率        Select round(sum(pins-reloads)/sum(pins) * 100, 2) from v$librarycache        (監控 SGA 中共用緩存區的命中率)命中率應大於0.99

4   監控 SGA 中字典緩衝區的命中率        Select round(sum(gets-getmisses-usage-fixed)/sum(gets) * 100, 2) from v$rowcache        (共用區字典緩存區命中率)命中率應大於0.85

5   檢測回滾段的爭用        select round(sum(waits)/sum(gets) * 100, 2) from v$rollstat        小於1%

6   檢測回滾段收縮次數        select sum(shrinks) from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn      

7   監控表空間的 I/O讀總數        select sum(f.phyrds) pyr from v$filestat f, dba_data_files df where f.file# = df.file_id        監控表空間的 I/O

8    監控表空間的 I/O塊讀總數        select sum(f.phyblkrd) pbr from v$filestat f, dba_data_files df where f.file# = df.file_id        監控表空間的 I/O

9    監控表空間的 I/O寫總數        select sum(f.phywrts) pyw from v$filestat f, dba_data_files df where f.file# = df.file_id        監控表空間的 I/O

10  監控表空間的 I/O塊寫總數        select sum(f.phyblkwrt) pbw  from v$filestat f, dba_data_files df where f.file# = df.file_id        監控表空間的 I/O

11   監控 SGA 中重做日誌緩存區的命中率        SELECT Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo copy')        應該小於1%

12   監控記憶體和硬碟的排序比率        select round(sum(case when name='sorts (disk)' then value else 0 end) / sum(case when name='sorts (memory)' then value else 0 end)*100,2) from (SELECT  name, value FROM v$sysstatWHERE name IN ('sorts (memory)', 'sorts (disk)'))        最好使它小於 10%

沒有留言: