Category: Oracle

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…

Oracle 11gR2 Database Flash Cache Patch For Oracle Enterprise Linux

Just a quick note that there is now a patch for the 11.2 Oracle Enterprise Linux (OEL) database ports to enable the database flash cache (not to be confused with the Exadata flash cache). Go to the My Oracle Support site [link] and search for patch 8974084 – META BUG FOR FLASH CACHE 11.2PL BUGS TO BACKPORT TO 11.2.0.1 OEL

You can download Oracle Database 11g Release 2 from OTN.

Note: The db flash cache is already built into the Solaris ports so no patch is needed.

Oracle OpenWorld Unconference

One of the less “traditional” tracks at Oracle OpenWorld is the Oracle Unconference. The Unconference allows for a much more informal setting for sessions and generally is more interactive and audience driven compared to the usually amounts of PowerPoint sessions contain which can cause eyes to bleed. This year I’ve signed up to give a session at the Unconference entitled Chalk & Talk: The Core Performance Fundamentals of Oracle Data Warehousing. This session will be a PowerPoint free session (hence Chalk & Talk) and it will be a very engineering heavy discussion around what techniques I use with customer POCs and VLDB databases and why I use those techniques. If you are a DBA that supports an Oracle data warehouse, especially a VLDB Oracle data warehouse, I think this session will give you some great insight on how to deal with performance challenges you may face. Topics that will be discussed will include partitioning, parallel execution, compression, statistics collection, Exadata, bulk data loads, ETL/ELT just to name a few. Bring your questions and join in the discussion. Hope to see you there!

Session Details:
What: Chalk & Talk: The Core Performance Fundamentals of Oracle Data Warehousing
Where: Moscone West Floor 3 Overlook #2
When: Monday, October 12 at 1pm

Oracle And Sun To Announce World’s First OLTP Database Machine With Sun FlashFire Technology

The internet buzz seems to be that Larry Ellison, CEO, Oracle Corporation and John Fowler, EVP, Sun Microsystems, Inc. will be announcing a new product, the world’s first OLTP database machine with Sun’s brand new FlashFire technology on Tuesday, September 15, 2009, 1 p.m. PT.

09030360_exadata_event_ers.jpg

