Re: Trace COMMIT/TRANSACTION

From: onedbguru <onedbguru_at_yahoo.com>
Date: Tue, 28 Dec 2010 20:02:02 -0800 (PST)
Message-ID: <8a38865a-3bb0-48c0-8a9f-4179f68926b1_at_g25g2000yqn.googlegroups.com>

On Dec 28, 1:08 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 28, 3:55 am, valigula <valig..._at_gmail.com> wrote:
>
> > Hi All,
>
> > Is it possible to trace commit's in oracle 11g ?,  A process is
> > inserting data into several database tables using hibernate, i would
> > like to check if commit is done one time per all the inserts or one
> > per every one.
>
> > I have activated the sql_traces/tkprof, but the stats  i am gaining
> > are referring to every query and not to the transaction.
>
> > Do i need to use AWR ? or any other tool?
>
> > Thanks in advance
>
> Also see the examples for v$session inhttp://www.dbspecialists.com/files/presentations/wait_events_10g.html
> so you can at least see if the log file sync is an issue.
>
> I also don't know anything about hibernate, but you might want to
> search about pl/sql using batch commit at asktom.oracle.com.  If
> hibernate does that, counting commits could be misleading.  I don't
> think it does, from a quick google about needing jdbc batching for
> mass inserts.  But that's all beyond me.
>
> jg
> --
> _at_home.com is bogus.
> OMG!  "Doctor" Turi is still around!http://www.examiner.com/astropsychology-in-phoenix/do-you-have-a-brai...
> This guy used to annoy ca.earthquakes with psychic earthquake
> predictions.

If you are licensed for AWR, then hopefully your insert statement should show up in the top 10 and you can look at number of rows inserted - these are commited inserts for the time period in question.

AWR is your friend - and if your company did not get it thrown in for "free" during contract negotiations, then fire the negotiator. Oracle is using your resources of approximately .5% to 1% to store and execute AWR stats whether or not you are licensed to use it. You cannot remove it, you cannot not install it. Therefore it should be free unless they agree to pay you for using your resources. [last 3 companies I dealt with, got it for free]

 Sort of on-topic here... Please note that with 11gR2 (linux and not sure about other platforms) there appears to be a bug where the developers left a trace level 4 turned on for all triggers potentially filling up your trace directory/file system.

This is On-Topic, because you could also potentially see high log file sync times and even database hangs depending on workload especially when the directory has 30K+ trc and trm files. The number of files is also dependent upon the number and types of triggers in your database.

Bug, see note 1098363.1 on My Oracle Support.

1. Move to Oracle 12.1 where the bug is fixed, once released. OR
2. Install Patch 9382101 if available for your DB version and OS platform.

Received on Tue Dec 28 2010 - 22:02:02 CST