Help On Performance Issue

From: mtwalla <mtwalla_at_bluefishgroup.com>
Date: Fri, 10 May 2002 12:45:43 -0600
Message-ID: <axUC8.75$_D5.42516@news.uswest.net>

I have a query running against two database. Database 1 has more than data than database 2, but I contend database 1 has a configuration problem and its not the size of the data resulting in the perofrmance issues of database 1.

Here is a tkprof from database 1 & 2. Would anyone like to take some stabs at what causing the differnces in performance?

Database 1:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.03 0.02 0 0 0 0
Execute 2 0.04 0.05 0 0 6 0
Fetch 276 28.26 142.66 29522 21028 1739 4115
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 279 28.33 142.73 29522 21028 1745 4115

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19

Rows Row Source Operation
------- ---------------------------------------------------

   4115 SORT UNIQUE
 124947 MERGE JOIN
 134349 MERGE JOIN

 394053     MERGE JOIN
 394053      INDEX FULL SCAN (object id 3977)
 788104      SORT JOIN
 450026       TABLE ACCESS FULL DM_SYSOBJECT_R
 528400     SORT JOIN
  54273      TABLE ACCESS FULL DM_SYSOBJECT_S
 124947    SORT JOIN
 131586     TABLE ACCESS FULL WEB_CONTENT_S

Database 2:
call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.08 0.17 0 63 0 0
Execute 2 0.06 0.05 0 0 2 0
Fetch 2613 15.16 17.19 302 1148627 158 39178
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2616 15.30 17.41 302 1148690 160 39178

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
  39178 SORT UNIQUE
  87755 NESTED LOOPS
  88893 NESTED LOOPS

 161689     NESTED LOOPS
 161689      TABLE ACCESS FULL WEB_CONTENT_R
 323376      TABLE ACCESS BY INDEX ROWID DM_SYSOBJECT_R
 323376       INDEX UNIQUE SCAN (object id 61115)
 250580     TABLE ACCESS BY INDEX ROWID DM_SYSOBJECT_S
 323376      INDEX UNIQUE SCAN (object id 61114)
  87755    TABLE ACCESS BY INDEX ROWID WEB_CONTENT_S
 177784     INDEX UNIQUE SCAN (object id 61450)

Why would the EXPLAIN PLAN differ, the same query is being run against both Databses? Also, might the # of
phyical disk reads be so much higher against database 1? Being a novice DBA, my thought is there might be an SGA
contention or sizing issue on database 1?

Mike Received on Fri May 10 2002 - 13:45:43 CDT