rajesh apps dba

Wednesday, August 29, 2012

Upgrade Advisor:

Upgrade Advisor: E-Business Suite (EBS) Upgrade from 11.5.10.2 to 12.1.3 [ID 269.1]

Master Class in Apps 11i Performance Tuning

PPT presentation for best performance tips:

Tuning the E-Business Suite (PDF, 2.0 MB)
(Username: cboracle, Password: oraclec6 to download)

Performance: How to enable Database trace for the Apps session which is already started running

You know how to enable Database trace for Forms & concurrent request, if you know in advance. But if you wish to enable Database trace for the Apps session which already started, then you can use oradebug .

This is required if you wish to do DB tracing when a program suddenly hangs / slow and you do not want to cancel and resubmit.

Steps to enable DB trace dynamically

1) Identify the sid using below sql.

select sid,program,module from v$session

where program like '%f60web%';

2) Identify the SPID

select spid from

v$process

where addr in (

select paddr

from v$session

where sid in (&&<from step1>)

);

3) Enable trace using ORADEBUG.

sqlplus /nolog

connect / as sysdba

set echo on

ORADEBUG SETOSPID <spidfrom step2>

ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8

4) This enables trace for the running session.

4.1) Trace file will be found in the user_dump_dest.

4.2) tkprof tracefile.trc output.txt explain=apps/<passwd> sort=prsela,exeela,fchel

5) If you want to disable , use below command from the same session (step 3)

ORADEBUG EVENT 10046 TRACE NAME CONTEXT off (Rem to turn off; to Switch off the Trace)

Hope this helps.

Gather Schema Statistics

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.

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 following concurrent programs have been defined to gather and maintain various statistics using the FND_STATS package.

Concurrent Programs

Analyze All Index Columns
Gather Table Statistics
Backup Table Statistics
Restore Table Statistics
Gather Schema Statistics

Note: When invoked using the seeded Concurrent Programs, only use the 'Gather Schema Statistics' or the 'Gather Table Statistics'.

Additional Information: The Optimizer, Oracle8i Concepts

When using the 'Gathering Schema Statistics' concurrent program, it is recommended that you only pass the schema name parameter and let the other parameters default to their default values. The schema name passed can be a specific schema name (e.g. GL) or 'ALL' if you prefer to gather CBO stats for Apps modules. This choice uses the “estimate” method with a sample size of 10% (default).

When using the 'Gather Table Statistics' concurrent program, only pass the owner of the table (schema name) and the table name. Let all other parameters default automatically, except when the table is a partitioned table.

To manually execute FND_STATS from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following syntax:

SQL> exec fnd_stats.gather_schema_statistics('GL'); <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL'); <- All schemas
SQL> exec fnd_stats.gather_table_stats('GL','GL_JE_LINES'); <- One table

The DBMS_STATS package contains all the procedures required to maintain the optimizer statistics of any schema.

you may gather schema statistics once per week with a specific estimate_percent value and gather schema index statistics every night, for this purpose the following simple PL/SQL may be used . I recommend if you create jobs and schedule them as appropriate.

