Kevin Closson's Blog: Platforms, Databases and Storage

I met someone at Rocky Mountain User Group Training Days 2009 who mentioned that they enjoyed my Little Things Doth Crabby Make…series ( found here). I was reminded of that this morning as I suffered the following Oracle Database 11g Automatic Storage Management (ASM) issue:

$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Feb 19 09:33:53 2009
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup

ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2158992 bytes
Variable Size             256605808 bytes
ASM Cache                  25165824 bytes

ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA2"
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA1"

Ho hum. I know the disks are there. I’ve just freshly configured this system. After all, this is Exadata and configuring ASM to use Exadata couldn’t be easier as you simply list the IP addresses of the Exadata Storage Servers in a text configuration file. No more ASMLib sort of stuff. Just point and go.

SQL> select count(*) from v$asm_disk;

COUNT(*)
----------
24

See, even ASM agrees with me. I set up 12 disks for each diskgroup and viola, there they are.

KFOD
There is even a nice little command line tool that ships with Oracle Database 11g 11.1.0.[67] that reports what Exadata disks are discovered. This is a nice little tool. It shows that I have 12 Exadata “griddisks” (ASM disks really) of 20GB and another 12 of 200GB all within a single Exadata Storage Server  (for testing purposes). Note, it also reports a list of other ASM instances in the database grid.

$ kfod -disk all
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
 1:      20480 Mb o/192.168.50.32/data1_CD_10_cell06       <unknown> <unknown>
 2:      20480 Mb o/192.168.50.32/data1_CD_11_cell06       <unknown> <unknown>
 3:      20480 Mb o/192.168.50.32/data1_CD_12_cell06       <unknown> <unknown>
 4:      20480 Mb o/192.168.50.32/data1_CD_1_cell06        <unknown> <unknown>
 5:      20480 Mb o/192.168.50.32/data1_CD_2_cell06        <unknown> <unknown>
 6:      20480 Mb o/192.168.50.32/data1_CD_3_cell06        <unknown> <unknown>
 7:      20480 Mb o/192.168.50.32/data1_CD_4_cell06        <unknown> <unknown>
 8:      20480 Mb o/192.168.50.32/data1_CD_5_cell06        <unknown> <unknown>
 9:      20480 Mb o/192.168.50.32/data1_CD_6_cell06        <unknown> <unknown>
 10:      20480 Mb o/192.168.50.32/data1_CD_7_cell06        <unknown> <unknown>
 11:      20480 Mb o/192.168.50.32/data1_CD_8_cell06        <unknown> <unknown>
 12:      20480 Mb o/192.168.50.32/data1_CD_9_cell06        <unknown> <unknown>
 13:     204800 Mb o/192.168.50.32/data2_CD_10_cell06       <unknown> <unknown>
 14:     204800 Mb o/192.168.50.32/data2_CD_11_cell06       <unknown> <unknown>
 15:     204800 Mb o/192.168.50.32/data2_CD_12_cell06       <unknown> <unknown>
 16:     204800 Mb o/192.168.50.32/data2_CD_1_cell06        <unknown> <unknown>
 17:     204800 Mb o/192.168.50.32/data2_CD_2_cell06        <unknown> <unknown>
 18:     204800 Mb o/192.168.50.32/data2_CD_3_cell06        <unknown> <unknown>
 19:     204800 Mb o/192.168.50.32/data2_CD_4_cell06        <unknown> <unknown>
 20:     204800 Mb o/192.168.50.32/data2_CD_5_cell06        <unknown> <unknown>
 21:     204800 Mb o/192.168.50.32/data2_CD_6_cell06        <unknown> <unknown>
 22:     204800 Mb o/192.168.50.32/data2_CD_7_cell06        <unknown> <unknown>
 23:     204800 Mb o/192.168.50.32/data2_CD_8_cell06        <unknown> <unknown>
 24:     204800 Mb o/192.168.50.32/data2_CD_9_cell06        <unknown> <unknown>
--------------------------------------------------------------------------------
ORACLE_SID ORACLE_HOME
================================================================================
 +ASM1 /u01/app/oracle/product/db
 +ASM2 /u01/app/oracle/product/db
 +ASM3 /u01/app/oracle/product/db
<pre>

I know why ASM is trying to mount these diskgroups because I set the parameter file to direct it to do so.

SQL> show parameter asm_diskgroups;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string      DATA1, DATA2

I suppose I should get some information about the diskgroups. How about names first:

