Introduction

This guide is aimed at those who wish to configure Oracle 10 – 11 databases to ensure that it can perform under large loads that may be placed upon it.

SAP utilizes Oracle database technology to store SAP activity data, in form of transaction. It requires a transactional type Oracle database configuration. Loaders interact with Oracle database to perform inserts. Analyst Station interacts with Oracle database to query and extract information.

As the volume of data captured can be in the terabytes range over a period of time, it is very important to design and implement the schema for SAP tool. Equally important is to tune the Oracle database instance startup parameters.

Included in this best practice guide is a set of queries that can be used for periodic monitoring of the state of the database.

Initialization Parameters

Note: ALL recommendations below based on Windows 2003 Platform with 4GB physical memory and system used only for Oracle database.

In this section, some views are used to get advice on initialization parameters. To collect statistics in these views, the following initialization parameter must be set first:

TIMED_STATISTICS = TRUE

The main Oracle memory caches that affect performance are: Shared pool, Large pool, Java pool, Buffer cache, Log buffer, Process-private memory (for example, used for sorting, hashing, and so on)

The size of these memory caches is configurable using initialization configuration parameters. The maximum amount of SGA memory usable by an Oracle instance is determined at instance startup by the initialization parameter SGA_MAX_SIZE.

For SAP, the aim should be to maximize memory availability to Oracle at the same time keeping operating system memory paging to a minimum. Because the purpose of the SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.

To see how much memory is allocated to the SGA and each of its internal structures, run the following:

Show Sga

For many types of operations, Oracle uses the buffer cache to store blocks read from disk. To monitor the buffer cache and buffer pool hit ratio, use the following query:

SELECT NAME, VALUE

FROM V$SYSSTAT

WHERE NAME IN ( ‘session logical reads’,

‘physical reads’,

‘physical reads direct’,

‘physical reads direct (lob)’);

SELECT NAME, PHYSICAL_READS,

DB_BLOCK_GETS, CONSISTENT_GETS,

1 – (PHYSICAL_READS/ (DB_BLOCK_GETS+CONSISTENT_GETS)) “Hit Ratio”

FROM V$BUFFER_POOL_STATISTICS;

There are views to get advice on buffer cache. Set the DB_CACHE_ADVICE parameter to ON, let the database run for a few hours, loading data and performing usual queries and investigation and then query the V$DB_CACHE_ADVICE view to examine buffer cache statistics.

column size_for_estimate format 999,999,999,999

column buffers_for_estimate format 999,999,999

column estd_physical_read_factor format 999.90

column estd_physical_reads format 999,999,999

SELECT size_for_estimate,

buffers_for_estimate,

estd_physical_read_factor,

estd_physical_reads

FROM V$DB_CACHE_ADVICE

WHERE name = ‘DEFAULT’

AND block_size = (SELECT value FROM V$PARAMETER

WHERE name = ‘db_block_size’)

AND advice_status = ‘ON’;

Suggested value for DB_CACHE_SIZE initialization parameter is:

DB_CACHE_SIZE = 509607936

Oracle uses the shared pool to cache many different types of data. The main components of the shared pool are the library cache and the dictionary cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary.

A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.

You can monitor statistics reflecting library cache activity by examining the dynamic performance view V$LIBRARYCACHE. The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the V$LIBRARYCACHE view.

In an application that reuses SQL effectively, on a system with an optimal shared pool size, the RELOADS statistic will have a value near zero.

The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. INVALIDATIONS should be near zero.

Use the following query to gather library cache statistics:

SELECT namespace,

pins,

pinhits,

reloads,

invalidations

FROM V$LIBRARYCACHE

ORDER BY namespace;

To calculate the library cache hit ratio, use the following formula:

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

The amount of free memory in the shared pool is reported in V$SGASTAT. Report the current value from this view using the following query:

SELECT * FROM V$SGASTAT

WHERE NAME = ‘free memory’

AND POOL = ‘shared pool’;

If free memory is always available in the shared pool, then increasing the size of the pool offers little or no benefit. Following views can be used to get advice for tuning shared pool: V$SHARED_POOL_ADVICE, V$LIBRARY_CACHE_MEMORY

