Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
How to prove that separating schemas increases performance

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
Answers
-
Hi User_H7E57,
Is the problem specifically related to spatial data? If not better move it to another topic..
The question is a little vague. Did you already identify the bottle necks? How else could you compare the performance...
-
Hi _jum,
Specifically, yes it is related to spatial data storage. But it also can happen in non spatial related right ?
Yes, I did identify the bottleneck, The loading of the map is really slow. One suggestion is to have separate the schemas. So, I need to prove this by running some queries (or something else...) that can prove wether the performance increases or not.
I just want to know what tables or parameters to look into, what queries to run to see the performance increase etc...
Thanks for the help.
-
kpatenge-Oracle Senior Principal Product Manager Spatial & Graph Potsdam, GermanyMember Posts: 31 Employee
Hi,
It is probably not necessary to separate data into different schemas. I´d start using different tablespaces to separate data and also spatial indexes. Partitioning your data is also an option to possibly increase performance.
For the latter please have a look at Dan Geringer´s session "Optimize Oracle Spatial Performance". The slide deck is already available via the AnDOUC Slack workspace. Join here: https://bit.ly/Join-ANDOUC-Slack, then navigate to the #spatial channel. Video recordings will be posted in about a week or two on the AnDOUG Youtube channel (https://www.youtube.com/channel/UC_gVAz6TSmWnUifnu-y-wBA/videos).
-
Hi kpatenge,
My goal is to separate system objects and user data. System objects are by defaults created by user called "SDE" where it has default tablespace called "SDE_TBS". All systems obejcts, SDE.*, are stored in SDE_TBS.
So, to separate user tables from system tables, I need to create different user. Hence I have to create a schema right ?
Can user be created without being assigned to a schema and only assigned to a default tablespace ? If yes, in which schema do the objects (tables, triggers, procedures) exist ?
And thanks for inform me about Dan Geringer's session.
thanks
-
Hi, Yockee!
As kpatenge indicated, I don't see how separating schemas by itself will improve performance. However, it will do no harm as far as I can see, so go ahead with that phase of the project.
However, reducing data contention by separating disk spindles, tablespaces and data partitions are a much better solution by improving DML and query scalability.
FYI
Rick