Kevin Closson's Blog: Platforms, Databases and Storage

Back in October 2007, I had a series of very interesting and enjoyable technology briefings/discussions with Bryan Cantrill, the inventor of Sun’s Dtrace dynamic tracing toolkit. Bryan had been following my blog and realized I moved on to the Oracle Server Technologies group after my tenure as Chief Software Architect of Oracle Database Platform Solutions at PolyServe. Who knows, he may have been speaking to Glenn Fawcett, my old friend and former Database Engineer at Sequent, as well. Bryan was investigating whether there could be some synergy between Oracle and the project he was currently working on codenamed FISH (Fully Integrated Software and Hardware). I lost track of how that project was moving along as I became too engrossed in my work on Oracle Exadata Storage Server. Nonetheless, it is nice to hear of this product coming to market as I recall being very impressed by the early technical details of the product.

From the FISHworks project comes the Sun Storage 7000 Unified Storage System.

Bryan Cantrill goes into some of the history of the Sun Storage 7000 in this blog entry.

It will be interesting to watch the happenings between Sun and NetApp over this

…and, no, I don’t think I’m special because Bryan and I had a chat about FISH back in Oct 2007 as Ashlee Vance had scooped the news 7 months prior.

I’ve had several people ask me questions recently about topics I’ve covered in at least a few of my Exadata Storage Server related posts. When I asked them if they’d seen a particular post, they’ve generally responded, “No, I didn’t know that post existed”, or words to that effect. So, this may seem odd, but in addition to updating my Exadata Posts page, I’ve pasted handy URLs to my Exadata related posts below. I wish there was time to add to the set of posts. I do have plenty of material queued up, but I’m scrambling for time. In the meantime, perhaps a few readers will appreciate the content pointed to by these URLs:

Oracle Exadata Storage Server. Part I.

Oracle Exadata Storage Server. Part II.

Oracle Exadata Server Related Web News Media and Blog Errata. Part I.

HP Oracle Database Machine. A Thing of Beauty Capable of “Real Throughput!”

I know Nothing About Data Warehouse Appliances and Now, So Won’t You – Part V. Why GreenPlum is Better Than Oracle Exadata Storage Server.

Podcast: Pythian Group Oracle Exadata Storage Server Q&A with Kevin Closson.

Pessimistic Feelings About New Technology. Oracle Exadata Storage Server – A JBOD That Can Swamp A Single Server.

Oracle Exadata Storage Server: Beaten by FLASH SSD and Worthless for OLTP.

Oracle Exadata Storage Server. No Magic in an Imperfect World. Excellent Tools and Really Fast I/O Though.

Oracle Exadata Storage Server: A Black Box with No Statistics.

Sing along with me now…

Happy Anniversary to my blog. It’s been two years as of today. And as Tom Kyte foretold, there has been a lot about “disk” on this blog.

This blog has had nearly 1 million page views to date and while I’d like to say it has been all fun and games it has been a lot of work.

My favorite topics include, of course, my Exadata Related Posts, but the Oracle on Opteron, NUMA (etc) posts have had tremendous readership as have the “famous” Oracle Over NFS (most notably the “Manly Man” series) posts.

How about a poll?

In my recent blog entry entitled “Oracle Exadata Storage Server. No Magic in an Imperfect World. Excellent Tools and Really Fast I/O Though“, I concluded with a reference to some anti-Exadata comments made by EMC’s Chuck Hollis in his blog entry entitled “Oracle Does Hardware.” I directed my readers to that blog post by writing the following:

In spite of how many times EMC’s Chuck Hollis may claim that there is “nothing new” or “no magic” when referring to Oracle Exadata Storage Server, I think it is painfully obvious that there is indeed “something new” here. Is it magic? No, and we don’t claim that it’s magic.

Six days after I posted that blog entry, Chuck submitted a lengthy comment on the post. Instead of responding to Chuck’s comments in the comment thread I’ve decided to do so here.

Readers please don’t confuse this as some sort of Kevin versus Chuck thread because it isn’t. What you’ll see in this post is an analysis of the words of someone representing one of the (if not the premier) conventional storage providers (EMC). My motives are to provide useful information in this analysis.

If you read Chuck’s assessment of Oracle Exadata Storage Server, you’ll see a positioning piece with an overtly anti-Exadata slant. Chuck’s words in that post are aimed at conveying facts. My first handling of that anti-Exadata piece was very light. I aimed to capitalize foremost on the repetitious use of the words “nothing new” and “magic.” Chuck likely saw my post where I called this out. Chuck answered my calling-out in the comment thread of this post. Chock wrote:

Sorry, Kevin, didn’t mean to come across as too pessimistic in my blog.

Asserting Beliefs
I need to point out that one cannot be pessimistic about facts. The word pessimistic only applies to beliefs and emotions. Chuck’s piece wasn’t pessimistic–it was flawed based on technical grounds. Chuck continued with:

Leaving hardware issues aside, how much of the software functionality shown here is available on generic servers, operating systems and storage that Oracle supports today? I was under the impression that most of this great stuff was native to Oracle products, and not a function of specific tin …

Last Chance for a First Impression

Chuck’s “pessimistic” post came out the day after the Oracle Exadata Storage Server launch so harboring such questions in his mind at that time would have been understandable. However, Chuck visited my blog some 22 days later and continued to ask questions that clearly demonstrate a lack of understanding of Oracle Exadata Storage Server. Chuck may have been “under the impression” that the underpinnings of Exadata are Oracle-generic (“native to Oracle products”), but he is wrong. Oracle Exadata Storage Server software is not a scalpel-job on the Oracle Database server. It is a totally new storage server software package.

To answer Chuck’s question about the software, none of the software functionality (Exadata) is available on generic servers, operations systems or storage. Chuck continued with:

If the Exadata product has unique and/or specialized Oracle logic, well, that’s a different case.

Yes, it is unique and specialized and a different case. Even light reading of the available material (e.g., the Exadata paper and, shucks, maybe a few of my blog posts) would have made that glaringly obvious. Chuck continued with:

Speaking strictly as a storage guy, here’s what I know.

– using commodity servers and storage arrays, we can usually feed in more data than a server can process, specifically true in an Oracle DW environment.

Chuck, swamping a commodity server is not the goal. Of course it’s easy to produce more raw, streaming data from even a midrange storage array than can be ingested by a single commodity server. Even the best commodity servers choke at less than 2GB/s data ingest rate when Oracle is performing data-rich functionality (e.g., joins, sorts, aggregation, etc). The design goal of Exadata was not to swamp commodity servers more efficiently. That would be a storage-only, bigger hose, speed-and-feed mentality-the “brute force only approach.”

You Don’t Always Get What You Want-Enter Exadata
The value proposition of Exadata is to scan disk without bottlenecks and return to the Database grid only the data the query wants, not blocks of disk. It’s a feature we call Smart Scan. Chuck needs to have his folks brief him on that. However, Exadata is more than capable of holding its own in the pure “brute force” camp.

Even Without Smart Scan, Exadata is Faster Than Conventional Storage
As a simple block server, Exadata is able to deliver 1GB/s per cell to the Database grid. If you don’t think that is “brute force”, consider a moderate Oracle Database Machine configuration consisting of a single rack serving 14 GB/s to the Database grid. If those numbers don’t speak loudly enough, just investigate what sort of conventional SAN array configuration it would take to deliver 14 GB/s to a Database grid. So, yes, Exadata is both “brute force” and intelligent and that is why I had to call out Chuck’s blog remarks about how Exadata is “nothing new.”

Chuck finished that paragraph with:

I’m having a hard time seeing the advantages of pairing a commodity Xeon-based server with JBOD and claiming a performance advantage for this part of the equation.

Oh my, where to start. Chuck, I understand why you would have difficulty seeing the advantage in what you just described, but what on earth does any of that have to do with Oracle Exadata Storage Server? First, where did you get “JBOD?” An Oracle Exadata Storage Server cell is not just a Xeon processor sitting in front of some disks (JBOD). The disks are down-wind of an intelligent HP P400 Smart Array with 512MB battery-backed write cache. And, what’s so terrible about fronting some disks with Xeon technology anyway? There are a few conventional storage arrays on the market that use Xeon in the array head.

