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:
- 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';
- Log in as the user who is experiencing poor performance and navigate to the point just before the performance issues
- 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.
- 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
- As the user in step 2, perform the navigation or actions that have performance issues.
- 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
- 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)