Skip Headers
Oracle® Fusion Applications Performance and Tuning Guide
11g Release 1 (11.1.4)

Part Number E16686-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
PDF · Mobi · ePub

3 Tuning the Database

This chapter discusses database tweaks that are specific to Oracle Fusion Applications.

This chapter contains the following sections:

3.1 Introduction

Oracle Fusion Applications set values on a number of v$session attributes to indicate how the connection is being used. When looking at a connection consuming high CPU on the database, or when trying to understand what connections are used for what processes, these attribute values may help provide the answer.

Table 3-1 v$session Attribute Values

Attribute in v$session Value Being Set

Process

Data Source Name (such as ApplicationDB)

Program

WebLogic Server Domain plus Managed Server name, prefixed by DS (such as DS/FinancialDomain/AccountsReceivableServer_1)

Module

  • For ADF: ADF BC application module name

  • For ESS:

    • For Java job type, the class name (minus oracle.apps)

    • For PL/SQL, the package and procedure name (such as mypkg.myproc)

    • For other job types, the executable name should be passed

  • For BI Publisher: the name of the report

Action

  • For ADF: jspx name

  • For ESS: jobDefinitionName

  • For BI Publisher, if request is submitted:

    • Using ESS scheduler: ESS Job Definition Name

    • Using BIP Scheduler Job: BIP Job Name submitted by the user

    • Using BIP online: static string "BIP:Online"

    • Using BIP Webservices: the name of the webservices

Client_Identifier

Application User Name


3.1.1 Monitoring the Oracle Fusion Applications Database

See the "Monitoring and Tuning Oracle Database for Oracle Fusion Applications" chapter in the Oracle Fusion Applications Administrator's Guide.

3.2 Tuning the Database

Table 3-2 provides the database initialization parameter guidelines for Oracle Fusion Applications. Within the Oracle Fusion Applications ecosystem, there exist four types of databases. They are Online Transaction Processing (OLTP) Starter or Production configuration, IDM, and 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 your 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 3-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, you may consider setting a minimum value for SHARED_POOL_SIZE and DB_CACHE_SIZE to minimize frequent resizing.

Table 3-2 Common init.ora Parameters

Parameter Name Description, Default value and suggested start value

audit_trail

Enables or disables database auditing.

Oracle Release 2 (11.2.0.2) default value: DB

The suggested value for all Oracle Fusion Applications databases is NONE.

_fix_control

This parameter addresses the dynamic sampling of global temporary tables.

Oracle Release 2 (11.2.0.2) default value: N/A

The suggested value for an Oracle Fusion Applications Starter or Production database is 6708183:ON

plsql_code_type

PLSQL_CODE_TYPE specifies the compilation mode for PL/SQL library units.

INTERPRETED: PL/SQL library units are compiled to PL/SQL bytecode format. Such modules are executed by the PL/SQL interpreter engine.

NATIVE: PL/SQL library units are compiled to native (machine) code. Such modules are executed natively without incurring any interpreter impacts.

Oracle Release 2 (11.2.0.2) default value is INTERPRETED.

The suggested value for all Oracle Fusion Applications databases is NATIVE.

nls_sort

NLS_SORT specifies the collating sequence for ORDER BY queries.

  • If the value is set to BINARY, the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires fewer system resources).

  • If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

Oracle Release 2 (11.2.0.2) default value: Derived from NLS_LANGUAGE.

The suggested value for all Oracle Fusion Applications databases is BINARY.

open_cursors

Specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors.

Oracle Release 2 (11.2.0.2) default value is 50.

The suggested value for all Oracle Fusion Applications databases is 500.

session_cached_cursors

Specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls find the cursor in the cache and do not reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.

This parameter also constrains the size of the PL/SQL cursor cache that PL/SQL uses to avoid having to re-parse as statements are re-executed by a user.

Oracle Release 2 (11.2.0.2) default value is 50.

The suggested value for all Oracle Fusion Applications databases is 500.

_b_tree_bitmap_plans

This enables use of bitmap access paths for b-tree indexes.

Oracle Release 2 (11.2.0.2) default value: TRUE

The suggested value for all Oracle Fusion Applications databases is FALSE.

processes

Sets the maximum number of operating system processes that can be connected to Oracle concurrently. The value of this parameter must account for Oracle background processes. SESSIONS parameter is deduced from this value.

Oracle Release 2 (11.2.0.2) default value: 100

The suggested value for an Oracle Fusion Applications DW or IDM database (OID, OIM) is 2500.