SQL> select name from v$asm_diskgroup;
no rows selected

SQL> host date
Thu Feb 19 09:37:25 PST 2009

Idiot!  When you have several configurations “stewing” it is quite easy to miss a step. Today that seems to be forgetting to actually create diskgroups before I ask ASM to mount them.

SQL> startup force

ASM instance started
Total System Global Area  283930624 bytes
Fixed Size                  2158992 bytes
Variable Size             256605808 bytes
ASM Cache                  25165824 bytes

ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA2"

SQL>  select name from v$asm_diskgroup;

NAME
------------------------------
DATA1

SQL> host date
Thu Feb 19 09:44:21 PST 2009

Magic. I created the DATA1 diskgroup in a separate xterm and did a STARTUP FORCE.

Summary
Stupidity is one of those little things that doth crabby make. And, yes, the title of this blog post was a come-on. Who knows, however, someday there may be a flustered googler that’ll end up feeling crabby and stupid (like I do now)  🙂  after finding this worthless post.

According to this post on blogs.oracle.com, Information Management is hosting a Web Seminar on February 25, 2009 covering the Winter Corporation findings in a recent Exadata proof of concept.

The signup page for the event is here.

I’ll be attending. I’m always curious about what people are saying when they do these things. Go ahead, sign up and join me.

Several readers sent in email questions after reading the Winter Corporation Paper about Exadata I announced in a recent blog entry. I thought I’d answer one in this quick blog entry.

The reader’s email read as follows (I made no edits other than to remove the SAN vendor’s name):

I have read through the Winter Corporation paper regarding Exadata, but is it ok for me to ask a questio? We have an existing data warehouse on a 4 node 10g RAC cluster attached to a [ brand name removed ]   SAN array by 2 active 4GB ports on each RAC node. When we test with Orion we see nearly 2.9 gigabytes per second throughput but with RDBMS queires we never see more than about 2 gigabytes per sec throughput except in select count(*) situation. With select count(*) we see about 2.5GB/s. Why is this?

Think Plumbing First

It is always best to focus first on the plumbing, and then on the array controller itself. After all, if the supposed maximum theoretical throughput of an array is on the order of 3GB/s, but servers are connected to the array with limited connectivity, the bandwidth is unrealizable. In this case, 2 active 4GFC HBAs per RAC node demonstrate sufficient throughput for this particular SAN array. Remember, I deleted the SAN brand. The particular brand cited by the reader is most certainly limited to 3GB/s (I know the brand and model well)  but no matter because the 2 active 4GFC paths to each RAC node limit I/O to an aggregate of 3.2GB/s sustained read throughput no matter what kind of SAN it is. This is actually a case of a well-balanced server-to-storage configuration and I pointed out so in a private email to the blog reader who sent me this question. But, what about the reader’s question?

Orion is a Very Light Eater

The reason the reader is able to drive storage at approximately 2.9GB/s with Orion is because Orion does nothing with the data being read from disk. As I/Os are completed it simply issues more. We sometimes call this lightweight I/O testing because the code doesn’t touch the data being read from disk. Indeed, even the dd(1) command can drive storage at maximum theoretical I/O rates with a command like dd if=/dev/sdN of=/dev/null bs=1024k. A dd(1) command like this does not touch the data being read from disk.

SELECT COUNT(*)

The reason the reader sees Oracle driving storage at 2.5GB/s with a SELECT COUNT(*) is because when a query such as this reads blocks from disk only a few bytes of the disk blocks being read are loaded into the processor caches. Indeed, Oracle doesn’t have to touch every row piece in a block to know how many rows the block contains. There is summary information in the header of the block that speeds up row counting. When code references just one byte of data in an Oracle block, after it is read from disk, the processor causes the memory controller to load 64 bytes (on x86_64 cpus) into the processor cache. Anything in that 64-byte “line” can be accessed for “free” (meaning additional loads from memory are not needed). Accesses to any other 64-byte lines in the Oracle block causes subsequent memory lines to be installed into the processor cache. While the CPU is waiting for a line to be loaded it is in a stalled state, which is accounted for as user-mode cycles charged to the Oracle process referencing the memory. The more processes do with the blocks being read from disk, the higher processor utilization goes up and eventually I/O goes down. This is why the reader stated that they see about 2GB/s when Oracle is presumably doing “real queries” such as those which perform filtration, projection, joins, sorting, aggregation and so forth. The reader didn’t state processor utilization for the queries seemingly limited to 2GB/s, but it stands to reason they were more complex than the SELECT COUNT(*) test.

