Exadata experience, what does that actually mean?

As an active member of the Oracle user community I really enjoy talking to delegates at user conferences and user group meetings. As such I was very lucky having had the opportunity to attend two of them recently. I have written about the OUGN spring conference in the post before this, and I also enjoyed the AIM meeting earlier in March.

One of the subjects that always seems to come up is Exadata. Many, many DBAs want to have Exadata experience, and if only to tick a box. Now Exadata means a significant investment, in other words not every company on the planet will have one. On the other hand it’s reasonably complex to administer, therefore recruiters and other HR personal are very interested in DBAs with “Exadata experience”. Now, the reason of this blog post is an open question to the readers: what do you consider as Exadata experience?

The cause for and against the Exadata simulator

I am on my way back from the best UKOUG conference I ever attended, unfortunately a lot earlier than planned. Before I start forgetting all these great moments it is time to write them up. To make use of James Morle’s words: if you weren’t there, you lose. I couldn’t agree more!

The Oak Table Network organised “Oak Table Sunday”, a hugely successful event on Sunday afternoon. This event featured some of the brightest Oracle minds, and thanks to a very relaxed atmosphere made it all a truly exceptional experience. I have to say that the audience was quite illustrious too-I didn’t recognise Paul Vallee from Pythian with his Movember moustache at first and to my great joy I finally met Piet de Visser again. After exchanging a few words with them I ran into so many people it was just great!

Unfortunately I couldn’t make it before the HA panel session, where Alex Gorbatchev, Dave Ensor, James Morle, Greg Rahn, Dan Norris, Graham Wood, Jonathan Lewis and Mogens Norgaard and all the others I just forgot to mention answered questions from the audience.

Collecting and analysing Exadata cell metrics

Recently I have been asked to write a paper about Exadata Flash Cache and its impact on performance. This was a challenge to my liking! I won’t reproduce the paper I wrote, but I’d like to demonstrate the methods I used to get more information about what’s happening in the software.

Hardware

The Exadata Flash Cache is provided by four F20 PCIe cards in each cell. Currently the PCI Express bus is the most potent way to realise the potential of the flash disk in terms of latency and bandwidth. SSDs attached to a standard storage array will be slowed by fibre channel as the transport medium.

Each of the F20 cards holds 96G of raw space, totalling in 384GB of capacity per storage cell. The usable capacity is slightly less. The F20 card is subdivided into 4 so called FMODs, or solid state flash modules visible to the operating system using the standard SCSI SD driver.

Cellcli can also be used to view the FMODs using the “LIST PHYSICALDISK” command. The output is slightly different from the spinning disks as they are reported SCSI drivee’s [host:bus:target:lun] notation.

Now please don’t be tempted to take the FMODs and transform them into celldisks!

Essential tools for Exadata performance experiments

Like I said in a previous post I have started working in the Exadata performance field, which is really exciting, especially after you get it to work really fast!

Also, finding out what your session is spending time on is important if you are just getting started.

I found the following indispensable tools for Exadata performance analysis:

• Session Snapper from Tanel Poder, available from his website
• The Tuning and Diagnostic Pack license
• The Real Time SQL Monitor package
• A good understanding of DBMS_XPLAN
• Oracle 10046 traces
• collectl – I have blogged about it before here and mention it for reference only

There are probably a lot more than those, but these are the bare essentials. Let’s have a look at them in a bit more detail.

Why is my Exadata smart scan not offloading?

After receiving the excellent “Expert Oracle Exadata” book I decided to spend some time looking into Exadata performance after I having spent most of the time previously on infrastructure related questions such as “how can I prevent someone from overwriting my DR system with UAT data”, patching etc.

Now there is one thing to keep in mind with Exadata-you need lots of data before it breaks into a little sweat. Luckily for me, one of my colleagues has performed some testing on this environment. For reasons unknown the swingbench order entry benchmark (version 2.3) has been chosen. For those who don’t know the OE benchmark: it’s a heavy OLTP style workload simulating a web shop where users browse products, place orders etc. OE is optimised for single block I/O, and despite what you may have heard, Exadata doesn’t provide a noticable benefit for these queries.

Anyway, what I liked was the fact that the order_items table had about 350 million rows organised in about 8GB. From discussions with Frits Hoogland I know that a full scan of such a table takes between 40 and 60 seconds depending on system load.