High buffer gets

  • From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 3 Feb 2012 11:31:46 -0500

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

------- ------  -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0

Execute   7811   8711.44    8501.30      14964  294967869      97122

Fetch     7811      0.61       0.61          0          0          0

------- ------  -------- ---------- ---------- ---------- ----------

total    15623   8712.05    8501.92      14964  294967869      97122


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.






Other related posts: