How to customize Oracle counters

I. Method for adding a custom counter

To create a custom query, perform the following operations:
1. In the installation pathMercuryLoadRunner/DAT/monitors find the vmon. cfg file and open it.
2. In the third row of the vmon. cfg file, customcounters = indicates the number of custom counters to be created.
3. Create a new section for the new Counter in the vmon. cfg file. Each section has the following format:
[Custom0]
Name = five hundred
Descr resume ption = this counter always returns 500.
Query = select 500 from dual
Israte = 0
4. In the [custom #] Line, assign the next number in the counter sequence to the new custom counter.
Note: The Custom counter must start with the number 0.
5. In the Name line, enter the name of the new Counter (Chinese can be entered ).
6. In the descr resume ption line, enter the description or explanation of the counter (Chinese can be entered ).
7. In the query row, the input returns exactlyDatabaseOne rowSQLThe query text. This row must contain a column of numbers.
Note: The Custom query text cannot exceed 512 characters.
8. In the israte row, enter 0 if you want the database to report the counter as an absolute value.
Enter 1 to report changes to the time counter per unit.
Note: a negative value cannot be returned for a custom query.

Example:
[Custom0]
; Name must be unique
Name = library fast memory hit rate
Descr saved ption = this counter returns the current fast memory hit rate
Query = select 100 * (sum (pins-reloads)/sum (PINs) from V $ librarycache
Israte = 0

3. sample configuration file object
Find the vmon. cfg file in the installation path of Mercury LoadRunner/DAT/monitors:

V $ monitor]
Count = 150
Customcounters = 12
; How many seconds for each data sample?
Samplingrate = 10

[Custom0]
; Name must be unique
Name = library fast memory hit rate
Descr saved ption = this counter returns the current fast memory hit rate
Query = select 100 * (sum (pins-reloads)/sum (PINs) from V $ librarycache
Israte = 0

[Custom1]
; Name must be unique
Name = cache hit rate
Descr resume 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

[M2 m2]
; Name must be unique
Name = hit rate of the cache area in the shared area
Descr resume ption = the hit rate must be greater than 0.99.
Query = select round (sum (pins-reloads)/sum (PINs) * 100, 2) from V $ librarycache
Israte = 0

[Custom3]
; Name must be unique
Name = shared area dictionary cache hit rate
Descr resume ption = the hit rate must be greater than 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 = check the contention of rollback segments
Descr resume ption = should be less than 1%
Query = select round (sum (waits)/sum (gets) * 100, 2) from V $ rollstat
Israte = 0

[Custom5]
; Name must be unique
Name = number of times the rollback segments are reduced
Descr resume ption = should be less than 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 = total I/O reads of the monitored tablespace
Descr tablespace ption = I/O of the monitored tablespace
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 = total I/O BLOCK reads of the monitored tablespace
Descr tablespace ption = I/O of the monitored tablespace
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 = Total number of I/O writes to the monitored tablespace
Descr tablespace ption = I/O of the monitored tablespace
Query = select sum (F. phywrts) pyw from V $ filestat F, dba_data_files DF where F. File # = DF. file_id
Israte = 0
.
.
.
.
.
(The above are 12 custom counters, and the following are the counters that come with the LR tool)

[0]
Name = CPU used by this session
Descr restart ption = This is the amount of CPU time (in 10 s of milliseconds) used by a session between when a user call started and ended. some user CILS 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 your context switches.
Israte = 0

[1]
Name = CPU used when call started
Descr restart ption = the CPU time used when the call is started.
Israte = 0
.
.
.
.

Ii. List of commonly used custom counters

Serial number monitoring name SQL Algorithm Description
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 ')) (Monitoring SGA hit rate) the hit rate should be greater than 0.90, preferably

2. Database fast storage hit rate select 100 * (sum (pins-reloads)/sum (PINs) from V $ librarycache this counter returns the current database fast storage hit rate

3. Select round (sum (pins-reloads)/sum (PINs) * 100, 2) from v $ librarycache (monitor the hit rate of the shared cache area in SGA). The hit rate should be greater than 0.99.

4. Monitor the Dictionary Buffer hit rate in SGA select round (sum (gets-getmisses-usage-fixed)/sum (gets) * 100, 2) from v $ rowcache (shared area dictionary cache hit rate) hit rate should be greater than 0.85

5. Check the contention of rollback segments using select round (sum (waits)/sum (gets) * 100, 2) from V $ rollstat less than 1%

6. Check the number of rollback segment shrinkage select sum (shrinks) from V $ rollstat, V $ rollname where V $ rollstat. USN = V $ rollname. USN

7. Monitor the total I/O reads of tablespaces select sum (F. phyrds) Pyr from V $ filestat F, dba_data_files DF where F. file # = DF. file_id monitors the I/O of a tablespace

8. Monitor the total number of I/O reads in the tablespace select sum (F. phyblkrd) PBR from V $ filestat F, dba_data_files DF where F. file # = DF. file_id monitors the I/O of a tablespace

9. Monitor the total I/O writes of tablespaces select sum (F. phywrts) pyw from V $ filestat F, dba_data_files DF where F. file # = DF. file_id monitors the I/O of a tablespace

10. Monitor the total write count of I/O blocks in the tablespace select sum (F. phyblkwrt) PBW from V $ filestat F, dba_data_files DF where F. file # = DF. file_id monitors the I/O of a tablespace

11. Monitor SGA for redevelopmentLogsSelect decode (immediate_gets + immediate_misses, 100, immediate_misses/(latency + immediate_misses) * 1%) ratio2 from V $ latch where name in ('redo copy') should be less

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)') it is best to make it smaller than 10%