Key Points of LoadRunner monitoring Oracle counters

Monitoring Method:

LoadRunner monitors Oracle
Http://www.docin.com/p-92077154.html

 

Counter introduction:

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) is the number of sort_area_size (so you do not need to sort data on the disk. Sorts (Disk) is the number of sort_area_size tasks that have to be sorted on the disk because the size required for sorting is too large. These two statistics are usually used to calculate in-memory sort ratio.

2. Sort (memory) (V $ sysstat)
If the number of disk writes is zero, then the sort was completed MED 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, maybe t maybe no sorts at all. sorting is usually caused by selection criteria specifications within table join SQL operations.

In-memory sort ratio: displays the proportion of sorted tasks in the memory. Ideally, In the OLTP system, most of the sorting tasks are small and can be fully sorted in the memory.
Formula: sorts (memory)/(sorts (memory) + sorts (Disk ))
Run:
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 )';

For the execution of SQL statements that require a large number of sorting operations (for example, select * from TT order by,;), the sort (Disk) and sort (memory) can be monitored to increase. If the performance is good, most of the sorting should be performed in the memory.

Query PGA statistics:
Select * from V $ pgastat;
View the increment values of bytes processed and extra bytes read/written and cache hit percentage
If the cache hit percentage is low, you need to consider adjusting the PGA

PGA
(Program global area program global area) is a memory area that contains data and control information of a service process. It is created by Oracle when a service process is started and is not shared. An Oracle process has a PGA memory zone. A pga can only be accessed by the service process that owns it. Only the Oracle code in this process can read and write it. Therefore, the structure in PGA does not require latch protection.

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.

The Oracle document provides the following explanation:
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.
---------------------------------------------------------------
The descriptions and relationships of the above three concepts are as follows:
1. db block gets (number of blocks requested currently)
The block in the current mode indicates the number of blocks to be extracted in the operation, rather than the number of blocks generated in the case of consistent read. Under normal circumstances, the block extracted by a query is the data block that exists at the start of the query. The current block is the data block that exists at this time, instead of the number of data blocks before or after this time point.

2. Consistent gets (the data block required for consistent read of data requests in the buffer of the rollback segment)
The concept here is how many chunks need to be processed in the consistent read state when processing this operation. The main reason for these chunks is that during your query, other sessions operate on data blocks and modify the blocks to be queried. However, because our queries are called before these modifications, therefore, you need to query the pre-image of the data block in the rollback segment to ensure data consistency. In this way, consistent reads are generated.

3. Physical reads (physical read)
Is the number of data blocks read from the disk. The main reason is:
1) these blocks do not exist in the database cache.
2) full table Scan
3) disk sorting

The relationships between them can be summarized as follows:
Logical read refers to the number of data blocks read from the memory of oracle. Generally, it is 'consistent gets' + 'db block gets '. If the required data block cannot be found in the memory, it needs to be obtained from the disk, so 'physical reads' is generated '.

Buffer cache hit ratio
View the hit rate of the Oracle cache (greater than 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 ';

Check whether the buffer cache size is suitable.

 

4. parse count (hard) (V $ sysstat)
Total number of parse CILS (real parses). A hard parse is a very expensive operation in terms of memory use, because it requires Oracle to allocate
Workheap and other memory structures and then build a parse tree. shocould be minimized. The ratio of hard parse to total shocould be less than 20%.

Parse count (hard): number of times that the parsing call did not hit in the shared pool. When an SQL statement is executed and the statement is not in the shared pool or is in the shared pool, it cannot be used due to some differences between the two. If the original SQL statement is the same as the existing one, but the query table is different, it is considered that they are two different statements, then hard parsing will happen. Hard parsing will incur high CPU and resource usage expenses, because it requires Oracle to re-allocate the memory in the shared pool, and then determine the execution plan, the final statement will be executed.

Parse count (total): Total number of resolution calls, including soft resolution and hard resolution. When a session executes an SQL statement that already exists in the Shared Pool and can be used, soft resolution is generated. When the statement is used (that is, shared), all existing SQL statements related to data (such as the optimal execution plan) must also apply to the current statement. These two statistics can be used to calculate the soft resolution hit rate.

Soft parse ratio: this will show if the system has too many hard resolutions. This value is compared with the original statistical data to ensure accuracy. For example, if the soft resolution rate is only 0.2, the hard resolution rate is too high. However, if the total number of partitions (PARSE count total) is low, this value can be ignored.
Formula: 1-(PARSE count (hard)/parse count (total ))
Run:
Select1-(A. Value/B. value)
Fromv $ sysstat A, V $ sysstat B
Wherea. Name = 'parse count (hard) 'andb. Name = 'parse count (total )';

The parse process parameters des the following phases:

Checking that the SQL statement is syntactically valid (that is, that 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 callto execute CILS. because Parsing is an expensive operation, it is better to parse statements once and then execute them submit 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 shoshould 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.

Diagnosis:
Find the SQL statements that cannot be fully shared (query SQL statements with low execution times in librarycache ):
Select SQL _text from V $ sqlarea where executions <5 order by upper (SQL _text );

Find the number of SQL executions and the number of SQL explanations (hard parse), and compare the difference between the two values:
Select SQL _text, parse_cils, executions from V $ sqlarea order by parse_cils;

Query the reloads value (reparsing) in the V $ librarycache view. The value should be close to 0. Otherwise, you should consider adjusting the shared pool size and adjust the library cache by adjusting the Shared Pool.

The value of invalidations should also be close to 0.

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

Select gethitratio from V $ librarycache where namespace = 'SQL region ';

Select SQL _text, users_executing, executions, loads from V $ sqlarea;
Further query the complete information of the SQL statement:
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: This item shows the total CPU consumption rate in execution and resolution. If this ratio is low, the system performs too many resolutions.
Formula: 1-(PARSE time CPU/CPU used by this session)
Run:
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 particle 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

 

 

Bytes ---------------------------------------------------------------------------------------------

 

Modify sample rate:

To change the length of each monitoring sample (in seconds), editDat \ monitors \ vmon. cfgFile 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.

 

Add a custom counter:

Find the vmon. cfg file in the \ dat \ monitors installation path of LoadRunner and modify the file:

 

[V $ monitor]

Count = 150

Customcounters = 9

; How many seconds for each data sample?

Samplingrate = 10

 

[Custom0]

; Name must be unique

Name = library fast memory hit rate

Description = this counter returns the hit rate of the current database.

Query = select 100 * (sum (pins-reloads)/sum (PINs) from V $ librarycache

Israte = 0

 

[Custom1]

; Name must be unique

Name = cache hit rate

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

 

[M2 m2]

; Name must be unique

Name = hit rate of the cache area in the shared area

Description = the hit rate should 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

Description = the hit rate should 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

Description = 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

Description = 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

Description = 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

Description = 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

Description = 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