set pages 200

set lines 100

select SHARED_POOL_SIZE_FOR_ESTIMATE,

ESTD_LC_SIZE,

ESTD_LC_TIME_SAVED,

ESTD_LC_TIME_SAVED_FACTOR

from sys.V_$SHARED_POOL_ADVICE;

Use the following query to monitor the dictionary cache statistics in the V$ROWCACHE view over a period of time while your application is running. The derived column PCT_SUCC_GETS can be considered the item-specific hit ratio:

column parameter format a21

column pct_succ_gets format 999.9

column updates format 999,999,999

SELECT parameter,

sum(gets),

sum(getmisses),

100*sum(gets – getmisses) / sum(gets) pct_succ_gets,

sum(modifications) updates

FROM V$ROWCACHE

WHERE gets > 0

GROUP BY parameter;

Calculate an overall dictionary cache hit ratio using the following query:

SELECT (SUM(GETS – GETMISSES – FIXED)) / SUM(GETS) “ROW CACHE” FROM V$ROWCACHE;

Increase the amount of memory available to the library cache and data dictionary cache by increasing the value of the initialization parameter SHARED_POOL_SIZE. The maximum value for this parameter depends on your operating system. You may also increase the value of the initialization parameter OPEN_CURSORS to take advantage of additional memory available for shared SQL areas.

Suggested value for SHARED_POOL_SIZE initialization parameter is:

SHARED_POOL_SIZE = 268435456

Suggested value for OPEN_CURSORS initialization parameter is:

OPEN_CURSORS = 300

If using recovery manager or shared server architecture, you may have to additionally tune the SHARED_POOL_SIZE

If using the parallel query option (with PARALLEL_AUTOMATIC_TUNING set to true), and for backup/restore operation, must tune LARGE_POOL_SIZE initialization parameter.

Without those options, suggested value for LARGE_POOL_SIZE initialization parameter is:

LARGE_POOL_SIZE = 33554432

Since SQL queries in SAP analyst station are pre-defined, the following initialization parameter can help speed parsing of the queries:

CURS
OR_SHARING = SIMILAR

Applications that insert, modify, or delete large volumes of data usually need to change the default log buffer size. The log buffer is small compared with the total SGA size, and a modestly sized log buffer can significantly enhance throughput on systems that perform many updates.

The statistic REDO BUFFER ALLOCATION RETRIES reflects the number of times a user process waits for space in the redo log buffer. This statistic can be queried through the dynamic performance view V$SYSSTAT.

Use the following query to monitor these statistics over a period of time while your application is running:

SELECT NAME, VALUE

FROM V$SYSSTAT

WHERE NAME in (‘redo buffer allocation retries’,

‘redo log space requests’);

The value of ‘redo buffer allocation retries’ should be near zero over an interval.

Suggested value for LOG_BUFFER initialization parameter is:

LOG_BUFFER = 262144

Whenever a connection is made to an Oracle database, a unique section of memory is allocated to store session-level information. The area of memory allocated for each session is known as the Process Global Area (PGA). Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor. For complex queries (for example, decision support queries), a big portion of the runtime area is dedicated to work areas allocated by memory intensive operators.

Enable automatic PGA memory management, by setting the initialization parameter PGA_AGGREGATE_TARGET. Following query gives details about automatic PGA memory management:

SELECT * FROM V$PGASTAT;

Also, the V$PGA_TARGET_ADVICE view can be used to get advice on the PGA

set pages 200

set lines 100

select PGA_TARGET_FOR_ESTIMATE,

ESTD_PGA_CACHE_HIT_PERCENTAGE,

PGA_TARGET_FACTOR,

ADVICE_STATUS

from sys.V_$PGA_TARGET_ADVICE;

Suggested value for PGA_AGGREGATE_TARGET initialization parameter is:

PGA_AGGREGATE_TARGET = 536870912

If Oracle is not running in automatic memory management mode, when the WORKAREA_SIZE_POLICY parameter is set to MANUAL, the maximum amount of memory allocated for a sort is defined by the parameter SORT_AREA_SIZE

