Page 806
This will give you an ordered listing of the tables having chained or migrated rows. To help repair the situation for migrated rows, you could drop and re-create the table. If the table is very large, you could run the utlchain.sql to create the CHAINED_ROWS table. Then run the following:
SQL> ANALYZE TABLE T1 LIST CHAINED ROWS;
The ROWIDs of the migrated rows are stored in CHAINED_ROWS. You can use this to select the migrated rows from your table into a temporary table. Then delete them from your original table. Insert them back into your original table from the temporary table.
Related topics to fragmentation performance issues include the High Water Mark, Freelists, and Table/Index Reorganization. The High Water Mark (HWM) is the number of blocks that have been used in that segment to date. DELETE does not lower the HWM. Oracle must read the unused blocks below the HWM during full table scans. Although it will not reset the HWM for the current segment, you can reclaim wasted space for other segments using:
SQL> ALTER TABLE T1 DEALLOCATE UNUSED;
Freelists are linked lists of the free blocks, one or more lists per segment. Examine freelist wait events in V$WAITSTAT or use the System wide wait events section of report.txt. If any of the freelist events are much greater than 0 or much greater than your baselined figures, consider adding freelists or freelist groups to your hot tables. They must be re-created to do so.
Consider re-creating your tables if there is a lot of free space, on average, in the blocks below the HWM. After analyzing your table and computing or estimating statistics, examine
SQL> SELECT TABLE_NAME, AVG_SPACE 2> FROM DBA_TABLES 3> WHERE AVG_SPACE > (.10 * BLOCKS) 3> ORDER BY AVG_SPACE;
In other words, if your table has greater than 10 percent free space in its blocks below the HWM, drop and re-create the table by using import/export or a temporary table select/ rename method.
Indexes that belong to heavily updated tables should bear inspection. They may often need re-creating. One general guideline is that if the index size approaches 1/3 of the actual table size, drop and re-create the index. Another general guideline is that if the number of levels of the index exceeds 3, the index may be too high. ANALYZE your indexes and examine the INDEX_STATS table to gather information. If necessary, drop and re-create the desired indexes or use the ALTER INDEX <index_name> REBUILD statement.
INITRANS is a fine-tuning parameter affecting I/O performance at the block level. INITRANS sets the number of initial transaction slots per block. This should represent the number of concurrent users or program access to the block. This is set at the table level. If your application has high concurrency requirements, set INITRANS high, 200 or so, to avoid dynamic extension of transaction slots. The byte overhead is low, and the performance gain is worthwhile.
Page 807
Rollback segments are more-or-less random I/O components. They are written to concurrently (by DBWR), are buffered in the database buffer cache, and are stored in specialized tablespaces. Rollback segments can experience contention among themselves, and between themselves and other database I/O components such as data tablespaces. They provide the capability of undoing the effects of uncommitted transactions. Hence, they are generally referred to as the undo logs, or the undo part of a general purpose transaction log, with regard to all RDBMSs. Undo data is the read-consistent Oracle blocks found in either the database buffer cache in memory or in the rollback segment itself on disk. They are used for transaction rollback, instance recovery, and shared read capabilities. They are allocated to transactions in a circular fashion.
The major performance-tuning goal is to reduce contention. In other words, you want to minimize transaction waits for rollback segments, especially in a high-concurrency environment such as many OLTP systems are. Contention for rollback segments can best be avoided by creating a sufficient number of properly sized segments. Rollback segments hold transaction tables in their headers. Concurrent transactions contending for the same rollback segment will show up as contention for the transaction table (undo header) itself. How can you detect this contention? Use the following:
SQL> SELECT CLASS, COUNT 2> FROM V$WAITSTAT 3> WHERE CLASS LIKE `%undo%' 3> AND COUNT > 0;
or use:
SQL> SELECT USN, WAITS 2> FROM V$ROLLSTAT 3> WHERE WAITS > 0;
or examine UNDO_SEGMENT where TRANS_TBL_WAITS > 0 from the rollback section of report.txt. You can also review the System wide wait events section of report.txt for `%undo%' events, or select these events from the view V$SYSTEM_EVENT. Also, if you encounter the ORA-01555: snapshot too old error, you have run out of rollback. You need either more segments, larger segments, or both.
NOTE |
Guideline: If ORA-01555 occurs frequently or transaction table wait events are much greater than 0, this indicates contention. Specifically, for very large databases, or for very high query concurrent databases (such as some DSS systems), if either of the WAITS / GETS from V$ROLLSTAT or TRANS_TBL_WAITS / TRANS_TBL_GETS ratios are > 1% (.01), there may be a rollback contention problem. Allocate the number of rollback segments as the number of concurrent users and programs divided by 4, up to a maximum of 50.n |
Other rollback segment tuning recommendations follow. Set NEXT = INITIAL for rollback segments. PCTINCREASE is always 0 for rollback segments. Set MINEXTENTS to >= 20, so that
Page 808
INITIAL * MINEXTENTS is about 1 percent below the tablespace (datafile) size to prevent dynamic extension, subject to increasing concurrency at the beginning of the rollback segments' lifespans. Set OPTIMAL = INITIAL * MINEXTENTS to prevent unnecessary shrinkage of the rollback segment. You can measure the amount of undo generated by transactions through the V$TRANSACTION view. Look at SUM(USED_UBLK) and MAX(USED_UBLK) at peak load times to determine the total and the maximum amount of undo during that interval. Set INITIAL to >= MAX(USED_UBLK) after several samplings. For additional fine tuning, set the init.ora parameters PROCESSES, TRANSACTIONS, and TRANSACTIONS_PER_ROLLBACK_SEGMENT appropriately. See the Oracle Server Administrator's Guide for further details on these parameters. However, these parameters have minimal effects relative to allocating the proper numbers and sizes of your rollback segments.
Small transactions (usually found in OLTP systems) are those that generate small amounts of undo information. For this situation, use up to 50 rollback segments as per the previous guideline and commit as frequently as possible within the application(s). Large transactions (usually found in batch systems) require single, large rollback segments. For this environment, you don't necessarily need many rollback segments, just larger ones. Use
SQL> SET TRANSACTION USE ROLLBACK SEGMENT <rollback_segment>;
to ensure that a particularly long-running (large) transaction uses a particularly large rollback segment.
TIP |
Set the init.ora parameter DISCRETE_TRANSACTIONS_ENABLED to TRUE if you have many small transactions that cause limited changes, such as updating only a few rows within one block at a time or inserting a single row at a time. See the Oracle Server Administrator's Guide for the detailed transaction criteria and prerequisites before using this parameter. |
Redo logs are sequential I/O components. In addition, they are written to (by LGWR) only one at a time, are buffered in a separate portion of the SGA (the log buffer), and are stored as OS files. So there is no contention for redo logs, only contention between redo logs and other database I/O components, such as data tablespaces. Redo logs serve only instance recovery ("roll forward"), as opposed to rollback segments, which serve additional purposes. The LGWR process writes to the redo logs (in effect, does a log flush) when one of the following occurs: a commit, a DBWR data flush (checkpoint), a checkpoint timeout occurs (at LOG_CHECKPOINT_INTERVAL), or the log buffer exceeds 1/3 full. They are allocated to LGWR in a circular fashion, and log switch always triggers a checkpoint.
Some major performance tuning goals are to minimize: