Database Hit Ratios
Database hit ratios are the most commonly used measures of performance. These include the buffer cache hit ratio, the library cache and dictionary cache hit ratios, the latch hit ratio, and the disk sort ratios. These hit ratios don’t indicate how well your system is performing. They’re broad indicators of proper SGA allocation, and they may be high even when the system as a whole is performing poorly.
The thing to remember is that the hit ratios only measure such things as how physical reads compare with logical reads, and how much of the time a parsed version of a statement is found in memory. As to whether the statements themselves are efficient or not, the hit ratios can’t tell you anything. When your system is slow due to bottlenecks, the hit ratios are of little help, and you should turn to a careful study of wait statistics instead.
Database Wait Statistics
When your users complain that the database is crawling and they can’t get their queries returned fast enough, there’s no use in your protesting that your database is showing high hit ratios for the shared pool and the buffer cache (and the large pool and redo log buffer as well). If the users are waiting for long periods of time to complete their tasks, then the response time will be slow, and you can’t say that the database is performing well, the high hit ratios notwithstanding.
Once it starts executing a SQL statement, an Oracle process doesn’t always get to work on the execution of the statement without any interruptions. Often, the process has to pause or wait for some resource to be released before it can continue its execution.
Thus, an active Oracle process is doing one of the following at any given time:
• The process is executing the SQL statement.
• The process is waiting for something (for example, a resource such as a database buffer or a latch). It could be waiting for an action such as a write to the buffer cache to complete.
That’s why the response time—the total time taken by Oracle to finish work—is correctly defined as follows:
response time = service time + wait time
Four dynamic performance views contain wait information:
V$SESSION
V$SYSTEM_EVENT
V$SESSION_EVENT
V$SESSION_WAIT
These four views list just about all the events the instance was waiting for and the duration of these waits. Understanding these wait events is essential for resolving performance issues.
Comments