Oracle collects statistics that reflect sort activity and stores them in dynamic performance views, such as V$SQLAREA and V$SYSSTAT.

SELECT NAME, VALUE

FROM V$SYSSTAT

WHERE NAME IN (‘sorts (memory)’, ‘sorts (disk)’);

SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS

FROM V$SQLAREA

ORDER BY SORTS;

Suggested value for SORT_AREA_SIZE initialization parameter is:

SORT_AREA_SIZE = 134217728 or higher

Additional suggestions for initialization parameters:

UNDO_MANAGEMENT = AUTO

UNDO_RETENTION = 3600

QUERY_REWRITE_ENABLED = TRUE

DB_FILES = 1200

Managing Tablespaces And Data Files

Physical disk I/O is one of the most important factors in database performance bottlenecks. Database should be designed with data files distributed over many disk drives to maximize the potential for parallel I/O. Heavily accessed data segments ideally should be placed in their own tablespaces with composing data files distributed across different disk drives and controllers.

Oracle keeps statistics on each data file’s I/O rates to help you identify hot spots. The V$FILESTAT contains the information needed to identify and reduce data file hot spots. The V$FILESTAT view can be queried for physical reads and physical writes on each data file. The following SQL statement shows physical read & write values for each data file sorted by the total number of physical accesses:

column name format a40

SELECT name, phyrds, phywrts, (phyrds+phywrts) Total

FROM v$filestat s, v$datafile d

WHERE s.file# = d.file#

ORDER BY total DESC;

By looking at data files at the top of the result from this SQL statement, you can identify which physical devices are being accessed the most. If any of the top data files are on the same physical disk drives or controllers, you should consider moving them to different drives or controllers.

You can use two key strategies to minimize data file contention caused by these hot spots:

Building each tablespace with multiple data files that are located on different disk drives and controllers

Isolating tables and indexes on different tablespaces

RAID technology

Managing REDO LOGS

Oracle must keep a log of all its write activity in order to facilitate lossless recovery from instance and media failure. The redo logs contain exceedingly detailed information about most every change Oracle makes to a database’s physical file components.

Because so much information is written to the active redo log, it can easily become a bottleneck that can significantly limit the performance of an otherwise well-tuned database. When you’re tuning the redo logs for performance, your objective will be to minimize the amount of time the database must spend writing redo information to disk.

When sizing redo logs, it’s absolutely essential that they be sufficiently sized so that checkpoints can finish well before a log switch is necessary. Oracle’s V$SYSSTAT view contains the statistical values for ‘background checkpoints started’ and ‘background checkpoints completed’. When the values for these statistics vary by more than 1, at least one checkpoint didn’t finish before a log switch was necessary and log sizes must be increased.

Use the following SQL statement to query the V$SYSSTAT view for the background checkpoint statistics:

SELECT name, value

FROM v$sysstat

WHERE name like ‘background checkpoint%’;

When a database is running in ARCHIVELOG mode and a log file switch occurs, the database will immediately begin filling a new redo log group as the ARCH process begins copying one of the completed redo logs to the archive log destination. It’s important to make sure that the ARCH process finishes its task well before another redo log group is filled. If the ARCH process can’t keep up with redo log generation, Oracle will have to suspend database activity when all redo log groups have filled. Although database activity will resume as soon as ARCH can catch up and release a redo log group, users and batch jobs will most definitely notice the lack of system responsiveness.

It is suggested that you implement one or more of the following options:

· Increase the size of redo logs. The ARCH process is more efficient when working with fewer and larger redo logs.

· Add more redo log groups. This will give the ARCH process more time to catch up during peak transaction rates without causing the database to suspend activity.

· Store the redo & archived logs on faster physical devices. By using RAID technology with high-speed disk drives, the ARCH process should be able to keep up with most any realistic redo log generation rate.

Because of the high I/O rates on redo log data files, remember to dedicate disk drives exclusively for redo log use. To ensure that ARCH and LGWR are always working from different physical drives, odd-numbered redo log data files should always reside on completely different disk drives (and, ideally, different controllers) than even-numbered redo log data files.

Home