It’s All About Balance
Fingering the fact that Intel Xeon processors execute storage intelligence software in the Oracle Exadata Storage Server doesn’t hold water–especially since the ratio is 2 sockets per 12 hard drives. Perhaps Chuck will tell us the maximum number of Xeon processors EMC supports in front of 960 drives in a fully loaded midrange EMC array (e.g., CX)?

Oracle has purpose-built a balanced system by coupling the power of 2 Xeon processors (Harpertown quad-core) in front of 12 drives.

Infiniband: The Exadata “Secret Sauce?”

Chuck continued with:

– you may be more knowledgeable than I, but we are under the impression that the IB compute node connection doesn’t bring much to the party. When we looked at many clustered Oracle DW implementations, there was plenty of bandwidth available between the compute nodes, using multiple 1Gb/sec links.

That’s why we don’t talk about it much. Infiniband is not why Exadata is so fast. Infiniband is one of the reasons why Exadata is not bottlenecked. First, I’ll point out that Infiniband is a unified fabric for both disk and inter-node communications with Exadata. I’ve been writing about storage up to this point and now the focus is shifted to Real Application Clusters (RAC) interconnect technology. I’ll be brief on this topic. I don’t doubt, nor do I care, that there are clustered Oracle DW systems currently deployed that are able to get by with multiple UDP Gigabit Ethernet networks configured as the RAC interconnect. That’s just fine with me. Does that somehow negate the value of Exadata because Oracle so foolhardy engineered a zero-copy RDMA interconnect for RAC while unifying interconnect and storage networking into a single fabric? I shouldn’t think so. UDP costs some (lots) of cycles compared to ZDP over Infiniband. Just because a network has headroom left over doesn’t mean resources are otherwise being utilized efficiently. Oracle didn’t aim to engineer bottlenecks into the Exadata architecture.

Chuck continued with:

And, I know this only matters to storage people, but there’s the minor matter of having two copies of everything, rather than the more efficient parity RAID approaches. Gets your attention when you’re talking 10-40TB usable, it does.

Yes, the initial release of Exadata requires 1:1 mirroring. Does that somehow insinuate that Exadata will never offer the more space-saving RAID approached Chuck is alluding to? Life is, after all, an unending series of choices.

Everyone Includes EMC
Chuck continued with:

Bottom line – what does the hardware bring to the party, rather than software? And if you can get the same benefits without dictating that customers buy a specific piece of tin, isn’t that a win for everyone?

Chuck, and my blog readers alike, should know by now what the hardware brings to the party. Oracle Exadata Storage Server hardware is-unlike conventional storage arrays-not configured with guaranteed throughput bottlenecks built in. That warrants a party. On the other hand, the software is the secret sauce. The choice of which “tin” gets to run the software is, of course, someone else’s decision. I will say, however, if you were to execute the software on systems less balanced than the current platform (HP Proliant DL180 G5), you would not realize the benefit. It’s all about balance.

Chuck finished with:

Finally, I’d be interested in your thoughts on how enterprise flash drives fit into all of this. Yes, they’re rather expensive now, but this won’t be the case before too long.

I’ve bored you all to death already. I’ll hit FLASH SSD in my next blog entry.

If you have Oracle Database deployed on Linux and have not yet settled upon collectl as your primary tool for system-level performance data collection, well, we need to have a long talk!

Christian Antognini is a fellow member of the Oaktable Network and while I’ve only (briefly) met him once face-to-face, I’ll still recommend his blog! No, honestly, all joking aside I must say that I appreciate Christian’s blogging.

I’ve never met Mike Ault, but some friends of mine, who are fellow OakTable Network members, say he’s a great guy and I believe them. Mike works at Texas Memory Systems and I know some of those guys as well (Hi Woody, Jamon). Pleasantries aside, I have to call out some of the content Mike posted on a recent blog entry about HP Oracle Database Machine and Exadata Storage Server. Just because I blog about someone else’s posted information doesn’t mean I’m “out to get them.” Mike’s post made it clear I need to address a few things. Mike’s post was not vicious anti-Technical Marketing by any means, but it was riddled with inaccuracies that deserve correction.

Errata

While these first two errata I will point out may seem moot to many readers, I think accuracy is important if one intends to contrast one technology offering against another. After all, you won’t find me posting blog entries about Texas Memory Systems SSD being based on core memory or green Jell-O.

The first error I need to point out is that Mike refers to Oracle Exadata Storage Server cells as “block” or “blocks” 8 times in his post.

The second error I need to point out is rooted in the following quote from Mike’s blog entry:

These new storage and database devices offer up to 168 terabytes of raw storage with 368 gigabytes of caching and 64 main CPUs

That is partially true. With the SATA option, the HP Oracle Database Machine does offer 168TB of gross disk capacity. The error is in the “368 gigabytes of cache” bit. The HP Oracle Database machine does indeed come with 8 Real Application Clusters hosts in the Database grid configured with 32GB RAM and 14 Exadata Storage Server cells with 8GB each. However, it is entirely erroneous to suggest that the entirety of physical memory across both the Database grid and Storage grid somehow work in unison as “cache.” It’s not that the gross 368GB (8×32 + 14×8) isn’t usable as cache. It’s more the fact that none of it is used as user-data cache–at least not cache that somehow helps out with DW/BI workloads. The notion that it makes sense to put 368GB of cache in front of, say, a 10TB table scan, and somehow boost DW/BI query performance, is the madness that Exadata aims to put to rest. Here’s a rule:

If you can’t cache the entirety of a dataset you are scanning, don’t cache at all.

– Kevin Closson

Cache, Gas or a Full Glass. Nobody Rides for Free.

No, we don’t use the 8x32GB physical memory in the Database grid as cache because cycling, say, the results of a 2TB table scan through 368GB aggregate cache would do nothing but impede performance. Caching costs, and if there are no cache hits there is no benefit. Anyone who claims to know Oracle would know that parallel query table scans do not pollute the shared cache of Oracle Database. A more imaginative, and correct, use for the 32GB RAM in each of the hosts in the Database grid would be for sorting, joins (hash, etc) and other such uses. Of course you don’t get the entire 32GB anyway as there is an OS and other overhead on the server. But what about the 8GB RAM on each Oracle Exadata Storage cell?

One of the main value propositions of Oracle Exadata Storage Server is the fact that lower-half query functionality has been offloaded to the cells (e.g., filtering, column projection, etc). Now, consider the fact that we can scan disks in the SAS-based Exadata Storage Server at the rate of 1GB/s. We attack the drives with 1MB physical reads and buffer the read results in a shared cache visible to all threads in the Oracle Exadata Storage Server software. To achieve 1GB/s with 1MB I/O requests requires 1000 physical I/Os per second. OK, now I’m sure all the fully-cached-conventional-array guys are going to point out that 1000 IOPS isn’t worth talking about, and I’d agree. Forget for the moment that 1GB/s is in fact very close to the limit of data transfer many mid-range storage arrays have to offer. No, I’m not trying to get you excited about the 1GB/s because if that isn’t enough you can add more. What I’m pointing out is the fact that the results of 1000 IOPS (each 1MB in size) must be buffered somewhere while the worker threads rip through the data blocks applying filtration and plucking out cited columns. That’s 125 1MB filtration and projection operations per second per processor core. There is a lot going on and we need ample buffering space to do the offload processing.

Mike then moved on to make the following statement:

The Oracle Database Machine was actually designed for large data warehouses but Larry assured us we could use it for OLTP applications as well. Performance improvements of 10X to 50X if you move your application to the Database Machine are promised.

I’m not going to write guarantees, but no matter, that statement only lead in to the following:

This dramatic improvement over existing data warehouse systems is provided through placing an Oracle provided parallel processing engine on each Exadata building block so instead of passing data blocks, results are returned. How the latency of the drives is being defeated wasn’t fully explained.

Exadata Storage Server Software == Oracle Parallel Query