The suggested value for an Oracle Fusion Applications Starter or Production database is 5000.

sga_target

Setting this parameter to a nonzero value enables Automatic Shared Memory Management. Consider using automatic memory management, both to simplify configuration and to improve performance.

Oracle Release 2 (11.2.0.2) default value: 0

The suggested value for Oracle Fusion Applications IDM databases (OID, OIM) is 4 GB.

The suggested value for an Oracle Fusion Applications Starter database is 9 GB.

The suggested value for an Oracle Fusion Applications DW database is 8 GB.

The suggested value for an Oracle Fusion Applications Production database is 18 GB (based on reference hardware having 32 GB of physical memory).

pga_aggregate_target

Specifies the target aggregate Program Global Area (PGA) memory available to all server processes attached to the instance.

Oracle Release 2 (11.2.0.2) default value is 0

The suggested value for Oracle Fusion Applications IDM databases (OID, OIM) is 2 GB.

The suggested value for an Oracle Fusion Applications Starter database is 4 GB.

The suggested value for an Oracle Fusion Applications DW database is 4 GB.

The suggested value for an Oracle Fusion Applications Production database is 8 GB (based on reference hardware having 32 GB of physical memory).

star_transformation_enabled

Determines whether a cost-based query transformation will be applied to star queries.

Oracle Release 2 (11.2.0.2) default value is FALSE

The suggested value for an Oracle Fusion Applications DW database is TRUE.

query_rewrite_integrity

Determines the degree to which Oracle must enforce query rewriting

Oracle Release 2 (11.2.0.2) default value is ENFORCED

The suggested value for an Oracle Fusion Applications DW database is TRUSTED.

parallel_max_servers

Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance.

Oracle Release 2 (11.2.0.2) default value is Derived

The suggested value for an Oracle Fusion Applications DW database is 16.

job_queue_processes

Specifies the maximum number of job slaves per instance that can be created for the execution of DBMS_JOB jobs and Oracle Scheduler (DBMS_SCHEDULER) jobs.

Oracle Release 2 (11.2.0.2) default value is 1000

The suggested value for an Oracle Fusion Applications DW database is 10.

disk_asynch_io

Controls whether I/O to datafiles, control files, and logfiles is asynchronous.

Oracle Release 2 (11.2.0.2) default value is TRUE

The suggested value for all Oracle Fusion Applications databases is TRUE

filesystemio_options

Specifies I/O on file system files to be asynchronous I/O or direct I/O.

Oracle Release 2 (11.2.0.2) default value: This parameter is platform-specific and has a default value that is best for a particular platform.

The suggested value for all Oracle Fusion Applications databases is to use a value that is supported by the specific platform. Do not set it to NONE.


3.2.1 How to Configure the Database for Performance

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. This is an empirical task. The recommendations in this section are intended to provide initial guidance in these areas.

UNDO

It is recommended 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.

TABLEPSACES

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.

TEMP

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.

REDO LOGS

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. You can obtain sizing advice on the Redo Log Groups page of Fusion Applications Control. In addition, depending on your storage configuration and performance characteristics, you may need 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.

3.2.2 How to Configure Kernel Parameters

The parameters listed here are only a subset of the parameters needed to properly configure your database environment. Consult the Oracle Database Installation Guide for your specific operating system to determine the overall requirements for the kernel parameter settings. Installation guides are available in the Oracle Database Documentation Library.

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.

3.2.3 How to Configure the Database Listener

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 your particular database connection load.

SQLNET.EXPIRE_TIME Parameter

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.

INBOUND_CONNECT_TIMEOUT Parameter

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.

3.2.4 How to Tune the Real Application Cluster (RAC)

Oracle Real Application Clusters (RAC) is a cluster database with a shared cache architecture that overcomes the limitations of a traditional shared-nothing approach to provide highly scalable and available database solutions for all business applications.

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.

RAC provides:

  • High Availability

    RAC provides the highest availability for applications by removing the single server as a single point of failure.

  • Flexible Scalability

    When more processing power and resources are needed, adding a server to the database cluster without taking users offline will gain horizontal scalability.

  • Automatic Workload Management

    Application workloads can be individually managed and controlled using managed services. Users connecting to a service are load balanced across the server pool.

Initial Setting

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

Supported links: Giga-bit 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

Troubleshooting

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, you can find the objects.

    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.

3.2.5 How to Optimize SQL Statements

