ORACLE: performance of query running across two schemas 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:


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.
Similar to scenario 1, but this time we're trying to join the STUDY table in both schema A &amp; 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)
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.