Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, June 30, 2009

Error Message: OutOfMemory What does it mean ?

In the SUN virtual machine there are two reasons why an OutOfMemoryError might occur, because the SUN VM uses two different memory blocks for allocating objects and for loading classes. So possible reasons are:

1. The VM cannot allocate memory for a new object because there is no space in the heap available.

2. A classloader tries to load a new class but cannot do so because
the perm space of the memory is full.

Monday, June 29, 2009

RMAN works best with RBO

Eventhough Oracle forced everyone to move to Cost Based Optimizer (CBO), their own tools continue to use RBO. RMAN is a classic example. Refer to Metalink Note 375386.1 which describes performance problems selecting from V$RMAN_STATUS. The cause is given as bug 5247609 and workaround provided is:

Add the following to your rman script:

sql "alter session set optimizer_mode=RULE";

A few customers have found that using RULE does not workaround this but gathering fixed statistics does:

SQL> exec dbms_stats.gather_fixed_objects_stats()

Friday, June 26, 2009

Oracle and the Law of Attraction

If you have seen the movie "The Secret", you would be well aware of Law of Attraction. One of the tenets of Law of Attraction is that you should always focus on what you want and never on what you do not want. The universe is all inclusive and doesn't exclude. So if you say you want money, you'll get money, but if you say I don't want poverty, you'll get poverty. If you say I don't want disease, you get disease. So you should say that I want wellness instead of saying I don't want disease.

During the HotSoS Oracle performance management, I learnt that Oracle's optimizer also follows similar principle.

The clauses NOT EXISTS and NOT IN take more time and do more LIOs. This is because the optimizer will grab all rows and check each row for the NOT condition and drop it. This always takes more time. For example

select name,salary from emp where salary <>50000 is going to take more time than

select name,salary from emp where salary < 50000

You should avoid these NOT clauses and try to create your query with EXISTS or IN.

Thursday, June 25, 2009

Hotsos PRO training Day 3/3

Today we learnt how to read raw trace files and manually calculate what HotSoS profiler does programmatically.

Metalink Note 39817.1 explains the same. In Oracle 10g we have a new package DBMS_MONITOR through which trace should be done.

We learnt to analyze trace files and did various exercises with profiler reports.

Ric said, he has stopped working with tkprof ever since he got introduced to HotSos profiler and that if he ever were to go independent, he would sell his soul for keeping the HotSos profiler in his bag of tools.

HotSoS profiler is the best. I have compared the outputs of other profilers like Oracle's trace analyzer and OraSRP. However none of the alternatives provides the depth of HotSoS profiler.

Hotsos PRO training Day 2/3

Today we learnt how to collect trace data with proper instrumentation, Hotsos free package ILO for implementing instrumentation in PL/SQL code was shown. We learnt how to read trace files.

One of the students, Rob had brought a production trace file. Our instructor, Ric, generated the profiler report for that trace file. There were 3 statements:

1. UPDATE 30% time
2. INSERT 25% time
3. UPDATE 27% time

Ric said that the first statement looked optimized as it was doing 1 LIO per execution which is very good. However, it was doing this thousands of time. In HotSos World, the best way to optimize something is not to do it. Rob explained that they were updating all the rows with very same data everytime. Ric told Rob to explore MERGE command which will combine INSERT and UPDATE and avoid the unnecessary UPDATE of very same data again.

Tuesday, June 23, 2009

Hotsos PRO training Day 1/3

I am attending the world famous 3 day training on Oracle Performance from Hotsos. Our instructor is Ric Van Dyke. We have 6 students in the class. Ric mentioned that he was our instructor and not a teacher as an instructor presents the information, but a teacher makes sure that the student learns whatever is taught. 3 days is a short period to teach.

7 years back when I was in New Delhi, India, I had read Cary Milsap's book Optimizing Oracle Performance from cover to cover. It had enlightened me about Oracle performance.

Here are some of the things we learnt today apart from the regular curriculum:

1. For each row returned you should touch 10 or fewer blocks
2. No. of Logical IOs = No. of tables in a query x No. of rows x 10
3. Nested loops work great with small data sets.