Oracle Fusion Applications use Cost Based Optimization (CBO) to choose the most efficient execution plan for SQL statements. Using this approach, the optimizer determines the most optimal execution plan by costing available access paths and factoring information based on statistics for the schema objects accessed by the SQL statement.

3.2.5.1 Collecting Optimizer Statistics

For the query optimizer to produce an optimal execution plan, the statistics in the data dictionary should accurately reflect the volume and data distribution of the tables and indexes. To this end, database statistics should be refreshed periodically. However, that does not necessarily imply that you should gather statistics frequently. Systems that are close to going live typically experience inserts of a large amount of data, as data from legacy systems is migrated. In that scenario, the statistics would probably need to be refreshed quite frequently (for instance, after each major load), as large loads could change the data distribution significantly. Once the system reaches steady state, the frequency of statistics collection at the schema and database level should be reduced to approximately once a month. However, statistics on some volatile tables can be gathered as frequently as required.

Oracle Fusion Applications has an automated way of gathering the statistics. It uses DBMS_STATS with the AutoTask feature. For more information about AutoTask, see the "Enabling and Disabling Automatic Optimizer Statistics Collection" section in the Oracle Database Performance Tuning Guide.

Gathering Statistics for Cost Based Optimizer

Oracle Fusion Applications use automatic optimizer statistics collection. That is, the database automatically gathers the optimizer statistics. Automatic statistics eliminates any manual intervention, thereby significantly reducing the onus on a system administrator. For a DW database, optimizer statistics are collected as part of ETL and BI apps, so it is recommended not to use automatic statistics gathering.

Table Statistics and Number of Distinct Values (NDV)

For versions prior to Oracle 12g, a database performance problem can occur by missing a non-popular value in the sample created from the table, leading to a frequency histogram where the number of buckets is less than the number of values.

To work around this problem, set this entry in init.ora:

_fix_control='5483301:off','6708183:ON';

Manual Statistics Gathering

Automatic optimizer statistics collection is sufficient for most of the database objects, but in a database that is close to going live or for tables that are modified significantly, manual statistic gathering is needed.

In these cases, use the DBMS_STATS.GATHER_TABLE_STATS procedure. Do not explicitly set any parameters apart from owner and table_name.

All the parameters, such as estimate_percent, parallel degree and method_opt, are globally seeded for Oracle Fusion database tables. Therefore, there is no need to use them when manually gathering the statistics. See Figure 3-1.

Figure 3-1 Using the Fusion Applications Control to Gather the Statistics

Using the Fusion Applications Control to Gather Statistics

Histograms

All the columns that need a histogram are seeded using DBMS_STATS.SET_TABLE_PREFS and the automatic optimizer statistic collection will create histograms for all the pre-identified columns apart from the columns identified by DBMS_STATS under size auto.

You can use DBMS_STATS.SET_TABLE_PREFS to manually seed histograms. See Figure 3-2

Figure 3-2 Using Oracle Enterprise Manager Cloud Control

Using Oracle Enterprise Manager Cloud Control

MDS DB: Collecting database statistics for optimizing the MDS database repository performance

Ensure auto-stats collection is enabled.

In most cases, the first 32 characters of PATH_FULLNAME in the MDS_PATHS table are the same. You can prevent the database putting them in the same section of the histogram by doing the following:

  • Drop the histogram for PATH_FULLNAME column by executing the following as system.

    execute dbms_stats.delete_column_stats(ownname=>'mdsSchemaOwner', tabname=>'MDS_PATHS', colname=>'PATH_FULLNAME', col_stat_type=> 'HISTOGRAM');
    
  • Set table preferences to exclude collecting histogram for the PATH_FULLNAME column.

    execute dbms_stats.set_table_prefs(mdsSchemaOwner, 'MDS_PATHS', 'METHOD_OPT', 'FOR COLUMNS SIZE 1 PATH_FULLNAME');
    

3.2.5.2 Pinning Packages and Cursors

Pinning the objects in the shared pool reduces the possibility of ora-4031 error messages, and increases the performance of the OLTP applications.

Objects are cached in a library cache that uses a Least Recently Used (LRU) algorithm to flush the objects. The problem worsens if these large library cache objects are executed only infrequently. That is, if they are loaded into the library cache whenever required if they have aged out of the cache. This causes most of the problems leading to ora-4031 errors and poor performance for UI-specific flows.

