queries that take place in a given transaction

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Mon, 23 Jan 2012 11:02:59 -0500
Message-ID: <CAE-dsO+S8LSaZ3pXny5NiZYttX1E0j3SSwj6RH9rt=oKntRyOQ_at_mail.gmail.com>



DB: 10.2.0.5
I am trying to figure out which sqls take place with in a specific transaction and log it.

We have sessions that are always connected. Remote servers run jobs from them. then wait with the connection open to run another job. Sometimes those transactions run for 2+ hours. Many times its because they did a set transaction and then didn't commit. Other times, SQLs run long. We need to keep our open transactions short (even idle ones with a set transaction) because Golden Gate has to keep logs back to the start of all transactions. (I am not a golden gate engineer, so I don't know exactly why).

right now I am logging the following every 30 minutes

  1. all open transactions over 30 minutes (I log v$session and v$transaction data to 1 table with a sequence based ID)
  2. log all records from v$open_cursor for those SIDs and use the sequence based ID as a key
  3. log all records from v$session_event and use the sequence based ID as a key.

v$open_curosr has all open cursors from the session. How do I figure out which sessions were run since the last open transaction. That would include select statements. Since any DDL starts a transaction. I have the SCN number from v$transaction.

We already have data that we store from the AWR that tracks all the SQL data for that hour. So I can tie those SQL statements to what I already stored to see what ran long. We keep this subset of the AWR since its less data and takes up less space.

Logminer isn't really a viable option. Because its performance intensive. I can't run that from,a job. I would have to do it manually and periodically. I would like to have all of this logged.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 23 2012 - 10:02:59 CST

Original text of this message