Understanding SQL Trace

...Previous

Color legends indicating the processes in a trace 2

The above screen shot indicates that the query execution time has exceeded its threshold with the link pink color. A change in object name is indicated with the change in color to yellow. These colors thereby indicate whether the query is well written and also the current status of the database server.

Extended list

(From basic trace list: click extended list button in application toolbar to view the extended list). The extended list shows the cursor id declared for the SQL statement, the connection type also.

Cursors: A cursor is a handle or name for an area in memory in which a parsed statement and other information for processing the statement are kept; such an area is also called a private SQL area. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically for the parsing of SQL statements embedded within the application.

Identical select statements

Use the menu path: Gotoà Identical Selects to view a comprehensive list of identical select statements executed during the trace and the number of times they were executed. Identical select statements are grouped based on the where clause.

Note that identical select statements have similar execution plan used for their processing thereby saving time of execution and provide increased performance, too.

Identical select statements displayed

The above screen shot shows the list of identical select statements and the number of time they got executed.

Database Operations Measured by SQL Trace

The SQL Trace analysis helps in measuring the execution time of the following data base operations that are performed when executing an SQL statement.

1) DECLARE: This operation declares a new cursor and assigns the SQL statement to that cursor created.

2) PREPARE: This operation converts the SQL statement into native SQL and frames out an execution plan for that statement.        

3) OPEN: This operation opens the declared cursor and passes the parameters for database access.

4) FETCH: This operation passes one or more data records fetched to the database interface of the R/3 system.

5) REOPEN: Once a new select statement is brought in then this operation opens the cursor previously declared for previous select statement and performs new parameter passing to the database.