Consider a large package, or any object, that has to be loaded into the shared pool. Large PL/SQL objects present particular challenges. The database has to search for free space for the object. If it cannot get enough contiguous space, it will free many small objects to satisfy the request. If several large objects need to be loaded, the database has to throw out many small objects in the shared pool. Finding candidate objects and freeing memory is very costly. These tasks will affect CPU resources.

The same situation applies to SQL statements that are executed occasionally but are very important from a response time perspective. For example, a statement that is part of a CEO's dashboard is executed once every 3 to 4 hours, but it takes a long time for parsing.

A common question asked is how to keep the LRU algorithm from forcing objects out of the shared pool. This is mainly useful in cases where a SQL statement that is very expensive to parse may just be executed once every 12 hours, such as a dashboard SQL statement. But due to the LRU algorithm, the results are flushed before the next execution. In these cases, you may prefer to pin the cursor so that subsequent parse times are reduced.

The DBMS_SHARED_POOL package provides procedures to facilitate this. With this, you have an easy way to ensure that the specified cursors always remain in the Most Recently Used (MRU) end of the cache. This prevents the cursor from being paged out and then re-parsed upon re-load. The DBMS_SHARED_POOL.KEEP procedure is used to pin the cursor and DBMS_SHARED_POOL.UNKEEP is used to unpin the cursor.

3.2.6 How to Configure the Database Resource Manager

To better ensure system stability during periods of high system load and prevent runaway queries, Oracle Database Resource Manager can be enabled on the Oracle Fusion Applications database instance.

For more details about the feature, including an explanation on when enabling this feature is desired, see the "Managing Resources with Oracle Database Resource Manager" chapter in the Oracle Database Administrator's Guide.

Use the resource manager to:

  • Assign connections to different resource consumer groups.

  • Create directives to manage resource allocations for connections in different resource consumer groups.

3.2.6.1 Assigning Connections to Resource Consumer Groups

There are multiple ways a connection can be assigned to a resource consumer group. See the Oracle Database Administrator's Guide for complete details.

See Table 3-1 for connection attributes settings for Oracle Fusion Applications.

3.2.6.2 Using the Oracle Database Resource Manager

This section shows how to use the Oracle Database Resource Manager for Oracle Fusion Applications. Connection attribute mappings, and explicit assignment using a database login trigger are used to assign connections to different resource consumer groups. The plan is enabled by default for a newly-provisioned instance. If you are upgrading from earlier releases, you can run the scripts listed in "Enabling the Resource Plan" to create the same resource plan that is enabled by default in a freshly-provisioned database. The resource plan is called FUSIONAPPS_PLAN.

The plan has two resource consumer groups:

  • FUSIONAPPS_ONLINE_GROUP - Connections used for servicing ADF UI pages are assigned to this resource group via a login trigger.

  • FUSIONAPPS_BATCH_GROUP - All other connections used by Fusion Applications are assigned to this group.

Resource Directives

These resource directives are defined:

  • Connections in FUSIONAPPS_ONLINE_GROUP get priority for 45% of the CPU. Parallel query is disabled, and any queries consuming more than 120s of CPU or resulting in more than 10GB of I/O will be canceled.

  • Connections in FUSIONAPPS_BATCH_GROUP get priority for 35% of the CPU. There are no other resource restrictions.

  • Connections used for sysdba activities get priority for 15% of the CPU. There are no other resource restrictions.

  • All non-Oracle Fusion applications connections get priority for 5% of the CPU.

    Note that if a particular resource group does not consume all its allocated CPU, the unused CPU can be used by other resource consumer groups.

Mapping Connections

To map connections to resource consumer groups, this approach is used:

  • All schemas containing the string FUSION are mapped to FUSIONAPPS_BATCH_GROUP.

  • A login trigger is used to check connections to schema FUSION_RUNTIME. If the connection is coming from an ADF server, assign the connection to FUSIONAPPS_ONLINE_GROUP.

Enabling the Resource Plan

