How to increase the Java Heap Size for OC4j instance (Oracle Application Server)

It is recommended that you increase the heap size -Xmx and -Xms by :

1. backup the opmn.xml
2. edit the opmn.xml and replace the old value with the new value 
3. restart the whole Oracle Application Server 

Ebusiness Suite Health Check : Discoverer Viewer Tuning

Discoverer Viewer Tuning

• Disable Query Prediction by setting QPPEnable=0 in pref.txt.
  
  • Set ObjectsAlwaysAccessible=1 to avoid extra workbook SQL validation, and related dictionary SQL.
    
  • Set the Query Governor option "Limit retrieved query data to:" to 100 rows.
    
  • Set the option "After opening a worksheet:" to "Don't run query."
    
  • Ensure Custom workbooks define mandatory parameters.
    

  
   

  
   

• Upgrade to Discoverer 10g (10.1.2.2)
  
  • Refer to MetaLink Note 313418.1
    
• Discoverer 10g Tuning
  
  • Enable SQL Flattening Feature - SQLFlatten=1
    
    • Significantly reduces sharable memory and parse time of
      

    SQL statements generated by Discoverer
    

  • Enable Item trimming - SQLItemTrim=1
    
    • Eliminates unused items from the SQL statement
      
  • Enable Join trimming - SQLJoinTrim=1
    
  • Eliminates joins which are not needed based on the selection.
    

Ebusiness Suite Health Check : AS

R12 Application Server Control

 

 

• Allows you to monitor JVM usage through a web interface
  
  • Number of sessions
    
  • Heap Usage
    
  • Number of active threads
    
  • Thread deadlocks
    
• With version 10.1.3.1 or above, you can monitor any JVMs in each group
  

 

• AOLJ Connection Pool (dbc configuration)
  
• Start with a reasonable default such as 100 or 200.
  
  • FND_JDBC_MAX_CONNECTIONS=200
    
  • Tune setting as per the amount of user concurrency (per JVM).
    
• AOLJ automatically decays idle connections and resizes the pool
  
• Reduce the number of OXTA JDBC connections
  
  • 11i: In jserv.properties file, add the following entries
    

    wrapper.bin.parameters=-DOXTAOutThreads=1
    

    wrapper.bin.parameters=-DOXTAInPoolSize=1
    

  • R12: In oc4j.properties     OXTAInPoolSize=1 OXTAOutThreads=1
    
  • If running multiple JVMs, the OXTA connections can be a
    

  significant portion of the total JDBC connections.
  

  • Automatically set in 11i10
    

 

 

• AOLJ Connection Pool (dbc configuration)
  
  • Disable sanity checks in production environments
    
    • FND_JDBC_USABLE_CHECK=false
      
    • FND_JDBC_PLSQL_RESET=false
      
• AOLJ Connection Pool Monitoring
  
  • Use the JSP (AoljDbcPoolStatus.jsp) to monitor the connection pool
    
    • Lists DBC configuration parameter settings.
      
    • Connection Statistics
      
    • Locked Connections
      
    • Available Connections
      
    • Closed Connections
      
    • Allows drill-downs to determine source of the connection.
      
    • For R12, should also inspect error logs for connection leaks
      

Ebusiness Suite Health Check : JSP’s

Java Server Pages (JSPs)

• Precompile the JSPs to avoid dynamic compilation.
  
• Users experience poor performance for the initial page loads.
  
• Potential deadlocks if multiple users attempt to compile the same JSP.
  
• Potential JVM death due to OutOfMemoryException during concurrent compilation.
  
• MetaLink Document 215268.1 provides the instructions and the patch reference for an automated script to perform the precompilation.
  
  • ojspCompile Script
    
  • With the latest version of ojspCompile, it takes 15 minutes to compile all the JSPs.
    

Ebusiness Suite Health Check : Jserv/OC4J

Application Tier –Jserv/OC4J

• Set logging level to warning
  
  • ApJServLogLevel warn (jserv.conf)
    
  • jserv.properties
    
    • log.channel.warning=true
      
    • log.channel.critical=true
      
    • log.channel.debug=false
      
• Review jserv and JVM log files for exceptions or errors
  
  • $IAS_CONFIG_HOME/Apache/Jserv/logs
    
    • jserv.log
      
    • mod_jserv.log
      
• $LOG_HOME/ora/10.1.3/opmn/OC4J~<coregroupname>
  

  ~default_group~<core#>
  

  
   

  • Enable verbose GC
    

1. java.sh or jserv.properties
   

• -verbose:gc
  
• XX:+PrintGCTimeStamps
  
• -XX:+PrintGCDetails
  

1. Direct verbosegc output to a specific file
   

• $IAS_CONFIG_HOME/Apache/Apache/bin/java.sh
  
• -Xloggc: $JVMLOGDIR/$jsgrpid.$jsgrpindex.g
  

 

• Ensure you are running a current version of the JDK
  
  • 304099.1
    
  • 304099.1
    
• Upgrade to JDK 6.0
  
  • 10% performance improvement.
    
  • Thread synchronization optimization.
    
  • Adaptive Spinning.
    
  • Large page support for x86 platform.
    
  • Faster HashMap and arraycopy implementations.
    
  • Updated class list for system jars.
    
  • Dtrace probes included in JVM.
    
  • Refer to MetaLink note 401561.1 for instructions on how to upgrade to JDK 6.0
    

 

• Set the servlet session timeout to 30 minutes
  
  • zone.properties
    
    • session.timeout=1800000
      
  • Larger timeout values increase overall JVM memory footprint
    
• Upgrade to ATG RUP7
  
  • Numerous performance and scalability fixes fixes
    

  
   

• OutofMemoryErrors
  
  • Trigger a heap dump when an OutofMemoryError occurs
    
    • -XX:+HeapDumpOnOutOfMemoryError
      
    • -XX:HeapDumpPath=<path>
      
  • Can use tools such as jhat to view the heap dump file and drill-down
    
    • jhat starts an http server on port 7000
      

Ebusiness Suite Health Check : APACHE

Apps Tier Performance Tuning: APACHE

• Use a hardware load balancer if available.
  
  • Select round-robin balancing method.
    
  • Enable cookie persistence.
    
  • Leverage hardware SSL acceleration.
    
    • Eliminates Apache SSL overhead
      

    
     

