This is one of the technical infrastructure data gathering methodologies and performance recommendations for server machines. The full list is defined in the Server Environment Recommendations topic here.
Any long-lasting SQL queries that reach the hundreds of milliseconds or more indicate that the application server is waiting for the Oracle database. Waiting for the Oracle database can be caused by latency issues, optimistic locking issues, etc. The cause should be analyzed further.
For more information, refer to the Activity topic in the Administration Portal documentation here.
Analyze Long-Lasting SQL Queries
- From the Start Page, click the System Administration button and supply the login credentials.
- On the Activity tab, set the Duration and Date / Time parameters according to when the performance issues were noticed.
- Click the Fetch data button to load the data.
- Click the Details heading to display the Services tab.
- Click the Services tab and drill down to the level where the SQL queries are visible to determine if the SQL query is long-lasting, specifically, more than hundreds of milliseconds.
- Compare the longest-lasting SQL query with the longest-lasting SQL query under the SQL tab using the 'Max duration' column.
In this example, the maximum duration of a SQL query was 9 milliseconds, which is fast. Nevertheless, if long-lasting SQL queries are found with hundreds of milliseconds, then this indicates that the solution is waiting for the Oracle database and further analysis is necessary.
Alternatively, you can download the profiling data and look up the longest-lasting query in the XML, as follows:
- Click the Download data button.
- Open the downloaded file.
- Search for the largest duration which was displayed under the 'Max Duration' column in the SQL tab.