One of the students asked Ric "How do we find out what a query is doing if it is taking a dirt nap" and Rick said that he had never heard of the term "dirt nap". I also never heard it.

Apparently "dirt nap" is a colloquial term which means dead. A dirt nap is when you die, you take the big dirt bath to be buried underground. similar to death.

Thursday, June 18, 2009

Oracle's v$ views

Vinu asked me whether we can insert a new directory for utl_file_dir into v$parameter. I explained to him that v$views are not tables and are synonyms of v_$ dynamic performance views. These views are available even before database is in OPEN state. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. These views can be selected from, but never updated or altered by the user.

The file $ORACLE_HOME/rdbms/admin/catalog.sql contains definitions of the views and public synonyms for the dynamic performance views.

Once the instance is started, the V_$ views that read from memory are accessible. Views that read data from disk require that the database be mounted.

You can find the description of the v$ synonyms here.

Monday, June 15, 2009

Classic Metalink to be retired by end of 2009

Oracle has announced that classic metalink is going to be retired by end of 2009. I don't like the new metalink much as it times out regularly and is not very intuitive. However, what must be must be. Oracle has a metalink note 838708.1 which has information about what you can do to prepare in advance. Adobe Flash player 9 is a pre-requisite and needs to be installed.

I have tried using the new metalink or "My Oracle Support", however I get frustrated when I am not able to do simple tasks. I'll have to get used to it, as Oracle will retire classic metalink which I access through metalink2.oracle.com now.

Wednesday, June 10, 2009

Xms Xmx and PermSpace

Guru asked me this question today about the Xms Xmx and Permspace parameters passed to java programs.

-Xms is the initial heap size and

-Xmx is the maximum

PermSpace is the area of the VM that is used to store data structures
and class information (not instances, but the class definitions
themselves).

From: http://forums.sun.com/thread.jspa?threadID=775925

Keep in mind that the permanent area is not a subset of the heap.
The pictures on the page http://java.sun.com/docs/hotspot/gc/index.html
erroneously show permspace as a subset of the heap defined by -Xmx
parameter. This is a bug in the documentation (state February 2004).

What is Cost ?

Alok asked this question today. The cost was coming as 231 in an explain plan. The best answer is given by Jonathan Lewis in his book "Cost Based Oracle". Chapter 1 of the book is called "What is Cost ?" He has defined it as:

The cost is the time spent on single-block reads, plus the time spent on multiblock reads, plus the CPU time required, all divided by the time it takes to do a single-block read. Which means the cost is the total predicted execution time for the statement, expressed in units of the single-block read time.

You can read the addenda on that chapter which has information about cost here.

Cost is time (30th Dec 2005)
Page 3, last complete paragraph. Following my claim that ‘cost’ really is ‘estimated time to run’, I explain that Oracle 9i chooses the sreadtim as the unit of time measurement for backwards compatibility so that the cost reported by 9i will generally be close to the cost reported by 8i for small, precise OLTP activity.

Another way of thinking about this is as follows:

By the definition in the 9.2 Performance Tuning Guide:

Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
This says the cost is: “the time to estimated run in milliseconds, divided the sreadtim”. But in 8i, Oracle assumes that all read requests take the same time, and that CPU doesn’t count; in other words sreadtim = mreadtim, and CPUCycles = 0. So what happens if you put those assumptions into the 9i formula:

Cost = (
#SRds * sreadtim +
#MRds * sreadtim +
0
) / sreadtim = #SRds + #MRds
So the 9i formula gives the 8i result when you apply the 8i assumptions. In other words, Oracle 9i estimates the time to run, then divides by the sreadtim so that a query that doesn’t involved multi-block reads will show (nearly) the same cost in 8i and 9i.

As a final thought, the execution plan that you get from dbms_xplan.display() in 10g includes a time column. Here’s the execution plan for a simple query to count the rows in a table. Note that the execution plan is a full tablescan, doing multi-block reads only, but that I’ve modified the single-block read time before generating the execution plan:

/* execute dbms_stats.set_system_stats('SREADTIM',10) */
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 329 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL| T2 | 48000 | | 329 (1)| 00:00:04 |
---------------------------------------------------------------------------
And here is it again, just moments later:

