High buffer gets

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 3 Feb 2012 11:31:46 -0500
Message-ID: <304F58144267C5439E733532ABC9A3A114039979_at_USA0300MS02.na.xerox.net>

We have an Oracle ERP system (11.5.10) with database version running on Solaris 9. There are batch jobs, submitted via concurrent managers, that have been running fine for a long time. About two weeks ago, we went through a release cycle where new code and functionality was introduced into this environment and since then some of the critical jobs that have been running fine are now running longer most of the times. We have taken traces of jobs and the one thing that is common to all of them is the sheer number of buffer gets from consistent reads. I am pasting statistics from one such job below. This is from a standard Oracle code. The obj# from the raw trace file showed an INVENTORY table that is updated heavily by the application in general. An interesting observation is that this particular job runs fine on days when the inventory table is not heavily updated concurrently by the other jobs, introduced by the new code, that run when this job runs longer. But this behavior is not just limited to this job as there are other critical jobs that have also started to show the same behavior all of a sudden.  

call count cpu elapsed disk query current rows

  • ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 7811 8711.44 8501.30 14964 294967869 97122 0

Fetch 7811 0.61 0.61 0 0 0 49048

  • ------ -------- ---------- ---------- ---------- ----------

total 15623 8712.05 8501.92 14964 294967869 97122 49048  

Has anyone seen this type of behavior? We are going to open an SR with Oracle to see if we are hitting some type of bug here. Any feedback will be appreciated.  




http://www.freelists.org/webpage/oracle-l Received on Fri Feb 03 2012 - 10:31:46 CST

Original text of this message