• Ensure keep alive is enabled
  
  • KeepAlive ON (httpd.conf)
    
  • KeepAliveTimeout 15 (httpd.conf
    

 

• Set logging level to warning.
  
  • httpd.conf
    
    • LogLevel warn.
      
    • SSLLogLevel war
      
• Disable DNS lookups
  
  • HostnameLookups Off (httpd.conf)
    
• Rotate logs
  
  • TransferLog "|/applmgr/prod/iAS/Apache/Apache/bin/rotatelogs /applmgr/prod/iAS/Apache/Apache/logs/access_log 86400
    
• Review access log file
  
  • Check for frequent downloads or timestamp checks of:
    
    • images, javascript, or style sheets.
      
    • JAR files
      
  • http codes
    
    • 200 (request for document)
      
    • 304 (timestamp check)
      
    • 404 (document not found)
      
  • Log entry format
    
    • <IP address> <date/time> <command> <URL> <status code> <bytes> <elapsed time.
      

Where to keep Concurrent Managers

·         Ideal location for Concurrent Managers:

Oracle recommends that CM should be at Database Tier, In some cases CM reside on Application Tier. If we relocate the CM to Database Tier it would boost the performance.

Historically the best practice was to install the Concurrent manager on the same server as the database itself to eliminate traffic on the network between the managers and their dedicated server process, however with the fast LAN availabilty now, the best practice is to configure the managers on separate middle tier servers, this gives you maximum flexibility to choose hardware platform and avoid to busy database server's ressources for processing database with no cycles needed for batch programs.

Note: How to move CM to another node: 373611.1

Oracle Ebusiness Suite Health Check : FORMS

Apps Tier Performance Tuning: FORMS

 

      Note:  Keep Forms Patchset Current (125767.1)

• Use Socket Mode (AutoConfig variable --> s_frmConnectMode=socket)
  

1. It reduces client network traffic (as compared to servlet mode)
   
2. Eleminates the need to run Forms Servlet JVMs
   

• Enable dead client detection FORMSS60_TIMEOUT(AutoConfig variable--> s_f60time)
  • Set to 5 or 10 minutes
    
• Use forms termination handler FORMS60_CATCHTERM(s_f60catchterm)
  • Set to 1                                                  
  
  
  
  Environment variable which restricts LOV fetch set  

1. FORMS60_RECORD_GROUP_MAX(PS 15 or higher)
   
2. Avoids large footprint of forms runtime process associated with none selective LOV queries.
   

• Purge/archive Forms runtime log files as part of Forms server restart:
  

1. $FORMS60_RTI_DIR/*.rti
   
2. $FORMS60_RTI_DIR/f69webmx_dump*
   
3. $FORMS60_TRACE_PATH/*.log
   

• Schedule purge program "Purge Obsolete Generic File Manager Data" if the forms Export function is being used.
  
• Enable Forms Dead Client Detection :
  Value specified in minutes : FORMS_TIMEOUT=10
  1: Terminates fwebmx processes for dead clients.
  2: Enable Forms Abnormal Termination Handle
  
•  Disable Cancel Query
  1:Cancel Query increases middle-tier CPU as well as DB CPU
   3:To Disable Cancel Query :  Set the Profile "FND: Enable Cancel Query" to 'No'
• Cancel Query
  

1. Useful for cases where users regularly or occasionally perform "accidental queries.
   
2. Not useful for cases where users have no intention of canceling the query or functionally require the results of the expensive query.
   
3. Increases forms and DB session CPU utilization.
   
4. Increases network traffic between client and forms tier.
   
5. Enable cancel query at the application or responsibility level (where required).  

• Review Network traffic
  

1. Reports cumulative bytes and round trips (between Forms process and the client applet) in the Forms message bar.
   
2. URL parameter netStats=true
   

• To reduce load on the database server, ensure users are optimally utilizing the professional Forms interfaces.
  

1. Avoid Blind queries
   
2. Provide selective criteria in Find windows and LOVs 

• Minimize network traffic and form open times
  
  • Avoid opening and closing forms across transactions
    
  • Combine forms from multiple-products onto a single menu
    
• Forms and DB Timings
  
  • Help_About Oracle Applications shows performance information
    
  • including time spent in the Form and the DB (11i10 & R12)
    

 


Forms (Tracing):

• If the form is slow, or a specific flow in the Form is slow, first generate a complete SQL trace using the Trace menu in Help-Diagnostics
  
• To analyze acitivity in the forms process, you can generate a Forms Runtime Diagnostics (FRD) Trace
  
  • Logon to Applications with the following parameters appended to the URL: play=&record=collect&log=/tmp/oeform.trc
    
  • Exit Apps completely after performing the transaction
    

 
 

Things you should know while you perform Database Health Check

OS:

No of CPU.
Memory.
Swap.
TMP.
User Profile.
Kernel Parameters.
OS patches.
OS Watcher.
iostat.
vmstat.
top.


Database:

AWR Reports/Stats Pack/ADDM/ASH.
Redo Switch Time/Redo Groups.
Redo Should'nt be on RAID 5.
Redo, Controlfile,dbfiles should not be on same mount point.
Data files and indexes should be separate.
Locate all important objects of important users.
System Architecture 32/64 bit.
db user default profile.
SGA.
Datafile and Segment |Shrinkig.

Oracle Ebusiness Suite Performance Tuning

Oracle Ebusiness Suite Performance Tuning

The Six Tuning Areas

 

Upgrade to latest Architecture & Techstack versions to get improved performance, this is especially true for JDBC.

Execute RDA where you find it necessary.

 

• TUNING THE CLIENT
  
  • For 11i 232833.1
    
  • For R12 405293.1
    
  
   
• TUNING THE MIDDLE-TIER
  
  • ONE OR MORE MACHINES?
    
  • THE JAVA VIRTUAL MACHINE
    
  • MAPPING USERS
    
  • ON-DEMAND DOWNLOADING
    
  • APACHE
    
  • FORMS RUNTIME DIAGNOSTICS
    
  • PERFORMANCE COLLECTOR
    
  
   
• TUNING THE DATABASE
  
  • Performance tuning flow chart 233112.1
    
  • Enhanced Explain Plan Utility 215187.1
    
  • STATSPACK/AWR/ADDM
    
  • COST AND RULE BASED OPTIMIZATION
    
  • MONITORING GROWTH
    
  • BLOCK SIZE AND RELATED SETUP
    
  • PACKAGE PINNING STRATEGY
    
  • SHARED POOL RESERVED ALLOCATION
    
  • LATCHING
    
  • HIT RATIO
    
  • MANAGING THE SYSTEM LOAD
    
  • Partitioning of custom tables 554539.1
    
  • Purging routines should be run on a systematic basis.
    
  • Database Initialization parameters 396009.1
    
• TUNING THE SERVER
  
  • DISK I/O: OFA VS RAW PARTITIONS
    
  • MEMORY BOTTLENECKS
    
  • PAGING AND SWAPPING BOTTLENECKS
    
  • CPU BOTTLENECKS
    
• TUNING THE SQL ACCESS PATHS
  
  • TRACING MODULES
    
  • ENCAPSULATING SQL
    
  • USING EVENT 10046
    
  • IDENTIFYING LITERAL SQL
    
  • ANALYZING LITERAL SQL
    
  • FINDING DYNAMIC FORM SQL
    
  • HIGH WATER MARKS
    
  • EXPENSIVE SQL
    
  • CUSTOMIZED VIEWS
    
  
   
• TUNING THE NETWORK
  
  • ISN'T BANDWIDTH ENOUGH?
    
  • PACKET SHAPERS
    
  • QUALITY OF SERVICE
    
  • ANALYZING THE NETWORK
    
  • DIAGNOSTIC TOOLS
    
  
   
• TUNING CONCURRENT PROCESSING
  
  • MONITORING CONCURRENT PROGRAMS
    
  • ENABLING SQL TRACE
    
  • APPLICATION OR PROGRAM TRACE/DEBUG PARAMETER
    
  • USING THE FDSQLCHK ENVIRONMENT VARIABLE
    
  • ENABLING TRACE FOR A REPORT
    
  • USING ORACLE TRACE
    
  • MONITORING CONCURRENT JOBS USING STANDARD SCRIPTS
    
  • ANALYZING HISTORICAL INFORMATION
    
  • ENHANCING CONCURRENT PROCESSING
    
  
   
• TUNING THE USERS
  
  • QUERYING FORMS
    
  • RECORD RETRIEVAL
    
  
   

SQL tracing for Oracle Database

Current Session Trace
To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;
You can also add an identifier to the trace file name for later identification:
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

You can find the trace file through below queries:

To find the trace file for your current session:
SELECT value FROM v$diag_info WHERE name = ‘Default Trace File’;
To find all trace files for the current instance:
SELECT value FROM v$diag_info WHERE name = ‘Diag Trace’;
To determine the trace file for each Oracle Database process:
SELECT pid, program, tracefile FROM v$process;
To find the trace file with specific name:
SQL> select value from v$diag_info where value like '%tracefilename%';
Stop session trace
To stop SQL tracing for the current session, execute:
ALTER SESSION SET sql_trace = false;

Tracing other user's sessions
DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions.
Steps:
• Get the SID and SERIAL# for the process you want to trace.
SQL> select sid, serial# from sys.v_$session where username = ‘MOHY’;

SID SERIAL#
---------- ----------
18 54
Enable tracing for your selected process:
SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(18, 54, true);
• Ask user to run just the necessary to demonstrate his problem.
To find if trace file has been created:
SQL> select value from v$diag_info where value like '%MOHY%';

• Disable tracing for your selected process:
SQL> execute dbms_system.set_sql_trace_in_session(18,54, false);

To enable SQL tracing for the entire database:
ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;
To stop:
ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;