6 Manage Oracle Database for Oracle Fusion Applications

Database statistics provide information about the type of load on the database and the internal and external resources used by the database. To accurately diagnose performance problems with the database using ADDM, statistics must be available.

A cumulative statistic is a count such as the number of block reads. Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics about segments and services. Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes.

By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by AWR are queried from memory. The gathered data can be displayed in both reports and views.

See section Gathering Database Statistics Using the Automatic Workload Repository in the Oracle Database 2 Day + Performance Tuning Guide for details about the database statistics collected and processed by AWR.

A built-in alerts infrastructure is provided to notify about impending problems with the database. By default, Oracle Database enables the following alerts:

• Tablespace Usage

• Snapshot Too Old

• Recovery Area Low on Free Space

• Resumable Session Suspended

In addition to these default alerts, performance alerts can be used to detect any unusual changes in database performance.

See the Monitoring Performance Alerts section in the Oracle Database 2 Day + Performance Tuning Guide for details on setting metric thresholds for performance alerts, responding to alerts, and clearing alerts.

On the Performance page, there are several charts that can be used to monitor database instance activity. The Performance page can also be customized so that the most useful instance activity charts are displayed by default.

See the Monitoring Instance Activity section in theOracle Database 2 Day + Performance Tuning Guide for details on monitoring:

• Throughput: measures the amount of work the database performs in a unit of time. The Throughput charts show any contention that appears in the Average Active Sessions chart.

• I/O: shows I/O statistics collected from all database clients. The I/O wait time for a database process represents the amount of time that the process could have been doing useful work if a pending I/O had completed. Oracle Database captures the I/O wait times for all important I/O components in a uniform fashion so that every I/O wait by any Oracle process can be derived from the I/O statistics.

• Parallel executions: shows system metrics related to parallel queries. Metrics are statistical counts per unit. The unit could be a time measure, such as seconds, or per transaction, or session.

• Services: shows services waiting for the corresponding wait event during the time period shown. Services represent groups of applications with common attributes, service-level thresholds, and priorities. Only active services are shown.

Database time (DB time) is an indicator of the total database instance workload. The average active sessions for a time period equals the total database time of all user sessions during the period divided by the elapsed time (wall-clock time) for the period.

The Average Active Sessions chart on the Performance page shows the average active sessions for CPU usage and wait classes in the time period. Drill down from the chart to identify the causes of instance-related performance issues and resolve them.

See the Monitoring User Activity section in theOracle Database 2 Day + Performance Tuning Guide for details on monitoring:

• Top SQL: On the Active Sessions Working page, the Top Working SQL table shows the database activity for actively running SQL statements that are consuming CPU resources. If one or several SQL statements are consuming most of the activity, then it should be investigated.

• Top sessions: On the Active Sessions Working page, the Top Working Sessions table displays the top sessions waiting for the corresponding wait class during the selected time period. A session is a logical entity in the database instance memory that represents the state of a current user login to the database. A session lasts from the time a user logs in to the database until the user disconnects. If a single session is consuming the majority of database activity, then it should be investigated.

• Top services: This table displays the top services waiting for the corresponding wait event during the selected time period. A service is a group of applications with common attributes, service-level thresholds, and priorities. If a service is using the majority of the wait time, then it should be investigated.

• Top modules: This table displays the top modules waiting for the corresponding wait event during the selected time period. Modules represent the applications that set the service name as part of the workload definition. If a single module is using the majority of the wait time, then it should be investigated.

• Top actions: This table displays the top actions waiting for the corresponding wait event during the selected time period. Actions represent the jobs that are performed by a module. If a single action is using the majority of the wait time, then it should be investigated.

• Top clients: This table displays the top clients waiting for the corresponding wait event during the selected time period. A client can be a Web browser or any client process that initiates requests for an operation to be performed by the database. If a single client is using the majority of the wait time, then it should be investigated.

• Top PL/SQL: This table displays the top PL/SQL subprograms waiting for the corresponding wait event during the selected time period. If a single PL/SQL subprogram is using the majority of the wait time, then it should be investigated.

• Top files: This table displays the average wait time for specific files during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.

• Top objects: This table displays the top database objects waiting for the corresponding wait event during the selected time period. This data is available from the Active Sessions Waiting: User I/O page.

The Host chart on the Performance page displays utilization information about the system hosting the database.

See the Monitoring Host Activity section in theOracle Database 2 Day + Performance Tuning Guide for details to determine if the host system has enough resources available to run the database. Appropriate expectations can be established for the amount of CPU, memory, and disk resources that the system should be using, and then verify that the database is not consuming too many of these resources.

Performance degradation of the database occurs when the database was performing optimally in the past, such as 6 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report makes possible to compare database performance between two periods of time to identify any performance degradation that may have occurred over time.