/* execute dbms_stats.set_system_stats('SREADTIM',2) */
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1641 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL| T2 | 48000 | | 1641 (1)| 00:00:04 |
---------------------------------------------------------------------------
What’s the difference between these two runs that could cause such a change in the cost ? The fact that I changed the optimizer’s assumption about the time it would take for a single block read to complete. (By scaling the single block read down by a factor of five, I effectively scaled the multiblock read time up by a factor of five – so this query, which did nothing but multiblock reads increased in cost by a factor of five).

Notice that even though the cost of the query has changed dramatically the time for the query to complete has not changed. So how has the optimizer calculated the time? It’s simply taken the 9i formula and multiplied the sreadtim back in to get back to the original time. Go back to my settings for the sreadtim statistic.

In the first case (10 milliseconds): ceiling (329 * 10/1000) = 4, hence the four second time.

In the second case (2 milliseconds): ceiling(1641 * 2/1000) = 4, hence the same four second time.

Cost is Time – but the units are a bit funny.

- Jonathan Lewis

Tuesday, June 9, 2009

Aliases or directories which can be accessed from 11i URL

Anand asked me the direct link to jinitiator for an instance. I launched a forms session and the window which launches the JVM had this text:

In order to access this application, you must install the Oracle JInitiator Plugin version 1.3.1.29. To install this plugin, click here to download the oajinit.exe executable. Once the download is complete, double-click the oajinit.exe file to install the plugin. You will be prompted to restart your browser when the installation is complete.

The click here was a hyperlink pointing to http://www.justanexample.com/jinitiator/oajinit.exe

To find out which directory on the server was pointing to /jinitiator I did this:

cd $IAS_CONFIG_HOME/Apache/Apache/conf
grep jinitiator *.conf

apps.conf:Alias /jinitiator/ "/gpscd140/erpapp/comn/html/"
apps.conf:Location /jinitiator/
apps.conf:Location ~ /(OA_HTML|html|jinitiator)/env.txt*
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/"
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/helpdoc/"
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/images/"
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/nonUix/launchMode/"
trusted.conf:# To add /html & /jinitiator as directories accessed by trusted nodes and remove /xsql/admin/xml.properties bug # 3851839
trusted.conf: Location ~ "/(OA_HTML|html|jinitiator)/bin/appsweb*"
url_fw.conf:#RewriteRule ^/jinitiator/oajinit\.exe$ - [L]

So then I opened apps.conf and found all the aliases:

Alias /OA_JAVA/ "$COMMON_TOP/java/"
Location /OA_JAVA/
Order allow,deny
Allow from all
/Location

Alias /OA_HTML/ "$COMMON_TOP/html/"
Location /OA_HTML/
Order allow,deny
Allow from all
/Location

Alias /OA_SECURE/ "$COMMON_TOP/secure/"
Location /OA_SECURE/
Order allow,deny
Allow from all
/Location

Alias /media/ "$COMMON_TOP/java/oracle/apps/media/"
Location /media/
ExpiresActive on
ExpiresDefault "now plus 1 day"
Order allow,deny
Allow from all
/Location

Alias /html/ "$COMMON_TOP/html/"
Location /html/
Order allow,deny
Allow from all
/Location

Alias /OA_MEDIA/ "$COMMON_TOP/java/oracle/apps/media/"
Location /OA_MEDIA/
ExpiresActive on
ExpiresDefault "now plus 1 day"
Order allow,deny
Allow from all
/Location

Alias /OA_TEMP/ "$COMMON_TOP/temp/"
Location /OA_TEMP/
Order allow,deny
Allow from all
/Location

Alias /jinitiator/ "$COMMON_TOP/html/"
Location /jinitiator/
Order allow,deny
Allow from all
/Location

Alias /dev60html/ "$ORACLE_HOME/tools/web60/html/"
Location /dev60html/
Order allow,deny
Allow from all
/Location

Alias /forms60java/ "$ORACLE_HOME/forms60/java/"
Location /forms60java/
Order allow,deny
Allow from all
/Location

Alias /dev60temp/ "$ORACLE_HOME/tools/web60/temp/"
Location /dev60temp/
Order allow,deny
Allow from all
/Location

Monday, June 8, 2009

Query to check characterset of your DB

