Occasionally the need arises to enable SQL trace for Oracle Mobile Web Applications (MWA) for WMS or MSCA related E-Business Suite modules to track down the source of poor performing queries. Enabling this is quite easy but may not be obvious when looking at the options in the configuration file. This particular example is assuming E-Business Suite 12.1.3.
Let’s take a look at the MWA configuration file (mwa.cfg) located in $INST_TOP/admin/install. It’s interesting to note that the parameter that we modify to enable SQL Trace, mwa.LogLevel, doesn’t list the value we need for performing a SQL Trace. It appears that the value trace would be the obvious answer but it’s actually performance. The value of trace is used when you want to perform debugging when experiencing application issues.
# LogLevel: Different log levels can be set for obtaining # more debug information. # Possible values include: fatal, error, warning, debug, trace mwa.LogLevel=error
Steps to enable SQL Trace
1. Navigate to the directory $INST_TOP/admin/install
2. Modify the file mwa.cfg and set the parameter mwa.LogLevel=performance.
3. Restart the MWA server. Note: Once this is enabled all users that login to the MWA server will have trace files generated.
4. Have the user perform the mobile application action that is deemed slow.
5. The trace file will be located on the database server in your udump directory. You can generate a TKPROF of the raw trace file to determine the problem query.
6. Once you’re finished SQL tracing you’ll want to disable it by stopping the MWA services, change the mwa.LogLevel to error and restart the MWA server.
Using this option to generate a SQL trace has helped me a few times with tracking down bad performing queries that were then fixed by tuning those queries or from an already available patch by Oracle.
Reference: My Oracle Support Doc ID 277655.1
