RE: 2 questions about transactions.

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Fri, 9 Dec 2011 14:52:37 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4454E46B2_at_LITIGMBCRP02.Corp.Acxiom.net>

For Question #2, here's what I use. I'm sure it was built as a combination of many contributors from this list:

---

--
--  txn_progress.sql
--
--  This query can be used to view progress of an existing transaction.  USED_UREC will show
--  how many records have been UPDATEd, DELETEd, or INSERTed.  It will decrease in size during
--  a rollback, which helps to see a rollback's progress.
--
----------------------------------------------------------------------------------------------

COLUMN sid FORMAT 99999
COLUMN current_time FORMAT a10 HEADING 'Current|Time'
COLUMN start_time FORMAT a18 HEADING 'TXN Start|Time'
COLUMN rows_per_sec FORMAT 99,999,999.99 HEADING 'TXN Rows|Per Sec'
COLUMN used_urec FORMAT 999,999,999 HEADING 'Undo Rows|Written'
COLUMN rollback_mb FORMAT 99,999.99 HEADING 'Undo|MB Used'
COLUMN command_or_action FORMAT A100 HEADING 'SQL Command or Action' WRAP

SELECT s.sid


     , t.start_time

     , TO_CHAR(sysdate, 'HH24:MI:SS') current_time

     , CASE WHEN (sysdate - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) = 0

          THEN t.used_urec
          ELSE t.used_urec / NVL(((sysdate - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 86400), 1)
       END  rows_per_sec


     , SUBSTR(s.username, 1, 15) username

     , program

     , DECODE(BITAND(t.flag,128), 0, NULL, 'Rollback') status

     , t.used_urec

     , ROUND(r.rssize / 1024 / 1024, 2) rollback_mb

     , DECODE(sq.sql_text, NULL, DECODE(aa.name, NULL, 'UNKNOWN', aa.name), sq.sql_text) command_or_action

  FROM v$transaction t


     , v$session s

     , v$rollstat r

     , v$sql sq

     , audit_actions aa

 WHERE (t.xidusn = r.usn)
   AND (t.addr = s.taddr (+))
   AND (    s.sql_hash_value = sq.hash_value (+)
        AND s.sql_address = sq.address (+))
   AND (s.command = aa.action)
 ORDER BY t.start_time, s.sid;

DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring_at_acxiom.com
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM  

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.


-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA
Sent: Thursday, December 08, 2011 9:58 AM
To: ORACLE-L
Subject: 2 questions about transactions.

Version: 10.2.0.5
Question 1:

SET TRANSACTION command

We are looking at logminer and noticing that one of our applications is
issung a set transaction read write. The problem with this is that oracle
records this as an open transacxtion. We are using golden gate and it has
issues with long running transactions. So we have processes that connect to
oracle, issue this, and then go idle. Before we go back to the developers,
we want to dig up some more information. Their application probably doesn't
need to do this. They may not even know they are doing it. It could be
built into what ever library they are using to connect to the database. We
noticed that set transaction is not recorded in v$sqlarea. Is there
anywhere else other than logminer where we can dig up data on set
transaction commands? I also noticed that you can name your transaction..
see link below. is that recorded in the data dictionary? I looked at the
docs for v$rtransaction and I do not see a field for transaction name?


Question 2:

 I googled this before I asked, but I didn't really find what I was looking
for. Is there a way to estimate how much work it will be to kill and
rollback a session? I have looked at v$undostat, but I am not sure how to
take the data I am seeing there and turn it into a rough estimate of how
long it will take to rollback. I know it will be based on how busy the
database is and it is application specific. Has anyone done any work with
estimate how long it takes to roll something back:?


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 09 2011 - 08:52:37 CST