IBM WebSphere Application Server Performance Cookbook

Oracle Database

Client

Read Timeout

Set a read timeout with oracle.jdbc.ReadTimeout:

Pass oracle.jdbc.ReadTimeout as connection property to enable read timeout on socket. The timeout value is in milliseconds.

Server

Review the Oracle Database (software and hardware) tuning in the latest SPECjEnterprise results submitted by Oracle:

  • Update Database Statistics: statistics are maintained on tables and indexes. Updating statistics allows the query optimizer to create better performing access plans for evaluating queries. One approach to manually updating statistics on all tables in a schema is to use the dbms_stats utility:

    execute dbms_stats.gather_schema_stats( -
    ownname => 'your_schema_name', -
    options => 'GATHER AUTO', -
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
    cascade => TRUE, -
    method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
    degree => 15);

  • Set Buffer Cache sizes correctly: this reference discusses this issue in detail: https://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm#g77696

  • Set Log Files Appropriately: Unlike DB2, Oracle performs an expensive checkpoint operation when switching logs. The checkpoint involves writing all dirty pages in the buffer cache to disk. Therefore, it is important to make the log files large enough that switching occurs infrequently. Also, applications which generate a high volume of log traffic need larger log files to achieve this goal.

  • Maintain proper table indexing: a database environment that requires additional indexes will often exhibit performance degradation over time; in some cases the performance degradation can be profound. Environments that need additional indexes often exhibit heavy read I/O on devices holding the tablespace datafiles. To assist in determining which additional indexes could improve performance, Oracle 10g provides the Automatic Database Diagnostic Monitor. It has the capability to help define and design indexes suitable for a particular workload.

  • When using the Oracle RAC product, configure the database nodes as Active-Passive. This generally provides optimal system performance while also maintaining high availability via failover support.

The following references are useful:

https://w3quickplace.lotus.com/QuickPlace/wasperf/PageLibrary852569AF00670F15.nsf/$defaultview/1CCEB50DD9A9C561852576030042A65C/$File/WebSphere%20BPM%206.2%20How%20To%20Win%20Performance%20POCs.pdf?OpenElement

The PROCESSES parameter is effectively equivalent to the maximum number of concurrent users plus the number of background processes.

The OPEN_CURSORS parameter value should be set high enough to prevent the application from running out of open cursors (handles to private SQL areas). For example, 3000.

The SESSION_CACHED_CURSORS parameter sets the number of cached closed cursors each session can have. For example, 1000.

The DB_FILES parameter specifies the maximum number of database files that can be opened for the database. For example, 3000.

The PRE_PAGE_SGA parameter determines whether Oracle reads the entire SGA into memory at instance startup. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

The DB_WRITER_PROCESSES parameter can be set to take advantage of a multi-cpu system that modifies data heavily by enabling multiple DB writer processes. For example, use the formula DB_WRITER_PROCESSES = CPU_COUNT / 8

Basic Commands

List connected clients:

SELECT * FROM v$session

Automatic Workload Repository Reports

Automatic Workload Repository (AWR) reports are commonly used to investigate Oracle database performance.

Common things to review:

  • SQL ordered by Elapsed Time (Global): Usually, review the per execution "Elapsed (s)" times of queries and consider the Execs column which is how many times those queries were executed.

Automatic Memory Management

Automatic Memory Management (AMM) was introduced in Oracle 11g and allows most memory usage (SGA, PGA, buffer pools, shared pools, large pools, etc.) to be automatically sized (excluding the log buffer). For example:

  1. Set a value for MEMORY_MAX_TARGET. Sufficient OS memory is required to support the value set. MEMORY_MAX_TARGET=14464M.
  2. Set SGA_TARGET and PGA_AGGREGATE_TARGET to 0. If these values are nonzero then it defines the minimum size for the specified region.
  3. Set MEMORY_TARGET to the total amount of memory you want to share between SGA and PGA. e.g. MEMORY_TARGET=14464M.