I have a database which has 150 tables -- 50 tables holds 10 million rows -- currently all tables belong to a single Oracle user. Do you think if I transfer these 50 tables in 10 users (each user will hold 5 tables) and create public/private synonyms to make this available to a single common user, the performance would improve?
Also, if I transfer these tables to a different database and use DBLINK, will DBLINK hog the performance?
No, separating tables among multiple users will not improve performance. In fact, the use of synonyms will degrade performance slightly. The use of private synonyms can cause multiple versions of the same SQL text to be maintained in the library cache, thus reducing the sharing of SQL; the use of public synonyms requires extra work during parsing, to ensure that the parsing user does not have an object with the same name as the public synonym.
You may wish to consider partitioning of your largest tables. Partitioning can be useful in improving performance, particularly if I/O is a bottleneck, because partitioning allows you to place parts of a table on different disk devices, potentially increasing I/O concurrency and thus throughput. Make sure, however, that you test carefully before deciding whether or not to implement in production.