Measuring Oracle Performance

The answer to this question is not simple. The Oracle database will always perform faster when given more resources. To use a simple example, an Oracle database that has enough memory to fully cache all programs and data will perform far faster than a database that must go to the disks to get programs and data. Hence, the tuning of Oracle must always be weighed against the resources of the computer where the server resides. Oracle should be given enough resources to prevent excessive I/O, but not given so many resources that other tasks on the computer will suffer from low resources.

There are specific metrics, things you can check to monitor the level of Oracle performance, to see how well different components are performing. Below are some common metrics. Of course there are external factors that can affect performance as well, but these metrics will help you examine the external factors which effect performance.

When measuring Oracle performance, we must always remember that the Oracle database does not run by itself. When an end-user complains of poor response time, the cause could be the network and CPU or a host of other non-Oracle causes.
There are external factors within the computer that may cause Oracle performance degradation. Network bottlenecks, shortages of RAM storage, disk I/O bottlenecks, and CPU overloads can all influence Oracle performance.
Your Oracle database is at the mercy of the server. If you have a lack of CPU or memory, no amount of tuning will improve performance.

1. The buffer hit ratio: This is a measure of the number of times that a requested data block is found in the RAM memory of the SGA. In general, the larger the database buffer, the higher the buffer hit ratio.
2. Redo log space requests: This describes the number of times that Oracle had to flush the redo log buffer because of high activity. The higher the value of the log_buffer init.ora parameter, the less the number of redo log space requests.
3. DB file read waits: This is the number of times that Oracle was required to wait for a physical I/O. This value is stored in the V$session_wait view. (You'll learn more about the V$views later in the course)
4. Buffer busy waits: This is the number of times that a transaction had to wait to access a data block that was already in the data buffer. This is generally caused by excessive demand for the same row, such as the segment header for a table when parallel tasks are inserting data rows into the same table.
5. Table fetch continued rows: This counts the number of times that Oracle encountered a row that was fragmented onto a successive data block. This occurs when rows with VARCHAR columns expand as a result of SQL UPDATE activity. Continued row fetches will also occur when very large rows are stored in tables with a small db_block_size, such that the row length is greater than the block size.
6. Disk sorts: This counts the number of times that Oracle must go to the temporary tablespace to perform a large sort. In general, the larger the sort_area_size, the less disk sorts.
7. Library cache hit ratio" This is the number of times that a library cache member (PL/SQL block or SQL statement) was found in the RAM memory of the SGA shared pool. In general, the higher the value of the shared_pool init.ora parameter, the better the library cache hit ratio.

Most of these Oracle metrics can be obtained with the Oracle utlbstat and utlestat utilities that will be discussed later in the course. The next lesson concludes this module.

Click the exercise link below to compete a matching exercise about tuning metrics.
Measure Oracle Performance - Exercise