I'm a DBA at an Oracle software development company. I'm hoping you can help us figure out a solution to our dilemma.
We have multiple Oracle 9i instances (9i is still used to ensure software backward-compatibility for our customers) running on Sun equipment. One for development, one for quality assurance, one for final production and another for internal software (help desk, time and attendance, etc.).
Inside each instance is typically one schema for each supported base version of our product in two different languages as well as one schema for each of our clients' custom development.
As of this writing we have 81 schemas in our development instance! While the schemas are usually relatively small data-wise, this arrangement makes for a SYSTEM tablespace approaching 20 GB! Of course, this is extremely inefficient for the data dictionary to be so huge. Performance issues do indeed exist.
We use scripts to perform many functions that rely on all development schemas being in the development instance, QA in the QA instance, and so on.
We're soon to be contemplating the replacement of our main server which handles all of these instances. I've put forth the idea of going to a RAC system to help alleviate the bottlenecks and downtime concerns, but the reality of a 20GB data dictionary is still daunting.
What best practices exist for such an environment? Recommendations, please?! Thanks!
If a 20GB SYSTEM tablespace is causing performance problems, then it is unlikely that RAC will help solve these problems. The first place I would look is to ensure that optimizer statistics have not been collected on any SYS objects. To be sure, run the following in SQL*Plus:
In many Oracle 9i environments, optimizer stats on SYS-owned objects actually causes performance problems.
The next thing I would consider is to put your production databases on a production server. Leave the development databases on this older server. It is optimal to separate development activities from production so that long running queries do not hog resources needed by the production databases.
If you are still having problems, then use Oracle's wait events to see where your bottlenecks lie. Just because your Data Dictionary is 20GB in size does not always mean this is the cause of your problems. The performance issues could be due to something like I/O contention. If you are sure this is caused by a large SYSTEM tablespace, now that you have moved your production databases to a new server you can create other dev databases and trim down the number of schemas in those databases to something more manageable.