Re: Need help understanding a performance problem

Harry Boswell wrote:

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

Hi Harry,
db file sequential read event is (contradictionary to the name of the event) is blocks gotten by index rowid, blocks gotten by index lookup. Scattered read is full table scans, in case of dropped or missing index.

Seems You are having a disk i/o problem or a hot spot - the same blocks are read over and over again from disk.
Check by selecting repeat from v$session_wait with the sid of a slow session (or all sessions). The dominating (non idle) event is Your target to fix.
It could be file read, p1 is file number, p2 is block number, p3 is number of blocks to read. Check the meaning of values in P1-3 in the v$event_name view.

If it is disk i/o, use OS tool to monitor like wmstat, iostat or......? v$filestat is not to be relied upon on all versions. You didn't mention your version and OS by any chance, did you?

Good hunting.

/Svend Jensen Received on Thu Jul 17 2003 - 13:27:49 CDT