Re: Trace COMMIT/TRANSACTION
Date: Wed, 29 Dec 2010 09:31:34 -0800 (PST)
Message-ID: <45247fb5-6ac5-43b1-9c26-046fa7d2de5c_at_j32g2000prh.googlegroups.com>
On Dec 28, 8:02 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> 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]
The negotiator for my company went out of business years ago. Ten years ago, before I got involved, the licensing came through a third party provider who apparently did some shady things, resulting in lawsuits all around. So I came in as an outside performance consultant to fix some problems, into a situation where Oracle was considered a big bad bully. Since I've been here, they haven't done anything to fix that perception, including threatening audits when I tried to ask the salesidiot simple licensing questions to try to improve the situation. Now I'm mostly an app person, performance tuning is the least of my duties, unless things go to hell of course.
Anyways, now on 10.2.0.4 and trying to be a good web 2.0 DBA, I've been watching periodic commit, log buffer and log file parallel write waits when certain programs run. I grabbed a screen print from dbconsole yesterday, and after drilling down to figure out where all the colorful blotches came from during two consecutive times of almost enough load for people to start complaining I got some confirmation of my suspicions. The user i/o component came from an inventory program - fair enough - log buffer waits came from that and another mass load analysis program - also fair enough - but a big chunk of commit waits and log buffer waits come from OMS insert into mgmt_severity. log_buffer has tuned itself to 14.5M, but it seems very much attuned to the normal situation, not these times of additive heavy writes. I'm thinking of going up a granule there, but I don't really have a way to simulate the load to see if there is a legitimate change. Archives are switching like clockwork, no waiting there.
Of course, with nobody complaining, why should I even be looking at this? I know that there will be situations where a couple of people will run these resource hungry programs, plus the OMS deciding to write stuff because it sees this hogging, to the point where people will complain. I hesitate to put any resource limits on anything, because this situation is a result of cheapness, which won't change unless users see some suffering feedback, and the worst offenders are the reports for upper management.
Now even though management is entirely unwilling to give Oracle any more money than they have to, my immediate management does understand why I would want to license and use AWR - pretty pictures can be a lot more convincing than dry lists of numbers, and I think it is a useful performance tool. How do I get free AWR licensing in a supportrenewal only situation with a dick salesperson? I'm under the impression the list price is just way out of line, though it's been a long time since I've even tried to figure it out. Of course, I'm on hp-ux Itanium, for which Oracle has explicitly raised core charges for new servers. I'm soliciting any suggestions, not just complaining here. It seems I'd have to get like 25 named user minimum licenses for several products at $100/user... for something only I would use. I wind up not doing a whole lot of the manual things that I would have to do in place of it, since people see development efforts, not tuning efforts, it's difficult to set measurable goals for tuning when there aren't obvious problems. Some of the potentially worst oltp (barcoding and that kind of inventory automation are being added and ramped-up) programs aren't showing up on the radar at all yet.
>
> 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.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/photos/galleries/2010/dec/28/top-photos-2010/16597/Received on Wed Dec 29 2010 - 11:31:34 CST