Impact of two databases sharing an oracle instance
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Impact of two databases sharing an oracle instance

  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Impact of two schemas sharing an oracle instance

    Sorry guys 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?

    Regards
    Nirupam
    Last edited by nirupam; 11-03-2005 at 08:23 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    two databases cannot share the same instance, it is impossible

    You need to work out what you really have got there

  3. #3
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    I think you 2 applications sharing the same database.....yes/no/maybe?

    Dont go there.

  4. #4
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    Quote Originally Posted by mrchrispy
    I think you 2 applications sharing the same database.....yes/no/maybe?

    Dont go there.

    English please !!!
    Able was I ere I saw Elba

  5. #5
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Quote Originally Posted by mrchrispy
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why? a single database takes up less resources than 2 databases.

    Twi separate schemas in a single database is the cleanest option

  7. #7
    Join Date
    Nov 2005
    Posts
    4
    Sorry guys 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?

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    right, ok two schemas in the same database. You just have to watch the one application isnt so crap that it hogs all the resources on the database.

    The normal tuning principles apply, just configure your memory correct, spread out your i/o across all available devices and things should be ok

  9. #9
    Join Date
    Nov 2005
    Posts
    4
    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)

    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.

  10. #10
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Quote Originally Posted by davey23uk
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width