Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: More Latch Stats : was re Fwd: Re: Library Cache Latch statis tics

RE: More Latch Stats : was re Fwd: Re: Library Cache Latch statis tics

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 16 Feb 2004 16:25:39 -0800
Message-ID: <35CFD500D7BDCE43B9030BBA5979DC181D9307@ussccem13.hds.com>


Hemant,

>I have been able to identify
>1. A Process which is a DBLink connect from a remote database
>that runs
>the same SQL
>a few thousand times in the space of a few minutes [I didn't
>get the actual
>counts before the session disconnected,
>but apparently it connects a few times a day and may remain
>connected for
>minutes to hours ]
>I will be following up on this on Monday -- this has been a "new"
>implementation in the remote database

I was think of the high recursive SQL counts... If this new process uses a view, and has to parse it, be aware that the View definitions will not be found in the shared pool and this may drive recursive (SYS) SQL calls to get this information. If this connects to the APPS user rather than directly to schema owner, the former would access it via the view in APPS while the latter will not require a view (and possibly reduce recursive calls?)

>2. The standard Forms query on Concurrent Request status
>having been fired
>a few million times
>in 10 days. I do wonder if some user just sits there and
>keeps querying
>concurrent requests

Look at something like OAM (Oracle Applications Manager) that could have been configured by your Apps SysAdmins - this will go against the AOL/FND tables a large number of times depending on the frequency. Or some other DBAs who has configured some other Gooey-Tool!

>3. A particular lookup on FND_LOOKUPS also being a few million times.
>--> a couple of months ago, a 10046 trace on a forms session
>had allowed us
>to identify
>a mistaken change in CUSTOM.PLL that was causing an FND_LOOKUP and the
>firing of a custom
>"security" [ie user validation] function every time a user
>opened a form --
>instead of the intended
>execution only the first time a user logged in ! {And with
>R11 forms, open
>form and close form is very frequent}.
>I wonder if a similar "mistaken" change in CUSTOM.PLL has recurred.

Yes - good one to check! [This underscores the need for a strict change control procedure]

>I will certainly be increasing KGL_LATCH_COUNT from 5 to 17
>Saturday night
>when I am getting downtime.
>Hopefully, by then, I would have more statistics on the SQL
>execution counts.

I would hold off changes to "_" parameters until you find out the cause of the problem, as you may be hiding the actual debilitating change...

Best of luck!
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

Received on Mon Feb 16 2004 - 18:25:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US