RE: More Latch Stats : was re Fwd: Re: Library Cache Latch statis tics
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 17 Feb 2004 23:06:08 +0800
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
-----------------------------------------------------------------
Other related posts:
