Re: Need help understanding a performance problem

Harry Boswell <hboswel1_at_bellsouth.net> schreef in berichtnieuws 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

You don't tell how you created the test clone. And please specify version and platform in your next posts.

Looks there is an IO problem. Most likely because a "bad" execution plan is choosen.
Take a few of the bad performing statements and compare the execution plans in test and production. Next things (and probably more) can all have influence on the execution plan.

Check the same indexes are created in test as in the production instance. Check important init.ora parameters like db_block_buffers (can be lower on test because there are less users and less memory but not too small), db_file_multiblock_read_count, optimizer_mode. But more likely: are statistics gathered the same way as in production? That is: if they exist in production, do they too in test? And are the statistics renewed lately? Received on Thu Jul 17 2003 - 14:56:53 CDT