The AWR Compare Periods report compares a new baseline or a pair of snapshots to an existing baseline. Before generating this report, an existing baseline that represents the system operating at an optimal level should be available, as described in Create a Baseline of the Database. If an existing baseline is unavailable, this report can be used to compare two periods of time using two pairs of snapshots. To generate the AWR Compare Periods report, use the Automatic Workload Repository page in Database Control, accessible from the Database Server page.

For more information about generating AWR Compare Periods reports, see the Running the AWR Compare Periods Reports section in the Oracle Database 2 Day + Performance Tuning Guide.

ADDM tries to report the most significant performance problems during an analysis period in terms of their impact on DB time. If a particular problem lasts for a very short duration, then its severity might be averaged out or minimized by other performance problems in the analysis period. Therefore, the problem may not appear in the ADDM findings. Whether a performance problem is captured by ADDM depends on its duration compared to the interval between the AWR snapshots.

To capture a detailed history of database activity, Oracle Database samples active sessions each second with the Active Session History (ASH) sampler. ASH gathers sampled data at the session level rather than at the instance level. By capturing statistics for only active sessions, the size of the sampled data is directly related to the work being performed.

To generate the ASH report, under Average Active Sessions on the Database Performance page, click Run ASH Report.

For more information about generating ASH reports, see the Running Active Session History Reports section in the Oracle Database 2 Day + Performance Tuning Guide.

Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing. Whenever an Oracle Database process waits for something, it records the wait using one of a set of predefined wait events. Wait event data reveals various symptoms of problems that might be affecting performance, such as latch contention, buffer contention, and I/O contention.

Wait event statistics include the number of times an event was waited for and the time waited for the event to complete. To minimize user response time, reduce the time spent by server processes waiting for event completion. Not all wait events have the same wait time. Therefore, it is more important to examine events with high wait time rather than wait events with a high number of occurrences.

It is possible to query wait event statistics from various V$ dynamic performance views. For more information about using wait events, see the Using Wait Event Statistics to Drill Down to Bottlenecks section in the Oracle Database 2 Day + Performance Tuning Guide.

Table 6-2 provides the database initialization parameter guidelines for Oracle Fusion Applications. Within the Oracle Fusion Applications ecosystem, there exist four types of databases:

• Online Transaction Processing Starter or Production configuration (OLTP)

• Oracle Fusion IDM databases: one for OID and one for OIM

• Oracle Fusion Applications Data Warehouse (DW)

These parameter values are intended to provide a baseline. The database that is installed during Oracle Fusion Applications provisioning is configured with the suggested values. As the deployment and workloads characteristics change, these values may need to be adjusted.

Tuning the database involves adjusting the sizing parameters based on the available resource and load on the database. The sga_target, pga_aggregate_target and processes parameters from Table 6-2 are examples of such parameters that need to be tuned based on SGA and PGA advisories and looking into the number of open processes during peak load.

In addition, setting a minimum value for SHARED_POOL_SIZE and DB_CACHE_SIZE to minimize frequent resizing may be considered.

This section contains an overview for configuring a database for performance. Although it is expected that modifications will be made to the database on an ongoing basis to maintain or improve performance, significant benefits can be gained by proper initial configuration of the database.

In addition to tuning the database parameters, the database administrator should properly configure the REDO Logs, and the UNDO and TEMP tablespaces, to meet the demands of the expected or observed database workload. The recommendations in this section are intended to provide initial guidance in these areas.

Under demanding workloads, the size of the redo log files can influence performance. Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and reduce performance. It is possible to obtain sizing advice on the Redo Log Groups page of Fusion Applications Control. In addition, depending on the storage configuration and performance characteristics, it may be necessary to redistribute redo logs to optimize I/O performance. The suggested minimum setting for Redo Logs is to have 3 log files of 2 GB each.

Oracle recommends that the default mode of automatic undo management be leveraged to maximize performance and efficiency. Oracle Enterprise Manager Fusion Applications Control Automatic Undo Management Advisor should be leveraged to set configuration details for undo tablespace and retention settings. This advisor also provides access to the Undo Advisor that assesses the effect and provides advice of a new undo retention setting. The suggested minimum size for the UNDO tablespace is 6 GB with auto-extend enabled.

The location of the datafiles should be optimized for I/O performance and growth. Also, Fusion Applications Control Segment Advisor should be leveraged to optimize the use of segment space and assure performance degradation does not occur. The advisor can provide historical growth trends of segments, which can be used to proactively plan for growth.

Oracle recommends the use of locally-managed temporary tablespaces with UNIFORM extents and the default size of 1 MB. Some workflows in Oracle Fusion applications can generate a large amount of disk sorts that require high temporary space requirements. Therefore, the use of multiple temporary tablespaces and tablespace groups is recommended to meet these requirements and assure optimal performance. A suggested minimum size for the TEMP tablespace (or tablespace group) assigned to the Oracle Fusion Applications schema owners is 6 GB with auto-extend enabled.

The parameters listed here are only a subset of the parameters needed to properly configure the database environment. Consult the Oracle Database Installation Guide for the specific operating system to determine the overall requirements for the kernel parameter settings. Installation guides are available in the Oracle Database Documentation Library. For additional details, go to My Oracle Support and search for DocID 169706.1.

If the current values of these parameters are larger than what is recommended, no change is necessary.

• SHMMAX - Set to the larger of either the largest System Global Area (SGA) on the system or half of the physical memory available.

• SEMMNS - Set to the larger of either 32000 or twice the sum of the PROCESSES initialization parameter for each Oracle database.

• SHMALL - Set to the sum of all SGAs divided by the page size (getconf PAGE_SIZE).

• SHMMNI - Recommended value is 4096.

• vm.nr_hugepages - Should be set for SGA larger than 8 GB. The value should be set to the sum of all SGAs.

The database listener is responsible for accepting and routing connection requests to the database. The parameters described in this section can change the behavior of the listener and, therefore, need to be examined and changed accordingly to address the needs of the particular database connection load.

For Fusion Application environments that leverage a firewall between the middle and database tiers, it is recommended to set the database listener setting SQLNET.EXPIRE_TIME. This parameter is defined in the sqlnet.ora file that is located in the TNS_ADMIN location. This parameter is used to set a time interval, in minutes, to determine how often to probe connections to verify their status.

Typically, a firewall is configured to terminate connections after it has been idle for a specified amount of time. Setting SQLNET.EXPIRE_TIME to an interval smaller than the firewall's will generate enough traffic within an appropriate interval to prevent the firewall from determining the connections are in an idle state and terminating these connections. A setting of SQLNET.EXPIRE_TIME = 10 is commonly used.

On occasion, due to network latency, connections can exceed the default timeout of 60 seconds. Typical symptoms of this include observing "TNS-12537: TNS:connection closed" errors in the database alert logs. One of the things that can resolve these issues is to increase the timeout parameters. A suggested value of INBOUND_CONNECT_TIMEOUT_<listenerName> = 120 (listener.ora) and SQLNET.INBOUND_CONNECT_TIMEOUT=130 (sqlnet.ora) is recommended.

If the Oracle Fusion Applications environment was installed using Real Application Clusters (RAC), refer to the Oracle Database 2 Day + Real Application Clusters Guide:

The sections below provide a few initial parameters and tips on using the Automatic Workload Repository (AWR) during any troubleshooting sessions.

RAC supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages in terms of availability, scalability, and low-cost computing.

• The network between the nodes of a RAC cluster must be private.

• Supported links: Gigabit Ethernet and InfiniBand

• Supported transport protocols: UDP or RDS

• Use multiple or dual-ported Network Interface Cards (NICs) for redundancy and increase bandwidth with NIC bonding.

• Set these parameters with respect to the ORACLE/OS release for Interconnect performance.

net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Use this information to identify if proper interconnect is used and picked up by ClusterWare.

• To identify the interconnect:

  show parameter cluster_interconnects
  or
  select name,ip_address,is_public from v$cluster_interconnects
  

  The Alert log will have an entry similar to this:

  Cluster communication is configured to use the following interface(s) for this instance
   123.45.67.89
  cluster interconnect IPC version:Oracle UDP/IP (generic)
  

• Identify network and contention issues.

  Check for "gc cr lost blocks" wait event in Automatic Workload Repository (AWR)/sysstats.

  If found, check for these errors on the NIC:

  • Dropped packets/fragments

  • Buffer overflows

  • Packet reassembly failures or timeouts

  • TX/RX errors

  Use these commands to find any errors:

  netstat -s 
  Ifconfig -a
  ORADEBUG
  

• Identify Interconnect performance from AWR.

  • Under Global Cache and Enqueue Services - Workload Characteristics

    Avg global cache cr block receive time (ms): should be <=15 ms

  • Global Cache and Enqueue Services - Messaging Statistics

    Avg message sent queue time on ksxp (ms): should be <1 ms

  • Under Interconnect Ping Latency Stats

    Avg Latency 8K msg should be close to Avg Latency 500B msg.

• These wait events from AWR and sysstat can indicate contention related to RAC.

  GC current block busy
  GV cr block busy
  GC current buffer busy
  GC buffer busy acquire/release
  

  See the Oracle Real Application Clusters Administration and Deployment Guide for a complete list.

  These wait events in the AWR indicate that there might be a Hot Block that is causing these wait events. From the AWR Segment Statistics, the objects can be found.

  Enq:TX Index Contention
  Gc buffer busy
  Gc current block busy
  Gc current split
  

  This issue will be noticed if multiple sessions are inserting into a single object or are using a sequence, and the indexed column is sequentially increasing. To address the specific issues:

  • Identify the indexes and Global Hash Partition them.

  • Increase the Sequence Cache if ordering is not a problem.