Interpreting Some Information in the Statement Execution Plan

Enabling the display of the execution plan

The RUNTIMESTATISTICS attribute can be used to obtain information about the length of the compile time and the execution time for each statement executed within a connection and the statement execution plan itself. The statement execution plan is sometimes referred to as the Query Plan.

An overview for using RUNTIMESTATISTICS is available in the Derby documentation in the Tuning Guide, under the Section "Working with RunTimeStatistics".

Interpreting the information in the Statment execution plan depends on various factors including the isolation level of the transaction, the type of result set and the type of statement (insert, update, delete or select.)

Reading join ordering information from the execution plan

You can determine the QueryPlanJoinOrder by reading the plan output.

Reading lock information from the execution plan

Looking at the truncated output from the statement execution plan below one might ask how many rows were locked and how many rows are currently locked?

Note that this example is using an isolation level of read committed and is using row level locking.

Source result set:
Index Scan ResultSet for TS_MYTAB using constraint PK_TS_MYTAB at read committed 
isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16 

Two of the parameters being reporting on here, Rows seen and Fetch Size do not relate directly to the number of rows locked during the execution of the statement.

For read committed, repeatable read and serializable Derby obtains a lock on every row that it looks at. For serializable, Derby actually obtains one more lock on the previous key to protect a range of keys for phantom protection. Depending on the isloation level, these locks may or may not be held until the end of the transaction.

However, in the case of read committed, which the example above is using, the read locks are released before the end of the transaction. This means at any one time in a single table scan there will be a value of zero or one for the number or locks outstanding.

For example in the scenario of a read committed isolation level using row-level locking with a table containing 1000 rows, 1000 locks will have been obtained, but 999 of them will have been released, and you will only be holding 1. This behaviour is true regardless of the fetch size.

For additional information about the scope of locks see the Derby documentation:

This page was compiled with information gleaned from Sunitha Kambhampati and Mike Matrigali.

StmtExecutionPlan (last edited 2009-09-20 22:12:50 by localhost)