To enable this resource plan, follow these steps:

  • Run this script using sqlplus as sysdba to create the resource plan, the resource consumer groups and the resource directives.

    begin
      DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
      DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
     
      begin
        DBMS_RESOURCE_MANAGER.DELETE_PLAN('FUSIONAPPS_PLAN');
      exception
        when others then
          null;
      end;
     
      begin
        dbms_resource_manager.delete_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_ONLINE_GROUP');
      exception
        when others then
          null;
      end;
     
      begin
        dbms_resource_manager.delete_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_BATCH_GROUP');
      exception
        when others then
          null;
      end;
     
      dbms_resource_manager.create_consumer_group(CONSUMER_GROUP => 'FUSIONAPPS_ONLINE_GROUP', COMMENT => 'Consumer Group for online users');
      dbms_resource_manager.create_consumer_group(CONSUMER_GROUP =>'FUSIONAPPS_BATCH_GROUP', COMMENT => 'Consumer Group for batch');
     
      dbms_resource_manager.create_plan(PLAN => 'FUSIONAPPS_PLAN',
      COMMENT => 'Fusion Applications Resource Plan');
     
      dbms_resource_manager.create_plan_directive(
        plan => 'FUSIONAPPS_PLAN',
        group_or_subplan => 'FUSIONAPPS_ONLINE_GROUP',
        comment => 'Online users at level 1',
        mgmt_p1 => 45,
        parallel_degree_limit_p1 => 0,
        switch_time => 120,
        switch_io_megabytes => 10000,
        switch_group => 'CANCEL_SQL');
     
      dbms_resource_manager.create_plan_directive(
        plan => 'FUSIONAPPS_PLAN',
        group_or_subplan => 'FUSIONAPPS_BATCH_GROUP',
        comment => 'Batch users at level 1',
        mgmt_p1 => 35);
     
      dbms_resource_manager.create_plan_directive(
        plan => 'FUSIONAPPS_PLAN',
        group_or_subplan => 'SYS_GROUP',
        comment => 'System administrator group at level 1',
        mgmt_p1 => 15);
     
      dbms_resource_manager.create_plan_directive(
        plan => 'FUSIONAPPS_PLAN',
        group_or_subplan => 'OTHER_GROUPS',
        comment => 'Other users at level 1',
        mgmt_p1 => 5);
     
      for rec in (select username from dba_users where username like '%FUSION%') loop
        if (rec.username <> 'FUSION_READ_ONLY') then
          dbms_resource_manager.set_consumer_group_mapping('ORACLE_USER',rec.username,'FUSIONAPPS_BATCH_GROUP');
        end if;
      end loop;
     
      begin
        dbms_resource_manager.set_consumer_group_mapping('ORACLE_USER','SEARCHSYS','FUSIONAPPS_BATCH_GROUP');
      exception
        when others then
          null;
      end;
     
      DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
      DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
      DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
      dbms_resource_manager_privs.grant_switch_consumer_group('PUBLIC','FUSIONAPPS_ONLINE_GROUP',FALSE);
      dbms_resource_manager_privs.grant_switch_consumer_group('PUBLIC','FUSIONAPPS_BATCH_GROUP',FALSE);
    end;
    /
    
  • As sysdba, issue the following command to enable the resource plan:

    (if using spfile)
     ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = FUSIONAPPS_PLAN SCOPE = BOTH;
     
    (if not using spfile)
     ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = FUSIONAPPS_PLAN;
    
  • Run this script using sqlplus as sysdba to create the login trigger:

    CREATE OR REPLACE TRIGGER fusion_resource_trigger
       AFTER logon ON fusion_runtime.schema
    declare
    login_sid pls_integer;
    login_program varchar2(40);
    old_plan21    varchar2(44);
      begin
     
    select 'Y' into login_program from dual where exists
    (select program from v$session where audsid=userenv('sessionid') and
      process like 'ApplicationDB%' and
      ( program is not null and
        program not like 'JDBC Thin Client' and
        program not like '%ess_server%' and
        program not like '%soa_server%' and
        program not like '%SearchServer%' and
        program not like '%search_server%' and
        program not like '%odi_server%' and
        program not like '%bi_server%'
        ) );
     
    if login_program = 'Y'
    then
    dbms_session.switch_current_consumer_group('FUSIONAPPS_ONLINE_GROUP',old_plan21,false);
    else
    null;
    end if;
    EXCEPTION
    when NO_DATA_FOUND
    then
    null;
      end;
      /
    
  • If applicable, change the init.ora parameter to enable the resource plan:

    RESOURCE_MANAGER_PLAN = FUSIONAPPS_PLAN
    
  • Bounce all middle tiers.

3.2.6.3 Monitoring the Resource Manager

For details, see the Oracle Database Administrator's Guide. In particular, v$rsrc_consumer_groups provides information on CPU wait time and the number of SQL statements canceled.

"Scripts and Tips for Monitoring CPU Resource Manager", which is note ID 1338988.1 in My Oracle Support, also has information about monitoring CPU usage for different resource consumer groups. If a resource consumer group is spending significant time waiting for CPU, review the resource directives and see if the waits are expected and if any adjustments are needed.