Oracle Database performance test reference indicators

Note: The following indicators are taken from the performance analysis indicators provided by the Oracle performance analysis tool Statspack.

 

Indicator Name	Indicator description	Indicator range	Indicator Unit
1. Performance indicators of Instance Efficiency Percentages
No buffer wait Rate (Buffer Nowait %)	It refers to the ratio of unwaited Buffer obtained in the Buffer.	The value of this indicator should be close to 100%. If the value is low, the buffer cache may be increased.	%
Redo buffer not waiting Rate (Redo NoWait %)	The ratio of unwaited Buffer obtained in the Redo Buffer.	The value of this indicator should be close to 100%. If this value is low, there are two possible situations: 1. The online redo log does not have enough space; 2. log switching is slow.	%
Buffer hit rate (Buffer Hit %)	Index the hit rate of data blocks in the data buffer.	The value of this indicator should usually be greater than 90%. Otherwise, it needs to be adjusted. If the duration is less than 90%, you may need to increase db_cache_size. However, sometimes the low cache hit rate does not mean that the cache settings are small. It is possible that full table scan reduces the cache hit rate.	%
Memory sorting Rate (In-memory Sort %)	The ratio of sorting operations in memory. When the query needs to be sorted, the database session is first sorted in the memory. When the memory size is insufficient, the temporary tablespace is used for disk sorting, however, the disk sorting efficiency is several orders of magnitude different from the memory sorting efficiency.	The value of this indicator should be close to 100%. If the indicator value is low, it indicates disk I/O operations for a large number of sorting operations. You can increase the value of the sort_area_size parameter.	%
Shared area hit rate (Library Hit%)	This indicator mainly represents the SQL hit rate in the sharing area.	The value of this indicator should usually be greater than 95%. Otherwise, you need to increase the sharing pool (modify the shared_pool_size parameter value), bind the variable, and modify cursor_sharing and other parameters.	%
Soft resolution percentage (Soft Parse %)	This metric refers to the percentage of soft parsing in Oracle's SQL parsing process. Soft parse (soft parse) means that when Oracle receives the SQL statement submitted by the Client) check whether there are SQL statements that have been parsed exactly the same as the one you just received. When the same SQL statement is found, the previously parsed results are directly used, which saves the parsing time and CPU resources consumed during parsing.	Generally, the value of this indicator should be greater than 95%. If the value is lower than 80%, the SQL statement may not be reused. If the SQL statement is not bound to a variable, you need to consider binding the variable.	%
Latch hit rate (Latch Hit %)	The ratio of the number of Latch requests to the number of Latch requests.	The value of this indicator should be close to 100%. If the value is lower than 99%, you can take some measures to reduce the competition for Latch.	%
SQL statement execution and Resolution Ratio (Execute to Parse %)	The ratio of SQL statement execution to parsing. The more times an SQL statement is executed after one resolution, the higher the ratio, indicating that the SQL statement is highly reusable.	The value of this indicator should be as high as possible. If it is too low, you can consider setting Session_cached_cursors parameter.	%
Shared Pool memory usage (Memory Usage %)	This metric refers to the proportion of the Shared pool memory used at the collection point time.	The value of this indicator should be 75% ~ 90%. If this value is too low, the memory will be wasted. If it is too high, the components outside the Shared Pool will become aging. If the SQL statement is executed again, hard analysis will occur.	%
2. Performance metrics of the Wait event (Wait events)
Distributed File Reading (Db file scattered read (cs ))	This wait event is usually related to a full table scan. Because full table scan is carried out in the memory, it is generally not possible to be placed in a continuous buffer, so it is distributed in the buffer cache.	If this wait event is significant, it may indicate that no index is created or an appropriate index is not created for some tables that are fully scanned. Although performing a full table scan under a specific condition may be more effective than an index scan, if such a wait occurs, it is best to check whether these full table scans are necessary.	Per second
Sequential File Reading (Db file sequential read (cs ))	This wait event is usually related to read operations related to a single data block.	If this wait event is significant, it may indicate that there is a problem with the table connection sequence in the Multi-Table connection, or the index may be inappropriate. For a large number of systems with good transaction processing and adjustment, this value is mostly normal, but in some cases it may imply a problem in the system. Index scanning should be checked to ensure that each scan is necessary and to check the connection sequence of Multi-Table connections. In addition, DB_CACHE_SIZE is also the deciding factor for the occurrence frequency of these waits.	Per second
Buffer busy (Buffer busy (cs ))	When a session wants to access a block in the cache and the block is being used by another session, the waiting event is generated. At this time, other sessions may be writing information from the data file to this block in the cache or modifying this block.	The frequency of this wait event should not exceed 1%. If the wait event is significant, you need to determine which part of the cache where the waiting event occurs (such as the field header, the rollback segment header block, the rollback segment non-header block, the data block, and the index block ), take appropriate optimization methods.	Per second
(Enqueue (cs ))	Enqueue is a lock mechanism to protect shared resources. This locking mechanism protects shared resources, such as recorded data, to prevent two people from updating the same data at the same time. Enqueue includes a queuing mechanism, namely, FIFO (first-in-first-out. Note: the latch mechanism of Oracle is not FIFO. Enqueue wait usually refers to ST enqueue, HW enqueue, TX4 enqueue and TM enqueue.	If the enqueue wait event is significant, you need to take the appropriate optimization method based on the enqueue wait type.	Per second
Release (Latch free (cs ))	This wait event means that the process is waiting for latch held by other processes. Latch is a low-level queuing mechanism (which is precisely referred to as the mutual exclusion mechanism) used to protect the shared memory structure in the global region of the system (SGA. Latch is like a memory lock that is quickly acquired and released. Latch is used to prevent the shared memory structure from being accessed by multiple users at the same time.	Common solutions for Latch wait: 1) Share pool latch: more bound variables should be used in OLTP applications to reduce latch waits. 2) Library cache latch: Similarly, You need to optimize SQL statements and bind variables to reduce latch waiting.	Per second
Log file synchronization (Log file sync (cs ))	This wait event means that when a session completes a transaction (commit or roll back data), it must wait for the LGWR process to write the redo information of the session from the log buffer to the log file, to continue.	This wait event takes too long, probably because the commit is too frequent or the lgwr process writes logs for too long at a time (probably because the log io size is too large). You can adjust the _ log_io_size, combined with log_buffer, make (_ log_io_size * db_block_size) * n = log_buffer to avoid conflicts with increasing log_buffer, or store log files on high-speed disks.	Per second

Author: ERDP Technical Architecture"