Double Negatives for Fun and Learning Purposes

You too can see what I’m talking about by running a select that ingests dispersed columns and all rows in a query after performing zero-effect filtration such as the following 16-column table example:

SELECT AVG(LENGTH(col1)), AVG(LENGTH(col8)), AVG(LENGTH(col16)) FROM TABX WHERE col1 NOT LIKE ‘%NEVER’ and col8 NOT LIKE ‘%NEVER’ and col16 NOT LIKE ‘%NEVER’;

This test presumes columns 1,8 and 16 never contain the value ‘NEVER’. Observe the processor utilization when running this sort of query and compare that to a simple select count(*) of the same table.

Other Factors?

Sure, the reader’s throughput difference between the SELECT COUNT(*) and Orion could be related to tuning issues (i.e., Parallel Query Option degree of parallelism). However, in my experience achieving about 83% of maximum theoretical I/O with SELECT COUNT(*) is pretty good. Further, the reader’s complex query achieved about 66% of maximum theoretical I/O throughput which is also quite good–when using conventional storage.

What Does This Have To Do With Exadata

Exadata offloads predicate filtering and column projection (amongst the many other value propositions). Even this silly example has processing that can be offloaded to Exadata such as filtration that filters out no rows and the cost of projecting columns 1,8 and 16. The database host spends no cycles with the filtration or projection. It just performs the work of the AVG() and LENGTH() functions.

I didn’t have the heart to point out to the reader that 3GB/s is the least amount of throughput available when using Exadata and Real Application Clusters (RAC). That is, with RAC the fewest number of Exadata Storage Servers supported is 3 and there’s no doubt that 3 Exadata Storage Servers do indeed offer 3GB/s query I/O throughput. In fact, as the Winter Corporation paper shows, Exadata is able to perform maximum theoretical I/O throughput even with complex, concurrent queries because there is 2/3rds of a Xeon 54XX “Harpertown” processor for each disk drive offloading processing from the database grid.

So, while Orion is indeed a “light eater”, Exadata is quite ravenous.

AdSense Nonsense

I don’t have a screen-shot for verification, but a blog reader sent me email notifying me that WordPress (the site that hosts my blog) is letting Google AdSense put advertisements for Netezza on my blog posts. The reader thought I was the one doing the AdSense but I assured him that it is not I. My blogging is a non-profit effort. It is WordPress and the AdSense nonsense is the “pay” for using a “free” site.

There’s No Such Thing as a Free Lunch

I knew WordPress did this sort of thing on occasion, but I had totally put it out of mind…until now. So, have no fear readers, I just used some of my very own bier money to pay WordPress so you don’t have to see any ads from Oracle’s competitors any more!

WinterCorporation has posted a paper covering a recent Exadata proof of concept testing exercise. Highlights of the paper are evidence of concurrent moderately complex queries being serviced by Exadata at advertised disk throughput rates. The paper can be found at the following link:

Measuring the Performance of the Oracle Exadata Storage Server

There is also a copy of the paper on oracle.com at this link: Measuring the Performance of the Oracle Exadata Storage Server and a copy in the Wayback Machine in case it ages out of oracle.com.

Quotable Quotes
I’d like to draw attention to the following quote:

14 Gigabytes per second is a rate that can be achieved with some conventional storage arrays — only in dedicated large-scale enterprise arrays, that would require multiple full-height cabinets of hardware – and would therefore entail more space, power, and cooling than the HP Oracle Database Machine we tested here. Additionally, with established storage architectures, Oracle cannot offload any processing to the storage tier, therefore the database tier would require substantially more hardware to achieve a rate approaching 14 GB/second.

Yes, it may be possible to connect enough conventional storage to drive query disk throughput at 14GB/s, but the paper correctly points out that since there is no offload processing with conventional storage the database grid would require substantially more hardware than is assembled in the HP Oracle Database Machine. One would have to start from the ground up, as it were. By that I mean a database grid capable of simply ingesting 14GB/s would have to have 35 4Gb FC host bus adaptors. That requires a huge database grid.

