We have a current production schema that stores all of our data. We're in the process of trying to integrate a different system to our system. They (vendor from 2nd system) proposed creating a 2nd schema on the same database that hosts our production schema. In that 2nd schema, they are going to migrate the data from 2nd system over and try to transform data so it's similar to our production system. Nothing changes in our production schema.

The plan is to create some reports that will query both the production schema as well as the tables from the 2nd schema (that contains transformed data from the other system). The report is going to be running from our original production system.

Here's one scenario:

  1. Table STUDY is in both production schema (Schema A) and the 2nd schema (schema B). STUDY table in schema A contains live production data from our current system. STUDY table in schema B contains different data that were migrated from the other system. We create a report that should look into both STUDY table in schema A as well as Schema B.
  2. Similar to scenario 1, but this time we're trying to join the STUDY table in both schema A & schema B. Generate a total STUDY report with data from both schemas.

Assuming STUDY table contains thousands of rows and its the header table, and there is multiple child / detail tables that links to STUDY table. Both schemas resides on the same oracle database instance, storage on the same server pretty much share the same infrastructure.

Questions I have: - What is the performance hit / negative impact on our current production system (schema A)? should we expect a big performance hit if we're going about our business like normally (before having the 2nd schema)

  • What is the pros and cons of this approach? (don't touch the current production schema, put data from 2nd system into a 2nd schema)

  • What are some of the other things we have to consider when we plan to go with this approach? do we need to increase the memory used by Oracle, increase share pool, SGA etc?

  • What are the ongoing maintenance issues we could have for the Oracle database (with the addition of the 2nd schema)? We have oracle replication to DR database to sync a dark database every 10 minutes for current production system.

  • Any other downsides you can think of? (as I'm technical but certainly not a DBA or an oracle expert)

| improve this question | | | | |
  • 2
    Ask one question at the time please. – Marco Jan 11 '17 at 14:55
  • Sorry, thought it's easier to have everything in 1 thread of discussion. Thanks for editing. – Jason Huang Jan 11 '17 at 15:07
  • The answer will become way too long. – Marco Jan 11 '17 at 15:22
  • 1
    StackExchange is not a discussion forum. It's a place to ask a single well-defined question, and get a great answer. If you need a discussion, you can join Database Administrators Chat. – Max Vernon Jan 12 '17 at 16:19

Briefly, such an approach appears to me as a valid first step in integration process. As long as the second schema is used for querying only, all its objects can be moved to readonly tablespace thus minimizing impact on backup time.

You may or may not need to tune the instance, it all depends on how much load it adds to the database. For running reports, it should be safe enough to create resource profile (and use it) which limits CPU/Session time. Depends on your license, you can use Database Resource Manager which gives many more options to control resource consumption compared to profile.

| improve this answer | | | | |
  • Thanks. Do you think when the report runs, would it adversely affecting the performance on the 1st system? how do you go about evaluating that? thanks. – Jason Huang Jan 11 '17 at 18:53
  • It might have some negative effect. You can check execution plan prior to running the query to get some idea on how slow the query will be. – a1ex07 Jan 11 '17 at 19:47
  • If you don't have a test environment to try integration and test various scenarios before doing it in the production, then we can only speculate about performance and other issues that may or may not appear. For example, if your server is not heavily loaded and has a lot of free resources(RAM, cpu...), then adding another schema and data may not be a problem( new data is usually added every second/minute ). On the other hand, if your server is fully loaded at the moment you will definitely have a problem with new schema and data. – mutap Jan 11 '17 at 23:44
  • Thanks. We are going to do dev and test shortly. Just want to see what I could find out or did not think of. – Jason Huang Jan 12 '17 at 15:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.