Folks, the Storage Server software running in the Exadata Storage Server cell is indeed parallel software and threaded, however, it is not entirely correct to state that there is a “parallel processing engine” that returns “results” from Exadata cells. More correctly, we offload scans (a.k.a. Smart Scan) to Exadata cells. Smart Scan technology embodies I/O, filtration, column projection and rudimentary joins. Insinuating otherwise makes Exadata out to be more of a database engine than intelligent storage and there is more than a subtle difference between the two concepts. So, no, “results” aren’t returned, filtered rows and projected columns are. That is not a nit-pick.

DW/BI and I/O Latency

Mike finished that paragraph with the comment about how Oracle Exadata Storage Server “defeats” (or doesn’t) drive latency. I’ll simply point out that drive latency is not an issue with DW/BI workloads. The problem (addressed by Exadata) is the fact that attaching just a few modern hard drives to a conventional storage array leaves you with a throughput bottleneck. Exadata doesn’t do anything for drive latency because, shucks, the disks are still round, brown spinning thingies.  Exadata does, however, make a balanced offering that that doesn’t bottleneck the drives.

Mike continued with the following observation:

So in a full configuration you are on the tab for a 64 CPU Oracle and RAC license and 112 Oracle parallel query licenses

Yes, there are 64 processor cores in the Database grid component of the HP Oracle Database Machine, but Mike mentioning the 112 processor cores in the Exadata Storage Server grid is clearly indicative of the rampant misconception that Exadata Storage Server software is either some, most, or all of an Oracle Parallel Query instance. People who have not done their reading quickly jump to this conclusion and it is entirely false. So, mentioning the 112 Exadata Storage Server grid processors and “Oracle parallel query licenses” in the same breath is simple ignorance.

Mike continues with the following assertion:

Targeting the product to OLTP environments is just sloppy marketing as the system will not offer the latency needed in real OLTP transaction intensive shops.

While Larry Ellison and other important people have stated that Exadata fits in OLTP environments as well as DW/BI, I wouldn’t say it has been marketed that way, and certainly not sloppily. Until you folks see our specific OLTP numbers and value propositions I wouldn’t set out to craft any positioning pieces. Let me just say the following about OLTP.

OLTP Needs Huge Storage Cache, Right?

OLTP is I/O latency sensitive, but mostly for writes. Oracle offers a primary cache in the Oracle System Global Area disk buffer cache. Applications generaly don’t miss SGA blocks and immediately re-read them at a rate that requires sub-millisecond service times. Hot blocks don’t age out of the cache. Oracle SGA cache misses generally access wildly random locations, or result in scanning disk. So, for storage cache to offer read benefit it must cover a reasonable amount of the wildly, randomly accessed blocks. The SGA and intelligent storage arrays share a common characteristic: the same access patterns that blow out the SGA cache also blow out storage array cache. After all, architecturally speaking, the storage array cache serves as a second-level cache behind the SGA. If it is the same size as the SGA it is pretty worthless. If it is, say, 10 times the size of the SGA but only 1/50^th the size of the database it is also pretty useless-with the exception of those situations when people use storage array cache to make up for the fact that they are using, say, 1/10^th the number of drives they actually need. Under-provisioning spindles is not good but that is an entirely different topic.

I know there are SAN array caches in the terabyte range and Mike speaks of multi-terabyte FLASH SSD disk farms. I suppose these are options-for a very select few.

Most Oracle OLTP deployments will do just fine running against non-bottlenecked storage with a reasonable amount of write-cache. Putting aside the idea of an entirely FLASH SSD deployment for a moment, the argument about storage cache helping OLTP boils down to what percentage of the SGA cache misses can be satisfied in the storage array cache and what overall performance increase that yields.

The Eye of a Needle

Recently, I was looking at the specification sheet for a freshly released mid-range Fibre Channel SAN storage array that supports up to 960 disk drives plumbed through a two-headed controller. The specification sheet shows a maximum of 16GB cache per storage processor (up to two of them). I should think the cache is mirrored to accommodate storage processor failure-maybe it isn’t, I don’t know. If it is mirrored, let’s pretend for a moment that mirroring storage processor cache is free even with modify-intensive workloads (subliminal man says it isn’t). Given this example, I have to ask who thinks 16GB of storage array in front of hundreds of drives offers any performance increase? It doesn’t, so let’s put to rest the OLTP storage cache benefit argument.

But Mike Wasn’t Talking About Storage Array Cache

Right, Mike wasn’t talking about storage array cache benefit in an OLTP environment, but he was talking about nosebleed IOP rates from FLASH SSD. When referring to Exadata, Mike stated (quote):

What might be an alternative? Well, how about keeping your existing hardware, keep your existing licenses, and just purchase solid state disks to supplement your existing technology stack? For that same amount of money you will shortly be able to get the same usable capacity of Texas Memory Systems RamSan devices. By my estimates that will give you 600,000 IOPS, 9 GB/sec bandwidth (using fibre Fibre Channel , more withor Infiniband), 48 terabytes of non-volatile flash storage[S1] , 384 GB of DDR cache and a speed up of 10-50X depending on the query (based on tests against the TPCH data set using disks and the equivalent Ram-San SSD configuration).

OK, there is a lot to dissect in that paragraph. First there is the attractive sounding 600,000 IOPS with sub-millisecond response time. But wait, Mike suggests keeping your existing hardware. Folks, if you have existing hardware that is capable of driving OLTP I/O at the rate of 600,000 IOPS I want to shake your hand. Oracle OLTP doesn’t just issue I/O. It performs transactions that hammer the SGA cache and suffer some cache misses (logical to physical I/O ratio). The CPU cost wrapped around the physical I/O is not trivial. Indeed, the idea is to drive up CPU utilization and reduce physical I/O through schema design and proper SGA caching. Those of you who are current Oracle practitioners are invited to analyze your current production OLTP workload and assess the CPU utilization associated with your demonstrated physical I/O rate. If you have an OLTP workload that is doing more than, say, 5000 IOPS (physical) per processor core and you are not 100% processor-bound, tell us about it.

Yes, there are tricked out transactional benchmarks that shave off real-world features and code path and hammer out as much as 10,000 IOPS per processor core (on very powerful CPUs), but that is not your workload, or anyone else’s workload that reads this blog. So, if real OLTP saturates CPU at, say, 5000 IOPS I have to wonder what your “existing hardware” would look like if it were also able to take advantage of 600,000 IOPS. That would be a very formidable Database grid with something like 120 CPUs. Remember, Mike was talking about using existing hardware to take advantage of SSD instead of Exadata. If you have a 120 CPU Database grid, I suspect it is so critical that you wouldn’t be migrating it to anything. It is simply too critical to mess with. I should hope. Oh, it’s actually more like about 2000 IOPS per processor core in real life anyway, but that that doesn’t change the point much. And Exadata isn’t really about OLTP.

Let’s focus more intently on Mike’s supposition that an alternative to Exadata is “keeping your existing hardware” and feeding it 9GB/s from SSD. OK, first, that is 36% less I/O bandwidth than a single HP Oracle Database Machine can do, but let’s think about this for a moment. The Fibre Channel plumbing required for the Database grid to ingest 9GB/s is 23 active 4GFC FC HBAs at max theoretical throughput. That’s a lot of HBAs, and you need systems to plug them into. Remember, this is your “existing system.”

How much CPU does your “existing hardware” require to drive the 23 FC HBAs? Well, it takes a lot. Yes, I know you can use just a blip of CPU to mindlessly issue I/O in such a fashion as Orion or some pure I/O subsystem invigoration like dd if=/dev/zero of=/dev/sda bs=1024k, but we are talking about DW/BI and Oracle. Oracle actually does stuff with the data returned from an I/O call. With non-Exadata storage, the CPU cost associated with I/O (e.g., issuing, reaping), filtration, projection, joining, sorting, aggregation, etc is paid by the Database grid. So your “existing system” has to be powerful enough to do the entirety of SQL processing at a rate of 9GB/s. Let’s pretend for a moment that there existed on the market a 4-socket server that could accommodate 23 FC HBAs. Does anyone think for a moment that the 4 processors (perhaps 8 or 16 cores) can actually do anything reasonable with 9GB/s I/O bandwidth? A general rule is to associate approximately 4 processor cores with each 4GFC HBA (purposefully ignoring trick benchmark configurations). I think it looks like “your existing system” has about 96 processor cores.

A Chump Challenge

I’d put a HP Oracle Database Machine (64-core/14 cell) up against a 96-core/9GBPS FLASH SSD system any day of the week. I’d even give them 128 Database tier CPUs and not worry.

People keep forgetting that scans are offloaded to Exadata with the HP Oracle Database Machine. People shouldn’t craft their position pieces against Exadata by starting at the storage-regardless of the storage speeds and feeds.

It will always take more Database grid horsepower, in a non-Exadata environment, to drive the same scan rates offered by the HP Oracle Database Machine.

FLASH SSD

Did I mention that there is nothing (technically) preventing us from configuring Exadata Storage Server with 3.5″ FLASH SSD drives? Better late than never, but it isn’t really worth mentioning at this time.

BLOG CORRECTION: I misread the announcement. Oracle’s own Robert Stackowiac is presenting Exadata at the PAOUG meeting. I have all the confidence in the world that he’ll do a fantastic job.

According to this post on blogs.oracle.com, the excitement level over Exadata Storage Server is increasing. It seems the Philadelphia Area Oracle Users Group is featuring Mark Rittman to discuss the future of Oracle BI/DW architecture. There will also be a presentation on Oracle Database Machine and Exadata Storage Server. I’m glad to hear interest is building in the product, but I have to admit I’m a bit confused because I think Exadata is the future (present) of Oracle BI/DW. I also don’t know how anyone outside Oracle Corporation could so quickly amass the required expertise to present Exadata. I suppose I’m being petty. Nonetheless, I’m excited Exadata is deemed important enough to make the keynote address at this gathering. The abstract for the keynote reads:

Data volumes are exploding generating larger and larger databases, and getting to the right data instantly requires a new way to manage today¿s systems. New and revolutionary solutions and methodologies are converging to address this need, and taking a fresh look at the challenge reveals new insights. This keynote will examine the intersection of new database, data warehouse, and storage solutions that deliver on these requirements.

Hmmm…”new database, data warehouse, and storage solutions…”

I hope they get it right.

I’ve met people before who’d rather drink muriatic acid than admit a mistake. I don’t cotton to such folks none too purdy good. This is a post about perfection and imperfection.

Cost Based Optimizer

There is no such thing as a perfect cost-based optimizer (CBO) and the expectations placed on cost-based optimizers run rampant. If the Oracle Database 11g CBO was perfect there would be no need for an excellent performance tuning tool like SQL Tuning Advisor and Reactive Tuning. In those cases where CBO does not generate a perfect plan, Oracle Database equips administrators with a tuning toolset that generally gets it right.

What’s This Have to do with Oracle Exadata Storage Server?

In my blog entry entitled Oracle Exadata Storage Server: A Black Box with No Statistics, I discussed the Affinity Card Program Test Database (ACPTD). One of the reasons I use this schema and query set is that, unlike a benchmark kit, it is not perfect. I expect Exadata to function as advertised in an imperfect world.

Imagine an organization that inherited some imperfect data with an imperfect schema. Nah, that would never happen. What about queries that aren’t perfect? Ever seen any of those? What about imperfect query plans?

One of the business questions, in English, that I test with the Affinity Card Program Test Database reads as follows:

List our customers with Club Cards who purchased more than $1,000.00 worth of goods/services in the west retail region. Omit non-affinity card purchases and all restaurant, travel and gasoline purchases.

The following text box has the SQL for this business question. Now, before you get all excited about the usage of the mcc.misc column (line 5 in the text box), please remember what I just explained in the previous paragraph. Sometimes I like imperfection when I’m analyzing performance. But what does this have to do with Exadata Storage Server? Well, I could certainly use a codes table and anti-join the purchases that are not restaurant, travel or gasoline. No question there. However, for this particular query I wanted imperfection, not a lesson in schema design. So, the purpose behind the not like ‘restaurant%travel%gasoline%’ predicate is to test a more heavily weighted filtration effort in the Exadata Storage Server. It’s synthetic, I know, but at least it is imperfect-which I like.

with act as
(
select   act.card_no, act.purchase_amt from zipcodes z ,all_card_trans act ,mcc m
where    (act.card_no like '4777%' or act.card_no like '3333%') and
act.mcc = m.mcc and m.misc not like 'restaurant%travel%gasoline%' and
act.merchant_zip = z.zip and z.state in ('CA', 'OR', 'WA')
)
select
cf.custid,
sum (act.purchase_amt) sales from  act,cust_fact cf
where act.card_no = cf.aff_cc_num and cf.club_card_num not like '0000%'
group by cf.custid
having   sum (act.purchase_amt) > 1000;

Notice in the following text box how the plan shows Exadata Storage Server is filtering out all_card_trans, cust_fact, zipcodes and mcc rows. The filtering effort on mcc is not colossal, but certainly heftier than the filtration on zip.state, since there are synthetic data-cleanliness values in mcc.misc such as ‘restaurant%travel%gas oline’ and so forth.

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(SUM("ACT"."PURCHASE_AMT")>1000)
7 - access("ACT"."CARD_NO"="CF"."AFF_CC_NUM")
10 - access("ACT"."MERCHANT_ZIP"="Z"."ZIP")
13 - access("ACT"."MCC"="M"."MCC")
18 - storage("ACT"."CARD_NO" LIKE '4777%' OR "ACT"."CARD_NO" LIKE '3333%')
filter("ACT"."CARD_NO" LIKE '4777%' OR "ACT"."CARD_NO" LIKE '3333%')
23 - storage("M"."MISC" NOT LIKE 'restaurant%travel%gasoline%' AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."MCC"))
filter("M"."MISC" NOT LIKE 'restaurant%travel%gasoline%' AND SYS_OP_BLOOM_FILTER(:BF0000,"M"."MCC"))
25 - storage("Z"."STATE"='CA' OR "Z"."STATE"='OR' OR "Z"."STATE"='WA')
filter("Z"."STATE"='CA' OR "Z"."STATE"='OR' OR "Z"."STATE"='WA')
27 - storage("CF"."CLUB_CARD_NUM" NOT LIKE '0000%')
filter("CF"."CLUB_CARD_NUM" NOT LIKE '0000%')

What Does This Have to do with Cost Based Optimizer and SQL Tuning Advisor?

The fact that Exadata does not leave any of the core Oracle Database 11g value propositions off the table is a point that has been made in the press and blogging community over and over again. I’m sure I’ll get spanked for saying this, but occasional bad plans are a fact of life with cost-based optimizers-and that includes Oracle. Those of us willing to accept and live with this reality are very appreciative of the goodness offered by the SQL Tuning Advisor. There are several canned queries in the ACPDT. The other day I loaded the scale that leaves me with an ALL_CARD_TRANS table of roughly 3TB. Of the several queries, I was disappointed with the performance of only one-the query listed above with the imperfect method for weeding out merchant codes. Since this is Exadata you can rest assured that the I/O was not a problem, but the query took 721 seconds to complete! I knew that was in error because there is partition elimination on this query along all_card_trans.card_no and 721 seconds of Oracle Exadata Storage Server Smart Scan throughput on this 6-cell configuration would rack up over 4TB of total I/O (6GB/s*721). Oracle Database does not mistakingly read an entire table when partition pruning is possible, so what was the issue?

The following text box shows the plan. Notice the estimate in step 18. The plan, it seems, didn’t have the best information about the relationship between ALL_CARD_TRANS and CUST_FACT.

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

| Id  | Operation                                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                           |                |    15 |  1395 |   207K  (2)| 00:48:32 |        |      |            |

|   1 |  PX COORDINATOR                            |                |       |       |            |          |        |      |            |

|   2 |   PX SEND QC (RANDOM)                      | :TQ10005       |    15 |  1395 |   207K  (2)| 00:48:32 |  Q1,05 | P->S | QC (RAND)  |

|*  3 |    FILTER                                  |                |       |       |            |          |  Q1,05 | PCWC |            |

|   4 |     HASH GROUP BY                          |                |    15 |  1395 |   207K  (2)| 00:48:32 |  Q1,05 | PCWP |            |

|   5 |      PX RECEIVE                            |                |    15 |  1395 |   207K  (2)| 00:48:32 |  Q1,05 | PCWP |            |

|   6 |       PX SEND HASH                         | :TQ10004       |    15 |  1395 |   207K  (2)| 00:48:32 |  Q1,04 | P->P | HASH       |

|*  7 |        HASH JOIN                           |                |    15 |  1395 |   207K  (2)| 00:48:32 |  Q1,04 | PCWP |            |

|   8 |         PX RECEIVE                         |                |     2 |   104 |   206K  (2)| 00:48:11 |  Q1,04 | PCWP |            |

|   9 |          PX SEND BROADCAST                 | :TQ10003       |     2 |   104 |   206K  (2)| 00:48:11 |  Q1,03 | P->P | BROADCAST  |

|* 10 |           HASH JOIN                        |                |     2 |   104 |   206K  (2)| 00:48:11 |  Q1,03 | PCWP |            |

|  11 |            PX RECEIVE                      |                |     2 |    88 |   206K  (2)| 00:48:11 |  Q1,03 | PCWP |            |

|  12 |             PX SEND BROADCAST              | :TQ10002       |     2 |    88 |   206K  (2)| 00:48:11 |  Q1,02 | P->P | BROADCAST  |

|* 13 |              HASH JOIN BUFFERED            |                |     2 |    88 |   206K  (2)| 00:48:11 |  Q1,02 | PCWP |            |

|  14 |               JOIN FILTER CREATE           | :BF0000        |     2 |    58 |   206K  (2)| 00:48:09 |  Q1,02 | PCWP |            |

|  15 |                PX RECEIVE                  |                |     2 |    58 |   206K  (2)| 00:48:09 |  Q1,02 | PCWP |            |

|  16 |                 PX SEND HASH               | :TQ10000       |     2 |    58 |   206K  (2)| 00:48:09 |  Q1,00 | P->P | HASH       |

|  17 |                  PX BLOCK ITERATOR         |                |     2 |    58 |   206K  (2)| 00:48:09 |  Q1,00 | PCWC |            |

|* 18 |                   TABLE ACCESS STORAGE FULL| ALL_CARD_TRANS |     2 |    58 |   206K  (2)| 00:48:09 |  Q1,00 | PCWP |            |

|  19 |               PX RECEIVE                   |                |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,02 | PCWP |            |

|  20 |                PX SEND HASH                | :TQ10001       |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,01 | P->P | HASH       |

|  21 |                 JOIN FILTER USE            | :BF0000        |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,01 | PCWP |            |

|  22 |                  PX BLOCK ITERATOR         |                |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,01 | PCWC |            |

|* 23 |                   TABLE ACCESS STORAGE FULL| MCC            |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,01 | PCWP |            |

|  24 |            PX BLOCK ITERATOR               |                |  3953 | 31624 |     3   (0)| 00:00:01 |  Q1,03 | PCWC |            |

|* 25 |             TABLE ACCESS STORAGE FULL      | ZIPCODES       |  3953 | 31624 |     3   (0)| 00:00:01 |  Q1,03 | PCWP |            |

|  26 |         PX BLOCK ITERATOR                  |                |  6278K|   245M|  1527   (1)| 00:00:22 |  Q1,04 | PCWC |            |

|* 27 |          TABLE ACCESS STORAGE FULL         | CUST_FACT      |  6278K|   245M|  1527   (1)| 00:00:22 |  Q1,04 | PCWP |            |

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

So, I executed the query under the SQL Tuning Advisor API as follows.

var tname varchar2(500);

exec :tname := dbms_sqltune.create_tuning_task(sql_text=>' with act as ( select act.card_no, act.purchase_amt from  zipcodes z ,all_card_trans act ,mcc m where

(act.card_no like ''4777%'' or act.card_no like ''3333%'') and act.mcc = m.mcc and m.misc not like ''restaurant%travel%gasoline%'' and act.merchant_zip = z.zip a

nd z.state in (''CA'', ''OR'', ''WA'')) select cf.custid, sum (act.purchase_amt) sales from act,cust_fact cf where act.card_no = cf.aff_cc_num and cf.club_card_n

um not like ''0000%'' group by cf.custid having   sum (act.purchase_amt) > 1000');

exec dbms_sqltune.execute_tuning_task(:tname);

select dbms_sqltune.report_tuning_task(:tname) AS recommendations from dual;

SQL Tuning Advisor did recommend a different plan and, as you can see in the following text box, the primary difference is in the cardinality estimate on ALL_CARD_TRANS. With this learned information the join order changed and the result was a 6.5X speedup as the query completed in 110 seconds.

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

| Id  | Operation                               | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                        |                |     1 |    93 |   208K  (2)| 00:48:33 |        |      |            |

|   1 |  PX COORDINATOR                         |                |       |       |            |          |        |      |            |

|   2 |   PX SEND QC (RANDOM)                   | :TQ10005       |     1 |    93 |   208K  (2)| 00:48:33 |  Q1,05 | P->S | QC (RAND)  |

|*  3 |    FILTER                               |                |       |       |            |          |  Q1,05 | PCWC |            |

|   4 |     HASH GROUP BY                       |                |     1 |    93 |   208K  (2)| 00:48:33 |  Q1,05 | PCWP |            |

|   5 |      PX RECEIVE                         |                |    86M|  7640M|   207K  (2)| 00:48:32 |  Q1,05 | PCWP |            |

|   6 |       PX SEND HASH                      | :TQ10004       |    86M|  7640M|   207K  (2)| 00:48:32 |  Q1,04 | P->P | HASH       |

|*  7 |        HASH JOIN BUFFERED               |                |    86M|  7640M|   207K  (2)| 00:48:32 |  Q1,04 | PCWP |            |

|   8 |         PX RECEIVE                      |                |  6278K|   245M|  1527   (1)| 00:00:22 |  Q1,04 | PCWP |            |

|   9 |          PX SEND HASH                   | :TQ10002       |  6278K|   245M|  1527   (1)| 00:00:22 |  Q1,02 | P->P | HASH       |

|  10 |           PX BLOCK ITERATOR             |                |  6278K|   245M|  1527   (1)| 00:00:22 |  Q1,02 | PCWC |            |

|* 11 |            TABLE ACCESS STORAGE FULL    | CUST_FACT      |  6278K|   245M|  1527   (1)| 00:00:22 |  Q1,02 | PCWP |            |

|  12 |         PX RECEIVE                      |                |  6802K|   337M|   206K  (2)| 00:48:11 |  Q1,04 | PCWP |            |

|  13 |          PX SEND HASH                   | :TQ10003       |  6802K|   337M|   206K  (2)| 00:48:11 |  Q1,03 | P->P | HASH       |

|* 14 |           HASH JOIN                     |                |  6802K|   337M|   206K  (2)| 00:48:11 |  Q1,03 | PCWP |            |

|  15 |            PX RECEIVE                   |                |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,03 | PCWP |            |

|  16 |             PX SEND BROADCAST           | :TQ10000       |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,00 | P->P | BROADCAST  |

|  17 |              PX BLOCK ITERATOR          |                |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,00 | PCWC |            |

|* 18 |               TABLE ACCESS STORAGE FULL | MCC            |   276 |  4140 |    83   (0)| 00:00:02 |  Q1,00 | PCWP |            |

|* 19 |            HASH JOIN                    |                |  7220K|   254M|   206K  (2)| 00:48:10 |  Q1,03 | PCWP |            |

|  20 |             PX RECEIVE                  |                |  3953 | 31624 |     3   (0)| 00:00:01 |  Q1,03 | PCWP |            |

|  21 |              PX SEND BROADCAST          | :TQ10001       |  3953 | 31624 |     3   (0)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |

