| Modification monitoring | - Why you should use it. |
| New pool rules | - Controlling shared pool fragmentation under Oracle 8.1. |
| New APT scripts | - A few additions to our collection of advanced performance tuning scripts. |
| Going to OpenWorld? | - Would you like to attend an Ixora performance tuning seminar too? |
To help DBAs to maintain accurate optimizer statistics, without wasting time by analyzing tables for which the statistics are still accurate, Oracle introduced a facility in release 8.1 to monitor table modifications. This facility can be enabled using the ALTER TABLE MONITORING command. The approximate number of rows updated, inserted to, and deleted from each monitored table is counted. The modification counts are maintained in a special data structure in the SGA and flushed to a table in the data dictionary by SMON periodically (every 3 hours under release 8.1) and at instance shutdown. The DBA_TAB_MODIFICATIONS view can be queried to see these modification counts.
You can use the modification counts to determine which tables may have stale statistics, and to establish an order of priority for gathering statistics. Alternatively, you can use the GATHER STALE option of DBMS_STATS.GATHER_SCHEMA_STATS to gather statistics for all tables that may have stale statistics, or you can use Auto-Analyze feature of the OEM Tuning Pack to gather statistics in priority order during a specified maintenance window. Statistics are not regarded as "stale" unless the table was previously analyzed and the number of modifications exceeds 10% of the number of rows at that time.
PS: There are still some teething problems with DBMS_STATS.GATHER_SCHEMA_STATS as of version 8.1.6, so it is preferable to use the modification counts to drive your own statistics gathering mechanism for now.
Despite the potential for improved statistics gathering, many DBAs have not yet adopted modification monitoring. One of the concerns that people have is that the monitoring might have a significant performance overhead. In fact, this is not the case. The modification counts are maintained in an efficient hash table in the SGA, and are updated without the protection of a latch (although the structure of the hash table itself is protected by the hash table modification latch). Even in heavy OLTP environments, the cost of maintaining the modification counts is likely to be less than 1% of additional CPU usage. However, because of the latch-free nature of the feature, the modification counts are not guaranteed to be accurate. Another source of potential inaccuracy is that if a transaction is rolled back, its changes to the modification counts are not rolled back as well. These inaccuracies have been allowed by Oracle to keep the performance overhead of this feature minimal. Therefore, you can use modification monitoring with confidence that it will not affect performance significantly, while giving you a very helpful indication of which tables may have stale statistics.
The main reason for shared pool latch contention is free memory fragmentation. If free memory is fragmented into large numbers of small chunks, then when a process searches for a free memory chunk it may need to hold the shared pool latch for a long time. The degree to which free memory is fragmented can be seen very easily using Ixora's shared_pool_free_lists.sql script. The key tuning strategies to avoid free memory fragmentation are to mark objects for keeping in the library cache, and to ensure that the shared pool is not oversized.
Fortunately, you are not alone in the fight against free memory fragmentation. Oracle has several feature that help to reduce the degree of fragmentation, and several improvements have been made in this area in recent releases.
Forward coalescing
When a chunk of memory is freed, if the chunk following it is also free, then Oracle can coalesce the two free chunks.
This is called forward coalescing.
Forward coalescing is efficient, because Oracle only needs to look at the 16-byte header of the following chunk to see if it is free.
That header also contains pointers to the previous and next chunks on its freelist.
These pointers make it possible to efficiently remove the chunk from its freelist and close up the gap.
Under Oracle 8.1, we have observed that Oracle will now forward coalesce all contiguous free chunks when freeing a chunk, rather than just one chunk as before. As a consequence, there is much less need to flush the shared pool manually to coalesce free chunks under release 8.1 than before. There is also correspondingly less benefit from flushing the shared pool manually, if any, particularly if the shared pool is not oversized.
Reserved permanent memory
Reserved permanent memory, or spare free memory,
is free memory reserved at instance startup in the main permanent memory chunk in the shared pool.
For example, if the shared_pool_size parameter is set to 10,000,000 bytes
and if the permanent memory structures need an additional 1,000,000 bytes initially,
then the total size of the shared pool will be 11,000,000 bytes
and at instance startup only 5,500,000 bytes of shared pool memory will be immediately available for use
with the other 4,500,000 bytes of free memory being reserved in the main permanent memory chunk
together with the 1,000,000 bytes of allocated permanent memory.
Reserved permanent memory is released progressively under memory pressure - 50% at a time.
For large shared pools under 32-bit Oracle the initial management of large free and permanent memory chunks differs somewhat due to a maximum chunk size of 64K. For details please see http://www.oracledba.co.uk/tips/shared_pool_perm.htm.Once the reserved permanent memory has been released, it tends to be much less subject to fragmentation than the memory that was initially available. However, under version 7, Oracle was unduly slow to release the spare free memory, and would even age out some chunks from the LRU lists in preference to releasing the reserved permanent memory. Under Oracle8, the reserved permanent memory is released before any chunks are aged out from the LRU lists.
The reserved pool
The reserved pool (or better, the reserved list within the shared pool) must not be confused with reserved permanent memory.
The reserved pool is reserved persistently for large chunks,
whereas the reserved permanent memory is reserved temporarily at instance startup merely to limit fragmentation.
The sizes of these two memory areas are related, however,
in that the reserved pool should not be larger than 20% of the shared pool,
lest the reserved permanent memory be released too quickly to effectively limit its fragmentation.
However, from Oracle 8.0 onwards, just when the reserved pool became mandatory, it is not uncommon to find the reserved pool almost entirely unused. There are two reasons for this. Firstly, Oracle will now allocate large chunks from the body of the shared pool if it already has a large enough chunk of free memory there without needing to age out any chunks from the shared pool LRU lists. Secondly, Oracle almost never needs to allocate chunks larger than 5000 bytes (the default value for the _shared_pool_reserved_min_alloc parameter) anymore. The minimum legal value for this parameter has however been reduced to 4000 bytes, and that allows you to use a small shared pool to control free memory fragmentation with much less risk of ORA-4031 errors than otherwise, and without aging out chunks from the LRU lists when allocating chunks between 4000 and 5000 bytes in size.
Here is the script for: 7.3, 8.0 or 8.1EVENT T0 T1 T2 T3 T4 T5 T6 T7 T8 T9 ----------------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- control file parallel write 1 direct path read 1 file open 1 LGWR wait for redo copy 1 db file scattered read 1 1 SQL*Net message from dblink 1 1 SQL*Net more data from client 1 1 control file sequential read 1 1 1 1 1 buffer busy waits 1 1 1 1 1 1 1 direct path write 1 1 1 1 1 1 1 1 1 db file sequential read 1 2 3 3 2 1 1 enqueue 1 2 2 2 2 2 2 log file sync 2 1 1 6 3 SQL*Net message to client 2 4 3 4 2 2 2 2 2 2 latch free 183 136 14 17 32 44 78 104 122 143
version_count.sql
There are several bugs in all current production releases of Oracle that can result in very high version counts for sharable SQL statements.
There are also several application development errors that can result in the same symptoms.
High version counts greatly increase the risk of library cache latch contention.
This script is intended to perform a quick check on whether any such problems are present
by displaying an exponential histogram of version counts.
The output above is taken from the same instance as before.
Here are the scripts for: 7.3, 8.0 or 8.1VERSION_COUNT CURSORS -------------------- ---------- 1 795 2 to 4 70 5 to 7 4 10 to 16 7 18 to 29 6 34 to 64 11 66 to 127 21 130 to 250 17
| Copyright © Ixora Pty Ltd |
|