Re: help tracking down root of high executions.
Date: Tue, 4 Mar 2008 21:09:17 +0100
Message-ID: <47cdac74$0$14352$e4fe514c@news.xs4all.nl>
"Ben" <benalvey_at_yahoo.com> schreef in bericht
news:5995aa96-ac0d-4dd4-a80d-71ef387bbbfd_at_e6g2000prf.googlegroups.com...
On Jan 9, 9:21 am, Ben <benal..._at_yahoo.com> wrote:
> On Jan 7, 12:28 pm, Ben <benal..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Jan 7, 11:43 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > > On Jan 7, 11:06 am, Ben <benal..._at_yahoo.com> wrote:
>
> > > > 10.2.0.2 AIX 5.2 64bit.
>
> > > > I've got a few sql statements that are being executed once every
> > > > centisecond or so, according to an AWR report. I can see the type of
> > > > program that is executing the statement but our ERP system is setup
> > > > in
> > > > a way that pretty much everyone gets the same database user name, so
> > > > it's hard to track down where or what is executing the statements.
> > > > With my knowledge of our ERP and our database in general I can't of
> > > > anything that should be executing this rapidly.
>
> > > > What would be a good way to attempt to find out exactly what is
> > > > causing this?
>
> > > Hi Ben,
>
> > > In the past, I have just selected everything from v$sessionwhere the
> > >sql_id= the one of interest. If it is your ERP, it may set the
> > > module or action elements of the v$sessionstructure and show you at
> > > least what may be causing it. The machine in v$sessionmay also be
> > > different than your ERP application servers.
>
> > > If it started out of the blue, I have also seen it be due to a recent
> > > patchset, or custom developer install, so I would check that also.
>
> > > HTH,
>
> > > Steve
>
> > I tried this and did find asessionwith the correspondingsql_id.
> > That user is at lunch right now though.
> > There are several other statements with the same kind of issues, they
> > look to all be originating from a few servers that are our terminal
> > servers for our users. The sessions all seem to be idle for some time.
> > every now an then when I run the select a randomsessionshows up but
> > just briefly.- Hide quoted text -
>
> > - Show quoted text -
>
> Everytime I find asessionthat has that correspondingsql_id, thesessionis
> inactive and shows the last activity was at 1 minutes past
> the hour. (that is based off of last_call_et)
>
> The few sessions that I found to be using that sql and be active, I
> turned on tracing but they weren't executing the sql at an alarming
> rate ( maybe 10 to 20 times in 10 minutes ) which is normal. Is there
> a bug with AWR reporting that makes it appear that the sql is being
> executed every centi-second?- Hide quoted text -
>
> - Show quoted text -
I have yet to totally track this down. The above statment about the session showing up idle since 1 minute past the hour is false. I had a typo in my sql statement causing the time to display the month instead of the minute. MM instead of MI.
Anyway, the one statement in particular is executing about 15,000
times a minute but yet it never shows up when I select from v$session
for that sql_id.
I opened an SR to try to research a bug with statspack or awr for
reporting more executions than are actually occuring and support
confirmed no bugs related to it.
You're not the first and certainly not the last to make the mm/mi error. Even the Oracle Portal export utility shows the month in stead of minutes in their filenames....
Shakespeare Received on Tue Mar 04 2008 - 14:09:17 CST