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 11g Data Warehouse tips

Oracle Tips by Burleson Consulting

Oracle 11g for the Data Warehouse

The large data buffer caches in most OLTP Oracle systems make them CPU-bound, but Oracle data warehouses are another story.  With terabytes of information to aggregate and summarize, most Oracle data warehouses are I/O-bound and the DBA must choose a server that optimizes disk I/O throughput. 


Oracle has always made very large database (VLDB) technology a priority as evidenced by their introduction of partitioned structures, advanced bitmap indexing, and materialized views.  Oracle11g provides some features that are ideal for the data warehouse application:

§         Read-only Tablespaces: If the DBA has a time-series warehouse where information eventually becomes static, using tablespace partitions and marking the older tablespaces as read only can greatly improve performance.  When a tablespace is marked as read only, Oracle can bypass this read consistency mechanism, reducing overhead and resulting in faster throughput.

§         Automatic Storage Management (ASM): The revolutionary new method for managing the disk I/O subsystem removes the tedious and time consuming chore of I/O load balancing and disk management.  Oracle11g ASM allows all disks to be logically clustered together into disk groups and data files spread across all devices using the Oracle11g SAME (Stripe and Mirror Everywhere) standard. By making the disk backend a JBOD (Just a Bunch of Disks), Oracle11g manages this critical aspect of the data warehouse.

§         Multi-level partitioning of tables and indexes: Oracle now has multi-level intelligent partitioning methods that allow Oracle to store data in a precise scheme.  By controlling where data is stored on disk, Oracle11g SQL can reduce the disk I/O required to service any query.

§         Advanced Data Buffer Management: Using the Oracle11g multiple block sizes and KEEP pool, the DBA can pre-assign warehouse objects to separate data buffers and ensure that the working set of frequently referenced data is always cached.  Oracle11g also offers Automatic Memory Management (AMM) whereby Oracle11g will automatically re-assign RAM frames between the db_cache_size and the pga_aggregate_target region to maximize throughput of the data warehouse.

§         Materialized Views: Oracle’s materialized views (MV) use Oracle replication to allow the DBA to pre-summarize and pre-join tables.  Best of all, Oracle MV’s are integrated with the Oracle 11g query rewrite facility, so that any queries that might benefit from the pre-summarization will be automatically rewritten to reference the aggregate view.  This will avoid a very expensive and unnecessary large-table full-table scan.

§         Automated Workload Repository (AWR) analysis: The AWR provides a time-series component to warehouse tuning that is critical for the identification of materialized views and holistic warehouse tuning.  The most important data warehouse tracking with AWR includes tracking large-table-full-table scans, hash joins which might be replaced with STAR joins, and tracking of RAM usage within the pga_aggregate_target region.

It is easy to identify when an Oracle warehouse is disk I/O bound. In the AWR report below, comparable to a STATSPACK report for Oracle9i and earlier, it is clear that this typical data warehouse system is clearly constrained by disk I/O, resulting from the high percentage of full-table and full-index scans.


Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                     % Total

Event                            Waits    Time (s)    Ela Time

------------------------- ------------ -----------    --------

db file scattered read           2,598       7,146       58.54

db file sequential read         25,519       3,246       12.04

library cache load lock            673       1,363        9.26

CPU time                                     1,154        7.83

log file parallel write         19,157         837        5.68


This listing shows that scattered reads, as full-table scans, constitute the majority of the total database time. This is very typical of a data warehouse that performs aggregations via SQL.  It is also common during the refresh period for Oracle materialized views.  The problem is the I/O bottleneck that is introduced during these periods. 


Due to the fact that the typical data warehouse is so data intensive, there is always a problem fully utilizing the CPU power.  UNISYS has addressed this issue by leveraging on Non-Uniform Memory Access (NUMA), whereby Windows and Oracle11g are automatically configured to exploit NUMA to keep the CPUs busy.  The data buffer hit ratio is not relevant for data warehouses, systems that commonly perform full-table scans, or those that use all_rows SQL optimization.


While a 30 GB db_cache_size might be appropriate for an OLTP shop or a shop that uses a large working set, a large SGA does not benefit data warehouse and decision support systems (DSS) where most data access is performed by a parallelized full-table scan. When Oracle performs a parallel full-table scan, the database blocks are read directly into the program global area (PGA), bypassing the data buffer RAM as illustrated in Figure 18.4.


Figure 18.4: Parallel Full Scans Bypass SGA data buffers


The figure above shows that having a large db_cache_size does not benefit parallel large-table full-table scans, as this requires memory in the pga_aggregate_target region instead.  With Oracle 11g, the multiple data buffer features can be used to segregate and cache dimension tables and indexes, all while providing sufficient RAM for the full scans.  When the processing mode changes during evening Extract, Transform, and Load (ETL) and rollups, Oracle11g AMM will automatically detect the change in data access and re-allocate the RAM regions to accommodate the current processing.


All 64-bit servers have a larger word size (2 to the 64th power) that allows for up to 18 billion GB of addressable RAM. DBAs may be tempted to create a super large RAM data buffer.  Data warehouse systems tend to bypass the data buffers because of parallel full-table scans, and maximizing disk I/O throughput is the single most critical bottleneck.


Most SMP servers have a specialized high speed RAM called a L2 cache that is localized near the CPUs as shown in Figure 18.5.


Figure 18.5: The non-uniform memory access architecture


Best of all, Oracle11g has been enhanced to recognize NUMA systems and adjust memory and scheduling operations accordingly.  NUMA technology allows for faster communication between distributed memory in a multi-processor server.  Better than the archaic UNIX implementations of the past decade, NUMA is fully supported by Linux and Windows Advanced Server 2003 and Oracle can now better exploit high end NUMA hardware in SMP servers.


Now that the use of Oracle11g has been justified for the warehouse, it is important to examine why many shops are moving from Linux to Windows for their mission critical warehouse applications.

Note:  AMM and dynamic Oracle memory management has measurable overhead.  See my important notes on

Oracle dynamic memory management.

Now, while the automated features of Oracle11g AMM, ASM and automatic query rewrite simplify the role of the Oracle DBA, savvy Oracle11g DBAs leverage other advanced Oracle11g features to get fast data warehouse performance:

§         Extensive Materialized Views: The Oracle11g dbms_advisor utility will automatically detect and recommend, then a materialized view will reduce disk I/O.

§         Automated Workload Repository: The AWR is a critical component for data warehouse predictive tools such as the dbms_advisor package.  AWR allows the DBA to run time-series reports of SQL access paths and intelligently create the most efficient materialized views for the warehouse.

§         Multiple Blocksizes: All data warehouse indexes that are accessed via range scans and Oracle objects that must be accessed via full-table or full-index scans should be in a 32k blocksize.

§         Data Caching: Small, frequently referenced dimension tables should be cached using the Oracle11g KEEP pool.

§         STAR query optimization: The Oracle11g STAR query features make it easy to make complex DSS queries run at fast speeds.

§         Asynchronous Change Data Capture: Change data capture allows incremental extraction, which allows only changed data to be extracted easily. For example, if a data warehouse extracts data from an operational system on a weekly basis, the data warehouse requires only the data that has changed since the last extraction, which would be only the data that has been modified in the past 7 days.

§         Oracle Streams: Streams based feed mechanisms can capture the necessary data changes from the operational database and send it to the destination data warehouse. The use of redo information by the Streams capture process avoids unnecessary overhead on the production database.

Oracle Database 11g is even more optimized for Itanium2 architecture than Oracle 9i was and many Oracle experts consider Intel-based servers as the best choice for running Oracle data warehouses.  The following section explores this more closely.



This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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