Depending on the nature of the performance problem, there are different methods to capture the raw trace file.
How to generate a trace file (forms) :
If the performance problem occurs while using Oracle forms, a trace file can be obtained using the following steps :
a. Navigate to the point in the application right before you are experiencing the problem.
b. Turn trace on by: Help > Diagnostics > Trace > Trace with Binds and Waits
c. Duplicate the error and then stop immediately afterwards.
d. Retrieve trace file from the user_dump_dest, which can be located as follows: select value from V$PARAMETER where name like 'user%';
How to generate a trace file (HTML) :
If the performance problem occurs while using web pages, a trace file can be obtained using the following steps :
1. In the System Administrator responsibility set the system profile FND: Diagnostics to YES at user level. (Note: Enabling this profile will consume greater system resources and may impend database and applications performance. Thus tracing an event during hours of low system usage may be a consideration).
2. Login to self service using the user id for which the above profile is enabled and select the responsibility applicable to the issue being traced.
3. In a self service application page click on the global button Diagnostic.
5. Choose the option Set trace level and set it to Trace with Binds and Waits.
6. Navigate Back to Home and go to the self service page for which the issue is reproducible.
7. Reproduce the issue while ensuring that the performance problem is captured in the trace.
8. Click on the Diagnostic link and select the Set trace level option. Numbers will appear on the left side of the screen. Note down these numbers as these are the identifiers of the trace files on the server. Disable the tracing.
9. Using these identifiers the DBA/System Administrator can get all the trace files from the server directory location where the database trace are saved.
How to generate a trace file (Concurrent Request) before 11.5.10 :
If the performance problem occurs while running a concurrent program (including reports), a trace file can be obtained using one of the following methods :
Prior to 11.5.10, the following steps can be used to generate a trace file :
1. Navigate Responsibility: System Administrator > Profile > System >Query User: User submitting the Report
Profile: Initialization SQL Statement - Custom
2. Click on User column - Edit Field and enter
begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'||''''); end;
3. Set Profile Option: SLA: Enable SQL Trace - Yes
4. Reproduce the issue by submitting the concurrent program
5. Use the following SQL to locate the trace file:
select value from v$parameter where name = 'user_dump_dest';
How to generate a trace file (Concurrent Request) on 11.5.10 and above :
From 11.5.10 onwards, the above method can still be used to create a trace file, however, there is an easier method to generate the trace file for concurrent programs. The following steps can be used :
1 - Navigate to System Administrator responsibility
2 - Navigate to Profiles->System
3 - Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
4 - Navigate to Purchasing responsibility
5 - From the Requests form, choose the Concurrent Program and set the required Parameters
6 - Click the Debug button
7 - Check the SQL Trace checkbox and specify Trace with Binds and Waits
8 - Submit the Concurrent program
9 - Retrieve the trace file created.
How to create a tkprof file :
TKPROF reformats the raw data so that it is easier to review and also shows the time taken by each SQL statement and can be used to determine the expensive SQL statements. Once the trace file has been gathered, a tkprof file can be created from the raw trace using the steps below :
a. Retrieve the trace file.
b. Issue a command like the following to create a TKPROF version of the trace file. This command sorts the results with the longest running queries first:
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)'
c. Additionally, the following command can be used which will create a tkprof that limits the results to the top ten queries:
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)' print=10
Gathering Statistics Concurrent Requests
The following recommended concurrent requests are available in Oracle Applications for gathering statistics:
Analyze All Index Columns
Backup Table Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics
Please refer to the Oracle Application System Administration Guide for concurrent request parameters. Here are common parameters and their meanings:
- Schemaname Enter the three character schema to analyze. An example for entry would be MRP or INV or ONT or ALL. You may enter ALL to analyze every defined App schema.
- Estimate_percentPercentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
- DegreeEnter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.
- Internal FlagIf the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is
'BACKUP' then it does an export_table_stats prior to gathering the statistics. - Restart Request IdEnter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave
this parameter null.
Summary of FND_STATS Subprograms
CREATE_STAT_TABLEThis creates the table where the statistics can be backed up. This table is created in the AOL (APPLSYS)schema and is called FND_STATTAB.
BACKUP_TABLE_STATS
Backups the statistics of the given table into FND_STATTAB. Statistics can be backed up with different statid . The default statid is 'BACKUP'. It is possible to keep different versions of the backup as different statid's. This also backs up the related index stats by default.
Concurrent Request equivalent is "Backup Table Statistics".
BACKUP_SCHEMA_STATS
Backs up the statistics of all the objects of the given schema into FND_STATTAB. Statistics can be backed up with different statid. The default statid is NULL. It is possible to keep different versions of the backup as different statids.
RESTORE_SCHEMA_STATS
Restore the previously backed up schema statistics.
RESTORE_TABLE_STATS
Restores the previously backed up table statistics from a given statid.
The default statid is 'BACKUP'.
Concurrent request equivalent is "Restore Table Statistics".
RESTORE_TABLE_STATS
This procedure retrieves statistics for a particular table from the FND_STATTAB for the given statid(optional)and stores them in the dictionary. Cascade results in all index and column stats associated with the specified table being imported as well.
GATHER_SCHEMA_STATS
This procedure gathers statistics for all objects in a schema. If schema name is specified as 'ALL' then all the Apps specific schema (having an entry in FND_PRODUCT_INSTALLATIONS table) statistics are gathered. Before gathering the
statistics, this also takes a backup of the existing statistics so that in case the database slows down after gathering statistics, things can be restored to its previous status. The statid used for this backup is 'NULL'. Also after gathering the schema level statistics this procedure creates the histogram for the specified columns in the FND_HISTOGRAM_COLS tables. And lastly it populates a default statistics for all the INTERFACE tables as specified in the FND_EXLCUDE_TABLE_STATS table.
If the procedure fails at any time during operation, it can be restarted by supplying the request id of the run that failed. The request id can be captured when the procedure is started from concurrent request manager.
Concurrent request equivalent is "Gather Schema Statistics".
GATHER_SCHEMA_STATISTICS
This is a simpler call for using GATHER_SCHEMA_STATS from the SQL prompt. This should be used for gathering schema statistics for a single schema only. Schema name should rarely be 'ALL'. Downside to using 'ALL' is that you will
not know if there was indeed any error. If it fails at any time during operation, it can be restarted by supplying the request id of the run that failed. The request id. To identify the request id for the last run select the maximum value for request_id from table FND_STATS_HIST.
This internally calls GATHER_SCHEMA_STATS. The only difference here is that this procedure doesn't have a output parameter and hence is easier to call from the SQL prompt.
GATHER_INDEX_STATS
This procedure gathers index statistics. It is equivalent to running ANALYZE INDEX[ownname.]indname [PARTITION partname] COMPUTE STATISTICS | ESTIMATE STATISTICS SAMPLE estimate_percent PERCENT.
It does not execute in parallel.
If the value of backup_flag is 'BACKUP' then it does an export_table_stats before gathering the stats. The exported data is stored in FND_STATTAB . If the value of backup_flag is anything other than 'BACKUP' export_table_stats
is not performed.
GATHER_TABLE_STATS
This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible. This operation does not parallelize if the user does not have select privilege on the table being analyzed. If the value of backup_flag is 'BACKUP' then it does an export_table_stats before gathering the stats. The exported data is stored in FND_STATTAB. If the value of backup_flag is anything other than 'BACKUP' export_table_stats is not performed.
ANALYZE_ALL_COLUMNS
This procedure analyzes all the indexed columns for all the tables in a given schema.
Concurrent request equivalent is "Analyze All Index Columns".
LOAD_XCLUD_STATS
This procedure loads the default stats as specified in the SEED data table FND_EXCLUDE_TABLE_STATS. There are two versions of this procedure. One loads for all the tables for a particular schema and the other one loads for a given table in a given schema.
LOAD_XCLUD_TAB
This procedure is for Oracle internal purpose only and should not be used by the customers. This is used for SEED data manipulation for interface tables.
LOAD_HISTOGRAM_COLS
This procedure is for Oracle internal purpose only and should not be used by the customers. This is used for SEED data manipulation for histogram columns.
CHECK_HISTOGRAM_COLS
This gives a report on which indexed columns for a list of given tables is a good candidate for histograms. For a given list of comma separated tables, this procedure checks the data in all the leading columns of all the non-unique indexes of those tables and figures out if histograms needs to be created for those columns. A count(*) of at least 3000 rows in the table(s) is
recommended. The procedure needs to be run from the SQL prompt after setting the serveroutput on.
VERIFY_STATS
This gives a report on the stats of the given list of objects.
For a given list of comma separated tables, or for the given schema name, this procedure reports the stats in the data-dictionary tables for the tables, indexes and the histograms. Run FND_STATS.VERIFY_STATS at any time to check
when statistics were last gathered.
No comments:
Post a Comment