If I could meet the guys (that would be me) that worked on this proof of concept I’d love to ask them what storage grid processor utilization was measured at the point where storage was at  peak throughput and performing the highest degree of storage processing complexity.  Now that would be a real golden nugget.  One thing is for certain, there was enough storage processor bandwidth to perform the Smart Scans which consist of applying WHERE predicates, column projection and performing bloom filtration in storage. Moreover, the test demonstrated ample storage processor bandwidth  to execute Smart Scan processing while blocks of data were whizzing by at the rate of 1GB/s per Oracle Exadata Storage Server. Otherwise, the paper wouldn’t be there.

Maybe 1.0476 processors per hard drive ( 176:168  )will become the new industry standard for optimal processor to disk ratio in DW/BI solutions.

In the comment thread of one of my old posts about Orion a reader posted an example of a problem he was having with the tool.

# ./orion10.2_linux -run simple -testname mytest -num_disks 1
ORION: ORacle IO Numbers — Version 10.2.0.1.0
Test will take approximately 9 minutes
Larger caches may take longer

storax_skgfr_openfiles: File identification failed: /mnt/1201879371/Orion, error 4
storax_skgfr_openfiles: File identification failed on /mnt/1201879371/Orion
OER 27054, Error Detail 0: please look up error in Oracle documentation
rwbase_lio_init_luns: lun_openvols failed
rwbase_rwluns: rwbase_lio_init_luns failed
orion_thread_main: rw_luns failed
Non test error occurred
Orion exiting

Carry on Wayward Googler

I didn’t work the problem with the poster of this issue, but I do know the answer to the issue and for the sake of any future wayward googlers I’ll post up the solution. The solution was to set  fs.aio-max-nr in sysctl.conf file to 4194304. The value can be change through the /proc interface as well:

# echo 4194304 > /proc/sys/fs/aio-max-nr

Juan Loaiza (SVP Oracle Systems Technologies Div) is offering a webcast this Wednesday. Here are the details:

Webcast: Extreme Performance for Your Data Warehouse
Wednesday, January 28th, 9:00 am PST

Data warehouses are tripling in size every two years and supporting ever-larger databases with ever increasing demands from business users to get “answers” faster requires a new way to approach this challenge. Oracle Exadata overcomes the limitations of conventional storage by utilizing a massively parallel architecture to dramatically increase data bandwidth between database and storage servers. In this webcast, we’ll examine these limitations and demonstrate how Oracle Exadata delivers extremely fast and completely scalable, enterprise-ready systems.

The signup sheet is here:

January 28, 2009 Exadata Webcast

My old friend Matt Zito of  GridApp has made a blog entry entitled Where is Exadata. The post takes issue with some of the latest rants uttered by Chuck Hollis of EMC. It seems Chuck thinks Oracle Exadata Storage Server is dead or dying based on his best guess of the field adoption rate over the approximate 2,880 hours Exadata has been in production.

I grew tired of Chuck’s musings on the topic weeks ago so I didn’t want to call it out on my own. Matt makes a good point about the fact that Exadata requires Oracle Database 11g and most sites take the sensibly cautious approach toward adopting software that is newer then what they currently have. I’m not saying that is any sort of mitigation for Chuck’s beliefs. I am saying that people like Matt are awake at the wheel-as it were.

Matt does point out correctly that nowhere in Exadata marketing literature is EMC called out by name as competition. To the contrary Oracle and EMC share a tremendous install base. That doesn’t even need to be said. What Matt further points out is how seemingly paranoid Chuck is about Exadata. I too find it odd, but not sufficiently odd to make a blog entry specifically about the point. I, therefore, have not done so.

Just a quick entry to point to Chen Shapira’s blog. I recommend it. Oh, that means I need to take a moment to update my blog roll!

You Need to See What These Folks Have to Say

I must put out a plug for Oracle’s Maximum Availability Architecture (MAA) team and the fruits of their labor now that I have personally worked with them on projects for over a year…I’m sure it’s no credit to them that I should say so,  but honestly, this team is really, really sharp!

Not only is this paper covering migration to Exadata Storage Server helpful for the actual act of deploying Exadata into an existing Oracle DW/BI environment, but it also goes a long way to suggest how much simpler it surely must be than dumping out an Oracle database and loading it.

Go get some of those papers!

I just took a look at the RMOUG 2009 Training Days Schedule to find out what day/time I’m speaking about Exadata performance and architecture. I have to say that RMOUG is always one of my favorite conferences and I’m looking forward to this year. I’ll have 90 minutes ending at noon on Wednesday and if all goes well I won’t have ruined attendees’ appetites just in time for lunch!

