Thanks for the question, Prast.
Asked: February 17, 2022 - 6:53 am UTC
Last updated: February 18, 2022 - 1:34 pm UTC
Version: 12c
Viewed 10K+ times! This question is 
You Asked
Hi,
I have this schema that contains a lot of tables (some 8000 tables which 7700 of them are generated automatically by the apps inner system).
I want to separate the system tables from the user tables by using different schemas, one schema for system, another one for user (storing just user data).
How do I prove that the action I take really increase performance ? What variables / parameters do I have to check ? I need to serve the before-and-after value on to a report.
Thanks
and Chris said...
Splitting tables into separate schemas can be a good idea.
But it's unlikely to have much impact on performance unless you're also rewriting the queries (beyond updating the schema).
Using different schemas is more about separation of duties and security. By default users have no access to tables in other schemas.
This improves security as you limit the data attackers can access if there are any vulnerabilities in the app.
It can also simplify testing and impact analysis, as you've decreased the number of tables that can be reached.
If you need to go ahead with performance analysis, then you'll want to conduct a load test, measuring things like:
- Mean, median, and max response times
- Mean, median, and max transactions/second
I doubt you'll find this has a major impact on performance though.
Is this answer out of date? If it is, please let us know via a Comment