Message-ID: <35CFD500D7BDCE43B9030BBA5979DC181D9307@ussccem13.hds.com>
From: John Kanagaraj <john.kanagaraj@hds.com>
To: "'oracle-l@freelists.org'" <oracle-l@freelists.org>
Subject: RE: More Latch Stats : was re Fwd: Re: Library Cache Latch statis
	 tics
Date: Mon, 16 Feb 2004 16:25:39 -0800
MIME-Version: 1.0
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle-l@freelists.org

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!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------