RE: Oracle trace syntax help?...ACTIVE sessions..."library cache" waits"?

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Marquez Sent: Monday, January 09, 2006 6:04 AM
To: Oracle-L_at_freelists.org
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 Received on Mon Jan 09 2006 - 19:41:38 CST