Re: General performance tips for Oracle

[Albert Howard <starbukk@concentric.net>]

You really need to put some checkpoints in your program and determine where
the delay points are.  Yes, 1000 employees in an hour is probably
unreasonable, even on a client.

Put some time displays in various checkpoints in your program and see whats
happening.  One of the key points is where you are calling another
paragraph that executes a select for each record, put a time display both
before the begin-select and within the select statement.  This will tell
you how long its taking to build the selection set for that sql for each
employee that you process.  If you see any countable clock time at all
(I'e. one second or greater, you probably have a problem with the WHERE
clause in that select statement.  Remember that one second in that
paragraph translates to 16 minutes for 1000 employees just for that one
paragraph.

If you find a situation like that, try either reworking the WHERE clause so
it better follows the indices (YES IT DOES make a difference sometimes)..or
take a look at joining the errant select statement into the main select.

You can see the results in the SQR.LOG file, or you can run it with the -CB
option so it will actually display on the screen.