|  22 |               PX BLOCK ITERATOR         |                |  3953 | 31624 |     3   (0)| 00:00:01 |  Q1,01 | PCWC |            |

|* 23 |                TABLE ACCESS STORAGE FULL| ZIPCODES       |  3953 | 31624 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |

|  24 |             PX BLOCK ITERATOR           |                |    78M|  2179M|   206K  (2)| 00:48:09 |  Q1,03 | PCWC |            |

|* 25 |              TABLE ACCESS STORAGE FULL  | ALL_CARD_TRANS |    78M|  2179M|   206K  (2)| 00:48:09 |  Q1,03 | PCWP |            |

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

Summary

After a brief bout with an imperfect query plan, the Oracle Database 11g SQL Tuning Advisor fixed what ailed me. With the plan offered me by SQL Tuning Advisor I was able to push my imperfect query through my imperfect schema and a perfect I/O rate of 6 GB/s for my small 6-cell Oracle Exadata Storage Server test configuration.

In spite of how many times EMC’s Chuck Hollis may claim that there is “nothing new” or “no magic” when referring to Oracle Exadata Storage Server, I think it is painfully obvious that there is indeed “something new” here. Is it magic? No, and we don’t claim that it’s magic. What we do claim is that we have moved filtration and projection down to storage and, more importantly, we don’t bottleneck disks (see Hard Drives Are Arcane Technology. So Why Can’t I Realize Their Full Bandwidth Potential?). So while we don’t claim Exadata is magic, there is one thing this blog post shows-all the pre-existing magic, like SQL Tuning Advisor, is still in the arsenal. When you deploy Exadata you leave nothing at the door.

Finally, a no-magic approach to something really new.

The Pythian Group recently conducted a short podcast interview with me to discuss a few topics related to Oracle Exadata Storage Server and the HP Oracle Database Machine. Sadly, the audio is troublesome in some segments, due to telephony issues, but in general I think you may find it informative. Here is the link:

The Pythian Group Interview: Kevin Closson on the Oracle Exadata Storage Server. Part I.

This is installment number five in my series on Oracle Exadata Storage Server and HP Oracle Database Machine frequently asked questions. I recommend you also visit The Index of my other Exadata posts. I’m mostly cutting and pasting questions from the comment threads of my blog posts about Oracle Exadata Storage Server and the HP Oracle Database Machine and mixing in some assertions I’ve seen on the web (and re-phrasing them as questions). If they read as questions when I see them I cut and paste them without modification.

Q. […] for joining large datasets, such as a fact tables anda very large dimension, we currently encourage the use of equipartitioning on the join key to reduce messaging (CPU) and the volume of data distributed to PQ slaves (memory/storage).Would there be (a) a benefit and (b) a mechanism for ensuring that matching partitions of commonly joined tables are colocated on the same cell, so that the join can be performed entirely at the storage level?

A. The answer to both (a) and (b) is no. There is no way to co-locate table or indexes on a cell given the fact that we use ASM to stripe and mirror between cells. To do this we would have to replicate partitions (in entirety) across cells-for redundancy. ASM is unaware of what type of data is stored in blocks. Besides, the only type of “join” we do at this stage is based on bloom filters. Bloom filters are created in the database tier (grid) and then pushed down to storage.

Q. Where can I find the official documentation for the Exadata Product Family? I could only find datasheet and white paper.

A. Documentation is only provided to licensed customers.

Q. How [does] the optimizer decide whether to use an index or a Smart Scan for predicate filtering? I guess he will choose the faster one, but what’s faster and when?

A. Ah, but we don’t call is Smart Table Scan. Exadata Storage Server uses Smart Scans on indexes with the same brute force and intelligence (e.g., filtration) as it does with tables.

Q. Why use the outer part of the disk for everything? I.e., why not use the slower part for mirroring?

A. This question is in reference to the fact that we recommend folks allocate the outer portions of their disks to disk groups that will contain “hot” data. Strictly speaking, there is a way to do this with Automatic Storage Management Failure Groups. I tend not to use Failure Groups to address what the reader is asking because the origin of that feature was to equip administrators with the necessary tools to do the hard work of ensuring ASM mirrors between separate controllers and/or cabinets, etc. Exadata is much more aware of the underlying storage so this level of admin effort is not needed.

Automatic Storage Management mirrors the contents of the logical management unit (a disk group) and does not mirror between disk groups unless you have Failure Groups. If we supported a RAID 0+1 (with BCL) style approach between disk groups then I would put a hot-mirror-side disk group on the sweet sectors and a cold-mirror-side disk group closer to the spindles and only read the cold-mirror-side in the event of a failure. But, we don’t do that with singleton disk groups. Instead, we do a quasi-RAID 1+0 **within** the disk group. As such we consume sweet disk for both the primary and secondary extents. In the event of a loss, I’d say our current approach is better because the application will continue to be serviced with I/O from sweet sectors. On the other hand, if there is never a loss we are consuming sweet disk for naught. It is a trade-off.

The other problem with a RAID 0+1 (hot-to-cold) striped-then-mirrored approach is suffered by OLTP workloads because with the typical read/write ratio of OLTP we’d be wildly flailing between the sweet and sour sectors to satisfy the writes. Remember, we are not a one-pony show.

This is installment number four in my series on Oracle Exadata Storage Server and HP Oracle Database Machine frequently asked questions. I recommend you also visit:

Oracle Exadata Storage Server Frequently Asked Questions Part I.

Oracle Exadata Storage Server Frequently Asked Questions Part II.

Oracle Exadata Storage Server. Frequently Asked Questions. Part III.

I’m mostly cutting and pasting questions from the comment threads of my blog posts about Exadata and mixing in some assertions I’ve seen on the web and re-phrasing them as questions. If they read as questions when I see then I cut and paste them without modification.

Q. What is meant in the Exadata white paper about Smart Scan Predicate Filtering by “…only rows where the employees hire date is after the specified date are sent from Exadata to the database instance..”? Does it really only return the rows matching the predicate or does it return all blocks containing rows which match the predicate? If the former is correct, how is this handled in the db block buffer?

A. Actually, that statement is a bit too liberal. Oracle Exadata Storage Server Smart Scans return only the cited columns from only the filtered rows, not entire rows as that statement may suggest. Results from a Smart Scan are not “real” database blocks thus the results do not get cached in the SGA. Consider the following business question:

List our Club Card members that have spent more than $1,000.00 at non-partner retail merchants in the last 180 days with our affinity credit card. Consider only non-partner merchants within 10 miles radius of one of our stores.

The SQL statement in the following text box answers this question (assuming 10-mile wide US postal code zones):

select    cf.custid, sum(act.purchase_amt) salesfrom all_card_trans act, cust_fact cf

where ( act.card_no like '4777%' or act.card_no  like '3333%' )

and act.card_no = cf.aff_cc_num and cf.club_card_num not like '0%'

and act.purchase_dt  >   to_date('07-MAR-2008','dd-mon-yyyy')

and act.merchant_zip in ( select distinct(zip) from our_stores)

and act.merchant_code not in (select merchant_code from partner_merchants)

group by custid having sum(act.purchase_amt) > 1000 ;

Intelligent Storage. Filtering and Projecting. Oh, and a Bloom Filter Too.

Now, let’s look at the predicate handling in the plan. The next text box shows that storage is filtering based on club cards, purchase dates and credit card. Given the way this query functions, all_card_trans is essentially like a fact table and cust_fact is more of a dimension table since there are 1,300 fold more rows in all_card_trans than cust_fact. You’ll also see there was a bloom filter pushed to the Exadata Storage Server cells used to join the filtered cust_fact rows to the filtered all_card_trans rows.

Predicate Information (identified by operation   id):---------------------------------------------------

3 -   filter(SUM("ACT"."PURCHASE_AMT")>1000)

7 -   access("ACT"."MERCHANT_CODE"="MERCHANT_CODE")

10 -   access("ACT"."MERCHANT_ZIP"="ZIP")

15 -   access("ACT"."CARD_NO"="CF"."AFF_CC_NUM")

