| Saving time | - How to tune Oracle CPU usage |
| Which way there? | - A script to consider alternative table access paths |
| A recursive ideal | - Tuning the buffer pool sizes under Oracle 8i |
| Oracle SQL | - A new edition of Guy Harrison's classic SQL tuning book |
Although we commented that much of Oracle's CPU usage is accounted for by mere access to data in memory, there are two things that are even more expensive in terms of CPU usage than access to a block in memory. They are access to a block on disk, and the parsing of SQL statements.
The CPU usage associated with parsing is easy to see in the parse time cpu statistic. The APT script response_time_breakdown.sql expresses this CPU time conveniently as a percentage of total foreground response time. If parse CPU time is a significant component of response time, then it can be tuned by using sharable SQL (or the cursor_sharing facility) to avoid hard parses, by using a session cursor cache to avoid soft parses, and by phrasing and hinting SQL statements so as to reduce the cost of each parse operation. In particular, the use of the ORDERED hint can greatly reduce the parse cost of complex joins.
Reload CPU time is similar to parse CPU time, in that it is the time taken to reload (reparse) SQL statements and other library cache objects when necessary, but the approach to tuning is different. Reload CPU time is not measured separately in the Oracle statistics, but response_time_breakdown.sql estimates it based on the library cache statistics in V$LIBRARYCACHE. If reload CPU time is a significant component of response time, it indicates either that the shared pool is much too small, or that library cache objects are being frequently marked as invalid.
The other activity that is much more expensive in terms of CPU usage than access to blocks in memory, is access to blocks on disk. Much basic Oracle tuning is focused on reducing disk I/O. In particular tuning query execution plans to avoid physical reads, and tuning data storage and caching to optimize the cache hit rate, both yield significant reductions in CPU usage. (See below for more on both these issues).
It is commonly thought that once parsing has been minimized, reloads avoided and the cache hit rate optimized, that there is nothing further that can be done about Oracle CPU usage -- that the remaining CPU usage is necessary overhead. This is not true. Indeed, we were recently asked to tune a poorly performing system that had 100% library cache and buffer cache hit rates. It never parsed a new SQL statement and almost never read a new database block into cache. It just sat there using CPU time -- lots of it.
The problem of course was a few bad SQL statements with inefficient query execution plans. These queries were doing nested full table scans and accessing far more data buffers than necessary. This was allowed intentionally on the understanding that these full table scans would be cheap because the buffer cache was large enough to cache the entire database. Not so. While cache hits are cheaper than cache misses, their cost is far from trivial. Although the exact ratio varies greatly on different platforms, in terms of CPU usage the cost of a cache hit is often between an eighth and a quarter of the cost of a cache miss. So buffer gets are not to be taken lightly even if there are no disk reads. Apart from parsing, reloads and disk reads, total Oracle CPU usage correlates very closely with the number of buffer gets, and you have not finished tuning CPU usage until all the SQL statements have been tuned not only to minimize disk reads, but buffer gets as well.
To speed up the process we have written a new APT script called table_access_paths.sql that can be used to examine the possible access paths to a table when tuning a problem query. By default it will look at the table subject to the most disk reads. For each column it reports the number of nulls, the average selectivity of equality predicates, and the access paths available for that column. This can be used to select the optimal join order for the query and thus the optimal join mechanism and access path for the table. It can also be used to identify unhelpful indexes on non-selective columns, and the absence of potentially useful indexes.
The script also identifies problems with the storage of the table itself including poor data density, row migration, cluster block chaining, freelist contention and inappropriate caching directives. Each of these issues can affect query execution and may be the cause of poor performance.
Similarly, for each index the script reports the index data density and a percentage metric as to how well ordered the table rows are with respect to the index key. This can be used to identify when poor index or table condition is affecting index range scan performance.
The script also shows which index columns are compressed and reports the average column length in bytes and the number of entries per key for each indexed column (or percentage selectivity for less selective columns). This can be used to check whether the index is optimally compressed.
For indexed columns with histograms the script reports the proportion of popular values and the density (median selectivity) of non-popular values. If there are few popular values and the density is approximately equal to the average selectivity, then either the histogram has too few buckets or it is redundant because the column values are well distributed.
This script is fairly new, and there are still several enhancements planned. So if you have any suggestions, please send them in. For now, you can see a sample of the output of the script here on the Ixora web site.
However, you can do a similar thing under Oracle8i without needing those parameters. The APT script ideal_cache_size.sql suggests an "ideal" number of buffers for each configured buffer pool. The script assumes that the ideal number of buffers in each pool is the current number, plus the number of cold buffers due to be heated, less free buffers and hot buffers due to be cooled.
Of course, this ideal will fluctuate from moment to moment. So the script should be run several times under distinct workload peaks before drawing any firm conclusions. Also, because the operation of the Oracle 8i LRU algorithm is very sensitive to the number of buffers, the script should be used again once the number of buffers has been tuned. It is possible that you will need to go through several iterations to find the real ideal.
| Copyright © Ixora Pty Ltd |
|