Both Sun and Oracle have Webcast invitations on their websites:

  • Oracle Invitation
  • Sun Invitation

    I plan on being at the Oracle Conference Center for the launch and will try and Tweet the highlights. First Oracle Database 11g Release 2, now an OLTP database machine. Are there more innovations up Oracle’s sleeve? I guess we’ll have to wait and see.

  • Top 10 Oracle 11gR2 New Features

    In catching up on blog posts I see that Jonathan Lewis, Christian Antognini and Nuno Souto picked up on the deferred segment creation new feature in Oracle 11gR2. In keeping with the theme, I thought I’d put together the top 10 new features in Oracle Database 11g Release 2 (11.2) that I consider significant.

    1. Analytic Functions 2.0
    2. Recursive WITH Clause
    3. Preprocessing Data for ORACLE_LOADER Access Driver in External Tables
    4. In-Memory Parallel Execution
    5. Auto Degree of Parallelism (Auto DOP) and Parallel Statement Queuing
    6. Significant Performance Improvement of MV On-Commit Fast Refresh
    7. Database Resource Manager Instance Caging
    8. ASM Intelligent Data Placement
    9. Database File System (DBFS)
    10. Hybrid Columnar Compression

    In future posts I’ll dive into some of these in more technical detail but for now I thought I’d throw my list out there to raise awareness of the things I am looking at as a database performance engineer.

    Oracle Database 11g Release 2 Is Now Generally Available

    Just a quick post to spread the news that Oracle Database 11g Release 2 is now generally available. Download it for Linux today. See the press release for the usual details.

    Here is an interesting tidbit of info from a PC World article:

    The new release is the product of some 1,500 developers and 15 million hours of testing, according to Mark Townsend, vice president of database product management.

    Update: In reading Lowering your IT Costs with Oracle Database 11g Release 2 I noticed this interesting tidbit:

    With Oracle Datanase 11g Release 2, the Exadata Storage servers also enable new hybrid columnar compression technology that provides up to a 10 times compression ratio, without any loss of query performance. And, for pure historical data, a new archival level of hybrid columnar compression can be used that provides up to 40 times compression ratios.

    Hybrid columnar compression is a new method for organizing how data is stored. Instead of storing the data in traditional rows, the data is grouped, ordered and stored one column at a time. This type of format offers a higher degree of compression for data that is loaded into a table for data warehousing queries. Different compression techniques can also be used on different partitions within a table.

    Cool, Exadata now has column organized storage. Things are certainly getting very interesting in Exadata land.

    Be sure to check out the Oracle Database 11g Release 2 New Features Guide for more goodies.

    Update 2: For those attending OOW 2009, there will be a session on the new technology – Oracle’s Hybrid Columnar Compression: The Next-Generation Compression Technology (S311358) on Tuesday 10/13/2009 13:00 – 14:00.

    Oracle OpenWorld 2009: The Real-World Performance Group

    Even though Oracle OpenWorld 2009 is a few months away, I thought I would take a moment to mention that the Oracle Real-World Performance Group will again be hosting three sessions. Hopefully you are no stranger to our Oracle database performance sessions and this year we have what I think will be a very exciting and enlightening session: The Terabyte Hour with the Real-World Performance Group. If you are the slightest bit interested in seeing just how fast the Oracle Database Machine really is and how it can devour flat files in no time, rip through and bend data at amazing speeds, this is the session for you. All the operations will be done live for you to observe. No smoke. No mirrors. Pure Exadata performance revealed.

    Session ID: S311237
    Session Title: Real-World Database Performance Roundtable
    Session Abstract: This session is a panel discussion including Oracle’s Real-World Performance Group and other invited performance experts. To make the hour productive, attendees need to write their questions on postcards and hand them to the panel at the beginning of the session. The questions should stick to the subject matter of real-world database performance. The panel members look forward to meeting you and answering your questions.
       
    Session ID: S311239
    Session Title: The Terabyte Hour with the Real-World Performance Group
    Session Abstract: Last year at Oracle OpenWorld, Oracle launched the Oracle Database Machine, a complete package of software, servers, and storage with the power to tackle large-scale business intelligence problems immediately and scale linearly as your data warehouse grows. In this session, Oracle’s Real-World Performance Group demonstrates how to use an Oracle Database Machine to load, transform, and query a 1-terabyte database in less than an hour. The demonstration shows techniques for exploiting full database parallelism in a simple but optimal manner.
       
    Session ID: S311238
    Session Title: Current Trends in Real-World Database Performance
    Session Abstract: The year 2009 has been an exciting one for Oracle’s Real-World Performance Group. The group has been challenged by bigger databases, new performance challenges, and now the Oracle Database Machine with Oracle Exadata Storage Server. This session focuses on some of the real-world performance ideas and solutions that have worked over the last year, including performance design philosophies, best practices, and a few tricks and tips.

    Oracle Parallel Execution: Interconnect Myths And Misunderstandings

    A number of weeks back I had come across a paper/presentation by Riyaj Shamsudeen entitled Battle of the Nodes: RAC Performance Myths (avaiable here). As I was looking through it I saw one example that struck me as very odd (Myth #3 – Interconnect Performance) and I contacted him about it. After further review Riyaj commented that he had made a mistake in his analysis and offered up a new example. I thought I’d take the time to discuss this as parallel execution seems to be one of those areas where many misconceptions and misunderstandings exist.

    The Original Example

    I thought I’d quickly discuss why I questioned the initial example. The original query Riyaj cited is this one:

    select /*+ full(tl) parallel (tl,4) */
           avg (n1),
           max (n1),
           avg (n2),
           max (n2),
           max (v1)
    from   t_large tl;
    

    As you can see this is a very simple single table aggregation without a group by. The reason that I questioned the validity of this example in the context of interconnect performance is that the parallel execution servers (parallel query slaves) will each return exactly one row from the aggregation and then send that single row to the query coordinator (QC) which will then perform the final aggregation. Given that, it would seem impossible that this query could cause any interconnect issues at all.

    Riyaj’s Test Case #1

    Recognizing the original example was somehow flawed, Riyaj came up with a new example (I’ll reference as TC#1) which consisted of the following query:

    select /*+ parallel (t1, 8,2) parallel (t2, 8, 2)  */
           min (t1.customer_trx_line_id + t2.customer_trx_line_id),
           max (t1.set_of_books_id + t2.set_of_books_id),
           avg (t1.set_of_books_id + t2.set_of_books_id),
           avg (t1.quantity_ordered + t2.quantity_ordered),
           max (t1.attribute_category),
           max (t2.attribute1),
           max (t1.attribute2)
    from   (select *
            from   big_table
            where  rownum <= 100000000) t1,
           (select *
            from   big_table
            where  rownum <= 100000000) t2
    where  t1.customer_trx_line_id = t2.customer_trx_line_id;
    

    The execution plan for this query is:

    ----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    ----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |           |     1 |   249 |       |  2846K  (4)| 01:59:01 |        |      |            |
    |   1 |  SORT AGGREGATE           |           |     1 |   249 |       |            |          |        |      |            |
    |*  2 |   HASH JOIN               |           |   100M|    23G|   762M|  2846K  (4)| 01:59:01 |        |      |            |
    |   3 |    VIEW                   |           |   100M|    10G|       |  1214K  (5)| 00:50:46 |        |      |            |
    |*  4 |     COUNT STOPKEY         |           |       |       |       |            |          |        |      |            |
    |   5 |      PX COORDINATOR       |           |       |       |       |            |          |        |      |            |
    |   6 |       PX SEND QC (RANDOM) | :TQ10000  |   416M|  6749M|       |  1214K  (5)| 00:50:46 |  Q1,00 | P->S | QC (RAND)  |
    |*  7 |        COUNT STOPKEY      |           |       |       |       |            |          |  Q1,00 | PCWC |            |
    |   8 |         PX BLOCK ITERATOR |           |   416M|  6749M|       |  1214K  (5)| 00:50:46 |  Q1,00 | PCWC |            |
    |   9 |          TABLE ACCESS FULL| BIG_TABLE |   416M|  6749M|       |  1214K  (5)| 00:50:46 |  Q1,00 | PCWP |            |
    |  10 |    VIEW                   |           |   100M|    12G|       |  1214K  (5)| 00:50:46 |        |      |            |
    |* 11 |     COUNT STOPKEY         |           |       |       |       |            |          |        |      |            |
    |  12 |      PX COORDINATOR       |           |       |       |       |            |          |        |      |            |
    |  13 |       PX SEND QC (RANDOM) | :TQ20000  |   416M|    10G|       |  1214K  (5)| 00:50:46 |  Q2,00 | P->S | QC (RAND)  |
    |* 14 |        COUNT STOPKEY      |           |       |       |       |            |          |  Q2,00 | PCWC |            |
    |  15 |         PX BLOCK ITERATOR |           |   416M|    10G|       |  1214K  (5)| 00:50:46 |  Q2,00 | PCWC |            |
    |  16 |          TABLE ACCESS FULL| BIG_TABLE |   416M|    10G|       |  1214K  (5)| 00:50:46 |  Q2,00 | PCWP |            |
    ----------------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("T1"."n1"="T2"."n1")
       4 - filter(ROWNUM<=100000000)
       7 - filter(ROWNUM<=100000000)
      11 - filter(ROWNUM<=100000000)
      14 - filter(ROWNUM<=100000000)
    

    This is a rather synthetic query but there are a few things that I would like to point out. First, this query uses a parallel hint with 3 values representing table/degree/instances, however instances has been deprecated (see 10.2 parallel hint documentation). In this case the DOP is calculated by degree * instances or 16, not DOP=8 involving 2 instances. Note that the rownum filter is causing all the rows from the tables to be sent back to the QC for the COUNT STOPKEY operation thus causing the execution plan to serialize, denoted by the P->S in the IN-OUT column.

    Riyaj had enabled sql trace for the QC and the TKProf output is such:

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=152 pr=701158 pw=701127 time=1510221226 us)
    98976295   HASH JOIN  (cr=152 pr=701158 pw=701127 time=1244490336 us)
    100000000    VIEW  (cr=76 pr=0 pw=0 time=200279054 us)
    100000000     COUNT STOPKEY (cr=76 pr=0 pw=0 time=200279023 us)
    100000000      PX COORDINATOR  (cr=76 pr=0 pw=0 time=100270084 us)
          0       PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
          0        COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
          0         PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
          0          TABLE ACCESS FULL BIG_TABLE_NAME_CHANGED_12 (cr=0 pr=0 pw=0 time=0 us)
    100000000    VIEW  (cr=76 pr=0 pw=0 time=300298770 us)
    100000000     COUNT STOPKEY (cr=76 pr=0 pw=0 time=200298726 us)
    100000000      PX COORDINATOR  (cr=76 pr=0 pw=0 time=200279954 us)
          0       PX SEND QC (RANDOM) :TQ20000 (cr=0 pr=0 pw=0 time=0 us)
          0        COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
          0         PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
          0          TABLE ACCESS FULL BIG_TABLE_NAME_CHANGED_12 (cr=0 pr=0 pw=0 time=0 us)
    

    Note that the Rows column contains zeros for many of the row sources because this trace is only for the QC, not the slaves, and thus only QC rows will show up in the trace file. Something to be aware of if you decide to use sql trace with parallel execution.

    Off To The Lab: Myth Busting Or Bust!

    I wanted to take a query like TC#1 and run it in my own environment so I could do more monitoring of it. Given the alleged myth had to do with interconnect traffic of cross-instance (inter-node) parallel execution, I wanted to be certain to gather the appropriate data. I ran several tests using a similar query on a similar sized data set (by row count) as the initial example. I ran all my experiments on a Oracle Real Application Clusters version 11.1.0.7 consisting of eight nodes, each with two quad-core CPUs. The interconnect is InfiniBand and the protocol used is RDS (Reliable Datagram Sockets).

    Before I get into the experiments I think it is worth mentioning that Oracle’s parallel execution (PX), which includes Parallel Query (PQ), PDML & PDDL, can consume vast amounts of resources. This is by design. You see, the idea of Oracle PX is to dedicate a large amount of resources (processes) to a problem by breaking it up into many smaller pieces and then operate on those pieces in parallel. Thus the more parallelism that is used to solve a problem, the more resources it will consume, assuming those resources are available. That should be fairly obvious, but I think it is worth stating.

    For my experiments I used a table that contains just over 468M rows.

    Below is my version of TC#1. The query is a self-join on a unique key and the table is range partitioned by DAY_KEY into 31 partitions. Note that I create a AWR snapshot immediately before and after the query.

    exec dbms_workload_repository.create_snapshot
    
    select /* &amp;amp;&amp;amp;1 */
           /*+ parallel (t1, 16) parallel (t2, 16) */
           min (t1.bsns_unit_key + t2.bsns_unit_key),
           max (t1.day_key + t2.day_key),
           avg (t1.day_key + t2.day_key),
           max (t1.bsns_unit_typ_cd),
           max (t2.curr_ind),
           max (t1.load_dt)
    from   dwb_rtl_trx t1,
           dwb_rtl_trx t2
    where  t1.trx_nbr = t2.trx_nbr;
    
    exec dbms_workload_repository.create_snapshot
    

    Experiment Results Using Fixed DOP=16

    I ran my version of TC#1 across a varying number of nodes by using Oracle services (instance_groups and parallel_instance_group have been deprecated in 11g), but kept the DOP constant at 16 for all the tests. Below is a table of the experiment results.

    Nodes
    Elapsed Time
    SQL Monitor Report
    AWR Report
    AWR SQL Report
    1
    00:04:54.12
    2
    00:03:55.35
    4
    00:02:59.24
    8
    00:02:14.39

    Seemingly contrary to what many people would probably guess, the execution times got better the more nodes that participated in the query even though the DOP constant throughout each of tests.

    Measuring The Interconnect Traffic

    One of the new additions to the AWR report in 11g was the inclusion of interconnect traffic by client. This section is near the bottom of the report and looks like such:
    Interconnect Throughput By Client
    This allows the PQ message traffic to be tracked, whereas in prior releases it was not.

    Even though AWR contains the throughput numbers (as in megabytes per second) I thought it would be interesting to see how much data was being transferred, so I used the following query directly against the AWR data. I put a filter predicate on to return only where there DIFF_RECEIVED_MB >= 10MB so the instances that were not part of the execution are filtered out, as well as the single instance execution.

    break on snap_id skip 1
    compute sum of DIFF_RECEIVED_MB on SNAP_ID
    compute sum of DIFF_SENT_MB on SNAP_ID
    
    select *
    from   (select   snap_id,
                     instance_number,
                     round ((bytes_sent - lag (bytes_sent, 1) over
                       (order by instance_number, snap_id)) / 1024 / 1024) diff_sent_mb,
                     round ((bytes_received - lag (bytes_received, 1) over
                       (order by instance_number, snap_id)) / 1024 / 1024) diff_received_mb
            from     dba_hist_ic_client_stats
            where    name = 'ipq' and
                     snap_id between 910 and 917
            order by snap_id,
                     instance_number)
    where  snap_id in (911, 913, 915, 917) and
           diff_received_mb &amp;gt;= 10
    /
    
    SNAP_ID    INSTANCE_NUMBER DIFF_SENT_MB DIFF_RECEIVED_MB
    ---------- --------------- ------------ ----------------
           913               1        11604            10688
                             2        10690            11584
    **********                 ------------ ----------------
    sum                               22294            22272
    
           915               1         8353             8350
                             2         8133             8418
                             3         8396             8336
                             4         8514             8299
    **********                 ------------ ----------------
    sum                               33396            33403
    
           917               1         5033             4853
                             2         4758             4888
                             3         4956             4863
                             4         5029             4852
                             5         4892             4871
                             6         4745             4890
                             7         4753             4889
                             8         4821             4881
    **********                 ------------ ----------------
    sum                               38987            38987
    

    As you can see from the data, the more nodes that were involved in the execution, the more interconnect traffic there was, however, the execution times were best with 8 nodes.

    Further Explanation Of Riyaj’s Issue

    If you read Riyaj’s post, you noticed that he observed worse, not better as I did, elapsed times when running on two nodes versus one. How could this be? It was noted in the comment thread of that post that the configuration was using Gig-E as the interconnect in a Solaris IPMP active-passive configuration. This means the interconnect speeds would be capped at 128MB/s (1000Mbps), the wire speed of Gig-E. This is by all means is an inadequate configuration to use cross-instance parallel execution.

    There is a whitepaper entitled Oracle SQL Parallel Execution that discusses many of the aspects of Oracle’s parallel execution. I would highly recommend reading it. This paper specifically mentions:

    If you use a relatively weak interconnect, relative to the I/O bandwidth from the server to the storage configuration, then you may be better of restricting parallel execution to a single node or to a limited number of nodes; inter-node parallel execution will not scale with an undersized interconnect.

    I would assert that this is precisely the root cause (insufficient interconnect bandwidth for cross-instance PX) behind the issues that Riyaj observed, thus making his execution slower on two nodes than one node.

    The Advantage Of Hash Partitioning/Subpartitioning And Full Partition-Wise Joins

    At the end of my comment on Riyaj’s blog, I mentioned:

    If a DW frequently uses large table to large table joins, then hash partitioning or subpartitioning would yield added gains as partition-wise joins will be used.

    I thought that it would be both beneficial and educational to extend TC#1 and implement hash subpartitioning so that the impact could be measured on both query elapsed time and interconnect traffic. In order for a full partition-wise join to take place, the table must be partitioned/subpartitioned on the join key column, so in this case I’ve hash subpartitioned on TRX_NBR. See the Oracle Documentation on Partition-Wise Joins for a more detailed discussion on PWJ.

    Off To The Lab: Partition-Wise Joins

    I’ve run through the exact same test matrix with the new range/hash partitioning model and below are the results.

    Nodes
    Elapsed Time
    SQL Monitor Report
    AWR Report
    AWR SQL Report
    1
    00:02:42.41
    2
    00:01:37.29
    4
    00:01:12.82
    8
    00:01:05.04

    As you can see by the elapsed times, the range/hash partitioning model with the full partition-wise join has decreased the overall execution time by around a factor of 2X compared to the range only partitioned version.

    Now let’s take a look at the interconnect traffic for the PX messages:

    break on snap_id skip 1
    compute sum of DIFF_RECEIVED_MB on SNAP_ID
    compute sum of DIFF_SENT_MB on SNAP_ID
    
    select *
    from   (select   snap_id,
                     instance_number,
                     round ((bytes_sent - lag (bytes_sent, 1) over
                       (order by instance_number, snap_id)) / 1024 / 1024) diff_sent_mb,
                     round ((bytes_received - lag (bytes_received, 1) over
                       (order by instance_number, snap_id)) / 1024 / 1024) diff_received_mb
            from     dba_hist_ic_client_stats
            where    name = 'ipq' and
                     snap_id between 1041 and 1048
            order by snap_id,
                     instance_number)
    where  snap_id in (1042,1044,1046,1048) and
           diff_received_mb &amp;gt;= 10
    /
    no rows selected
    

    Hmm. No rows selected?!? I had previously put in the predicate “DIFF_RECEIVED_MB >= 10MB” to filter out the nodes that were not participating in the parallel execution. Let me remove that predicate rerun the query.

       SNAP_ID INSTANCE_NUMBER DIFF_SENT_MB DIFF_RECEIVED_MB
    ---------- --------------- ------------ ----------------
          1042               1            8                6
                             2            2                3
                             3            2                3
                             4            2                3
                             5            2                3
                             6            2                3
                             7            2                3
                             8            2                3
    **********                 ------------ ----------------
    sum                                  22               27
    
          1044               1            7                7
                             2            3                2
                             3            2                2
                             4            2                2
                             5            2                2
                             6            2                2
                             7            2                2
                             8            2                2
    **********                 ------------ ----------------
    sum                                  22               21
    
          1046               1            1                2
                             2            1                2
                             3            1                2
                             4            3                1
                             5            1                1
                             6            1                1
                             7            1                1
                             8            1                1
    **********                 ------------ ----------------
    sum                                  10               11
    
          1048               1            6                5
                             2            1                2
                             3            3                2
                             4            1                2
                             5            1                2
                             6            1                2
                             7            1                2
                             8            1                2
    **********                 ------------ ----------------
    sum                                  15               19
    
    

    Wow, there is almost no interconnect traffic at all. Let me verify with the AWR report from the 8 node execution.

    The AWR report confirms that there is next to no interconnect traffic for the PWJ version of TC#1. The reason for this is that since the table is hash subpartitoned on the join column each of the subpartitions can be joined to each other minimizing the data sent between parallel execution servers. If you look at the execution plan (see the AWR SQL Report) from the first set of experiments you will notice that the broadcast method for each of the tables is HASH but in the range/hash version of TC#1 there is no broadcast at all for either of the two tables. The full partition-wise join behaves logically the same way that a shared-nothing database would; each of the parallel execution servers works on its partition which does not require data from any other partition because of the hash partitioning on the join column. The main difference is that in a shared-nothing database the data is physically hash distributed amongst the nodes (each node contains a subset of all the data) where as all nodes in a Oracle RAC database have access to the all the data.

    Parting Thoughts

    Personally I see no myth about cross-instance (inter-node) parallel execution and interconnect traffic, but frequently I see misunderstandings and misconceptions. As shown by the data in my experiment, TC#1 (w/o hash subpartitioning) running on eight nodes is more than 2X faster than running on one node using exactly the same DOP. Interconnect traffic is not a bad thing as long as the interconnect is designed to support the workload. Sizing the interconnect is really no different than sizing any other component of your cluster (memory/CPU/disk space/storage bandwidth). If it is undersized, performance will suffer. Depending on the number and speed of the host CPUs and the speed and bandwidth of the interconnect, your results may vary.

    By hash subpartioning the table the interconnect traffic was all but eliminated and the query execution times were around 2X faster than the non-hash subpartition version of TC#1. This is obviously a much more scalable solution and one of the main reasons to leverage hash (sub)partitioning in a data warehouse.

    Oracle Press Release: Customers are Choosing the Oracle Database Machine

    Oracle put out a press release today entitled “Customers are Choosing the Oracle Database Machine” mentioning the new Exadata and Oracle Database Machine customers. I’ve quoted a few parts of it below. Oracle cites twenty initial customers.

    Initial Customers

    Initial Oracle Exadata customers including Amtrak, Allegro Group, Automobile Association of the UK, CTC, Garanti Bank, Giant Eagle, HISCOM (Hokuriku Coca Cola), KnowledgeBase Marketing, Loyalty Partner Solutions, M-Tel, MTN Group, Nagase, NS Solutions, NTT Data, OK Systems, Research in Motion, SoftBank Mobile, Screwfix, ThomsonReuters, and True Telecom, confirm the benefits Oracle Exadata products bring to their Oracle data warehouses.

    Supporting Quotes

  • “The HP Oracle Database Machine beat the competing solutions we tested on bandwidth, load rate, disk capacity, and transparency. In addition, Allegro Group saw a significant performance boost from the new data warehouse. A query that used to take 24 hours to complete now runs in less than 30 minutes on the HP Oracle Database Machine, and that’s without any manual query tuning.” — Christian Maar, CIO of Poznań, Poland-based Allegro Group
  • “After carefully testing various options for a new data warehouse platform we chose the HP Oracle Database Machine over Netezza. Oracle Exadata was able to speed up one of our critical processes from days to minutes. The HP Oracle Database Machine will allow us to improve service levels and expand our service offerings. We also plan to consolidate our current data warehouse solutions onto the Oracle Exadata platform. This should eliminate several servers and a number of storage arrays and help reduce our operating overhead and improve margins.” — Brian Camp, Sr. VP of Infrastructure Services, KnowledgeBase Marketing
  • “We anticipate the move of our Data Warehouse to Oracle Database 11g running on our first HP Oracle Database Machine with Oracle Exadata will deliver a substantial boost in performance and scalability, simply and easily. Our business users expect to benefit from faster access to information more quickly than ever before. The resulting agility should make a huge difference to our business.” — Andreas Berninger, Chief Operating, Loyalty Partner Solutions
  • “The biggest technological challenge we face when we architect a database is how to create a system that performs fast with huge volumes. Oracle Exadata helps solve our performance demands. It's highly available and reliable, and it can essentially scale linearly. All of the queries we tested were faster with Oracle Exadata. The smallest performance boost we experienced was 10 times; the fastest was 72 times faster.” — Plamen Zyumbyulev, Head of Database Administration, M-Tel
  • “A key component of RIM's manufacturing process is extensive testing of each handheld device. This testing generates large volumes of data, which is extensively analyzed by our quality and test engineers and business users to ensure RIM is producing the highest quality devices for our customers. The HP Oracle Database Machine is an ideal platform to store and analyze this data since it provides the performance, scalability and storage capacity for our requirements. It’s a cost-effective platform to meet our speed and scalability needs and is an integral component used for analysis in our manufacturing process.” — Ketan Parekh, Manager Database Systems, Research in Motion
  • “The benchmark result of Oracle Exadata was amazing! Since it is based on Oracle Database 11g, we determined that it is compatible with other systems and the most suitable solution for our increasing data infrastructure.” — Keiichiro Shimizu, General Manager, Business Base Management Dept., Information System Div., SoftBank Mobile Corp.
  • “Oracle Exadata is among the most successful new product introductions in Oracle's history,” said Willie Hardie, vice president of Database Product Marketing, Oracle. “Repeatedly in customer proof of concepts and benchmarks, Oracle Exadata has delivered extreme performance for customers' data warehouses.”