TUNING ORACLE7 FOR LARGE

MN>This runs along very nicely until it gets to the BIG customer (One customer
MN>accounts for approximately 16500 out of 34000 invoices.) At this point, things
MN>slow down to an absolute CRAWL. (Where before I was getting perhaps 20 MN>transactions/second processed, at this point I'm getting more like 2/second.)

MN>What strategies are others using to improve performance in this sort of MN>situation?

This is a case where indexes will hurt more than help! If a query is expected to return mpre than 15% of the records in a file, you are better off with a full table scan.

This doesn't mean you should drop your indexes - then all your other customers would take forever. Since this is v. 7, see if a hint can disable use of an index or, for this customer only, word the select statement so you disable use of indexes on the invoice table.

hth -

jh

---
. MR/2 1.39x NR . OS/2 - the user-friendly Unix!