20 -   storage("CF"."CLUB_CARD_NUM" NOT LIKE '0%')

filter("CF"."CLUB_CARD_NUM" NOT LIKE   '0%')

25 -   storage("ACT"."PURCHASE_DT">TO_DATE(' 2008-03-07   00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("ACT"."CARD_NO"   LIKE '4777%' OR

"ACT"."CARD_NO" LIKE '3333%'))

filter("ACT"."PURCHASE_DT">TO_DATE(' 2008-03-07   00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("ACT"."CARD_NO"   LIKE '4777%' OR

"ACT"."CARD_NO" LIKE '3333%') AND   SYS_OP_BLOOM_FILTER(:BF0000,"ACT"."CARD_NO"))

This example should make the answer a bit clearer. Storage is ripping through the rows, yes, but only returning named columns and heavily filtered data.

The following text box shows frozen output of a tool we used to monitor I/O at a high level across cells. The names of the Exadata Storage cells in this case are called sgbeta1s01-sgbeta1s06 (I’m like you, I have to scrounge for hardware). These are 2 3-second averages showing I/O in excess of 1,000,000 KB per cell or an aggregate of 6 GB/s (see the data under the “bi” column). This is, of course, what we’ve been referring to as a “half-rack” in spite of the fact that it only has 6 Exadata Storage Server cells instead of the 7 that would be a true half-rack. Nonetheless, this is a 4-table query doing real work and, as you can see in the last text box, I’m connected to a single instance of RAC-on a dual socket/quad-core server! Without Exadata, a server would have to have 15 fibre channel host bus adaptors to drive this query at this I/O rate and a great deal of CPU power. That would be a very large server-and it would still be slower.

Kids, don’t try this at home without supervision

procs -----------memory---------- ---swap-- -----io----  --system-- -----cpu------15:29:43:   r  b     swpd    free   buff     cache   si   so        bi    bo    in      cs us sy id wa st

sgbeta1s01: 4    0    204   63292 134928 1523732    0      0 1033237    24  5062 37992 25  3 72  0  0

sgbeta1s02: 8    0    204   65708 141800 1596124    0      0 1038949     9  5238 37869 25  3 72  0  0

sgbeta1s03: 4    0    208 1183780  57832    584988    0    0 1049712    35  5862 40258 26  3 69    2  0

sgbeta1s04: 2    0    204 1254456  79084    568052    0    0 1053093    13  5329 38462 25  3 72    0  0

sgbeta1s05: 4    0      0  144924 122252 1453324    0      0 1022032    29  4991 38164 25  3 72  0  0

sgbeta1s06:11    0      0   95808 124200 1418236    0      0 1000635    24  4767 38249 25  2 73  0  0

Minimum:   2  0      0   63292  57832    568052    0    0 1000635     9  4767 37869 25  2 69    0  0

Maximum:11  0    208 1254456 141800 1596124    0      0 1053093    35  5862 40258 26  3 73  2  0

Average:   5  0    136  467994 110016   1190742    0    0 1032943    22  5208 38499 25  2 71  0  0

procs -----------memory---------- ---swap-- -----io----  --system-- -----cpu------

15:29:46:   r  b     swpd    free   buff     cache   si   so        bi    bo    in      cs us sy id wa st

sgbeta1s01: 3    0    204   63012 134928 1523732    0      0 1005643    24  5099 38475 25  2 72  0  0

sgbeta1s02: 2    0    204   65188 141800 1596124    0      0 1011285     3  4867 37671 25  2 73  0  0

sgbeta1s03: 4    0    208 1184408  57832    584988    0    0 1005371    20  5775 39378 25  3 72    0  0

sgbeta1s04: 3    0    204 1255200  79084    568052    0    0    993264    13  4891 38240 24  3 73  0  0

sgbeta1s05: 5    0      0  144320 122256 1453324    0      0 1016859    29  4947 38073 25  3 72  0  0

sgbeta1s06: 6    0      0   95824 124200 1418236    0    0 1019323    24  4961 38224 25  2 73  0  0

Minimum:   2  0      0   63012  57832    568052    0    0    993264     3  4867 37671 24  2 72  0  0

Maximum:   6  0    208 1255200 141800 1596124      0    0 1019323    29    5775 39378 25  3 73  0    0

Average:   3  0    136  467992 110016   1190742    0    0 1008624    18  5090 38343 24  2 72  0  0

[oracle@sgbeta1c01]$ sqlpus ‘/ as sysdba'SQL*Plus: Release 11.1.0.7.0 - Production on Thu   Oct 2 15:35:55 2008

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release   11.1.0.7.0 - 64bit Production

With the Partitioning, Real Application Clusters,   OLAP, Data Mining

and Real Application Testing options

SQL> select instance_name from gv$instance ;

INSTANCE_NAME

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

test1

SQL>

This is installment number three in my series on Oracle Exadata Storage Server and HP Oracle Database Machine frequently asked questions. I recommend you also visit:

Exadata Storage Server Frequently Asked Questions Part I.

Exadata Storage Server Frequently Asked Questions Part II.

I’m mostly cutting and pasting questions from the comment threads of my blog posts about Exadata and mixing in some assertions I’ve seen on the web and re-phrasing them as questions. If they read as questions when I see then I cut and paste them without modification.

Q. Is there a coming Upgrading Guide kind of document or a step-by-step installation metalink note planned for the database machine?
A. HP Oracle Database Machine and Oracle Exadata Storage Servers are installed at the factory by HP.

Q The ODM spec sheets says there are four 24-port InfiniBand switches (96 total ports) in each DB machine. If each of the 8 RBMS hosts has 2 links to the switches and each Exadata server (14) also has two, then it is just 2×8 + 2 x 14= 44 links
A. Since the HP Oracle Database Machine is an appliance installed at the factory, by HP, I’m hesitant to go too deep in this area. The short answer is that the extra switches are there to address the loss of an entire HP Oracle Database Machine rack in a multi-rack scale-out configuration.

Q. How does this architecture deal with data distribution and redistribution? It seems like that’s still going to be a problem with joining data that isn’t distributed the same way. Does all the data then go back to the RAC?
A. Data distribution is a multifaceted topic. There is partition-wise data distribution and ASM extent distribution. Nonetheless, the answer is the same for both types of distribution: no change. ASM treats what we refer to as “grid disks” in Exadata Storage Cells no differently than disks in a SAN when it comes to laying out extents. Likewise, partitioning does not change. In fact, nothing about partitioning changes with Exadata.

If, for instance, you have data with poor data distribution (e.g., partitioning skew) with ASM on a SAN, it would be the same with Exadata-but at least the I/O would be extremely fast <smiley>

Exadata changes how data comes from disk, not how it is placed on disk.

Q. If I do a big query and sort, will that bottleneck one of the RAC nodes?
A. Exadata changes nothing about Oracle in this regard. Nonetheless, sorts are parallelized with Intra-node Parallel Query in a Real Applications Clusters environment so I’m at a loss for what you are referring to.

Q. Is temp space managed at the storage layer or on the RAC nodes?
A. Exadata changes nothing about Oracle in this regard. Temporary segments are a logical collection of extents in a file. It’s the same with or without Exadata.

Q. Not sure this is exactly in your field, but what does the cell do when it hits a block that was flushed to disk before being committed (ie needs an UNDO check)? Can it return a mix of rowset and block data so the DB server checks UNDO ?
A. Data consistency and transaction concurrency are not offloaded to Exadata Storage Servers. The integrity of the scan is controlled by the RDBMS. I think it is counterproductive to discuss the edge-cases where a Smart Scan will not be possible. If you are using a database as a data warehouse, you will get Smart Scans. If you are doing reporting against an active OLTP database, you will see queries that are not serviced by Smart Scans.

Smart Scans are optimized for data warehousing workloads and, just as is the case in non-Exadata environments, it is not good practice to be modifying the active query data set while running queries. Adding partitions and loading data while queries are running, sure, but changing a row here and there in a data warehouse doesn’t make much sense (at least to me).

