Archive for September 2010

Exadata V3 – Oops – EXADATA X2-8

Oracle had the new version of the Exadata Machine on display at Oracle Open World this week. It’s called the Exadata X2-8. That’s a catchy name. It sounds very Iron Manish! In fact they had these fellows on display next to the demo machines.


The X2-8 uses two 4U Sun Fire x4800 servers which each have 8 x eight-core intel CPUs (X7560) and 1 Terabyte of memory along with 14 Exadata Storage Servers. Here’s a link to the spec sheet for the X2-8 and for the Oracle Sun X4800. Below are a couple of pictures. The first one shows one of the database servers (X4800) with one of the CPU modules out.

The storage cells have not changed much from the original V2. They still have 2 CPUs and 12 drives and 384G of flash cache. Although I’ve been told they have newer (faster) 6 core Intel CPUs. (I did get a look at an unpublished spec sheet on an Oracle employee’s iPhone and it said they were using the 5670 CPUs) Oh, and they will allow you to run Solaris on the database servers. Of course they will have to finish a new version of Solaris 11 before that can happen. It’s worth noting that X2-8 can be ordered but that they don’t have a firm delivery date yet.

So this configuration is definitely for the high end market and addresses a couple of issues. The increased memory will allow us to have a more robust consolidation platform. It will also allow bigger OLTP type systems to run better (i.e. the additional memory means we can support many more concurrent users and have much larger buffer caches). Note that Exadata’s offloading generally reduces the memory requirements, but nevertheless, very large systems, particularly ones with lot’s of fast OLTP type transactions and lot’s of users will be better satisfied by this type of configuration. Also note that there is no little brother version of the X2-8. It comes in a full rack only. Which makes sense because there are only two database machines. I don’t believe the price has been set yet, but the word on the street is that the hardware will be about 50% more than the full rack version with the small 2 CPU blades (now renamed X2-2 by the way).

I did a post a couple of weeks ago (Thoughts on Exadata V3) about what I thought we might get to see in the way of Exadata changes. We got most of the things I was expecting but not all of them. Among the things we got are bigger/beefier servers with more memory and available slots for HBAs to provide some additional connection capabilities (although I’m not sure if Oracle is going to want people to open the machines up and put additional controllers in). I did see mention in the x4800 spec sheet of an HBA so they may actually have one in there already (I need to check that out). They also announced that they will be offering a version of Solaris that can run on the database servers which I was expecting, although they are still using Intel chips. The thing I was expecting that didn’t happen was a change of mind set about flexibility of configuration. They seem pretty set on maintaining a fixed configuration that will be the same for every customer. That is probably not such a bad idea. Certainly it’s easier to support and faster to deploy. But you know how customers are, they want their hamburgers with extra mustard and no pickles. So we’ll see how that works out over time. But for now, it’s a fixed menu. To quote Henry Ford, “You can get it in any color you want as long as it’s black”.

So that’s all I can think of at this point. Please let me know if you have any questions and I’ll see what I can find out.

Do Storage Indexes Work with Bind Variables?

I saw a post today where the subject of Exadata Storage Indexes were being discussed. One of the things that caught my eye was a discussion of whether Storage Indexes worked with Bind Variables. One of the posters observed that since smart scan was aimed at data warehouse type queries, bind variables were pretty much irrelevant. Which is true. Still it’s an interesting question. So I thought I’d give it a quick test.

As usual I used a couple of scripts:

fsx.sql – queries v$sql and shows whether a statement has been offloaded or not (slightly modified to remove 2 columns)
mystats.sql – just queries v$mystat

We’ll look at a test with a number column first.

SYS@LABRAT1> -- Do SI's work with bind variables? - Yes
SYS@LABRAT1>
SYS@LABRAT1> -- first here's basic info on my test table (SKEW3) 
SYS@LABRAT1>
SYS@LABRAT1> desc kso.skew3
 Name                                                                                   Null?    Type
 -------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
 PK_COL                                                                                          NUMBER
 COL1                                                                                            NUMBER
 COL2                                                                                            VARCHAR2(30)
 COL3                                                                                            DATE
 COL4                                                                                            VARCHAR2(1)

SYS@LABRAT1> select count(*) from kso.skew3;

  COUNT(*)
----------
 384000048

1 row selected.

Elapsed: 00:00:26.53
SYS@LABRAT1>  -- 27 seconds to do a full scan with no where clause (there are no indexes)
SYS@LABRAT1> @mystats
Enter value for name: storage

NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                                        0

SYS@LABRAT1> -- no Storage Index usage by this session yet
SYS@LABRAT1> -- let's try a query using a variable
SYS@LABRAT1> set echo on
SYS@LABRAT1> @test_bv_si
SYS@LABRAT1> 
SYS@LABRAT1> variable X NUMBER
SYS@LABRAT1> 
SYS@LABRAT1> begin
  2  
  3  :X := -1;
  4  
  5  end;
  6  
  7  /

PL/SQL procedure successfully completed.

SYS@LABRAT1> 
SYS@LABRAT1> select count(*) from kso.skew3 where col1 = :x;

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

Elapsed: 00:00:00.08

SYS@LABRAT1> set echo off
SYS@LABRAT1> @mystats
Enter value for name: storage

NAME                                                                             VALUE
---------------------------------------------------------------------- ---------------
cell physical IO bytes saved by storage index                              16025346048

