Re: Need help understanding a performance problem

Harry Boswell <hboswel1_at_bellsouth.net> wrote in message news:<ltedhvsdrbgsn7ilselminfqp28egba3ln_at_4ax.com>...
> I have a small instance which is a clone of the production database, except
> for one table which contains blobs. The cloned instance is being used for
> testing a new release of the application (which is written in Powerbuilder).
> The cloned instance also sits on a smaller server (Sun E250 vs. E4500 for
> the prod db).
>
> The testing users are complaining about very long delays during certain
> operations. In monitoring the instance, it has some wait event stats that
> are very different from the production. The biggest difference is with
> db_file_sequential_read. Here's some numbers:
>
> TEST INSTANCE PRODUCTION INSTANCE
> (8-10 users) (50-60 users)
> ------------- -------------------
>
> buffer busy waits 36 2
> control file parallel write 12493 10151
> control file sequential read 116 145
> db file parallel read 13 0
> db file parallel write 390 255
> db file scattered read 23669 36038
> db file sequential read 753289 16729
>
>
> Something obviously is very "off" in the test instance, but I'm not sure
> where to start looking. A web search for 'db file sequential read' didn't
> turn up much.
>
> Would a dropped index cause this?
>
> Thanks,
> Harry Boswell

Harry,

just a stab, but:
- are the values for v$parameter where name like '%area_size' set the same?
- are the values for v$parameter where name like 'optimizer%' set the same?
- are the values for v$parameter where name like '%io%count%' set the same?
- are statistics correct? (dbms_stats)

The execution path may have been using a hash_join on the other server (no index used, less sequential reads) where now it is using indexes (index range scan, index fast full scan) instead.

hth,

Paul Received on Thu Jul 17 2003 - 17:26:26 CDT