I also noticed an unfortunate schedule conflict. During the same time slot friends and fellow Oaktable Network memebers Tim Gorman and Tanel Poder are also speaking. Choices, choices…

Details

Title: Oracle Exadata Storage Server. Introducing Extreme Performance for Data Warehousing.

Abstract: Kevin Closson will present an overview of the Oracle Exadata Storage Server and HP Oracle Database Machine architecture and internals including performance analysis of typical workloads the Exadata Storage Server is solely capable of accelerating. The presentation will include comparisons to the typical storage architectures supporting Oracle Database workloads today. Attendees will leave with a good understanding of why, and how, Exadata is what it is. Kevin will leave ample time for a fruitful question and answer session.

Oracle Ace or Oracle Dunce?

For quite some time the “about” section of my blog stated that I am an Oracle Ace. Well, I was getting pelted with email from people that got the scoop on the fact that I am not an Oracle Ace. I was an Oracle Ace. Moreover, I was actually an Oracle Ace who wasn’t self-nominated! But, no matter, I am no longer an Oracle Ace and I have updated my blog accordingly.

Why?

Oracle employees are not eligible for participation in the Oracle Ace program. So the Oracle Ace-dom that I had prior to joining Oracle was revoked. I still have the vest though!

Yes, there is unstructured data and if you have an awful lot of it, the HP StorageWorks 9100 Extreme Data Storage System looks like a really great place to put it. I’m biased though because the software that drives the StorageWorks 9100 is PolyServe-my former company. I’m glad to see HP doing good things with PolyServe since the acquisition in 2007. Too many large corporate mergers end up in a mess. I’m glad to see that isn’t happening to my old friends and former PolyServe colleagues!

This offering is geared more towards density and cost than performance from what I can see. Nonetheless, having over 3 GB/s NFS bandwidth will come in handy given the capacities this offering supports.

Cool technology!

In my blog entry entitled Exadata Storage Server: 485x Faster than…Exadata Storage Server. Part I., I took issue with the ridiculous multiple orders of magnitude performance improvement claims routinely made by the DW Appliance vendors. These claims are usually touted as comparisons to “Oracle” (without any substantive accounting for what sort of Oracle configuration they are comparing to) and never seem to include any accounting for where the performance improvement comes from. After learning a bit about marketing from a breakfast cereal commercial, I decided to share with my readers how easy it is to do what these guys generally do-compare apples to bicycles. To make it more interesting I decided to show a 485-fold performance increase of Oracle Exadata versus Oracle Exadata. A long comment thread ensued and ultimately ended with a reader posting the following:

Block density perhaps?

You didn’t mention that the number of records per block
was a constant. So it would be possible that in the first scenario you created a table with a low amount of records per block, resulting in a large segment, needing a lot of io’s. (you could have used 1 row/block for example)

While in the second scenario you could have used a high number of blocks per record, resulting in a smaller segment, and thus needing a lower amount of io’s to fulfill the query.

BINGO.

Here’s the deal. I chose my words carefully and took a huge dose of Semantic-a-sol(tm). I set the stage as a follows:

• I said, “There are no … partitioning or any sort of data elimination.” True, there were no forms of data elimination. I didn’t say anything about eliminating unused space.
• I said the data in the table was the same; I never said it was the same table.
• I said there was the same storage bandwidth and the same number of CPUs and that was true.

The 485x was a product of querying a table with PCTFREE 0 versus PCTFREE 99. When I queried the vacuous blocks I also did so with a normal scan instead of a Smart Scan. So it is true that storage bandwidth remained constant but I created an artificial bottleneck upwind by forcing the single database host (used in both cases) to ingest the full 1.6 TB which is how much round-brown spinning stuff needed to store the vacuous blocks (PCTFREE 99). That took 970 seconds.

With ~107 million rows, and a query that cited only the PURCHASE_AMT column, the amount of data actually needed by the SQL layer is a measly 86 MB. So, when I “magically” switched the card_trans synonym to point to the PCTFREE 0 table (which is only 8.4 GB) and scanned it with the full power of 14 Exadata Storage Servers, the data was off disk and the PURCHASE_AMT column plucked from the middle of each row and DMAed into the address space of the Parallel Query Processes on the database host in 1.96 seconds….485x speed up.

So, does anyone else hate it when these DW Appliance guys go around spewing ridiculous multiple orders of magnitude performance increases over who-knows-what without any accounting? It truly is an insult on your intelligence.