SYS@LABRAT1> -- so it used the storage index
SYS@LABRAT1> @fsx
Enter value for sql_text: select count(*) from kso.skew3 where col1 = :x 
Enter value for sql_id: 
Enter value for inst_id: 

 INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------ ----------- ---------- --------------------------------------------------
    1 1nsxv1zpawmsa      0 2684249835          2           .08      0 Yes             100.00 select count(*) from kso.skew3 where col1 = :x

1 row selected.

SYS@LABRAT1> @dplan
Enter value for sql_id: 1nsxv1zpawmsa
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1nsxv1zpawmsa, child number 0
-------------------------------------
select count(*) from kso.skew3 where col1 = :x

Plan hash value: 2684249835

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   533K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |   385 |  1925 |   533K  (1)| 01:46:43 |
------------------------------------------------------------------------------------

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

   2 - storage("COL1"=:X)
       filter("COL1"=:X)


20 rows selected.

So the Storage Index was clearly used on this statement using a SQL*Plus number variable. Here’s some 10046 trace data to show that smart scan wait event was used – note also the “enq: KO – fast object checkpoint” wait event which is done before the direct path reads (replaced by the “cell smart table scan” event in Exadata land).
Continue reading ‘Do Storage Indexes Work with Bind Variables?’ »

Hotsos Symposium 2010 Presentations

I got an email a few days ago asking if I would provide the scripts from my Hotsos Symposium 2010 presentations. I didn’t even realize the presentations had been posted anywhere but I managed to find them on my company’s website. So anyway, I decided to go ahead and post a link to the PDF’s and the scripts here as well. So click on the pretty pictures to get the PDFs and the cleverly titled text links to get the accompanying zip files with the scripts.

Controlling Execution Plans Zip File

My Favorite Scripts 2010 Zip File


Oh and Bob Sneed as “Disco Duck” (Thanks Marco)

I’m in the Wrong Business

Well I stayed at home today to do some writing on a book project while one of my sons and one of my daughters and my wife went to the Fort Worth Natural Science Museum. So I was feeling a little overworked and like I was missing out because I’ve been slaving away writing “scholarly technical material”. Then I got a text from my son. Just a picture that’s all.

I’m sure these two books will probably sell a few orders of magnitude more copies than any book that I contribute to. Kind of puts it all in perspective. I think next weekend I’ll go to the zoo with them.

Thoughts on Exadata V3

I expect we’ll see some announcements at Oracle Open World in a couple of weeks (I don’t know if they will really call it V3 yet by the way).

DISCLAIMER: This is all complete conjecture on my part. I don’t work for Oracle and I have not had any conversations with any Oracle employees about official future directions or plans. (I probably would have had to sign a non-disclosure before I could have any of those discussions and then I wouldn’t be able to do this post).

text

Anyway, here’s the list of things I am thinking we might see:

Bigger Database Servers – I mean physically bigger, with open slots, so we can put HBA’s in them to attach to external storage for migration and backup purposes. Sun 4275’s perhaps since they are already using them as storage servers. The only real issue is that we’d run out of space on a full rack configuration, but if the machines are beefier, perhaps we wouldn’t need as many DB servers anyway.

Bigger Database Servers – I mean more memory and more processing power. Faster chips and bigger DIMMs are a no-brainer. Just put them in the existing 4170 boxes. But how about different models altogether. M series perhaps (which also means a change in O/S). Should be fairly easy to do actually as the DB already runs on Solaris. Might make the Sun shops really happy as well. 😉

More Options – I expect we’ll see a little more flexibility in the configurations, because “One Size Fits All” really doesn’t (or at least many people think that it doesn’t). Anyone want a 2/3 rack?

Incrementally Better Software – It’s a great leap forward already, but I expect more things to be offloaded to the storage layer (some of the analytic functions, some of the aggregate functions, etc…)

Exadata SAN – I could see Oracle announcing a stand alone storage unit with a variable number of “trays”. They might even announce some software for doing some of the more SAN like features (think Snap Mirror).

By the way, I doubt they’ll be buying Netapp just yet (need to drive the price of the stock down a bit first I think).

Well that’s what I’m thinking. What do you think?

Exadata Book

Here’s some white Exaddata text

Well, I guess it’s official. A couple of weeks ago I committed to write an Exadata book for Apress, along with my intrepid co-author Randy Johnson. For those of you who don’t know Randy, he’s a very experienced Oracle Guy with a wealth of knowledge, particularly around RAC. I think the two of us make a pretty good team – making up for each others weaknesses (oh wait, I should say we have “Complimentary Skill Sets” – yeah that sounds better).

Anyway, it turns out that writing a book is a lot of work! The way Tom Kyte turns them out I thought it must be pretty easy, but I’ve always been a little overconfident. So I’m starting to realize that I may not have time to do as many blog posts as I might like. But I must say that I am really excited about the subject matter! So I think it will be worth the effort. By the way, that’s not the official cover art (or even the official title as far as I know). I just hacked that together with a Adobe Illustrator. 😉

As I have said many times, I think the Exadata storage software will usher in a whole new era in relational databases. Not just for Oracle, because you know the other guys will be trying to follow in their footsteps. But I think Oracle is miles ahead at this point, and they own their own hardware platform. Oracle claims that it has been their most successful product launch ever and I believe it. They are starting to pop up like weeds. It will be very interesting to see what the future holds for this platform. To be honest, I think we’re just seeing the tip of the iceberg at this point. Anyway, I appreciate the guys at Apress having the faith in us to take on this project. I hope we don’t disappoint them (I don’t think we will).