RE: Large in memory DW table

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Fri, 16 Nov 2007 08:28:00 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CFDA2BA3@WIN02.hotsos.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rob Dempsey Sent: Friday, November 16, 2007 8:04 AM
To: oracle-l_at_freelists.org
Subject: Large in memory DW table

Setup

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

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

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

Rob

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 16 2007 - 08:28:00 CST