The Core Performance Fundamentals Of Oracle Data Warehousing – Introduction

At the 2009 Oracle OpenWorld Unconference back in October I lead a chalk and talk session entitled The Core Performance Fundamentals Of Oracle Data Warehousing. Since this was a chalk and talk I spared the audience any powerpoint slides but I had several people request that make it into a presentation so they could share it with others. After some thought, I decided that a series of blog posts would probably be a better way to share this information, especially since I tend to use slides as a speaking outline, not a condensed version of a white paper. This will be the first of a series of posts discussing what I consider to be the key features and technologies behind well performing Oracle data warehouses.

Introduction

As an Oracle database performance engineer who has done numerous customer data warehouse benchmarks and POCs over the past 5+ years, I’ve seen many data warehouse systems that have been plagued with problems on nearly every DBMS commonly used in data warehousing. Interestingly enough, many of these systems were facing many of the same problems. I’ve compiled a list of topics that I consider to be key features and/or technologies for Oracle data warehouses:

Core Performance Fundamental Topics

In the upcoming posts, I’ll deep dive into each one of these topics discussing why these areas are key for a well performing Oracle data warehouse. Stay tuned…

17 comments

  1. Karl Arao

    Hi Greg,

    Readables (books, articles, links) or any materials, or a deep dive on capacity planning on Data Warehouse environment (includes BIEE, OWB, etc.) would also be great :)

  2. Marco Gralike

    Hi Greg,

    indeed, I enjoyed the “chalk and talk” session very much, one of my highlights during OOW and grateful that that Scottish guy tagged me along (toys included…). I really think your insights are applicable on more than Data Warehousing so one happy guy will follow the series and see how I can make use of them in my “domain”…
    ;-)

  3. Greg Rahn

    @Marco Gralike
    I spend 99% of my time in the Data Warehouse area so the topics are more DW focused, but glad that Doug guy invited you. ;)

  4. Eric Sun

    Hi Greg,

    Do you which parameters in Oracle 11g and 10g determine how the coordinator/slave queries are distributed among nodes? I’ve observed a strange thing in my 4-node RAC. The coordinator is on node-A, but all the 8 slaves are all on node-B. The slaves are never on the same node as coordinator. Even for full partition-wise joins among hash-partitioned tables, the slaves are never distributed across multiple nodes.

    Load balance is enabled in the TNS entry. Is there any fundamental setting that I missed?

    Thank you!

  5. Greg Rahn

    @Eric Sun

    This comment doesn’t have much of anything to do with the post and this is not a support forum but I will give you some advice this one time. Parallel Execution (PX) will use as few nodes as possible to satisfy the execution because it is more efficient to do so (why run something on 4 computers when there are enough resources to run it on 1?). Given you have only 8 slaves (maybe that is DOP=8 and there are 8 producer slaves and 8 consumer slaves, or you have DOP=4 and 4 produces/4 consumers) and 4 nodes, it seems likely that in either case 1 single node can satisfy the PX requirements. If you want to use all 4 nodes, use a DOP of the number of CPUs in the cluster (or 2x the CPUs [cpu_count] in the cluster, which is the “default” DOP).

  6. Pingback: The Core Performance Fundamentals Of Oracle Data Warehousing – Data Loading
  7. Pingback: DW Introduction « Oracle Scratchpad
  8. Pingback: The Core Performance Fundamentals Of Oracle Data Warehousing – Set Processing vs Row Processing
  9. Pingback: The Core Performance Fundamentals Of Oracle Data Warehousing – Set Processing vs Row Processing
  10. Pingback: The Core Performance Fundamentals Of Oracle Data Warehousing – Balanced Hardware Configuration
  11. Pingback: The Core Performance Fundamentals Of Oracle Data Warehousing – Table Compression | Structured Data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s