I’ve previously posted articles about monitoring activity within the database. These have focused on analysing details for the session processes from the internal views, such as v$session. A slightly different approach is to look at things from the currently active transactions.
Oracle conveniently provides a dynamic performance view that lists the active transactions in the database; v$transaction. According to the Oracle database documentation:
V$TRANSACTION lists the active transactions in the system.
Okay, so that sounds simple and straight forward… until you look at the details it exposes. A quick scan of the column details in the documentation shows a lot of acronyms (SCN, UBA, XID) and block number details. So, do we need to have intimate knowledge of the internals of Oracle in order to use this view from a monitoring perspective? Not really*.
You’ll notice that the view contains a couple of columns that are quite straight forward:
- START_TIME is noted as the start time (wall clock) for the transaction
- SES_ADDR is the user session object address
Interestingly, the START_TIME is stored not as a DATE or TIMESTAMP but as a VARCHAR2. For format for the column is mm/dd/yy hh24:mi:ss, giving us a 1 second precision as to when the transaction started. The SES_ADDR is a RAW column and can be used to relate to the SADDR column in v$session. So, using the SES_ADDR we can link v$transaction to v$session to extract all the details that we are familiar with.
The following is a simple query I put together during a system problem. Inside the database it was noted that contention was building up with lots of sessions waiting on row level locks and user locks (from dbms_lock). In an OLTP system this was not supposed to happen and so I wanted to gather some details on the extent of this from the sessions with longer than normal transaction durations… where “longer than normal” pretty much meant more than one or two seconds.
SELECT (SYSDATE - TO_DATE(t.start_time,'mm/dd/yy hh24:mi:ss'))*24*60*60 AS trans_dur_secs
, s.sid
, s.serial#
, s.sql_id
, s.sql_child_number
, s.username
, s.program
, s.module
, s.action
, s.client_identifier
, s.blocking_session
, s.final_blocking_session
, s.event
, s.seconds_in_wait
, s.plsql_entry_object_id
, s.plsql_entry_subprogram_id
FROM v$transaction t
, v$session s
WHERE s.taddr = t.addr
-- we'll only look at the application account
--AND s.username = :l_username
ORDER BY
(SYSDATE - TO_DATE(t.start_time,'mm/dd/yy hh24:mi:ss'))*24*60*60 DESC
The query displays primarily session details for those sessions that have an open transaction that originate from a specific account (for me this was the application account I was interested in). The listing is ordered with the details of the longest transaction holder shown first.
A few points to note about the details selected:
- The SQL_ID and SQL_CHILD_NUMBER give details of the currently executing statement by the session, which may not be the statement(s) that have obtained locks for the transaction
- The MODULE, ACTION and CLIENT_IDENTIFIER are used heavily within the system for instrumentation purposes. As noted in previous posts, this strategy is highly recommended.
- The BLOCKING_SESSION gives the SID of the session currently blocking the session being displayed.
- The FINAL_BLOCKING_SESSION gives the SID of the session that is at the end of the blocking lock chain. So, in the scenario of Session A blocking Session B, which in turn is blocking Session C, the entry for Session C will report Session B as the BLOCKING_SESSION and Session A as the FINAL_BLOCKING_SESSION. I believe FINAL_BLOCKING_SESSION is a new attribute for Oracle 11.2.
- The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID show the details of PL/SQL call currently being made. This will only be applicable if the session is invoking PL/SQL, which was the case in the situation I was dealing with.
As it turned out, the query revealed a series of transactions for a particular process. Many of the sessions were idle but holding locks for up to 60 seconds. These idle sessions were part of a distributed transaction where another system in the transaction was having trouble. Oracle was holding the transactions open for 60 seconds before rolling them back as it was supposed to do. The contention was being caused by repeated activity from other sessions (think “click here to retry”) that was being blocked by the waiting transactions. The solution was not to do anything with the database, as it was doing exactly what it was supposed to do, but instead address the root cause outside of Oracle.
*: Knowledge of Oracle’s internals may not be necessary but can be extremely beneficial at times. To anyone wanting to know more about the internals of Oracle then I would recommend Oracle Core: Essential Internals for DBAs and Developers by Jonathan Lewis. A fairly slender volume compared to many computer books these days, it will take a few reads to absorb fully… and it will be time well spent.
Mark Hoxey 