Need help with database tuning

[gandolf989]

I am working on an OLTP application that is used in a call center. The customer is running Oracle 8.1.6 standard edition, on Windows NT, the box has twogb ram + single raid 5 partition into two drives. They have 300 users and the system is slow. Most of their problems comefrom the fact that they have 10 users with 60,000+ cases each, 98% of their cases are closed. Oracle ends up doing full table scans on the 2.5 million records tabls as well as other tables. On the case table there is a field called sysstatus which can be O - open, N - new, C - closed and P - pending.
I imported thier database on my workstation which is enterprise 9i sr2 and created a partition on sysstatus which works well, but they can't use since they have standard. So I am trying to come up with another solution that work as well as partitioning.

I thought about trying materialized views whcih we can give them if we upgrade them to 9i. This is a small database and the view I am thinking about will be less than 20mb. Total size f the case table is 600mb, 2%*600MB=12mb.

What are the implications of using materialized views in an OLTP environment? Can I use bitmapped indexes in the standard edition? If the bitmap index option is not turned on how do I turn it on?

I'm hoping someone has time to make a few suggestions regarding this issue.

Thanks in advance.

[Edited by gandolf989 on 09-10-2002 at 02:48 PM]

[gandolf989]

Does anyone have any ideas?

Thanks for fixing site.