Database Reference
In-Depth Information
Table 24-5.
CLOSE Parameters
Parameter
Meaning
c
CPU time
e
Elapsed time
dep
Recursive call depth
type
Type of close operation:
0: hard close; the cursor is not put into the server-side cursor cache
1: the cursor is cached in a previously empty slot of the server-side
cursor cache, since it executed at least 3 times
2: the cursor is placed in a slot of the server-side cursor cache at the
expense of aging out another cursor, since it executed at least 3 times
3: the cursor remains in the server-side cursor cache
tim
Timestamp
The
CLOSE
entry is suitable for calculating the effectiveness of the server-side cursor cache.
Oracle11
g
is the first release that enables the server-side cursor cache with a default size of 50 slots.
In all prior releases the server-side cursor cache was disabled by default. The paper
Designing
applications for performance and scalability
([Engs 2005]) released by Oracle Corporation in 2005
states that the scalability of applications that repeatedly soft-parse the same SQL statements
does improve with a sufficiently large value of
SESSION_CACHED_CURSORS
. Applications that
avoid repeated soft-parses, e.g., by enabling a client-side cursor cache, are even more scalable.
The method for enabling a client-side cursor cache depends on the Oracle API used. The
Oracle JDBC drivers include support for client-side cursor caching through the methods
setImplicitCachingEnabled
and
setStatementCacheSize
of the class
OracleConnection
.
COMMIT and ROLLBACK
The ORACLE DBMS does not require clients to explicitly start a transaction. The DBMS auto-
matically opens a transaction as soon as the first data item is modified or a distributed operation,
such as a
SELECT
from a table via a database link is performed. The latter operation takes a TX
and a DX enqueue, which are released when
COMMIT
is issued. Transaction boundaries in a trace
file are marked by
XCTEND
entries. The minimum SQL trace level for enabling
XCTEND
entries is 1.
Their format is as follows:
XCTEND rlbk=[0-1], rd_only=[0-1]
Parameters used in
XCTEND
entries are explained in Table 24-6.
Table 24-6.
XCTEND Parameters
Parameter
Meaning
rlbk
Short for rollback,
rlbk=0
:
COMMIT
,
rlbk=1
:
ROLLBACK
rd_only
Read only transaction,
rd_only=0
: read/write operations have occurred,
rd_only=1
:
read only—no data changed