Re: Blocks are not getting cache

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. Received on Tue Nov 25 2008 - 07:22:54 CST