Re: Lousy performace

From: Paul <pnutts_at_writeme.com>
Forms take a long time to open and the wait for information during the order entry process is a show-stopper. I realize that this is not very specific information (it is about all I know without asking someone), but our old AS400 at 9600 BAUD (character based) runs rings around our new system. <<

The odds are your performance problems are SQL related, but what does the database look like, and what does the server have available?

How much memory and how many processors does the server have? How many disks? Are the redo logs on separate disks from the data tablespace data files? Are the application indexes on different disks than the tables that the index references? How many and how large are the rollback segments, and where are they placed in relation to the rest of the database?

You should check the stats in v$waitstat for system wide problem indications. Checking all this should only take an hour or two, then I would start looking at the sql. Are you using the cost based optimizer? When and how were your statistics last updated. Explain plan all heavily used sql and check the plan for reasonableness. I have seen a couple of analyzes and a one or two hints make a world of difference to an application.

Also check for lock conflicts, v$lock, between multiple users of the application. Sometimes developers build bottlenecks (like control records for the order number) right into the application and substitution of a sequence generator can make you look like genuis.

Good hunting.
Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Wed Apr 30 1997 - 00:00:00 CDT