RE: Oracle trace syntax help?...ACTIVE sessions..."library cache" waits"?
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: <marquezemail@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 9 Jan 2006 10:41:38 -0800
Chris,
Did you change any stats collection or other init.ora parameter
recently? Did you introduce any DB monitoring tool, etc?
John
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Chris Marquez
Sent: Monday, January 09, 2006 6:04 AM
To: Oracle-L@xxxxxxxxxxxxx
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
Other related posts:
