SQLTrace Question

From: mtwalla <mtwalla_at_bluefishgroup.com>
Date: Wed, 1 May 2002 09:44:53 -0600
Message-ID: <R1Uz8.35$AS3.38704@news.uswest.net>

The following SQLTRACE output is generated running a particular query.

On database 1 (Query performs as expected, performance wise. To reinforce point, this DB actually contains many more records) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 2 0
Fetch 2613 0.00 0.00 301 1145160 168 39178
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2616 0.00 0.00 301 1145160 170 39178

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

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

 161112     NESTED LOOPS
 161112      TABLE ACCESS FULL WEB_CONTENT_R
 322222      TABLE ACCESS BY INDEX ROWID DM_SYSOBJECT_R
 322222       INDEX UNIQUE SCAN (object id 61115)
 250003     TABLE ACCESS BY INDEX ROWID DM_SYSOBJECT_S
 322222      INDEX UNIQUE SCAN (object id 61114)
  88055    TABLE ACCESS BY INDEX ROWID WEB_CONTENT_S
 177784     INDEX UNIQUE SCAN (object id 61450)

On Database 2:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.06 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 14.07 248.17 6773 19799 312 100
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 14.13 248.25 6773 19799 312 100

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

Can someone explain/detail what might be causing so many more disk accesses on databse 2 as compared to database 1? Or, is there anything else that jumps out about the two SQLTrace results?

Some other info, database 2 is running on a fairly powerful Solaris machine, database 1 is running on NT box.

Mike W. Received on Wed May 01 2002 - 10:44:53 CDT