Oracle trace syntax help?...ACTIVE sessions..."library cache" waits"?
- From: Chris Marquez <marquezemail@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Mon, 9 Jan 2006 09:03:58 -0500
-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
Other related posts:
