For each SQL statement in the program, the trace list shows a number of lines corresponding to the various operations, thus making the list very huge in size. We can see a summarized (view), and also apply table-related functions on the list values. In this recipe, we will see how to apply these functions.
For displaying summarized information, carry out the following steps:
The summarized table view generates a shorter list, as shown in the following screenshot. The details of the operations such as OPEN, FETCH, and so on are omitted, and only one line exists for each SELECT or UPDATE statement used in the program. In addition, no detail of the SQL statement or parameters passed from the program for the WHERE
clause is shown. If there are multiple accesses to a particular table, each access is shown in the list along (in the following screenshot) with the time at which the access was made:
If we further want to refine and want an Aggregate Table Accesses list showing the total time taken for accessing a particular table along with the number of records read and the number of times the respective table was accessed throughout the trace period, we will go for the Aggregate Table Accesses view. For each table accesses, there is a single line shown in this list, as shown in the following screenshot:
We also have a percentage column that will show the table whose access takes the most percentage of the runtime.
If the appropriate path is chosen, a list of identical selects may also be generated.
Here, the number of times the SELECT
statement was executed is shown, along with the number of records read and the duration of the execution. Reducing/eliminating the number of identical selects (particularly the more expensive ones in terms of execution time) will greatly help in performance optimization. They are just repeated SELECT
statements tha only consume runtime and resources.
18.191.192.59