Re: Blocks are not getting cache

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 25 Nov 2008 10:31:59 -0800 (PST)
Message-ID: <7a60a3b4-559d-49d4-b2c7-5f77c1675183@f3g2000yqf.googlegroups.com>


On Nov 25, 8:22 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 24, 10:20 pm, "lyx..._at_gmail.com" <lyx..._at_gmail.com> wrote:
>
>
>
> > Hi All -
>
> > HP-UX 64bit, Oracle 9.2.0.5
>
> > We are facing this problem in one of our instance. This is 11i
> > instance and near about 5TB.
>
> > This query is running fine in of the development instance but in one
> > instance it's taking much time. Same query is completing in less than
> > a minute but here it's taking 7 to 8 minutes.
>
> > I have 2 doubts here
>
> > 1 - Why it's taking much time at first place.
>
> >      We are using same storage for both boxes
> >      We have 24 CPUs + ton of ram on this box where other one is much
> > smaller
> >      Both instance were cloned from one production ( in production is
> > also running fine )
> >      Dev and this newly system has same ( almost ) no. of records in
> > both tables
> >      same indexes .. proper analyzed. Trace showing that same plan is
> > being used.
>
> >     But still, because of this physical reads it is taking much time.
>
> > 2 - once this query is done with given literals and it takes 7/8
> > minutes and if you just press
> >     enter again ( / ) then again it's taking same time like 7/8
> > minutes.
>
> >      Again it's showing same no. of physical reads where as in other
> > box it shows 0 physical reads second time.
>
> > Can any please help me in this... ?
>
> > SELECT count(*)
> > FROM
> >  FND_ATTACHED_DOCUMENTS FAD, FND_DOCUMENTS FD, FND_DOCUMENTS_TL FDTL
> > WHERE
> >   FAD.DOCUMENT_ID = FD.DOCUMENT_ID AND FD.DOCUMENT_ID =
> > FDTL.DOCUMENT_ID AND
> >   FDTL.LANGUAGE = USERENV('LANG') AND FAD.ENTITY_NAME = :B8 AND
> > FAD.PK1_VALUE
> >   = :B7 AND (:B6 IS NULL OR FAD.PK2_VALUE = :B6) AND (:B5 IS NULL OR
> >   FAD.PK3_VALUE = :B5) AND (:B4 IS NULL OR FAD.PK4_VALUE = :B4) AND
> > (:B3 IS
> >   NULL OR FAD.PK5_VALUE = :B3) AND (:B2 IS NULL OR (FAD.CATEGORY_ID
> > = :B2 OR
> >   (FAD.CATEGORY_ID IS NULL AND FD.CATEGORY_ID = :B2))) AND
> >   FAD.AUTOMATICALLY_ADDED_FLAG LIKE DECODE(:B1,NULL,'%',:B1)
>
> (snip)
>
> > Misses in library cache during parse: 1
> > Optimizer goal: CHOOSE
> > Parsing user id: 183     (recursive depth: 1)
> > Rows     Row Source Operation
> > -------  ---------------------------------------------------
> >       0  NESTED LOOPS
> >       0   NESTED LOOPS
> >       0    TABLE ACCESS BY INDEX ROWID FND_ATTACHED_DOCUMENTS
> >  332014     INDEX RANGE SCAN FND_CUSTOM (object id 16883602)
> >       0    TABLE ACCESS BY INDEX ROWID FND_DOCUMENTS
> >       0     INDEX UNIQUE SCAN FND_DOCUMENTS_U1 (object id 34673)
> >       0   TABLE ACCESS BY INDEX ROWID FND_DOCUMENTS_TL
> >       0    INDEX UNIQUE SCAN FND_DOCUMENTS_TL_U1 (object id 34687)
>
> From the plan, it looks like the FND_CUSTOM index which is used to
> retrieve the rows from the FND_ATTACHED_DOCUMENTS may not be terribly
> selective.  That index is returning 332,014 rows, all of which are
> eliminated by restrictions on the table.  The plan starts with
> returning the rows referenced by the FND_CUSTOM index.
>
> Questions:
> * What does the execution plan look like in development when the query
> executes in one minute?
> * What wait events appear in the trace file when you start a 10046
> trace at level 8 just before executing the query on the slow instance?
> * What are the initialization parameters for the instance, and are
> they the same in both the fast and slow instances - while there may be
> a lot of memory in the server, Oracle will not use most of the memory
> without appropriate initialization parameters.
> * There were 36,531 blocks read from disk (about 12ms per block read)
> - do those blocks belong to the FND_CUSTOM index or the
> FND_ATTACHED_DOCUMENTS table?
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Charles asked a lot of very good questions. I think the first step is to compare the explain plan for the SQL on both systems and see if there is a difference in the plan.

If the plans are the same then I would be comparing the amount of data on both systems.

But if the plans are different then I would be looking to see why? Maybe the statistics need regeneration or because of the skew of the data the CBO is being mislead.

HTH -- Mark D Powell -- Received on Tue Nov 25 2008 - 12:31:59 CST

Original text of this message