Blocks are not getting cache

From: <lyxx09_at_gmail.com>
Date: Mon, 24 Nov 2008 19:20:50 -0800 (PST)
Message-ID: <ee2218ae-c35d-4435-af10-30e046902877@j11g2000yqg.googlegroups.com>

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)

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.01       0.00          0          0
0           0
Execute      7      0.02       0.00          0          0
0           0
Fetch        7      5.30     441.73      36531      82509
0           0

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

---

total       15      5.33     441.74      36531      82509
0           0

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)

Received on Mon Nov 24 2008 - 21:20:50 CST