Managed Hosting 고객의 경우 이 주제는 적용되지 않습니다.

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.

More on using TRUNCATE


CPU utilization tuning

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

More on CPU utilization tuning


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.