queries that take place in a given transaction
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Jan 2012 11:02:59 -0500
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » queries that take place in a given transaction - Dba DBA
