Re: Blocks are not getting cache
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