Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Data Warehouse Read-only Tablespaces

Oracle Data Warehouse Tips by Burleson Consulting

Read-only tablespaces in the real world

For a production Oracle data warehouse it is very important to develop a method for determining when the data becomes permanent.  For example, a large data warehouse may store historical data for the past decade, but the last 18 month?s worth of data are subject to updates as the data status changes within the OLTP database. In Figure 8.14 we assume that our fact tables have been horizontally partitioned, that is, a very long table with rows going back ten years in time will be separated into tables that govern a smaller period of time.

Figure 8.14 - Horizontal partitioning of an Oracle warehouse

Here we see that the main fact table has been logically partitioned into ten horizontal partitions, and only the most recent section of the table (fact_97) is subject to update.  The other partitions for the fact table are static and will never change.  For details of using horizontal table partitioning with Oracle tables, see Chapter 4, Oracle Data Warehouse Design. For details on automatic table and index partitioning with Oracle8, see Chapter 14, Oracle8 for the Warehouse.

We should also note that each chunk of the fact table has been segregated into its own tablespace, such that there are ten independent tablespaces for each table.  We should also note that this is a rolling strategy.  As fact_96 ages into the static category, the Oracle DBA will reorganize this table chunk to tightly pack it into a static tablespace.  This is achieved by calculating the precise amount of storage for the table, and using Oracle export-import utility to offload the data and pack it into the new chunk .  The index for each chunk also reside in its own tablespace, and this is also packed into its new, static tablespace.

But how do we ?pack? Oracle data for use as a read-only tablespace?  Essentially, there are two areas where space is wasted within an Oracle table.  On each data block, Oracle will reserve an amount for row expansion and this value is controlled by the PCTFREE table parameter.  The PCTFREE parameter is useful when we are loading incomplete rows that have columns that are defined with VARCHAR data definitions.  After initial loading, Oracle reserves space at the end of each data block for row expansion.  For example, if we have defined the fact_96 table with 8K blocks and PCTFREE = 25, we have reserved 2000 bytes at the end of each and every data block for row expansion.  Later when is issue an SQL UPDATE command to add column values for a null value, the row size will expand into this free space.  The second area of free space in a tablespace is the space left over that the end of the table. (figure 8.15)  Here we might see that the fact_96 table consumes only 75% of its tablespace, allowing for the table to extend as new rows are added.  In short, table grow wider as column values are added (in each data block), and table grow longer as rows are added (at the end of the tablespace).

Figure 8.15   Free space distribution within a tablespace

If we know that the table chunk is now static, we can export the table into a flat file, and re-define the table and tablespace characteristics to maximize the amount of occupied space.  At the table level, we set PCTFREE equal to 0, not reserving any space within the data blocks for growth.  At the tablespace level, we redefine the tablespace to consume only as much space as the table requires. (The total amount of space for a table can be estimated fairly accurately by estimating the average row length, and multiplying by the number of rows)

After we have packed the row data and the indexes within their respective tablespace, we can now alter the tablespaces to make them read-only.  Once the tablespace has been altered to become read-only, a single backup can be used for all future recovery.  Unlike ordinary tablespaces, online backups never need to issue the ALTER TABLESPACE FACT_96 BEGIN BACKUP commands.  During startup of an Oracle instance, Oracle verifies that a tablespace is in read-only mode and recognizes that it does not need any media recovery.  In short, a recovery of an Oracle data warehouse will only consist of applying the redo logs against those tablespaces that were in update mode. This greatly simplifies database recovery for a very large Oracle warehouse. This technique also allows a tablespace to avoid the time consuming overhead of writing to the online redo logs since roll-forward operations are performed by re-running the nightly update routines.  In addition read-only tablespaces will never write to the database file headers, saving system I/O.

With Oracle version 7.1 and above, any Oracle tablespace may be made read-only by issuing the alter tablespace command. 

SVRMGRL > shutdown;

SVRMGRL > startup restrict;

SVRMGRL > alter tablespace warehouse_one read only;

SVRMGRL > alter database open;

Note: In order to change the status of a tablespace, the Oracle database must be quiesced.  That is, there must be no active transactions running against the tablespace at the time that the alter tablespace command is issued.  In the above example, we have shutdown Oracle and started in database in restricted mode.

Oracle and CD-ROM media

It is not uncommon in an Oracle data warehouse for a tablespace to be transferred onto a CD-ROM when a tablespace will no longer be updated.  Read-only media such as a CD-ROM disk is far cheaper and more compact than disk storage, and it is not substantially slower.  While every data warehouse uses a different technique, the following shell script demonstrates how a read-write tablespace may be archived onto CD-ROM and made into a read-only tablespace.


