Re: SQLTrace Question

On Wed, 1 May 2002 09:44:53 -0600, "mtwalla" <mtwalla_at_bluefishgroup.com> wrote:

>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.
>

Is this a riddle or what? Can we earn something?

Two quotes
>On database 1 (Query performs as expected, performance wise. To reinforce
>point, this DB actually contains many more records)

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

My crystal ball tells me without further info (init.ora settings etc) the only correct answer would be that you force the group to compare apples and pears, as you are providing almost no info at all, to make a useful comparison.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed May 01 2002 - 12:00:41 CDT