Skip to main content

Obtaining a performance trace

If you experience performance issues with certain pages or transactions, you may be requested to obtain a SQL performance trace when raising a support call.  Before you raise a performance service request, please ensure you have already checked the following:

  • That the Gather Schema Stats concurrent program has been run recently and is running on a regular basis.  The parameters must cover the XXAS schema.
  • You have enabled Database Result Cache with the minimum recommended parameters.  Please see Installation - Result Cache.
  • That your Oracle environment is generally healthy and performing well in standard Oracle applications.

To obtain a SQL performance trace, follow these steps:

  1. Ask your Database Administrator to navigate to the udump directory on the Database Tier and check the latest files (ls -lrt).  A DBA can find the udump directory with the following SQL query:
SELECT value
FROM   v$parameter
WHERE  name = 'user_dump_dest';
  1. Log in as the user who is experiencing poor performance and navigate to the point just before the performance issues
  2. In another browser or on a separate computer or device, set the XXAS: Enable SQL Trace profile option to Yes at User level for this user in step 2.

Do not use a separate browser tab because the user will have been logged out.

  1. You will also need to clear the cache using the Functional Administrator responsibility after setting this profile option:

Functional Administrator: Core Services: Caching Framework: Global Configuration: Clear All Cache

  1. As the user in step 2, perform the navigation or actions that have performance issues.
  2. In the other browser or computer or device, clear the XXAS: Enable SQL Trace profile option value set in step 3 and again clear the cache through Functional Administrator
  3. Ask the Database Administrator to generate tkprof'd files for all the new files created since the DBA performed step 1.  A DBA should obtain the tkprof using the following command:
tkprof <raw_trace_file>.trc <new_tkprof_file>.prf explain=apps/<apps_pw> table=xxas.xxas_explain sort=(EXECPU,FCHCPU)

Sections in this article