Simply, database 2 is doing a lot more disk I/O than database 1. Why? The
data is not in cache. Why? Could be any number of reasons. Here're a few.
- Bad init.ora settings.
- Query is run right after start up.
- Previous queries flushed the cache.
- Insufficient memory available.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:3l70du43k35mcs58k480gvkm70schl4heq_at_4ax.com...
> 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 Thu May 02 2002 - 08:48:08 CDT
