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?
I think you 2 applications sharing the same database.....yes/no/maybe?
Dont go there.
sorry, typo on my part.
I think hes asking about 2 applications sharing the same database and what are the implications. It would be possible but its hardly worth the stress
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?
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)
8) 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.
why? a single database takes up less resources than 2 databases.
Twi separate schemas in a single database is the cleanest option
I know where your coming from but if they are 2 entirely different applications (with different business owners, business functions and such) I thinks it best to split them. Having them on the same server is bad enough (e.g. if one instance is burning up CPU) but if they are sharing the instance it'll be all the more difficult to isolate any problems.
I just don't think its worth the hassle doing this with two production systems.
Reply With Quote
Originally Posted by mrchrispy
Bookmarks