A character set is the mapping of specific set of characters to binary values. In 8-bit character sets like ASCII, the values range from 0-255 and one character will be mapped to each (or most of) these values. This means that if your terminal is set to support a particular character set, every time you press a certain key, that key will send its value eg: 65, to the terminal, which will interpret that value as the letter A. It can also be known as an encoding scheme. Microsoft and IBM have traditionally called it a code page.

The query to check the characterset of your Oracle database is:

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

To enable your E-Business Suite instance for multiple languages, the characterset should be set to UTF8 or higher.

Friday, June 5, 2009

ERP Concurrent User Estimator

I found a very interesting spreadsheet called ERP Software Concurrent User Estimator from TGI on this link.

The formula used by them is:

No. of Concurrent Users = (0.3 x Low Activity Users) + (0.6 x Medium Activity Users) + High Activity Users

This is a good estimator which can be used to come up with number of concurrent users, which is needed for sizing an environment.

Thursday, June 4, 2009

Disabling core dump generation in Oracle Database 10g

In a recent SR with Oracle, the support analyst told us to disable core dumps as all the information captured in core dumps is also present in ORA-7445 trace files.

Metalink Note 397404.1 gives these two methods for disabling core dumps.

Method #1 - Set database parameter SHADOW_CORE_DUMP to NONE.

SHADOW_CORE_DUMP is a static parameter so the database must be restarted in order for the parameter value to be enabled. Setting SHADOW_CORE_DUMP to NONE will block the generation of core dumps from user (client) processes attached to the SGA. Setting this parameter has no impact on core dumps being generated by background processes. It is not possible to set BACKGROUND_CORE_DUMP to NONE. So this method will prevent core dumps from being generated by user processes but will not prevent the generation of core dumps by background processes.

Method #2 - modify the OS ULIMIT
ulimit -c 0

doing this will block core dumps from being generated by all processes executed by this user, which should include all user and background processes on the server. The database will have to be restarted in order for the new ulimit settings to take effect.

R12 Logo customization

Mohan queried me about logo personalization in R12. He had copied custom ORACLE_HR.jpg and FNDSSCORP.gif to $OA_MEDIA and bounced Apache. However custom logo is not displaying on R12 pages
He wanted to know whether he needed to call these two files in any of the file
He had already cleared cache on desktop and server.

The profile option "Corporate Branding Image for Oracle Applications" controls the
corporate branding image "ORACLE" displayed at the top of all OA Framework pages.
If no value is set for this profile, OA Framework renders the corporate branding image by using the $OA_MEDIA/FNDSSCORP.gif file.

If you change the value of this profile with the name of your custom image you will see your logo in all pages except in the login page because the "FNDSSCORP.gif " is hard coded in MainLoginPG.xml.

Metalink Note 741459.1 has some details about logo customization. Hence if you want to change the corporate branding image with your own logo and thus in all pages including the login page the simplest solution is to:

- rename the FNDSSCORP.gif file for backup
- edit the FNDSSCORP.gif with your preferred graphic software
- log out and log into the Applications
- you should see the new logo at the top of all the page (including the login page)

Mohan said that it is null. I asked him whether he replaced the file with his own gif or did he copy it. I mean modified the existing FNDSSCORP.gif after taking backup

In case you want a different logo for the login page then the only (non supported) solution consists in editing the $FND_TOP/mds/sso/login/webui/MainLoginPG.xml file,

Erroring with Product 'Company' is not a currently-installed product

Still working on this one.

Wednesday, June 3, 2009

Forms not launching from self service after migration to new app tier

Raju reported the issue of forms not launching from self service after migration to new desktop. Sure enough it was happening for me too.

192.168.10.188 - - [03/Jun/2009:23:21:22 -0400] "GET /dev60cgi/f60cgi?&appletmode=nonforms&HTMLpageTitle=&HTMLpreApplet=&code=oracle/apps/fnd/formsClient/FormsLauncher.class&width=400&height=100&archive=/OA_JAVA/oracle/apps/fnd/jar/fndforms.jar,/OA_JAVA/oracle/apps/fnd/jar/fndformsi18n.jar,/OA_JAVA/oracle/apps/fnd/jar/fndewt.jar,/OA_JAVA/oracle/apps/fnd/jar/fndswing.jar,/OA_JAVA/oracle/apps/fnd/jar/fndbalishare.jar,/OA_JAVA/oracle/apps/fnd/jar/fndaol.jar,/OA_JAVA/oracle/apps/fnd/jar/fndctx.jar,/OA_JAVA/oracle/apps/fnd/jar/fndlist.jar&gp14=jinit_appletcache&gv14=offjinit_appletcache=off&gp2=resp_app&gv2=SYSADMIN&gp3=resp&gv3=SYSTEM_ADMINISTRATOR&gp4=sec_group&gv4=STANDARD&gp5=function&gv5=FND_FNDCPQCR_SYS&gp6=other_params&gv6=&gp7=forms_url&gv7=http%3A%2F%2F11ioltp.justanexample.com%3A8029%2Fpls%2Ferppgrd2%2Ffnd_icx_launch.runforms%3FICX_TICKET%3D%26resp_app%3DSYSADMIN%26resp_key%3DSYSTEM_ADMINISTRATOR%26secgrp_key%3DSTANDARD%26start_func%3DFND_FNDCPQCR_SYS%26other_params%3D&encoding=UTF-8&gp8=error_url&gv8=http%3A%2F%2Fdev56.justanexample.com%3A8029%2FOA_HTML%2Fjsp%2Ffnd%2Ffnderror.jsp&gp12=port&gv12=6945&gp13=dbc&gv13=dev56&gp15=icx_ticket&gv15=qT_sKHY-DtwzZo6CWStyqg.. HTTP/1.1" 200 42954 0




Do you wish to force regeneration of all jar files? [No] ? Yes


Forcing generation of all product jar files.
Creating and signing every jar file can take about thirty
minutes depending on the hardware being used.
You can watch the file $APPL_TOP/admin/$TWO_TASK/log/adadmin.log to see the progress of jar file generation.

Signing product JAR files in JAVA_TOP -
$OA_JAVA
using entity DEV21_ERPDEV21 and certificate 1.

Successfully created javaVersionFile.

Generating product JAR files in JAVA_TOP -
$OA_JAVA with command:

adjava -mx512m -nojit oracle.apps.ad.jri.adjmx @$APPL_TOP/admin/$TWO_TASK/out/genjars.cmd

After regenerating jar files and logging in as administrator on my laptop, it worked fine. It did not work for Raju as he did not have administrator rights on his laptop.

Concurrent Managers require physical hostname

In our environment we have DB and Concurrent Manager on one box. This box is clustered with Veritas 5.1. We use virtual cluster name for connecting to DB. However we manually add the physical hostnames of all the servers in the cluster.

This is because the Concurrent Managers require the physical hostname to be registered and enabled for Concurrent Processing.

Please refer to Metalink Note 568115.1 for more details.

Tuesday, June 2, 2009

mwactl.sh start gives java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleStatement

Anand called at 7 AM today to report this error which was coming in a Production environment when Mobile and Wireless service was started with mwactl.sh start:

oracle.apps.mwa.tools.GenerateLauncher.main(GenerateLauncher.java:60)

java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleStatement

We compared the instance with Development instances. The classpath matched. We even compared the truss and found that in the instances working, it was unable to find OracleStatement.class but was going ahead and looking for other classes.

Later in the day, Ben asked Anand to put jdbc111.zip in the classpath as a workaround. The root cause was also found. Someone had issued the command compress appsbrog2.zip which had converted appsborg2.zip to appsborg2.zip.Z. Uncompressing it and reinstating it resolved the issue.

Monday, June 1, 2009

Oracle.Forms.Engine.Main Not Found

Sandeep gave me a call in the evening today and told me that he is getting this error when he tries to launch forms from self service interface:

Oracle.Forms.Engine.Main Not Found

Metalink Note 465063.1 has this error and advises this:

-- To implement the solution, please execute the following steps::
1. Go to control panel. Jinitiator 1.3.1.18
2. Click on the proxy TAB
3. Enter Proxy name and port
Or
hardcode proxy name in the browser.

It was indeed because of the proxy. Sandeep confirmed this after checking with another user.