Memory Architecture   «Prev 

Distribute System Overhead evenly over Available Drives

System overhead consists of I/O to the SYSTEM tablespace for the data dictionary, the TEMP tablespace for sorting, and the tablespaces that contain rollback segments for undo information. You should consider the system profile in spreading the system overhead over multiple drives. For example, if the application generates a lot of data changes versus data reads, the I/O to the rollback segments may increase due to higher writes for changes and higher reads for consistent read functionality.
Sort activity can also affect disk I/O. Prior to Oracle Database 10g, youwou ld get the majority of sorts to occur in memory through tuning the SORT_AREA_SIZE parameter in the initialization file. Oracle constantly queries and updates the data dictionary stored in the SYSTEM tablespace, and this information is cached in the shared pool section of the SGA, so sizing your shared pool properly is a key to overall performance. As of Oracle Database 10g, Oracle can automatically and dynamically size the different pools in the SGA.

Oracle Sort Area

1) If you expected most sorts to take less than 3,000,000 bytes, you might configure your database as shown here

2) Small sorts would cause memory to be allocated below the retained size threshold

3) This memory would not be released after the sort.It would remain allocated to the sort area to be used for future sorts.

4) A large sort could consume up to the amount of memory specified by SORT_AREA_SIZE

5) After a large sort, enough memory would be released to bring the sort area's size back down to the level specified by the SORT_AREA_RETAINED_SIZE parameter