Oracle® Fusion Applications Performance and Tuning Guide 11g Release 7 (11.1.7) Part Number E16686-08 |
|
|
PDF · Mobi · ePub |
This chapter discusses database tweaks that are specific to Oracle Fusion Applications.
This chapter contains the following sections:
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 |
|
Action |
|
Client_Identifier |
Application User Name |
See the "Monitoring and Tuning Oracle Database for Oracle Fusion Applications" chapter in the Oracle Fusion Applications Administrator's Guide.
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 |
---|---|
|
Enables or disables database auditing. Oracle Release 2 (11.2.0.2) default value: The suggested value for all Oracle Fusion Applications databases is |
|
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 |
|
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 The suggested value for all Oracle Fusion Applications databases is |
|
NLS_SORT specifies the collating sequence for ORDER BY queries.
Oracle Release 2 (11.2.0.2) default value: Derived from NLS_LANGUAGE. The suggested value for all Oracle Fusion Applications databases is |
|
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 The suggested value for all Oracle Fusion Applications databases is |
|
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 The suggested value for all Oracle Fusion Applications databases is |
|
This enables use of bitmap access paths for b-tree indexes. Oracle Release 2 (11.2.0.2) default value: The suggested value for all Oracle Fusion Applications databases is |
|
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: The suggested value for an Oracle Fusion Applications DW or IDM database (OID, OIM) is The suggested value for an Oracle Fusion Applications Starter or Production database is |
|
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: 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). |
|
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 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). |
|
Determines whether a cost-based query transformation will be applied to star queries. Oracle Release 2 (11.2.0.2) default value is The suggested value for an Oracle Fusion Applications DW database is |
|
Determines the degree to which Oracle must enforce query rewriting Oracle Release 2 (11.2.0.2) default value is The suggested value for an Oracle Fusion Applications DW database is |
|
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 The suggested value for an Oracle Fusion Applications DW database is The suggested value for all Oracle Fusion Applications IDM databases is The suggested value for all Oracle Fusion Applications Production databases is |
|
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 The suggested value for an Oracle Fusion Applications DW database is The suggested value for all Oracle Fusion Applications IDM databases is The suggested value for all Oracle Fusion Applications Production databases is |
|
Controls whether I/O to datafiles, control files, and logfiles is asynchronous. Oracle Release 2 (11.2.0.2) default value is The suggested value for all Oracle Fusion Applications databases is |
|
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 |
|
Specifies whether or not to treat LOB files as SecureFiles. Oracle Release 2 (11.2.0.2) default value is The suggested value for all Oracle Fusion Applications databases is |
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.
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.
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.
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.
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.
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.
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
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');
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.
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.
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.
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.
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.
Oracle Fusion Applications use the PS_TXN table to store the intermediate processing state. When there are many concurrent users, this table receives a high number of inserts and could suffer from concurrency issues. To detect this contention issue, check the wait event "enq: HW contention" in the AWR report of the database.
Follow the steps outlined in note ID 1444959.1 in My Oracle Support to alleviate the contention.
By default, Oracle enables various AUTOTASK clients and preconfigures maintenance windows that can be optimized to reduce unnecessary overhead.
Starting with Oracle Release 11.2.0.2, you should evaluate these AUTOTASKs. We recommended that they be disabled if the respective features are not being used.
AUTO SPACE ADVISOR
SQL TUNING ADVISOR
Starting with Oracle Release 11.2.0.2, various weekday and weekend windows are preconfigured with durations of 4 and 20 hours respectively. You should adjust these windows to run during time frames with minimal application workloads. In addition, you should reduce the weekend windows from the default configuration to a duration lower than the "byhour" value defined in the maintenance window's "repeat interval" settings. These values can be reviewed by querying the DBA_SCHEDULER_WINDOWS view. A suggested maintenance window configuration is to enable both the WEEKNIGHT_WINDOW and WEEKEND_WINDOW with a duration of 4 hours each.