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: exec fnd_stats.ENABLE_SCHEMA_MONITORING (SCHEMA_NAME); 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.

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> SQL> create table test1 2 (A number 3 ,B varchar2(100)); Table created SQL> SQL> select monitoring from user_tables where table_name = 'TEST1'; MONITORING ---------- YES -- In 10g table automatically has monitor option enabled. -- For 9i you should execute the following: SQL> SQL> alter table test1 monitoring; Table altered SQL> SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- SQL> 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> SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- SQL> SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- ------------- TEST1 3 0 0 7/29/2007 1 NO 0 SQL> 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. Regards :-)

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...

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 11.5.10.2 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 (adstpall.sh) 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 ./patch_install.sh - execute the following commands: cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install cp –b /developer6i_patch18/bin/genshlib $ORACLE_HOME/bin 3) Apply patch 5713544 - sh patch.sh - adrelink.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 patch.sh 6) Apply patch 5753922 - sh patch.sh - adrelink.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 patch.sh - adrelink.sh force=y "fnd f60webmx" 9) Apply patch 3830807 - sh patch.sh - execute adadmin -> "Maintain Applications Files" -> "Relink Applications programs" 10) Apply patch 4586086 - mv env_forms60.mk env_forms60.mk.PRE_BUG4586086 - cp /env_forms60.mk $ORACLE_HOME/forms60/lib - cd $ORACLE_HOME/forms60/lib - make -f cus_forms60w.mk libso_install - adrelink.sh 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 (adstrtall.sh) 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.

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" ,ses.sid ,ses.serial# ,ses.module ,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 l.pid = v.pid 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' union select usr.user_name ,l.start_time ,ses.sid ,ses.serial# ,ses.module ,v.spid ,ses.process ,rsp.responsibility_name ,r.start_time ,null ,null ,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 l.pid = v.pid 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' union select usr.user_name ,l.start_time ,ses.sid ,ses.serial# ,ses.module ,v.spid ,ses.process ,null ,null ,frm.user_form_name ,ff.type ,f.start_time 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 l.pid = v.pid 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';