Post-Installation Recommendations in an Oracle Environment

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.

Size the System Global Area

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.

Key SGA Components
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 Library 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 DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE	Write list LRU 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.

Size the Program Global Area

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.

Automatic SQL Tuning

The best practice for oracle 11g R2 is to enable automatic SQL Tuning by setting DBMS_AUTO_SQLTUNE.

Enable automatic SQL Tuning

Use the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/

Disable automatic SQL Tuning

Use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/

Nightly archive process

Use the TRUNCATE statement on the nightly archive process table to optimize space.

CPU utilization tuning

Use the MAX_UTILIZATION_LIMIT directive to limit CPU utilization of low priority workload.

JDBC driver

Blackboard recommends that you use JDBC driver version 11.2.0.2 for Oracle 11gR2.You can download the driver from

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html.

Language