How to prove that separating schemas increases performance

Yockee

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

_jum

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...

Yockee

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

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).

Yockee

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

Rick Anderson-Oracle

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