This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,506 Users
  • 2,269,776 Discussions


How to prove that separating schemas increases performance

Yockee Member Posts: 24 Green Ribbon


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.



  • _jum
    _jum Member Posts: 554 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...

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

  • kpatenge-Oracle
    kpatenge-Oracle Senior Principal Product Manager Spatial & Graph Potsdam, GermanyMember Posts: 31 Employee


    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:, then navigate to the #spatial channel. Video recordings will be posted in about a week or two on the AnDOUG Youtube channel (

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


  • Rick Anderson-Oracle
    Rick Anderson-Oracle Member Posts: 162 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.