RE: Large in memory DW table

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: <Rob.Dempsey@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 16 Nov 2007 08:28:00 -0600


Yes it will improve over all performance, maybe just some maybe a lot,
depends on what is happening with the data. When a tablespace is READ
ONLY this in effect takes it off the list from the list of blocks that
DBWR will write back to disk.  However the SCN and such is still checked
each time the block is accessed.  This can be a problem if old ITL slots
have not be cleaned out prior to make the table space READ ONLY.  Make
sure all ITLs have been cleared before switching a tablespace to READ
ONLY.  To do this just make sure a full object scan is done on each
object in the table space prior to making it read only.


ITLs are not used for a SELECT, however they are checked by a SELECT (or
any other statement that touches a block) to see if there are still
valid transactions in the block. The disadvantage to forcing only one
ITL slot is that there can never be more then one transaction ever doing
DML in the block.  I think setting it to at least 2 might be a better
idea, just gives you some options for the future. 



Ric Van Dyke

Hotsos Enterprises




From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rob Dempsey
Sent: Friday, November 16, 2007 8:04 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Large in memory DW table






- Large read only table (100 million rows) in a DW that is required to
be based mainly in memory for performance reason. 

- Version


I have some queries that I can not find information on, wonder if anyone


- Setting the tablespace to READ ONLY.  Would this improve performance?
Would oracle know that the block is read-only hence not have to worry
about checking whether SCN is current, mess around with undo information
(I guess I am asking whether a different code path would be taken)

- Do select statements need ITL slots?  If not I am thinking of setting
INITTRANS and MAXTRANS to 1 so that I can squeeze as much data into a
block as possible? Disadvantages? (It is a table for only reading).
Only one slot would be needed to load the data via a single process




GIF image

Other related posts: