The large pool was introduced by Oracle8 to help alleviate contention in the shared pool. The large pool is defined by setting the LARGE_POOL_SIZE and LARGE_POOL_MIN_ALLOC parameters in the init.ora file. If defined, memory for session processes for the multithreaded server and I/O buffer space for Oracle backup processes. The default behavior of these processes is to use the share pool.

Online redo logs, or redo logs, give the Oracle database the ability to recover from media failure. The backup/recovery options of this Oracle feature are covered in Chapter 23, “Backup and Recovery.” The size and number of redo logs needed have a direct relationship to the frequency of hot backups, checkpointing, and the size of the redo logs. If the archive logs are being created frequently, depending on data availability needs, it may be desirable to make the redo logs larger and review the timings of hot backups and checkpointing. The redo log buffer can be monitored with the redo-log-waits SQL statement in the following code. If the log waits are greater than zero, processes are waiting for space in the redo log buffer. Increase the init.ora parameter LOG_BUFFER to correct this issue.

select value "Redo Log Waits" from v$sysstat where name = 'redo log space



requests'

Rollback segments give the Oracle RDBMS its read-consistency ability. These segments are stored in tablespace structures and are basically before-images of data blocks. They are similar to table and index objects in that they have similar storage parameters and grow by dynamically adding extents. They differ from other data objects in the fact that they have an “optimal size” feature that they will return to after dynamically extending. They are circular in nature, in that before-image data blocks are added in a sequential manner. When the end of the segment is reached, it wraps back to the beginning and continues writing. If the rollback segment is not large enough for the transaction that is using it, the error snapshot too old will be issued and the transaction will be rolled back. Single transactions cannot use more than one rollback segment at a time. Transactions can be pointed to particular rollback segments, particularly nice for known large transactions; they can be assigned to a large rollback segment. They need to be large enough to contain the largest update expected by a single transaction. The rollback segment space is made available again after a commit or rollback SQL statement marks the conclusion of the transaction. Rollback segments can be public (used by any database instance) or private (only used by the instance they are associated with). These rollback segments also play an important part of instance recovery. It is the job of these rollback segments to roll back any uncommitted transactions after an abnormal termination of the Oracle RDBMS or after a SHUTDOWN ABORT.

---

Tip:  
I recommend putting the rollback segments in their own tablespace for I/O distribution purposes.

---

The optimal-size option of rollback segments is nice for that occasional but not frequent large update. The rollback segment will dynamically grow while saving the before data-block images during the course of the update. After a commit or rollback, the rollback segment will release the extra extents and return to the optimal size.

---

Tip:  
Set the optimal size for the rollback segments for the typical transaction size expected. Assign large transactions to a large rollback segment.

---

If transactions have to wait for available rollback segments, there are not enough rollback segments. Use the following code (rollback segment waits SQL statement) to monitor for waits:

select waits "Rollback Segment Waits" from v$rollstat

The following code shows the syntax for implementing the rollback segment optimal size option. In this example, optimal size is set to 150KB, so once the rollback segment added 3 extents (the storage clause of this example has the extents at 50KB each), the Oracle RDBMS will maintain the size of this rollback segment from this time on at 3 extents. The optimal size option is a nice automatic rollback segment resizing feature but at considerable overhead for the Oracle RDBMS.

CREATE public rollback segment rb_1

tablespace <tablespace name>

storage (initial 50K next 50K OPTIMAL 150K maxextents 10)

If rollback segment shrinks (see the following code) or continually grows, the DBA should research the application that is consistently having large update transactions and assign the transaction to its own rollback segment, or make the default size of the rollback segments larger.

Select shrinks "Rollback Segment Shrinks" from v$rollstat