Monday, July 30, 2007

Gather statistics for Oracle Applications 11i

Following the last post Monitoring DML operations, In this post I'm going to talk about Gathering Statistics in Oracle Applications 11i system.

All of us gathering statistics in our databases, the question is whether it is the best way to do it?
We are struggling with a lot of questions regarding for which schema needs to run? How much percent sampling? In what frequency it considered enough? Etc...

So I will try to make some light in the dark...

The topics in this post will be:
• The classic way to gather statistics - by the "Gather Schema Statistics" concurrent with Gather Options parameter set to "GATHER".
• Using "Monitoring" with Gather Schema Statistics to enhance Gather Statistics run time and efficiency.

You can read more about monitoring in my previous post Monitoring DML operations

The Gather Schema Statistics concurrent, based on fnd_stats package, gather statistics for the required schema(s) with some additional parameters:

Schema Name - the schema in which statistics should gathered. You can specify 'ALL' for gather statistics for all schemas registered in FND_PRODUCT_INSTALLATIONS table.

Estimate Percent - The sampling percents. Default value is 10.

Degree of parallelism - The degree of parallelism for gathering statistics. Default value is MIN (parallel_max_servers, cpu_count). (database init parameters)

Backup Flag - Indicate whether to backup last statistics (current situation). BACKUP/NOBACKUP - indicate to save/not save current statistics.

Restart Request Id - if Gather Schema Statistics concurrent failed, you can rerun it with this parameter set to the failed request_id. The concurrent will continue from where the failed request stopped.

History Mode - indicate how much history will save. History includes for each object when statistics gather for it, when it ends and more info'. Parameter options are: LAST_RUN - will save history only for the last time for each object. FULL - will save additional history each run. None - don't save history.

Gather Options - indicates for which objects statistics will gather. Parameter options are: GATHER (the classic & default) - gather stat' for all objects in the appropriate schema. GATHER_AUTO - This option considers how much DML operations executed on each object to decide whether gather statistics or not, I will explain later in details. GATHER_EMPTY - will gather stat only for tables/indexes with no statistics. LIST_AUTO - provide a list of objects for gather statistics if GATHER_AUTO is used. LIST_EMPTY - provide a list of objects for gather statistics if GATHER_EMPTY is used.

Modifications Threshold - relevant only when using GATHER_AUTO/LIST_AUTO in the previous parameter. This parameter specifies the percentage of modifications on objects that must execute before gathering statistics. I will explain later in details.

Invalidate Dependent - indicate whether to invalidate cursor for the analyzed objects. The default is 'Y'.

The Gather Schema Statistics concurrent should schedule to run periodically (usually once a week/2 weeks).
With gather options parameter set to GATHER and schema set to ALL, it will take a very long time to run since it gather statistics for all objects in database (for schemas registered in FND_PRODUCT_INSTALLATIONS table).

Using "Monitoring" with Gather Schema Statistics

To reduce Gather Schema Statistics concurrent run time, we can set the Gather Options parameter to "GATHER AUTO", it indicates to gather stats only for tables that have [Modifications Threshold] % changes since last analyze.

First we should enable monitoring for the relevant schemas.
We can do it by executing the following:

You can specify ‘ALL’ as parameter to enable monitoring for all tables in registered schemas.

The Gather Schema Statistics concurrent will do the following:

• Flush database monitoring info from SGA to dba_tab_modifications view
• Gather statistics for tables that cumulative modifications compared to the dba_tables.num_rows are more than Modification Threshold percents.
• Gather statistics for table that never analyzed (last_analyzed in NULL)
• Gather statistics for tables which does not have monitoring enabled and afterwards enable monitoring for them so we have data in dba_tab_modifications next time we Gather Statistics.

As you can see, it’s not necessary to enable monitoring before gather statistics with Gather Auto option, since it does it anyway.
(But I did…:-))

In fact, gather schema statistics become significantly more efficient, less run time, since we gather statistics for heavy using tables only.
This enables us to run Gather statistics more frequently for less time.

Try it!

For any questions or if you have additional information you are welcome to leave a comment.

Sunday, July 29, 2007

Monitoring DML operations

A very useful feature introduced in Oracle DB 9i called MONITORING.

This feature monitors DML operations - insert/update/delete (and truncate) on monitored tables.
Table monitoring can be implemented on a table by executing the following statement:
"alter table TABLE_NAME monitoring".