-- Gathering Schema Statistics
execute dbms_stats.gather_schema_stats('SCHEMA1', estimate_percent=>39, cascade=>TRUE);
execute dbms_stats.gather_schema_stats('SCHEMA2', estimate_percent=>45, cascade=>TRUE);
-- Gathering Index Statistics
set serveroutput on
begin
for ind in
(select object_name
from user_objects
where object_type='INDEX'
and object_name like 'YOUR_INDEX_PATTERN_HERE'
)
loop
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||'.....');
dbms_stats.gather_index_stats('SHEMA1', ind.object_name , estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '||ind.object_name||' is Complete!');
end loop;
end;
/

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.

Important Documents:

What is the Gather Statistic Program or Gather Schema Statistics? (Doc ID 155766.1)
How Often Should Gather Schema Statistics Program be Run? (Doc ID 168136.1)
Gather Schema Statistics with LASTRUN Option does not Clean FND_STATS_HIST Table (Doc ID 745442.1)
Does Gather Schema Statistics collect statistics for indexes? (Doc ID 170647.1)
Definition of Parameters Used in Gather Schema Statistics Program (Doc ID 556466.1)
Gathering Schema or Database Statistics Automatically in 8i and 9i - Examples (Doc ID 237901.1)
Gather Schema Statistics Fails for Some Tables Percentages Not Displaying Properly (Doc ID 149603.1)
Gather Schema Statistics (FNDGSCST) Hangs with DR$ Tables With Monitoring (Doc ID 435482.1)
Gather Schema Statistics Hangs & Returns ORA-600: Internal Error, Arguments: [25012] (Doc ID 454205.1)
Gather schema statistics occupies huge TEMP space without releasing it (Doc ID 731674.1)
Gather Schema Statistics Completed Err ORA-20000 Index in unusable state (Doc ID 373488.1)
How To Gather Table Statistics For Custom Schema Tables (Doc ID 389944.1)
Gather Schema Statistics Program Fails When Scheduled (Doc ID 1050582.1)
Running Gather Optimizer Statistics On Schema Generates Error (Doc ID 854527.1)
Gather Schema Statistics Fails With Errors ORA-04063 and ORA-04068 (Doc ID 215897.1)

Some of the known issues on Forms in EBS

[Document 758661.1]

After Upgrading the Forms Tier JDK to the Latest Certified Version of J2SE 5 or 6, Ebusiness Suite Forms Fail to Open With an FRM-92102 Error

[Document 973632.1]

FRM-40735 Error Received When Trying To Define A Responsibility

[Document 1269544.1]

R12: "FRM-92050: Failed to connect to Server:/forms/lservlet:-1" Error When Launching Forms After Adding New Language

[Document 1062246.1]

While Trying To Resolve FRM-92101 On E-Business Suite Release 12 By Recompiling Forms, It Fails With "ldflags: Too many levels of symbolic links"

[Document 465926.1]

R12: "FRM-92101: There Was a Failure in the Forms Server During Startup" Error After Fresh R12 Installation

[Document 429627.1]

"FRM-92050: Failed to connect to the server :/forms/lservlet -1" Error When Attempting to Launch Forms with Reverse Proxy

[Document 779065.1]

R12: "FRM-92050: Failed to connect to the Server: /forms/lservlet: -1" Error After Upgrading JDK

[Document 1175400.1]

R12: Forms Timeout More Than 2 Hrs Is Not Working After R12 Upgrade

[Document 734077.1

Note 1258305.1 Users Receive FRM-92101 oracle.forms.net.ConnectionException Errors When Launching E-Business Suite Forms Responsibility and Function
Note 454427.1 R12: "FRM-92101:There was a failure in the Forms Server during startup" Error When Attempting to Launch Forms
Note 438231.1 FRM-92050 Connecting and "Failed to exec runform f60webmx" Starting Forms Server
Note 1103744.1 R12: "FRM-92101: There Was a Failure in the Forms Server During Startup" Error When Loading Forms
Note 1056333.1 Forms Error With FRM-91126 invalid value timezlrg#dat
Note 744093.1 FRM-92050 With "Connection reset by peer: JVM_recv in socket input stream read" Error When Launching Form On Microsoft Servers
Note 1203773.1 Forms raises FRM-92100 Caused By Java.IllegalStateException:Cannot Open System Clipboard
note 130686.1 How to Generate Form, Library and Menu for Oracle Applications
Note 438652.1 R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications
Note 840577.1 Getting FRM-41839 FRM-41092 FRM-40737 FRM-40734 When Using Mouse Scroll on Oracle Applications 12.0.4
Note 550364.1 FRM-40735: Pre-Update Trigger Raised Unhandled Exception ORA-04062
Note 754347.1 FRM-40833: Could not completely load the dynamic user exit libraries
Note 399652.1 Getting FRM-41800 when navigating to any LOV and Focus Lost
Note 1369830.1 R12 Forms Slow performance / Freezes / Hangs for large # Record details using View -> Last -> Record

For troubleshooting you may review the following:

Note 365529.1 Troubleshooting FRM-92XXX Errors in Oracle Applications
Note 311019.1 Troubleshooting FRM-92100 Issues In Oracle Applications 11i
Note 402674.1 Tips and Queries for Troubleshooting Forms Session Timeout Issues
Note 164220.1 Troubleshooting "FRM-92160: Web client version too old" Error in R11i
Note 810084.1 Troubleshooting"Forms CGI error: URL Contains One Or More Restricted Characters" In Apps 11i
Note 136027.1 Troubleshooting Oracle Forms 6i Issues With Oracle Applications 11i
Note 299187.1 11i: How to Troubleshoot Error "FRM-92050 Failed to connect to the server"
Note 164220.1 Troubleshooting "FRM-92160: Web client version too old" Error in R11i

Generate and Review Trace for a Concurrent Program

1. Run the following query:

select max(log_sequence) from fnd_log_messages

Make a note this log_seq.

2. Set the following profile options at the user level:

FND: Debug Log Enabled = YES

FND: Debug Log Filename = NULL

FND: Debug Log Level = STATEMENT

FND: Debug Log Module = %

3. Enable trace for the concurrent program: In System Administrator responsibility Navigate to: Concurrent -> Program -> Define, query up the concurrent program you wish to trace. Make sure that the "enable trace" checkbox is UN-checked.

4. Turn on tracing: Responsibility: System Administrator

Navigate: Profiles > System

Query Profile Option Concurrent: Allow Debugging

Set profile to Yes

5. Submit the Concurrent Request, but before submitting, click Debugging Options, and select for SQL Trace: Trace With Binds And Waits.

Make a note of your request id.

6. After the request finishes, please retrieve the trace file by running this sql query which will give the exact trace file name, and will prompt you for the request id:

SELECT 'Request id: '||request_id ,

'Trace id: '||oracle_Process_id,

'Trace Flag: '||req.enable_trace,

'Trace Name:

'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',

'Prog. Name: '||prog.user_concurrent_program_name,

'File Name: '||execname.execution_file_name|| execname.subroutine_name ,

'Status : '||decode(phase_code,'R','Running')

||'-'||decode(status_code,'R','Normal'),

'SID Serial: '||ses.sid||','|| ses.serial#,

'Module : '||ses.module

from fnd_concurrent_requests req, v$session ses, v$process proc,

v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,

fnd_executables execname

where req.request_id = &request

and req.oracle_process_id=proc.spid(+)

and proc.addr = ses.paddr(+)

and dest.name='user_dump_dest'

and dbnm.name='db_name'

and req.concurrent_program_id = prog.concurrent_program_id

and req.program_application_id = prog.application_id

and prog.application_id = execname.application_id

and prog.executable_id=execname.executable_id;

7. Run the following sql:

select * from fnd_log_messages

where log_sequence > &log_seq_noted_above

order by log_sequence

8.Upload the data from the above SQL in an xls file.

9. Convert the trace file into tkprof format, using the following command:

tkprof <filename.trc> <output_filename_SORT.txt> explain=apps/<password>

10. Review trace,log and tkprof files.

Queries Help Analysing Concurrent Requests

- Connect sqlplus as apps user and execute:

NOTE : modify the select statement to change <request_id> for the request in position wherever needed.

SELECT request_id, status_code, phase_code

from fnd_concurrent_requests

WHERE request_id = <request_id>;

SELECT b.concurrent_queue_name, a.TYPE_APPLICATION_ID,a.TYPE_ID, a.INCLUDE_FLAG, a.TYPE_CODE

FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b,

fnd_concurrent_requests c

WHERE a.queue_application_id = b.application_id

and a.concurrent_queue_id = b.concurrent_queue_id

and a.TYPE_ID = c.concurrent_program_id

and a.TYPE_APPLICATION_ID = c.program_application_id

and c.request_id = <request_id>;

select distinct type_code

from FND_CONCURRENT_QUEUE_CONTENT;

SELECT b.concurrent_queue_name, a.TYPE_APPLICATION_ID,a.TYPE_ID, a.INCLUDE_FLAG,a.TYPE_CODE

FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b

WHERE a.queue_application_id = b.application_id

and a.concurrent_queue_id = b.concurrent_queue_id

order by b.concurrent_queue_name;

SELECT creq_run.request_id, fcq.concurrent_queue_name,

creq_run.program_application_id, creq_run.concurrent_program_id, creq_run.REQUESTED_BY

FROM fnd_concurrent_processes fcp,

fnd_concurrent_queues fcq,

fnd_concurrent_requests creq_run

WHERE fcp.concurrent_process_id = creq_run.controlling_manager

and fcp.concurrent_queue_id = fcq.concurrent_queue_id

AND fcp.queue_application_id = fcq.application_id

AND creq_run.request_id = <request_id>;

select

d.CONCURRENT_TIME_PERIOD_ID, d.SLEEP_SECONDS, d.MIN_PROCESSES, c.CONCURRENT_QUEUE_NAME

from fnd_concurrent_queue_size d, fnd_concurrent_queues c

where d.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID

order by c.CONCURRENT_QUEUE_NAME;

SELECT cprog.TO_RUN_APPLICATION_ID, cprog.TO_RUN_CONCURRENT_PROGRAM_ID

FROM fnd_concurrent_requests creq_run,

fnd_concurrent_program_serial cprog

WHERE creq_run.program_application_id = cprog.RUNNING_APPLICATION_ID

AND creq_run.concurrent_program_id = cprog.RUNNING_CONCURRENT_PROGRAM_ID

AND creq_run.request_id = <request_id>;

SELECT cprog.RUNNING_APPLICATION_ID, cprog.RUNNING_CONCURRENT_PROGRAM_ID

FROM fnd_concurrent_requests creq_run,

fnd_concurrent_program_serial cprog

WHERE creq_run.program_application_id = cprog.TO_RUN_APPLICATION_ID

AND creq_run.concurrent_program_id = cprog.TO_RUN_CONCURRENT_PROGRAM_ID

AND creq_run.request_id = <request_id>;

SELECT creq_run.request_id,

cprog.concurrent_program_name,

cprog.run_alone_flag,

creq_run.number_of_arguments,

creq_run.argument_text,

creq_run.REQUESTED_BY,

creq_run.cd_id,

fcq.concurrent_queue_name,

to_char(creq_run.actual_start_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_run.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_base.request_id),

to_char(creq_base.requested_start_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_base.actual_start_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_base.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),

creq_base.single_thread_flag,

creq_base.request_limit,

creq_base.REQUESTED_BY,

creq_run.cd_id

FROM fnd_concurrent_requests creq_run,

fnd_concurrent_programs cprog,

fnd_concurrent_requests creq_base,

fnd_concurrent_processes fcp,

fnd_concurrent_queues fcq

WHERE ( (creq_run.program_application_id = cprog.application_id)

AND (creq_run.concurrent_program_id = cprog.concurrent_program_id)

AND (nvl(creq_base.actual_start_date,sysdate) >=

creq_run.actual_start_date)

AND (creq_base.requested_start_date <=

nvl(creq_run.actual_completion_date,sysdate))

AND (creq_base.request_id = <request_id>))

and fcp.concurrent_process_id = creq_run.controlling_manager

and fcp.concurrent_queue_id = fcq.concurrent_queue_id

AND fcp.queue_application_id = fcq.application_id

order by creq_run.actual_start_date;

SELECT creq_run.request_id,

creq_run.number_of_arguments,

creq_run.argument_text,

to_char(creq_run.actual_start_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_run.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_base.requested_start_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_base.actual_start_date, 'DD-MON-RR HH24:MI:SS'),

to_char(creq_base.actual_completion_date, 'DD-MON-RR HH24:MI:SS')

FROM fnd_concurrent_requests creq_run,

fnd_concurrent_program_serial cprog,

fnd_concurrent_requests creq_base

WHERE ( (creq_run.program_application_id = cprog.RUNNING_APPLICATION_ID)

AND (creq_run.concurrent_program_id = cprog.RUNNING_CONCURRENT_PROGRAM_ID)

AND (creq_base.program_application_id = cprog.TO_RUN_APPLICATION_ID)

AND (creq_base.concurrent_program_id = cprog.TO_RUN_CONCURRENT_PROGRAM_ID)

AND (nvl(creq_base.actual_start_date,sysdate) >=

creq_run.actual_start_date)

AND (creq_base.requested_start_date <=

nvl(creq_run.actual_completion_date,sysdate))

AND (creq_base.request_id = <request_id>));

select fo.profile_option_name, level_id, level_value,nvl(to_char(fpv.profile_option_value), 'NULL')

from fnd_profile_option_values fpv, fnd_profile_options fo

where fo.PROFILE_OPTION_NAME Like 'CONC%'

and fpv.profile_option_id(+)=fo.profile_option_id;