OraFAQ Forum: Performance Tuning » Impact of two databases schema in an oracle instance

Home » RDBMS Server » Performance Tuning » Impact of two databases schema in an oracle instance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Impact of two databases schema in an oracle instance [message #145442]

Thu, 03 November 2005 01:56

nirupam
Messages: 3
Registered: November 2005

Junior Member

As of now I have two seperate databases (DB1, which is Oracle 8.1.7.4 & DB2, which is Orcale 8.1.7.2) on a single Solaris box. Lets assume that DB1 has schema S1 and DB2 has schema S2

now we want to move to a new Solaris box which has a single database instacne (9i) and put both S1 & S2 in it (i.e. an oracle database with two schemas, right?)

what will be the impact on performance, capacity? what areas should we consider to avoid any issues?

Regards
Nirupam

[Updated on: Thu, 03 November 2005 07:19]

Report message to a moderator

Re: Impact of two databases sharing an oracle instance [message #145479 is a reply to message #145442]

Thu, 03 November 2005 07:03

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>What am trying to figure out is the impact on performance, capacity when two databases share the same oracle instance.
Depends on how those two 'schemas' are used / nature of application.
In most cases, multiple schemas in the same database will do.

Report message to a moderator

Re: Impact of two databases sharing an oracle instance [message #145480 is a reply to message #145479]

Thu, 03 November 2005 07:08

nirupam
Messages: 3
Registered: November 2005

Junior Member

Sorry I didn't put my question correclty

As of now I have two seperate databases (DB1, which is Oracle 8.1.7.4 & DB2, which is Orcale 8.1.7.2) on a single Solaris box. Lets assume that DB1 has schema S1 and DB2 has schema S2

now we want to move to a new Solaris box which has a single database instacne (9i) and put both S1 & S2 in it (i.e. an oracle database with two schemas, right?)

what will be the impact on performance, capacity? what areas should we consider to avoid any issues?

Report message to a moderator

Re: Impact of two databases sharing an oracle instance [message #145482 is a reply to message #145480]

Thu, 03 November 2005 07:29

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

No. You did put your question in right way. May be , my response was not clear.
It depends on nature your database/application (Last time i said this, it was mis-intrepreted. The client was about to place PROD and TEST schemas in the same database, which would have ended up disastrous).
If there are thousands of users in your database doing thousands of operations/transactions, then having them in a separate servers(rather than separate databases) is better.
If your databases are of low usage, you can have multiple schemas in the same database ( as your proposal). It is all good.
Because you can save a bunch on memory/striping/general maintenance and administration etc.
You may need to have to have two (separate) databases for two different schema/application if the schemas are very differently treated or application is a third party application, which may need specific instance wide settings.
>>what will be the impact on performance, capacity? what areas should we consider to avoid any issues?
We cannot generalize ( without understanding your system usage /hardware /without testing etc).

Report message to a moderator

Re: Impact of two databases sharing an oracle instance [message #145484 is a reply to message #145482]

Thu, 03 November 2005 07:41

nirupam
Messages: 3
Registered: November 2005

Junior Member

Thanks,

Following are the areas I can think of, please let me know your views

1) they will share the SGA, which means
A) The database buffer cache holds copies of data blocks read from data files of both the schemas.
B) redo entries of both the schemas will share the redo log buffer
C) Common shared pool for both the schemas
D) common Large Pool which is an optional memory area to provide large memory allocations for sessions, backup and restore operations etc

2) Program Global Area (PGA) will not be shared? Am I right?

3) The background processes will be shared? Like DBWR (Data Base Writer), LGWR (Log Writer),SMON (System Monitor),PMON (Process Monitor), ARCH (Archiver), RECO (Recovery),USER, CKPT (Check Point), Lock Manager Server (LMS), Queue Monitor (QMN), Dispatcher, Server. Do you know if they are the single thread processes or can handle multiple threads? It looks like only DBWR (Data Base Writer, ARCH (Archiver) and Queue Monitor (QMN) can spawn multiple threads as DBWR0, DBWR1 etc
Do you see any impact due to this sharing?

4) ARCH process will be shared by both schemas so the archive/backup should be schedule properly to avoid overlapping

5) To avoid disk I/O issues, the data and index tablespaces (for each schema) will be created on separate disk blocks. I guess, the DB data files will be on SAN

6) Rollback segment will be shared. it will make restoration complicated

7) SGA would need more physical memory (RAM) to avoid paging (swapping the pages from disk)

Cool

Maintenance in one database might have impact on the availability of the second schema

9) shared temporary table space which is used for sorting might have impact if sqls on both schemas need sorting at same time

10) any impact if both the schemas uses DBLink to other databases? One in a single instance both will share the Net*8 right?

Overall, my interest is to figure out the affected areas if we move two related schemas (which are in separate database instances as of now) to a single database instance.

Report message to a moderator

Re: Impact of two databases sharing an oracle instance [message #145495 is a reply to message #145484]

Thu, 03 November 2005 08:54

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Overall, Background Processes are shared.
I see nothing 'bad' enough.
PGA is just a memory area specific to a process.
Upgrade to 10g (at least 9i). Look into Automatic PGA management.
>> 4) ARCH process will be shared by both schemas so the archive/backup should be schedule properly to avoid overlapping
What overlap?
Archiving is done by the database level. NOT BY schema level.

>>5) To avoid disk I/O issues, the data and index tablespaces (for each schema) will be created on separate disk blocks. I guess, the DB data files will be on SAN
Agree with this to some extent.
Fact is, data and index access is always done in serial. Not in Parallel.
So even you have data and index tablespace in different disks, in a real multi-user system, there might a contention.
You cannot completely avoid it. ( Multiple users querying the same data and index entry?).
So this may occur even when you have two different databases.
>>6) Rollback segment will be shared. it will make restoration complicated
Why would you say so?
'Undo' during recovery is logical. NOT Physical.
And You can always do a point-in-time restoration of a single tablespace.
So have both schemas in different different tablespaces.

>>7) SGA would need more physical memory (RAM) to avoid paging (swapping the pages from disk)
can you avoid it?
liberally speaking,atleast by having one SGA you can avoid paging / swapping that could be caused by two SGA's.

>>8) Maintenance in one database might have impact on the availability of the second schema

What kind of maintenance are you talking about here?

>>9) shared temporary table space which is used for sorting might have impact if sqls on both schemas need sorting at same time.
Make them bigger. Write better code. Have an optimal sort_area_size ( in 9i pga_aggregate_target).
You may be running out temp space even if you have two databases. Can you avoid it?
>>10) any impact if both the schemas uses DBLink to other databases? One in a single instance both will share the Net*8 right?
Will be very minimal.

Report message to a moderator

Re: Impact of two databases schema in an oracle instance [message #145635 is a reply to message #145442]

Fri, 04 November 2005 05:38

bmahireddy
Messages: 14
Registered: March 2005

Junior Member

You might have to check for same Public Synonyms from both schemas.

Report message to a moderator

Previous Topic:	locking in oracle
Next Topic:	modify columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 17:17:12 CST 2024