[Db-derby Wiki] Update of "StmtExecutionPlan" by SusanCline

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by SusanCline:
http://wiki.apache.org/db-derby/StmtExecutionPlan

New page:
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 Tunning Guide, under the Section "Working with RunTimeStatistics".

Intrepreting 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.)

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:

http://db.apache.org/derby/docs/dev/devguide/rdevconcepts8424.html