Q. Suppose I have a server (e.g. linux) or a number of RAC nodes, how do I connect them to the Exadata and how do I access the disk space?
A. If you wish to adopt Exadata into an existing Oracle Database 11.1.0.7 environment there are SKUs for that. Talk to your sales representative and make room for Infiniband switches and HCAs.

Q. Do I need fibre or ethernet connections, switches, special hardware between my server and the Exadata?
A. Of course! Exadata is Infiniband based. You’ll at least need Infiniband HCAs and switches to get to the data stored in Exadata. Once you are up to the correct Oracle version you can run with non-Exadata and Exadata tablespaces side-by-side. This fact will aid migrations.

Q. Do I still need OS multipath software?
A. No. Well, not for Exadata.

Q. Do I see raw luns that I present to an ASM instance running on my own machine(s) or does my database communicates directly with the ASM on the Exadata?
A. ASM will have visibility to Exadata Storage Server “grid disks.” There happens to be a command line tool that makes it easy for me to illustrate the point. In the following text box I’ve cut and pasted session output from an xterm where I used the kfod tool to list all known Exadata Storage Server grid disks and grep’ed for ones I named “data1” on cell number 6 of the configuration. To further illustrate the point I then changed directories to list the DDL I used to incorporate all “data1” grid disks in the configuration into an ASM disk group called “DATA1.” Other than the fact that DATA1 is a candidate for Smart Scan, there is really nothing different between this disk group and any other Oracle Database 11g ASM disk group.

Q. Do I still have to struggle with raw devices on os level?
A. No.

Q. Can I create multiple databases in the available space?
A. Absolutely. I haven’t even started blogging about I/O Resource Management features of Exadata. This is the only platform that can prevent multiple applications from stealing resources from each other-all the way down to physical I/O.

Q. Do I still need to create asm disks or diskgroups, or do I just see one large asm disk of e.g. 168Tb?
A. Physical disks in Exadata Storage Server cells are “carved” up into what we refer to as grid disks. Each grid disk becomes an ASM disk. The fewest ASM disks you could end up with in a full-rack HP Oracle Database Machine is 168.

Q. […] don’t some of the DW vendors split the data up in a shared nothing method. Thus when the data has to be repartitioned it gets expensive. Whereas here you just add another cell and ASM goes to work in the background. (depending upon the ASM power level you set.)
A. All the DW Appliance vendors implement shared-nothing so, yes, the data is chopped up into physical partitions. If you add hardware to increase performance of queries against your current dataset the data will have to be reloaded into the new partitioning scheme. As has always been the case with ASM, adding new disks-and therefore Exadata Storage Server cells-will cause the existing data to be redistributed automatically over all (including the new) drives. This ASM data redistribution is an online function.

Q. [regarding] Supportability – Oracle software support has always been spotty. Now with a combination of Oracle Linux, Oracle database and HP hardware, it is going to be interesting to see how it all comes together – especially upgrades, patches etc.
A. Support is provided via a single phone number.

Q. How easy or difficult is it to maintain? Do we need to build specialized skills inhouse or is it hands-off like Teradata?
A. In my reckoning, you need to the same Oracle data warehousing skills you need to day, plus a primer on Exadata.

Q. [regarding] Ease of use – Can I simply move an existing oracle warehouse instance to the new database machine and can use it day 1? How easy or difficult is it? Do I need to spend significant time like with a RAC instance – partitioning etc?
A. Data from an existing data warehouse will have to be physically moved into an Exadata environment. You will be either moving entirely from one environment to another (e.g., 10g on Unix to Exadata with Linux) or adding Exadata Storage to your existing environment and copying the data into Exadata storage. The former would be done in the same manner as any cross-platform migration while the latter would require the warehouse be upgraded to Oracle Database 11g Release 11.1.0.7. Once upgraded to 11.1.0.7 and Infiniband connectivity is sorted out, the data can then copied with the simplicity of a CTAS operation or other such operation.

Q.The Exadata storage concept is excellent – more storage comes with additional CPU and Cache – Can we use it for non-oracle applications – such as Log processing etc?
A. Anything that can go into an Oracle Database can go into Exadata. So such features as SecureFiles are supported. Exadata is not scalable general-purpose storage.

Q. Why would I want to use Oracle rather than Teradata or Netezza which is proven?
A. Because, perhaps, the data you are extracting to load into Netezza is coming from an Oracle Database? There are a lot of answers to this question I suppose. In the end, I should think the choice would be based foremost on performance. Most of Netezza’s customers are either Oracle customers as well, or have migrated from Oracle. I think in Netezza’s “early days” the question was likely reversed. We aim to reverse the question.

Q. Backup using RMAN – RMAN backups are not really geared for big databases, so is there any other off host alternatives available?
A. The data stored in Exadata is under ASM control. The same backup restrictions apply for Exadata as any other ASM deployment.

This is just a quick blog entry to correct some minor (and some not-so-minor) errors I’ve stumbled upon in blog and Web news media.

Exadata Storage Server Gross Capacity

In his recent Computerworld article, Eric Lai was trying to put some flesh on the bones as it were. It is a good article, but a few bits need correction/clarification. First, Eric wrote:

The Exadata Storage Server, a standard rack-mountable HP Proliant DL180 G5 server sporting two Intel quad-core CPUs connected to 12 hard drives of 1TB each.

The HP Oracle Database Machine has 2 hard drive options-SAS and SATA. The SAS option is comprised of 12 300GB 15,000 RPM drives. The SATA option is indeed 12 drives each 1TB in size.

HP Oracle Database Machine Communications Bandwidth

Later in the article the topic of bandwidth between the RDBMS hosts and Exadata Storage Servers was covered. The author wrote:

[…] users can expect a real-world bandwidth today of 1Gbit/sec, which he claimed is far faster than conventional disk storage arrays.

The HP Oracle Database Machine has 1 active and one failover Infiniband path between each of the RDBMS hosts (for RAC inter-process communication) and from each RDBMS host to each Exadata Storage Server. Each path offers 20Gb bandwidth which is more aggregate streaming disk I/O than a Exadata cell can produce for up-stream delivery as I explain in question #2 two of my Exadata Storage Server FAQ. Since the disks can “only” generate roughly 1GB of streaming data, we routinely state that users can expect real-world bandwidth today of 1GB/second. Note the difference in notation (e.g., GB vs Gb) because it accounts for nearly one order of magnitude difference.

A Rack Half-Full or Half-Empty?

When discussing the Beta testing activity, the author quoted Larry Ellison as having said the following at his Keynote address:

A number of Oracle customers have been testing the Machine for a year, putting their actual production workloads onto half-sized Oracle Database Machines, because we’re really cheap

I was there and he did say it. While I may be dumb (actually I can talk), I am not stupid enough to “correct” Larry Ellison. Nonetheless, when Larry said the Beta participants were delivered a half-sized HP Oracle Database Machine he was actually being too generous. He said we sent a half configuration because “we’re really cheap”, but in fact we must be even cheaper because, while we sent them half the number of RDBMS hosts, we sent them 6 Exadata Storage Servers as opposed to 7, which would be exactly half a Database Machine.

Good for the Goose, Good for the Gander

Finally, on my very own blog (in this post even!) I have routinely stated the wire bandwidth of the Infiniband network with which we interconnect Oracle Database instances and Oracle Database instances to Oracle Exadata Storage Server cells as being 20Gb/s. Of course with all communications protocols there is a difference between the wire-rate and the payload bandwidth. One of my blog readers commented as follows:

Yet another minor nit As I commented elsewhere, 20Gb/s is the IB baud rate, the useful bit rate is 16Gb/s (8b/10b encoding). I am not sure why the IB folks keep using the baud numbers.

And to that I replied:

Not minor nits at all. Thanks. We have used pretty poor precision when it comes to this topic. Let me offer mitigating circumstances.

While the pipe is indeed limited to 16Gb payload (20% less than our routinely stated 20Gb), that is still nearly twice the amount of data a cell can produce by streaming I/O from disk in the first place. So, shame on us for being 20% off in that regard, but kudos to us for making the pipes nearly 100% too big?