A quick update on this email thread ...
I did go ahead and set _kgl_latch_count=17, _kgl_bucket_count=6 and play around
with _spin_count [take it up to 4000] to act on the high waits and sleeps
on the library cache.
On 8.1.7 on Tru64, I found that _spin_count was defaulting to 256, not 2000.
Now, the library cache latch isn't a serious issue, although there are
still high waits
and sleeps. cache buffer chains and multiblock read objects latches are
the next highest.
I need to concentrate on the SQLs causing far too many [logical] I/Os,
including FTSs.
The particular view and function I talked about in my previous email still
exist and we
are trying to reduce the usage till we can get some other code to implement
that functionality.
Hemant
X-Original-To: oracle-l@xxxxxxxxxxxxx Delivered-To: oracle-l@xxxxxxxxxxxxx X-Sender: hkchital@xxxxxxxxxxxxxxxxxx X-Mailer: QUALCOMM Windows Eudora Version 5.1.1 Date: Tue, 17 Feb 2004 23:06:08 +0800 To: oracle-l@xxxxxxxxxxxxx From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx> Subject: RE: More Latch Stats : was re Fwd: Re: Library Cache Latch statis tics X-archive-position: 912 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@xxxxxxxxxxxxx X-original-sender: hkchital@xxxxxxxxxxxxxx Reply-To: oracle-l@xxxxxxxxxxxxx
John,
Re "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?)"
What I've found is that the particular view used in the query also calls a function. The View definition goes something like
SELECT col1, FUNCTIONX.col2, FUNCTIONX.col3, col4 FROM ... WHERE
and FUNCTIONX itself is a complicated PLSQL block.
The remote database uses an MV and refreshes it as COMPLETE refresh [since this is a complex view], with a SELECT * FROM <view>
What happens is that the Function PLSQL block is being executed repeatedly !!
The View and Function are in the APPS schema and the remote database connects as another database account with SELECT on the view.
Hemant
At 04:25 PM 16-02-04 -0800, you wrote: >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@xxxxxxxxxxxxx >put 'unsubscribe' in the subject line. >-- >Archives are at //www.freelists.org/archives/oracle-l/ >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >-----------------------------------------------------------------
Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 24-Jan-04}
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
Hemant K Chitale Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 24-Jan-04} "Failure is not an option. It comes bundled with the software [or hardware, as the case may be]"
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