There is no reason to be mystified. If DW Appliance vendor XYZ is spouting off about a query processing speed-up of, say, X, just plug the values into the following magic decoder ring. Quote me on this, performance increase X is the product of:

1. Executing on a platform with X-fold storage bandwidth, or
2. Executing on a platform with X-fold processor bandwidth, or
3. The query being measured manipulated 1/X^th the amount of data, or
4. Some combination of items 1 through 3

Any reasonable vendor will gladly itemize for you where they get their magical performance gains. Just ask them, you might learn more about them than you thought.

Part II in these series can be found here.

I recently read an article by Curt Monash entitled Interpreting the results of data warehouse proofs-of-concept (POCs).  Curt’s post touched on a topic that continually mystifies me. I’m not sure when the phenomenon started, but I’ve witnessed a growing trend towards complete lack of scrutiny when it comes to the performance claims made by most vendors in the data warehousing space. For example, Netezza makes a blanket claim that their appliance is 100-fold faster than Oracle. Full stop. Er, not full stop… Netezza doesn’t stop there. They claim:

While Netezza makes claims of 100x performance gains, it is not uncommon to see performance differences as large as 200x to even 400x or more when compared to existing Oracle systems.

100x Speed-up: Child’s Play

But, honestly, 100x is child’s play. Forget for a moment that there is no itemization of where that speedup would come from in Netezza’s high-level messaging. Such information would be technical marketing and I wouldn’t expect Netezza to disclose any sort of justification for where 100x speedup comes from. Lowered expectations. Shucks, these DW arms-race marketing claims remind me of that famous Saturday Night Live skit that seems to have served as the play book for these marketing guys-in more ways than one!

Ok, chuckles aside, Curt’s post on the topic included a link to a spreadsheet of recent Proof of Concept results where “the incumbent” was trounced to the tune of 335x in reporting tasks. Like I said, 100x is child’s play.

Intellectual Curiosity

Nobody should look at a claim such as 335x without wondering where in the world such a speedup comes from and shame on any vendor that isn’t willing to itemize the benefit. After all, without some knowledge of what produces such astounding speedup, how is the dutiful DW practitioner to expect the speedup to remain intact over time or, moreover, how to replicate the “magic” elsewhere. I’m more than willing to itemize to anyone any claim of Oracle Exadata Storage Server speed up on any query. Exadata is not “magic” so accounting for its benefit is very easy to do. But, back to the 335x for a moment. This is actually quite simple. To get 335x speedup one of the following is true:

1. The query was executed on a platform with 335x storage bandwidth
2. The query was executed on a platform with 335x processor bandwidth
3. The query manipulated 1/335^th the amount of data
4. Some combination of items 1 through 3

Number 3 in the list is achieved through things like partition elimination, indexing, materialized views, more efficient joins, and so forth. This is what Oracle refers to as the “Brainy Approach” to improved data warehouse query performance. Of course Oracle has, and retains all these “Brainy” optimization approaches, and more, when Exadata is in play. Exadata is a solution offering both “Brainy” and, most importantly, “Brawny” technology.

Let’s think this 335x thing through for a moment.  Imagine that the 335x was a Netezza 10100 and the 335x was an improvement over a traditional Oracle incumbent (no Exadata). One of Netezza’s main value propositions is that they are able to utilize full bandwidth of all the disks in the system in parallel-just like Exadata. That’s the “Brawny” approach. As I point out in my post about “arcane” disk technology, this value proposition is the least we deserve, but because of typical storage provisioning most Oracle deployments don’t benefit from the aggregate bandwidth their drives could actually offer. So kudos to Netezza for that.  What if this was Netezza and the 335x was due to the NPS 10100 “Brawny”  disk bandwidth capability? Well, that chalks the win to item 1 in the list and therefore the incumbent system was configured with 1/335^th the amount of disk bandwidth of the NPS system. If I grant the NPS system 70 MB/s per disk drive I get roughly 7.5 GB/s (108 * 70MB). Does that mean the incumbent was ingesting only 22 MB/s (7.5 GB/335)? Would anyone care about that result? Would you be proud if you got more performance from 108 SATA drives than a single USB 2.0 drive? I shouldn’t think the 335x came solely from list item 1.

The NPS 10100 has 108 processors pounding on the data as it comes off the drives. Can we get 335x over our imaginary incumbent from sheer processing power? Sure, so long as the incumbent was running Oracle on a processor with 1/3^rd the bandwidth of a single PowerPC processor (the embedded CPU on a Netezza SPU). Would anyone be excited to beat 1/3^rd a CPU with 108 CPUs?

