Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :-

Friday, August 3, 2012

Oracle Apps R12 -Few Middle Tier Tuning Tips

Applications Tier Tuning In R12

Check for necessary upgrades required in tech stack
a) Upgrade to the latest certified technology stack

OC4J: OracleAS 10g, Metalink Note 454811.1

Forms: OracleAS, Metalink Note 437878.1

ATG: RUP 12.0.4 (Patch 6272680)

b) Upgrade to the latest JDK

Metalink Note 418664.1 for using Java with R12

Metalink Note 300482.1 for the latest certifications

c) Apps Version JDK Version Metalink Note


a) Deploy with socket mode for internal users

R12: Refer to Note 384241.1.

b) Enable Forms Dead Client Detection

Value specified in minutes: FORMS_TIMEOUT=10

In context file search for the following l
FORMS_TIMEOUT oa_var="s_forms_time"

Terminates fwebmx processes for dead clients.

c) Enable Forms Abnormal Termination Handler


    FORMS_CATCHTERM oa_var="s_forms_catchterm"

d) Disable Cancel Query
Cancel Query increases middle-tier CPU as well as DB CPU

To Disable Cancel Query

Set the Profile “FND: Enable Cancel Query” to ‘No’

In context file search for the following line

FORMS_BLOCKING_LONGLIST oa_var="s_forms_blocklist"

Note: - For any forms related issues check for errors in the following log file




• Only one JVM per 2 CPUs

• No more than one JVM/CPU

• No more than 100 concurrent users per JVM

Response Time/CPU Usage

Customer complains about response time?

Solution: - Configure Apache to log the time it takes to service a request

Edit: $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf

LogFormat "%h %T

Logs: $LOG_HOME/ora/10.1.3/Apache/access_log*

Example: -

Log Files to check


For example:

• Status 500 (internal server error) may typically be seen for a JServ request and often means the JVM has some kind of problem or has died.

For example: This entry may indicate that the JServ JVM is not responding to any requests: - - [21/Jun/2006:13:25:30 +0100] "POST /oa_servlet/actions/processApplicantSearch HTTP/1.1" 500 0

• Status 403 (forbidden) could typically be seen for oprocmgr

For example: This entry in access_log may indicate a problem with system configuration (oprocmgr.conf): requests and often means there is a misconfiguration that needs to be resolved. - - [21/Jun/2006:13:25:30 +0100] "GET /oprocmgr-service?cmd=Register&index=0&modName=JServ

&grpName=OACoreGroup&port=16000 HTTP/1.1" 403 226

Run the below script to search for the above errors from access_log

## Start of script


## Check for HTTP statuses in 400 or 500 range for JServ

## or PLSQL requests only


awk ' $9>=400 && $9<=599 { print $0 }' access_log*
grep -e "servlet" -e "\/pls\/"
grep -v .gif ## ## Check for requests taking more than 30 seconds to be returned ## awk ' $11>30 {print $0} ' access_log*


## This one is not an exception report, you need to manually check

## Look for when the JVMs are restarting


grep "GET /oprocmgr-service?cmd=Register" access_log*


## End of script

Framework applications
If there are no database-related issues, then you need to analyze the JVM

Techniques you can use:



94562.018: [GC 670227K->595360K(892672K), 0.0221060 secs]

94617.600: [GC 672480K->617324K(799104K), 0.0307160 secs]

94648.483: [GC 694444K->623826K(872384K), 0.0405620 secs]

94706.754: [Full GC 756173K->264184K(790720K), 0.8990440 secs]

94718.575: [GC 458782K->424403K(737536K), 0.0471040 secs]

94740.380: [GC 501646K->436633K(793600K), 0.0656750 secs]

94817.197: [GC 512473K->441116K(795136K), 0.0749340 secs]

Description: -

Here the first column 94562.018, 94617.600 show the time in seconds when GC happened. Inside the square bracket it indicates whether it’s a minor GC or FULL GC. That is followed by some number 670227K->595360K. The number on left side of -> indicate original size of live objects before GC and number after -> indicate size of live objects after GC. Number in the bracket (892672K) indicates total size of live objects allocated. Number after comma indicates time it took to complete garbage collection. For example in the first rows it took 0.0221060 secs for completing GC.

Review the frequency of collections; especially major collections (i.e. Full GC)

Recommendations to be given

a) Enable verbose GC to tune heap sizes based on the GC traffic
b) If full GCs are too frequent, consider increasing -Xms and -Xmx GC tuning
c) Bigger heaps => GC will take longer
d) Longer GCs => users may experience pauses
e) For the OACoreGroup JVMs start with the lower of the following two values:

Number of cores on the middle tier

Peak Concurrent users / 100

For example:

If you have 2 x Dual Core CPUs in your server and have 500 peak users, then 4 JVMs is the recommended starting point, since the number of cores is the lower number. However, if you only had 300 peak users, then you would configure 3 JVMs as a starting point as this is now the lower figure.

f) Size your maximum heap size as either 512 MB or 1 GB. If you start with 512 MB and find that more memory is required, increase to a maximum of 1 GB. If more memory than 1 GB is required, then add another JVM instead (free physical memory allowing) to increase the total memory available overall.

For example:

You are using 1 x JVM with 1 GB heap size and find you need to increase memory. Configure your system for 2 JVMs, each with 750 MB heap size, thus providing 1.5 GB in total.


Creating Reports Using Oracle BI Publisher & APEX integration

I had recently explored the way to use BI publisher for creating reports and intgrating it with any APEX application regions.

(a) APEX - BI Publisher Integration Requirements
1) Download & Install Oracle BI publisher(trial version of course) from OTN, It needs a license.

2) Download & Install Oracle BI publisher desktop again from OTN. (After this install you can see an additional BI pulisher plugin in MS word).

3) You need to start the BI pulisher using the start icon that is created on ur desktop after step-2.
(BI Publisher will be hosted by weblogic server..You will notice this when you start it)

Now you are ready to create reports in various formats, like PDF, Excel, HTML, RTF etc....

(b) Creating Reports and Integrating it with APEX Applications
1) Login to your APEX workspace, and then click on the application to which reports are to be added.

2) Click on the page with your reporting regions.

3) Now select the region for which you intend to have a report download in PDF format.

4) The region source query needs to be copied to a notepad

5) Now again go back to the applications and select shared components.

6) Under shared components, click on report queries and create a report query with the query you saved in step-4

7)While creating the report query, download the XML output of the report query and save it, Further, it asks for the report layout and the source for it. Pause here the activity. (We are continuing to create a RTF template for our report using MS word)

8) Now open MS Word, Under BI Publisher tab. Click on Sample XML, and select your sample XML you saved on step-7, The XML data will be loaded into the word.

9) Now using table wizard, design your report layout as you require.

10) After creating the report table, you can preview the report(of course with actual data), in various formats.

11) Save the word report layout created as turnover.rtf.

12) Again go back to step-7, Under shared components page of the application, select the turnover.rtf file created in step-11 as the report layout and click finish for creating the report queries. (Save the report URL soemthing like below)

13) Now, goto the page definition of with the reports region and create a button and a branch as shown below.(Branch should be to the Print URL as shown above)
14) Now, you are all set to test your report, run your reports page in your application, and just click your button to view your repor tin PDF format.