This topic provides best practices for configuring the hardware environment in which Oracle will reside.
Most hardware and storage vendors provide specific guidance for designing a high performance Online Transaction Processing (OLTP) Oracle environment as part of the installation and set-up activities. Before installing Oracle, contact the vendor to obtain the best practices for configuring the storage sub-system.
Forecast memory utilization
Oracle offers automatic tuning capabilities that simplify the performance management process for database administrators. To address memory management, regularly measure the following four aspects:
- Host Operating System
- System Global Area (SGA)
- High-Watermark of Connections
- Program Global Area (PGA)
Blackboard recommends allocating roughly 10% to 20% of available memory to the operating system, and then calculating the connection high-water mark to identify the demands of PGA.
When not using the Oracle multi-threaded server, each Oracle connection can use up to 2MB of RAM. Additionally, factor in the SORT_AREA_SIZE and HASH_AREA_SIZE, which are session-controlled variables.
Oracle offers the ability to auto-size the System Global Area (SGA) using an automatic tuning mechanism. By setting the SGA_TARGET to a particular memory allocation and setting STATISTICS to TYPICAL, Oracle will be able to decide when to move memory between the database cache and the shared pool. If a dedicated DBA is not available to study SGA utilization and make appropriate forecasting changes, this makes sense as a viable setting.
For guidance on whether to use an automatic tuning approach and the best approach to doing so, see Automatic SQL Tuning.
The following table provides a short summary of the key SGA components. If manually tuning the SGA region, it is imperative that the DBA forecast the memory needs of the system. This requires a set of exercises to characterize the workload, as well as account for unexpected utilization increases when demand in not necessarily transparent. Blackboard recommends accounting for a 20% spike.
|SGA Component||Initialization Setting||Areas Of Influence||Description|
|Shared Pool: Library Cache||SHARED_POOL_SIZE||Shared SQL areas |
Private SQL areas
PL/SQL procedures and packages
Various control structures
|Oracle needs to allocate and deallocate memory as SQL or procedural code is executed based on the individual needs of users' sessions and in accordance with the LRU algorithm.|
|Shared Pool: Dictionary Cache||SHARED_POOL_SIZE||Row cache |
|Highly-accessed memory structures that provide information about object structures to SQL statements being parsed.|
|Java Pool||JAVA_POOL_SIZE||Data in JVM||Memory available for the Java memory manager to use for all things Java.|
|Streams Pool||STREAMS_POOL_SIZE||Stream activity||Memory available for stream processing.|
|Redo Log Buffer||LOG_BUFFER||Redo Entries||Holds changes made to data and allows for the reconstruction of data in the case of failure.|
|Database Buffered Cache||DB_2K_CACHE_SIZE |
|Write list |
|Holds copies of data requested by SQL and reduces requests to disk by having data in memory. Many different buffer caches can help segregate on usage patterns.|
|Large Pool||LARGE_POOL_SIZE||Shared server |
I/O server processes
Backup and restore
|For large memory allocations.|
The Program Global Area (PGA) is a private memory region containing data and control information for a server process. Access to it is exclusive to that server process and is read and written only by the Oracle code acting on behalf of it.
Oracle provides automatic PGA memory management to simplify and improve the way in which PGA memory is allocated. By default, PGA memory management is enabled. In this mode, Oracle dynamically adjusts the portion of the PGA memory dedicated to work areas based on 20% of the SGA memory size. The minimum value is 10MB. Sizing this area depends heavily on factors such as the amount of RAM available on the system, the number of processes/connections accessing the system (high-water mark), and the degree of sort and hash area space for query operations.
To learn more about determining how much PGA is needed, see How-to-Determine-if-PGA-Size-is-Set-Properly.
The best practice for oracle 11g R2 is to enable automatic SQL Tuning by setting DBMS_AUTO_SQLTUNE.
Use the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
Use the TRUNCATE statement on the nightly archive process table to optimize space.
Use the MAX_UTILIZATION_LIMIT directive to limit CPU utilization of low priority workload.
Blackboard recommends that you use JDBC driver version 188.8.131.52 for Oracle 11gR2.You can download the driver from