No, folks, the 335x was certainly the product of item 4 on the list-with a very heavy slant towards item 3-regardless of which appliance vendor it actually was.

A 335 Fold Improvement is Child’s Play? I want 485 Fold!

Humor me as I walk through a little exercise to elaborate more on this topic. In the following session I’ll demonstrate a query accessing precisely the same amount of data using the same SQL, in the same Oracle session, attached to the same Oracle database. You’ll see that I execute a host command to prove that within the scope of 15 seconds I am able to demonstrate a 485x speedup. You can choose to believe me or not, but the facts are as follows:

• The amount of data in the table is the same in each case.
• The data in every column of every row is the same.
• The order of rows in the table is the same.
• There is no compression involved at any point.
• The table datatypes are the same.
• The query plan is the same.
• The Oracle Parallel Query Degree of Parallelism remains constant. That means equal CPUs attacking the data.
• There are no indexes, materialized views, partitioning or any sort of data elimination.
• The Oracle Results Cache feature was not used.
• The data in each case resided on the same disks.

And, oh, before I forget to say so, this is Exadata. So, can Oracle market Exadata as 485x faster than Exadata without the use of any data elimination techniques? See for yourself and fill out a comment with your explanation for what I have shown here.

First, a listing of the “demo” script:

SQL> !cat demo.sql

set echo off
set timing off
col sum_sales format 999,999,999,999,999,999
host date

desc card_trans

set echo on
select count(*) from card_trans;
set timing on

select sum(purchase_amt) sum_sales from card_trans;
host date

In the following screen capture I’ll show that the query took 970 seconds to complete. I used the SUM aggregate against the 100+ million purchase_amt column values as a means to show I’m querying the same content in both cases.

SQL> @demo
Wed Dec 10 08:59:48 PST 2008

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CARD_NO                                   NOT NULL VARCHAR2(20)
CARD_TYPE                                          CHAR(20)
MCC                                       NOT NULL NUMBER(6)
PURCHASE_AMT                              NOT NULL NUMBER(6)
PURCHASE_DT                               NOT NULL DATE
MERCHANT_CODE                             NOT NULL NUMBER(7)
MERCHANT_CITY                             NOT NULL VARCHAR2(40)
MERCHANT_STATE                            NOT NULL CHAR(3)
MERCHANT_ZIP                              NOT NULL NUMBER(6)

SQL> select count(*) from card_trans;

COUNT(*)
----------
107389152

SQL>
SQL> set timing on
SQL>
SQL> select sum(purchase_amt) sum_sales from card_trans;

SUM_SALES
------------------------
6,443,502,770

Elapsed: 00:16:10.15
SQL>
SQL> host date
Wed Dec 10 09:32:08 PST 2008

The first pass of the script ended in the same session at 9:32:08 and 11 seconds later I executed the script again. The session capture shows that there was a 485x speed up (970 seconds down to 2 seconds). Like I said, “100x is childs play.” Well, at least it is when there is no accounting offered for the improvement. Pshaw, it seems I learned a lot from that “training” video I reference above.

SQL>  @demo
SQL>
SQL> set echo off
Wed Dec 10 09:32:19 PST 2008

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CARD_NO                                   NOT NULL VARCHAR2(20)
CARD_TYPE                                          CHAR(20)
MCC                                       NOT NULL NUMBER(6)
PURCHASE_AMT                              NOT NULL NUMBER(6)
PURCHASE_DT                               NOT NULL DATE
MERCHANT_CODE                             NOT NULL NUMBER(7)
MERCHANT_CITY                             NOT NULL VARCHAR2(40)
MERCHANT_STATE                            NOT NULL CHAR(3)
MERCHANT_ZIP                              NOT NULL NUMBER(6)

SQL> select count(*) from card_trans;

COUNT(*)
----------
107389152

SQL>
SQL> set timing on
SQL>
SQL> select sum(purchase_amt) sum_sales from card_trans;

SUM_SALES
------------------------
6,443,502,770

Elapsed: 00:00:01.96
SQL>
SQL> host date
Wed Dec 10 09:32:23 PST 2008

SQL> select count(*) from user_indexes ;

COUNT(*)
----------
0

Elapsed: 00:00:00.11

Part II in this series: click here.