export PATH

svrmgrl << ALL_DONE
shutdown immediate;

cp read_write_tablespace_file CD_ROM_tablespace_file;
svrmgrl << ALL_DONE
startup mount;

alter tablespace fact_97 rename datafile
        read_write_tablespace_file to CD_ROM_tablespace_file;

startup restrict;

alter tablespace warehouse_one read only;

alter database open;

rm -f read_write_tablespace_file

Now that the tablespace has been packed and copied to cheaper media, we can perform some system tuning tricks to boost performance.  Oracle recommends that the data warehouse DBA use the table CACHE option and issue a full-table scan against all small, frequently accessed tables in the read-only tablespace to insure that they have been loaded into the Oracle buffer pool.  For small tables defined with the CACHE option, issuing the select count(*) against the table at database startup time will cache the table in the most frequently used portion of Oracle?s data buffer.

SVRMGRL>  select count(*) from sales_fact_table;

Now that we understand how read-only tablespaces are used, let?s invalidate this entire discussion by noting that in a few years it will not be necessary to use these techniques since system performance will be so fast that this type if tuning will not significantly influence performance.   To illustrate, let?s take a look at how unbelievable speed is being achieved using Oracle data warehouses on 64 bit processors.

Oracle?s 64-bit option - A look into the future

For very large data warehouses where sub-second response time is critical, few database engines can beat the performance of Oracle with the 64-bit option.  Designed for use with Digital Equipment Corporation Alpha series (DEC-Alpha), Oracle?s 64-bit architecture performs more than 1000 times faster than standard Oracle 7 software.  It is interesting to note that the DEC-alpha family of 64-bit processors has been around since 1991, and the database vendors are only now beginning to realize their potential for data warehousing. Since all of the major hardware vendors are creating 64 bit systems, let's t6ake a look into the future by examining the existing state of Oracle?s 64-bit option for data warehousing.

SQL complexity and response time

As we are aware, when Oracle performs n-way joins of a very large fact table against dimension tables, Oracle will denigrate in performance as the number of joined tables increases.  (Figure 8.16) However, using Oracle' star query hints with bit databases, Oracle claims that queries will actually improve their response time as the complexity of a SQL query increases.  In an Oracle study a DEC-Alpha with Oracle's 64-bit option showed response time improving as the number of joined tables increased.

Figure 8.16  An Oracle benchmark

As we may know, Oracle star queries maps the selection criteria conditions from the query and creates a cross-product of the dimension tables.  This cross-product is then compared to the fact table by using a multi-key index into the fact table.  For more information on star query hints, see the indexing section in this chapter.

Using a large SGA configuration with Oracle 64 on a DEC-Alpha, Oracle corporation demonstrated that a query that took more than one hour on a UNIX Oracle database ran in less than three seconds on a 64-bit processor with a STAR schema and six gigabytes of data buffer.

Figure 8.17  Query speed comparison, 32 bit versus 64-bit processors

What about BOB?

The Oracle 64 bit option has two enhanced features, big Oracle blocks (BOB), and Large SGA (LSGA).  BOB is probably one of the most important features of Oracle on 64-bit machines.  Because block sizes can reach 32k an entire track of data can be read with a single I/O, and system performance improves dramatically.  This is especially important in data warehouses that scan large ranges of tables.  When used with the init.ora parameter db_multiblock_read_count, physical I/O for table scan operations will be greatly reduced.  BOB is also very useful if you are storing non-tabular data in your Oracle database, as is the case with Oracle's spatial data option.  In these cases, binary large objects (BLOBs) such as images can be accessed with a single physical disk I/O.  

LSGA is something that is even more important to the future of Oracle data warehouse systems.  As the cost of RAM memory falls we will be able to see huge Oracle SGAs, some of which will be able to cache an entire database.  In some 64-bit Oracle warehouses, large tables are read into Oracle data buffer using the table CACHE option.  For example, assume that we have a 10 gigabyte data warehouse with 32K blocksizes and db_block_buffers parameter set to 10,000, for a total buffer size of 320 megabytes.  Here, we have an SGA large enough to hold the entire database, which can be read at startup where it will remain for the entire processing day without any disk I/O (assuming, of course, that we are in query mode).  Since accessing RAM takes 50 nanoseconds when compared to a disk I/O that takes 50 milliseconds, we see that I/O will proceed one million times faster in a system that does not perform disk I/O.

Sixty-four bit architectures also remove many of the traditional barriers of UNIX systems.  File sizes can now exceed 2 gigabytes, SGA regions can reach up to 14 gigabytes, and database block sizes can now be made 32K, the same as Oracle's mainframe cousins. 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational