0576-0578

Previous Table of Contents Next

Page 576

transactions and long queries. It is acceptable to mix large and short rollback segments and to select explicitly which rollback segments to use.

TIP

When you use the SET TRANSACTION USE ROLLBACK SEGMENT statement, the rollback segment specified is used only for the life of the transaction. The lifetime of a transaction lasts until a commit or a rollback is issued, at which time the rollback segment is randomly selected. If you need to use the same rollback segment after a commit or rollback transaction, you must execute the SET TRANSACTION USE ROLLBACK SEGMENT statement again.

Like tables, rollback segments are constrained by the maximum extent size to which they can grow and by the amount of physical space available in a tablespace. Once these limits are reached, the database does not use a new rollback segment. Therefore, if a rollback segment or its tablespace is sized incorrectly, it is possible that the amount of rollback space needed will exceed the total size of the rollback segment. Oracle recommends allocating 20_40 extents with equal sizing, with each extent being about .25 to .50 percent of the largest active table in the database. Also, try to spread rollback segments across as many disks as possible, to spread I/O as much as possible.

CAUTION

Make sure that database users are issuing frequent commits during batch transactions, especially if batches are being run during hours of normal user activity. Failure to issue commits effectively could result in a halted datbase, disallowing further transactional activity until space is freed in the rollback segments (by issuing commits or rollbacks ).

Redo Log Contention

A buffer cache area is in the SGA for redo information. This information is stored in memory and regulated through the use of two latches, or RAM-level locks. The redo allocation latch controls the allocation of space for writing redo information to the buffer. The redo copy latch is used to copy information to the buffer.

The wait latch requests wait to make a request, sleep, and then make the request again until it acquires the latch. Conversely, the immediate latch requests do not wait; instead, they continue processing. Use the following query to determine the status of both types of latches:

 select name, gets, misses, sleeps, immediate_gets, immediate_misses from v$latch where name in (`redo allocation', `redo copy') / 

Page 577

Information about wait requests appears on the left and immediate requests on the right. After you execute this query ”as SYS or another user with access to the V$ views ”calculate the contention values:

 immediate contention = (immediate_misses / (immediate_gets + immediate_misses)) * 100 wait contention = (misses / (gets + misses)) * 100 

If either value is greater than 1, contention is occurring for that latch. To alleviate contention for a redo allocation latch, reduce the amount of time that a latch is held by a process by lowering the value of the LOG_SMALL_ENTRY_MAX_SIZE parameter in the INIT.ORA parameter file. To alleviate contention for a redo copy latch, increase the number of latches by raising the value of the LOG_SIMULTANEOUS_COPIES parameter.

Checkpoints

A checkpoint is an event that occurs within the database whenever information is written from the caches within the SGA to disk. This occurs periodically to bring the database and control files in sync with the SGA. Disk I/O slows down processing time, which holds true for checkpoints. The database must synchronize the contents of its memory and data files, but too frequent synchronizing can reduce overall performance.

Checkpoints generally occur at various intervals that the DBA can control, as a result of certain events that the DBA cannot control, or when the DBA forces them.

Checkpoints occur based on one of two intervals: quantity or time. The value of the LOG_CHECKPOINT_INTERVAL parameter in the INIT.ORA parameter files specifies the number of redo blocks that the database will fill. When this value is reached, a checkpoint occurs. Likewise, when the amount of time specified in the LOG_CHECKPOINT_TIMEOUT parameter since the last checkpoint elapses, a checkpoint occurs. These values should usually be set to minimize checkpoints. In other words, set LOG_CHECKPOINT_INTERVAL to a value greater than the size of the largest redo log and set LOG_CHECKPOINT_TIMEOUT to zero, which disables it.

Checkpoints continue to occur whenever the database is shut down (normal or immediate) or when a redo log switch occurs. The only thing you can do to tune at this level is to make larger redo logs so that redo log switches occur less frequently.

To force a checkpoint, issue the following SQL command:

 alter system switch logfile; 

You might have to force a log switch when you perform maintenance on redo logs, such as when you relocate them from one physical disk to another.

Database Objects

Performance tuning does not stop with checking buffer caches and adjusting parameters in the INIT.ORA file. You also need to optimize the database's objects. This includes monitoring the

Page 578

objects for changes in condition, such as fragmentation, that can adversely impact performance. Unlike memory and contention problems, which generally remain stable unless a change in the database occurs, many database objects must be tuned on a regular basis.

Tables and Indexes

The database objects that cause the most problems are tables and indexes. Because transactions are constantly extracted from and inserted into database tables and indexes, problems such as chaining, migration, dynamic extension, and fragmentation can occur regularly. Because they occur often, most DBAs wait until these problems exceed a threshold, or they follow a maintenance schedule.

Migrated and Chained Rows

After the Oracle RDBMS places information in a row, it remains in that row ”and utilizes its allocated space ”until a change occurs. For example, an UPDATE that causes the row not to fit in a single database block might occur. The RDBMS searches for a free block in which it will fit. If it locates one, it moves the row to the new database block. The row is said to have been migrated. On the other hand, if a single database row is too large to fit in a single database block, the Oracle RDBMS stores the pieces of the row across several database blocks. This is called chaining.

Migrated and chained rows reduce performance for input and output operations. This is because the data spans multiple blocks. Instead of being able to return a single row in a single I/O operation, the database must perform multiple reads to return one row. Depending on the number of rows being returned and the number of rows that are chained or migrated, this can double or even triple the number of reads in a database operation.

Oracle provides a tool for detecting chaining and migration within the database. The SQL command analyze ”used in the Cost-Based Optimizer ”searches for chained rows. Before you run this query, however, you must run the utlchain.sql script provided with the database. The analyze command looks for a table called chained_rows, in which it stores the information returned. The query cannot run unless chained_rows exists. The utlchain.sql script creates this table:

 SQL> @$ORACLE_HOME/rdbms/admin/utlchain SQL> analyze table table_name list chained rows; 

You must perform this operation on every table that you want to have checked for chaining or migration. If any rows appear in the chained_rows table, you should remove them. To remove chained or migrated rows:

1. Create a copy of the table in which chained or migrated rows occur and place the chained or migrated rows into the new table.

    create table new_table as select * from table where rowid in 

Previous Table of Contents Next