Re: Oracle trace syntax help?...ACTIVE sessions..."library cache" waits"?
- From: goran bogdanovic <goran00@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, marquezemail@xxxxxxxxx
- Date: Mon, 16 Jan 2006 23:14:02 +0100
Hi Chris,
You may try to identify why you have additional parsing (you said that
app. is designed to use bind variables) by checking
V$SQL_SHARED_CURSOR for cursors in V$SQLAREA which have big count for
parse_calls (comparing to executions).
You may also try to set session_cached_cursors on a DB level to
relieve some of lib. cache lookups.
HTH.
Goran
Date: Mon, 9 Jan 2006 09:03:58 -0500
From: Chris Marquez <marquezemail@xxxxxxxxx>
Subject: Oracle trace syntax help?...ACTIVE sessions..."library cache" waits"?
-Env:
Oracle 9205
RHEL3
Web Application
-Issue:
New problem; "ACTIVE session stacking..."latch free" "library cache" waits".
What is the best database trace syntax and parameters to use *during*
problem time to find/analyze session SQL (and waits)?
-Detail:
I have a (new) situation. Whenever any (additional) load is put on our DB
and DB server from reporting SQL, batch data load, or RMAN backup for
example, our core application starts experiencing significant session ACTIVE
stacking and latch free" "library cache" waits" and the load then spirals.
Normally our app runs and our load is under 1, spikes at 2. We start dying
between 6-10 Linux load and nonfunctional at 15+ Linux load. Years past we
have been able to function at loads in the 20's+ and when it *was* a RAC
database the load was always 4-5 (IO mostly).
After "chasing" the LOAD causing session or process for a couple of weeks
I'm convinced now that the additional LOAD caused by these one off processes
is not the problem at all, but rather the core application will not scale.
On the surface it seems obvious we have a SQL
parse/execute/reuse/bind-variable issue...however this is a rather new
problem...could be new code? We did change physical DB servers recently,
but no OS-Oracle version changes...overall everything runs faster.
In the past like many we have had SQL reuse issues, but additional code
tuning and cursor_sharing=FORCE parameters have helped. Again, this seems
to be new.
Thanks in advance.
--
Chris Marquez
Oracle DBA
--
//www.freelists.org/webpage/oracle-l
Other related posts:
