This section describes some general guidelines for tuning a repository database:
Update statistics frequently to monitor what is happening with the repository database.
Defragment indexes and tables more frequently.
Report top queries.
Preallocate the user table to be sure it is large enough.
Estimate how many users you have and make the table big enough to accommodate the data. If you just allow the table to extend itself, loading users can become a very slow process. Periodically the table will stop to double its size, reformat itself, and push all the data back in.
Similarly, preallocate the accounts table to be sure it is large enough.
Estimate how many accounts you have per user and make the table large enough to accommodate the data.
Oracle's Professional Services group and Partners have estimator tools that you can use to estimate user table and accounts table sizes.
Periodically run Waveset's built-in Audit Log Maintenance Task and System Log Maintenance Task to configure log record expirations. Log records can grow without bound, so use these tasks to prevent the repository database from running out of space. For information, see the Oracle Waveset 8.1.1 Business Administrator’s Guide.
Keep in mind that the tasks are volatile. DBAs should consider defragmenting the database more frequently, consider what type of storage to use, and leave plenty of free space in the database.