Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mixing OLTP with DSS

Re: Mixing OLTP with DSS

From: Tom Pall <>
Date: Tue, 29 Oct 2002 16:43:37 -0800
Message-ID: <>

Oracle 8i uses a different aging algorithm than before. It waits for blocks to be accessed several times before considering keeping them around. That way the full scan of a big table won't push other tables out. If aging becomes a problem, remember you can now size a keep and recycle buffer pool (The names only suggest the buffers' intended purposes. Each follows the same aging algorithm.) and can alter table/index/partition to assign it a new buffer cache online.

If the data OLTP and DSS don't have to be accessed in the same query, it makes sense to create a new database to contain the OLTP data. Not only will the caches be separate, their rollback, redo and process will be separate as well. Plus, you'll be able to backup or take one down without bothering the other one. If the data has to be accessed together, you'll have to weigh the impact using database links in with the other pros and cons of creating a separate database.

  Am I looking for trouble by mixing an OLTP type database with a DSS type database?

  I currently have a relatively small database that is used for quick lookup type actions. The company wants to add a much bigger and constantly growing application for historical reporting on the same machine. Should I create another instance or keep it in the same instance?

  Keeping it in the same instance would seem to defeat having anything cached seeing that the large reports will flush out the cache each and every time.

  I am running Oracle 8.1.7, on Solaris 2.8.

  Any suggestions would be helpful.


  Rick Stephenson


Please see the official ORACLE-L FAQ:

Author: Tom Pall

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Oct 29 2002 - 18:43:37 CST

Original text of this message