| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Update on : Fwd: RE: More Latch Stats : was re Fwd: Re: Library Cache Latch statis tics
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_at_freelists.org
>Delivered-To: oracle-l_at_freelists.org
>X-Sender: hkchital_at_pop.singnet.com.sg
>X-Mailer: QUALCOMM Windows Eudora Version 5.1.1
>Date: Tue, 17 Feb 2004 23:06:08 +0800
>To: oracle-l_at_freelists.org
>From: Hemant K Chitale <hkchital_at_singnet.com.sg>
>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_at_freelists.org
>X-original-sender: hkchital_at_singnet.com.sg
>Reply-To: oracle-l_at_freelists.org
>
>
>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_at_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
> >-----------------------------------------------------------------
>
>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_at_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
>-----------------------------------------------------------------
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]"
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 04 2004 - 07:06:46 CST
 |
 |