Pages

Thursday 12 January 2012

SOME IMPORTANT TUNING TIPS

UGA and Oracle Shared Server: The point about UGA is that Shared server sessions take space from the available space in the shared pool for SQL and PL/SQL objects. Example the Shared pool may be tuned properly with 20 users but not large enough for 200 users. The space in the large pool can be set aside for the UGA eliminating this tuning factor. The streams pool should be configured if you are using Oracle Streams. If the streams pool is NOT configured Streams will use the Shared pool, When streams uses 10% of the shared pool Streams starts spilling to disk, so Streams could use more than than 10% of the shared pool. To avoid the memory sizing problems, fragmentation, and contention, configure the Streams Pool.

Large Pool: RMAN only uses the Large pool when BACKUP_TAPE_IO_SLAVE=TRUE

Database High Availability: Best Practices: Flashback Database can have a 1-2% overhead on write intensive systems.

Database High Availability: Best Practices: These practice are high recommended for HA databases, and are often implemented in other databases: Block Checking includes two parameters DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING. Major difference is that DB_BLOCK_CHECKSUM calculates the checksum to discover corruptions in block transfers (I/O), DB_BLOCK_CHECKING checks the internal consistancy of the block, by going through the data in the block, and can discover other types of corruptions. From Metalink Note: 336194.1 DB_BLOCK_CHECKSUM can be OFF (or) TYPICAL (or) FULL. OFF - DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. TYPICAL (default) - Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL. For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values. FULL – In FULL mode setting Oracle will compute the checksum before any change and compare the checksum to the stored value and it causes 4% to 5% overhead. This approach uncovers any data corruption in the memory itself and reports errors at that point—which is highly useful for preventing data corruption at the disk level as well as its propagation to the standby database.

From the Reference guide DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks. The checking that is performed depends on the value you supply, as follows: OFF (default) - no block checking is performed for blocks in the user tablespaces. However, semantic block checking for SYSTEM tablespace blocks is always turned on. LOW - basic block header checks are performed after block contents change in memory (for example, after UPDATE or INSERT statements, on-disk reads, or inter-instance block transfers in RAC) MEDIUM - all LOW checks are performed, as well as semantic block checking for all non-index-organized table blocks FULL - all LOW and MEDIUM checks are performed, as well as semantic checks for index blocks (that is, blocks of subordinate objects that can actually be dropped and reconstructed when faced with corruption) Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable. For backward compatibility the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

Internal Fragmentation Considerations: The problem with all of these scenarios in the first set of bullets is that they leave empty space below the HWM. If PCTFREE is set too high the space is never used, too low and you get migrated rows. LOBs use space inside the LOB for versioning instead of undo. The amount of space that is used depends on PCTVERSION and RETENTION. PCTVERSION integer - Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space. You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode. You cannot specify both PCTVERSION and RETENTION. RETENTION: Use this clause to indicate that Oracle Database should retain old versions of this LOB column for an amount of time. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. You can specify the RETENTION parameter only if the database is running in automatic undo mode. In this mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION. If there is significant update activity on LOBs the amount of space used by old versions of the LOBs can be large. Direct loads insert above the HWM, when the load is complete the HWM is moved. Deletes remove rows below the HWM. Unless there are standard INSERTs the space released by the deletes will never be used. Update and deletes on indexed colums produce deleted leaf rows in the index. These occupy reusable space in the index, but if there are few or no inserts the space will not be used.

Sizing the Redo Log Buffer: From 10gR2 onward there is a separate log buffer per cpu. There are possibly multiple redo allocation latches because each log buffer requires an allocation latch. So now there are multiple redo allocation latch children and not one single latch. The log file sync wait event P1 has the buffer number experiencing the wait.

Related links:


HOME


No comments: