How many database users are too many users?
I have a question about number of database users and performance. We have an internally-developed application where 3 schemas own all of the application database objects. We have public synonyms on all of the application database objects. We use database roles to restrict user access to the database objects - we currently have 30 database roles. Here is my question - we currently have 180 database users, and I was just informed that we could have thousands (3,000 - 5,000) of users shortly. Most likely, we will only have about 200 concurrent users on the system, but will all of those database schemas have an affect on the performance of the database - queries, creating records, updating recored, etc?
We have a 10g (10.2.0.3) database on RedHatEL4 (update 4) on a Dell PowerEdge 2950 Server with 2 dual-core Intel processors and 4GB RAM. The database is about 8GB and has about 250 tables - not very big. I am just starting to keep track of the growth of the database, but have no statistics yet. This is just a ballpark guess, I estimate that the database will grow about 1GB every 6 months. Most of the new users will be performing queries and not creating or updating data. Our development tools are Oracle Forms and Reports (10g) as well as Apex. We are planning on using Discoverer and Portal soon. We would like to keep the database authentication as "flexible" as possible. Meaning that we would like to users to use the same login to access Forms, Apex, Discoverer and Portal.
I have worked on projects where user authentication was handled in tables in the application and that worked out fine - much the same way that Oracle Apps does it. The application logs into the database using a generic username (that the user is totally unaware of) and is authenticated through the application tables. I was wondering if this would be a better solution for an application with so many users. I have never worked on an application with this many users.
So, is there any database performance degradation by having 3,000, 5,000, or even 10,000 database schemas in a database?
Thanks in advance-
Dan