RE: High buffer gets

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 3 Feb 2012 12:26:26 -0500
Message-ID: <304F58144267C5439E733532ABC9A3A114039A35_at_USA0300MS02.na.xerox.net>

We run statistics on schemas as shown below: - Once a week on ALL schemas

• Daily on the custom schema

On this particular table, gather statistic was run on 30-JAN-2012 and the job ran longer on 31-JAN-2012 and 01-FEB-2012. The explain plan for this statement is:

Rows Row Source Operation

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

      1 FOR UPDATE (cr7248 pr#51 pw=0 time=0 us)

      2 SORT ORDER BY (cr7248 pr#51 pw=0 time=0 us cost=9 size!6 card=2)

      1 CONCATENATION (cr7248 pr#51 pw=0 time=0 us)

      0 FILTER (cr=0 pr=0 pw=0 time=0 us)

      0 TABLE ACCESS BY INDEX ROWID MTL_ONHAND_QUANTITIES_DETAIL
(cr=0 pr=0 pw=0 time=0 us cost=4 size8 card=1)

      0 INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N5 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 402327)

      1 FILTER (cr7248 pr#51 pw=0 time=0 us)

      1 TABLE ACCESS BY INDEX ROWID MTL_ONHAND_QUANTITIES_DETAIL
(cr7248 pr#51 pw=0 time=0 us cost=4 size8 card=1)

203798 INDEX RANGE SCAN MTL_ONHAND_QUANTITIES_N5 (cr00 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 402327)  

Amir

From: John Clarke [mailto:john.clarke_at_centroid.com] Sent: Friday, February 03, 2012 12:21 PM To: Hameed, Amir; oracle-l_at_freelists.org Subject: Re: High buffer gets  

Without seeing any SQL statements or execution plans, I'd probably guess it's statistics-related (i.e., they're out-of-date, missing, etc). Difficult to say though without seeing more of the trace file.  

• John

From: "Hameed, Amir" <Amir.Hameed_at_xerox.com> Reply-To: "Amir.Hameed_at_xerox.com" <Amir.Hameed_at_xerox.com> Date: Fri, 3 Feb 2012 11:31:46 -0500
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Subject: High buffer gets  

Folks,

We have an Oracle ERP system (11.5.10) with database version 11.1.0.7

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.    

Thanks  

Amir      

--

http://www.freelists.org/webpage/oracle-l

 

 

 


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 03 2012 - 11:26:26 CST