Once monitor option is enable for a table, all DML operations on it are register in a view - sys.DBA_TAB_MODIFICATIONS (based on sys.mon_mods$ table).
The DML counters from this view represent the number of DML operations since the last gather statistics.
The overhead on performance is not to be considerable since this mechanism work on memory and flush changes to DBA_TAB_MODOFICATIONS view periodically.

In 9i, tables should be implemented with monitoring by executing "alter table ... monitoring", but in 10g monitor is enabled by default.

The advantage of this feature is when gathering statistics.
Based on the dba_tab_modifications view we can gather statistics for only tables that have more than X% changes (compared to num_rows in user_tables) since last gather statistics.
Actually, there is no reason for us to re-analyze tables that has no enough changes.

Here is a simple example:

SQL> create table test1
2 (A number
3 ,B varchar2(100));

Table created

SQL> select monitoring from user_tables where table_name = 'TEST1';


-- In 10g table automatically has monitor option enabled.
-- For 9i you should execute the following:

SQL> alter table test1 monitoring;

Table altered

SQL> select * from user_tab_modifications;

------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------

SQL> insert into test1 values (1,'a');

1 row inserted

SQL> insert into test1 values (2,'b');

1 row inserted

SQL> insert into test1 values (3,'c');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from user_tab_modifications;

------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select * from user_tab_modifications;

------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------
TEST1 3 0 0 7/29/2007 1 NO 0


In the next post I will explain how to integrate monitoring feature with Gather Schema Statistics concurrent in Oracle Apps 11i.
The monitoring discovered as very efficient when gathering statistics in Oracle Apps database or any quite large database.

For questions, remarks, clarifications or any issue please leave a comment.


Sunday, July 22, 2007

ADPATCH with "options=prereq" - what really happens there?

With adpatch utility we have a possibility to ask for a prerequisite check prior to running patch driver files.
Actually, some patches must apply with prerequisite check before applying them.

The adpatch command should look like: adpatch option=prereq
This flag indicate to adpatch to check prerequisite before applying patch.

adpatch checks the prerequisite based on information from patch files and current snapshot on APPL_TOP.

When running adpatch with "prereq" flag, we might get an error message like:
Analyzing prerequisite patch information...
AutoPatch error: This patch has some prerequisites specified, but a "snapshot" of this APPL-TOP's file-system has never been taken, thereby rendering it impossible to check for the prerequisites.
Please take a "snapshot" of this APPL-TOP using "AD Administration" first.

This error message will show up if a snapshot on current APPL_TOP doesn't exists.

To create such snapshot on APPL_TOP:
1) run adadmin
2) Select "Maintain Applications Files menu"
3) Select "Update current view snapshot"
4) Rerun adpatch

**It might take couple of hours depends on your hardware and APPL_TOP size.

So how adpatch check the prerequisites?

1) Check if a snapshot on current APPL_TOP exist.
using sql script - adbkflsn.sql (if not, will terminate with above error message.....)

2) adpatch uploads a ldt file with FNDLOAD utility into system (bug-fixes).
ldt file name is: b[PATCH_NUMER].ldt - comes from patch root directory.

3) Execute the UMS analysis engine based on the snapshot and bug-fixes to check if all prerequisites exists.

You are welcome to leave a comment...

Thursday, July 19, 2007

Upgrading Developer 6i with Oracle Apps 11i

In this post I will explain - step by step - how to upgrade an Oracle Applications 11i environment to the latest certified Developer 6i patchset - nowadays patchset 18.

In this post I assume you have an Oracle Applications on Linux with autoconfig enabled.
Other OS needs another patches (take a look on note #125767.1).

So let's begin...

1) Stop all applications processes ( on all application tier nodes.

2) Apply latest certified Developer 6i patchset 18 (4948577) on all application tier nodes.
- make sure ORACLE_HOME refer to 8.0.6 Oracle home.
- run ./
- execute the following commands:
cd $ORACLE_HOME/procbuilder60/lib; make -f install
cd $ORACLE_HOME/forms60/lib; make -f install
cd $ORACLE_HOME/graphics60/lib; make -f install
cd $ORACLE_HOME/reports60/lib; make -f install
cp –b /developer6i_patch18/bin/genshlib $ORACLE_HOME/bin

3) Apply patch 5713544
- sh
- force=y "fnd f60webmx"

4) Apply patch 4261542
- cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class.PRE_BUG4261542
- cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class.PRE_BUG4261542
- cp /oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
- cp /oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class

5) Apply patch 5216496
- sh

6) Apply patch 5753922
- sh
- force=y "fnd f60webmx"

7) Apply patch 5355158
- cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG5976594
- cp /oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
- execute adadmin -> "Generate Applications Files menu" -> "Generate product JAR files"

8) Apply patch 5938515
- sh
- force=y "fnd f60webmx"

9) Apply patch 3830807
- sh
- execute adadmin -> "Maintain Applications Files" -> "Relink Applications programs"

10) Apply patch 4586086
- mv
- cp / $ORACLE_HOME/forms60/lib
- cd $ORACLE_HOME/forms60/lib
- make -f libso_install
- force=y "fnd f60webmx"

11) Relink applications executables
- execute admin
- “Maintain Applications Files Menu”
- "Relink Applications program"
- List of product to link: “fnd”
- Generate specific executables ….: “y”
- Relink with debug: “n”
- Enter executables to relink: “f60webmx ar60run ar60runb ar60rund *”

12) Apply Apps Interoperability patch - 4888294
- adpatch...

13) execute adadmin -> "Generate Applications Files menu" -> "Generate product JAR files"

14) Start all applications processes (

Now your system are upgraded with the latest Developer6i patchset.

Additional information you can find at Oracle Metalink Note #125767.1 - Upgrading Developer 6i with Oracle Applications 11i.

If you need more information or something is not clear, please leave a comment and/or Mail.

Monday, July 16, 2007

Apps User Connection Details

This is going to be my first post... so I decided to bring a very useful SQL for apps DBA's.

This SQL shows details about users connected to the system (sid, responsibilty, form name, Oracle pid, Application pid and more..)

The parameters are Apps Username or Oracle Session Id (put % where you don't pass a parameter).

Very useful when you have a heavy session or when you have a DB lock and you want to know who is standing from behind (the applicative user)...

It contains 3 SQL's with unions:
1) 1st sql - OA Framework screens (login screen, iSupport etc...)
2) 2nd sql - Responsibility connection details (for Java Applet)
3) 3rd sql - Forms connection details

select usr.user_name "Apps Username"
,i.first_connect "First Connect Date"
,v.spid "Oracle Server Process"
,ses.process "Application Server Process"
,rsp.responsibility_name "Responsibility Name"
,null "Responsibility Start Time"
,fuc.function_name "Function Name"
,i.function_type "Function Type"
,i.last_connect "Function Start Time"
from icx_sessions i
,fnd_logins l
,fnd_appl_sessions a
,fnd_user usr
,fnd_responsibility_tl rsp
,fnd_form_functions fuc
,gv$process v
,gv$session ses
where i.disabled_flag = 'N'
and i.login_id = l.login_id
and l.end_time is null
and i.user_id = usr.user_id
and l.login_id = a.login_id
and a.audsid = ses.audsid
and =
and l.serial# = v.serial#
and i.responsibility_application_id = rsp.application_id(+)
and i.responsibility_id = rsp.responsibility_id(+)
and i.function_id = fuc.function_id(+)
and i.responsibility_id not in (select t1.responsibility_id
from fnd_login_responsibilities t1
where t1.login_id = l.login_id)
and rsp.language(+) = 'US'
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
select usr.user_name
,null form_start_time
from fnd_logins l
,fnd_login_responsibilities r
,fnd_user usr
,fnd_responsibility_tl rsp
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and =
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = r.login_id(+)
and r.end_time is null
and r.responsibility_id = rsp.responsibility_id(+)
and r.resp_appl_id = rsp.application_id(+)
and rsp.language(+) = 'US'
and r.audsid = ses.audsid
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID'
select usr.user_name
from fnd_logins l
,fnd_login_resp_forms f
,fnd_user usr
,fnd_form_tl frm
,fnd_form_functions ff
,gv$process v
,gv$session ses
where l.end_time is null
and l.user_id = usr.user_id
and =
and l.serial# = v.serial#
and v.addr = ses.paddr
and l.login_id = f.login_id(+)
and f.end_time is null
and f.form_id = frm.form_id(+)
and f.form_appl_id = frm.application_id(+)
and frm.language(+) = 'US'
and f.audsid = ses.audsid
and ff.form_id = frm.form_id
and usr.user_name like '&APPS_USER_NAME'
and ses.sid like '&SID';