Forum Stats

  • 3,852,847 Users
  • 2,264,142 Discussions
  • 7,905,157 Comments

Discussions

Tagged

How to prove that separating schemas increases performance

Yockee
Yockee Member Posts: 24 Green Ribbon
in Spatial Discussions

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

Tagged:
· Share on TwitterShare on Facebook

Answers

  • _jum
    _jum Member Posts: 543 Bronze Trophy

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

    · Share on TwitterShare on Facebook
  • Yockee
    Yockee Member Posts: 24 Green Ribbon

    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.

    · Share on TwitterShare on Facebook
  • kpatenge-Oracle
    kpatenge-Oracle Senior Principal Product Manager Spatial & Graph Berlin/DEMember Posts: 29 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).

    · Share on TwitterShare on Facebook
  • Yockee
    Yockee Member Posts: 24 Green Ribbon

    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

    · Share on TwitterShare on Facebook
  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 157 Employee

    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

    · Share on TwitterShare on Facebook