July 24, 2012, 6:33 pm
We started on an interesting mad scientist kind of project a couple of days ago.
One of our long time customers bought an Exadata last month. They went live with one system last week and are in the process of migrating several others. The Exadata has an interesting configuration. The sizing exercise done prior to the purchase indicated a need for 3 compute nodes, but the data volume was relatively small. In the end, a half rack was purchased and all four compute nodes were licensed, but 4 of the 7 storage servers were not licensed. So it’s basically a half rack with only 3 storage servers.
Meanwhile, we had been talking with them about Hadoopie kind of stuff. They are in the telecom space and are interested in pulling data via a packet sniffer which captures info directly from the tcp traffic. During the talks we discussed hardware requirements for building a Hadoop cluster as they didn’t really have any spare hardware available to test with. That’s when the crazy science project idea was born. Someone (who shall remain nameless) suggested that we build the pilot Hadoop cluster on the 4 unused storage nodes from the Exadata half rack. Since the storage servers use basically the same hardware as is used in the Oracle Big Data Appliance (BDA), it’s kind of like having a mini BDA. Of course the storage servers have slower CPU’s and a little less memory so it’s not apples to apples, but the servers do have InfiniBand and the same 3T drives so it’s pretty similar. And since they already had the servers sitting there …
So now we have a mini Hadoop cluster installed (CDH3) with 3 data nodes (roughy 100T raw storage). We also set up the Oracle Big Data Connectors on one of the Exadata compute nodes which allows us to create external tables on files stored in HDFS. Pretty cool. Let the games begin!
Oh and by the way. I’ll probably be talking about this project a bit at E4 (Enkitec Extreme Exadata Expo) on Aug. 13-14 in Dallas.
July 12, 2012, 1:26 pm
Last year at Oracle Open World I was introduced to a guy named Zhang Leyi (Kamus). He said he had been hired to translate our Expert Exadata book into Chinese. There were a couple of other guys on the team as well – Kaya Huang 黄凯耀 and Jacky Zhang 张瑞. Well they have apparently finished their work and the book is due to be released this month. Kamus sent us a copy of the cover (see below). It looks very sexy don’t you think? We exchanged a few emails with the guys while they were working on it mainly just clarifying our intentions and helping them fix typos in the original version Unfortunately I wasn’t as responsive as I should have been (sorry about that guys), but Tanel and Randy did a pretty good job I think. Not surprisingly, the most difficult part seemed to be translating some of the “Kevin Says” sections, as he has a way of packing a lot into very few words.
So anyway, congratulations to the guys for getting through the project. I hope they send me a copy. 😉
April 11, 2012, 11:36 pm
Well apparently you can teach an old dog new tricks. I recently sent the following email to Enkitec’s internal techie email list:
Begin forwarded message:
Have you ever tried to decipher a really ugly, unformatted SQL statement that someone gave you or you pulled out of one the database views or an AWR report? Tim Fox turned me on to the SQL formatting capability of SQL Developer a few weeks ago (actually Brian Hill found it first and showed Tim). It is awesome! It’s a little unintuitive to use though. Here’s how I use it.
1. start up the SQL Developer (there are versions for Mac OS and Windows by the way)
2. Click File->New and choose the SQL File option
– this opens a Query Builder Pane (you don’t need to connect to a database)
3. Pick any random file to open
4. Paste your nasty 10 page long query into the Query Builder pane replacing the text from your random file
5. Right click on the text in the window (brings up a long menu)
6. Click the Format menu Item (it’s at the bottom of the menu in version 3.0)
Viola – nicely formatted SQL text
The latest version of SQL Developer (3.0.x) is actually much better than the previous version by the way.
I haven’t closed SQL Developer since I found out about this capability!
SQL Developer is a free tool that can be downloaded from Oracle’s web site here:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Note version 3.1 is now available but I haven’t tried it yet.
March 15, 2012, 3:33 pm
Since I’m on vacation and not “really” working, I thought I might have time to write up a quick blog post. The idea for this one was triggered by one of Maria Colgan’s presentations at Hotsos last week. Maria was talking about SQL Plan Management and Baselines and somehow got me thinking about the DBMS_XPLAN option to display plans for Baselines. This is a pretty neat feature that allows you to the see the plan associated with a Baseline (well sort of).
The 11.2 documentation (Oracle® Database PL/SQL Packages and Types Reference) says this about the DISPLAY_SQL_PLAN_BASELINE function:
This procedure uses plan information stored in the plan baseline to explain and display the plans.It is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.
But what does that mean? Well in short it means that Baselines don’t store plans, they store hints that when fed to the optimizer will hopefully cause it to come up with the desired plan. Baselines also store a plan_hash_value so it’s possible to tell whether the hints worked or not. Baselines do not actually store all the steps of a plan. So if that’s the case, then it’s obviously not possible for the display_sql_plan_baseline function to show the plan if the optimizer can’t reproduce it for some reason. When the doc’s say “it is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan”, that’s what they are talking about. I decided to create a test case to see what happens when the generated plan can’t match the original. Here’s the basic idea:
- run a statement that uses an index and check the plan
- create a Baseline on the statement using the index (using my create_baseline.sql script)
- check the hints stored with the baseline (using my baselines_hints.sql script)
- run the statement again and check the real plan to see that the Baseline was used
- use the display_sql_plan_baseline function to show the Baseline plan
- make the index invisible (thus rendering the Baseline plan non-reproducible)
- execute the statement again and check the real plan
- use the display_sql_plan_baseline function to show the Baseline plan
So here’s the test:
Continue reading ‘Displaying SQL Baseline Plans’ »
February 19, 2012, 4:43 pm
It’s almost time for Hotsos’s 10th annual Symposium. This year’s conference will be held March 4 – 8 in Irving, Texas. The Hotsos Symposium is probably the best performance oriented Oracle conferences in the world. I am happy and humbled to be speaking at it again this year as the lineup of speakers is once again world class. It’s great to have several other Enkitec’ies on the bill as well. Both Karen Morton and Tanel Poder will be presenting too. Enkitec is also a sponsor of the event this year, so we’ll be hosting a couple of Exadata focused happy hours as well. There’s still time to register. Hope to see you there.
February 8, 2012, 9:14 pm
I’ll be speaking at a couple of conferences over the next month or so. Here’s the schedule:
February 16th, in Denver at RMOUG Training Days 2012 – Topic: DIY Exadata
February 23rd, in Redwood Shores at NoCOUG Winter Conference – Topic: DIY Exadata
March 7th, in Dallas at Hotsos Symposium 2012 – Topic: Exadata Optimization – Case Studies
Hope to see you at one of these events.
January 9, 2012, 7:56 pm
Chris Date is one of the founding fathers of relational databases. Having worked with Ted Codd at IBM during the time when relational databases were being defined gives Chris a perspective that most of us just don’t have. I’ve had the good fortune to hear him speak in the past (at the Hotsos Symposium) and thought I would do a quick post to highlight the fact that he is scheduled to speak in Dallas the week of Jan 30. Method-R is hosting the event in the Enkitec training facilities in Dallas. So maybe I’ll get to hang around with Chris and Cary that week – that would be cool! Anyway, there are actually 2 classes:
SQL and Relational Theory: How to Write Accurate SQL Code
Normal Forms and All That Jazz: a Database Professional’s Guide to Database Design Theory
And here’s a link to the registration page: C. J. Date Seminar Registration
By the way, I think every developer and every database architect should have a clear understanding of how the SQL language is designed to work and how relational databases were intended to be laid out. Chris obviously has a unique insight into those topics. One of the tenants of Chris’s teaching is that SQL is a complicated language and since comprehensive testing is almost never really feasible, it is important to write SQL using a disciplined approach based on the underlying relational theory. As a side note, I was talking to a few cohorts around the coffee pot today and was shocked to hear that one of the guys had a CS degree but was not required to take a relational theory class. Back when I got started that was the first class that people took, probably because there were almost no real implementations of the theory at that point. Oracle was just getting started and DB2 was still a distant gleam in Mr. Codd’s eye. But I digress.
It does seem to me that we have an awful lot of systems running on Oracle these days that were designed and written by people without a strong background in relational database fundamentals. I can’t even begin to count the number of times I’ve worked on systems that performed poorly due to poor SQL coding techniques and/or poor database design. Chris’s courses are designed to help you avoid these issues. So this is your chance to learn how to know for sure that your SQL is correct.
Hope to you see you there!
December 8, 2011, 3:44 pm
Here’s a lovely Candy Striped pattern in an Explain Plan. Looks like the traditional Christmas candy canes. Just in time for the Holiday Season!
| 156 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 157 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 158 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 159 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 160 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 161 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 162 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 163 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 164 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 165 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 166 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 167 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 168 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 169 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 170 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 171 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 172 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 173 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 174 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 175 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 176 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 177 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 178 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 179 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 180 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 181 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 182 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 183 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 184 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 185 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 186 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 187 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 188 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 189 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 190 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 191 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 192 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 193 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 194 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 195 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 196 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 197 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 198 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 199 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 200 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 201 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 202 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 203 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 204 | L FAST DUA | | 1 | | 2 (0)| 00:00:01 |
| 205 | AL FAST DU | | 1 | | 2 (0)| 00:00:01 |
| 206 | UAL FAST D | | 1 | | 2 (0)| 00:00:01 |
| 207 | DUAL FAST | | 1 | | 2 (0)| 00:00:01 |
| 208 | DUAL FAST | | 1 | | 2 (0)| 00:00:01 |
| 209 | T DUAL FAS | | 1 | | 2 (0)| 00:00:01 |
| 210 | ST DUAL FA | | 1 | | 2 (0)| 00:00:01 |
| 211 | AST DUAL F | | 1 | | 2 (0)| 00:00:01 |
| 212 |.FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 213 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 214 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 215 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 216 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 217 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 218 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 219 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 220 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 221 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 222 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 223 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 224 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 225 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 226 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 227 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 228 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 229 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 230 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 231 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 232 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 233 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 234 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 235 |. FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
November 23, 2011, 2:15 pm
I had an interesting little project this morning. Of course it takes longer to write it down than to do actually do it, but it was kind of interesting and since I haven’t done a post in quite some time (and it’s the day before Thanksgiving, so it’s pretty quite at the office anyway) I decided to share. One of the Enkitec guys (Tim Fox) was doing a performance comparison between various platforms (Exadata using it’s IB Storage Network, Oracle Database Appliance (ODA) using it’s direct attached storage, and a standard database on a Dell box using EMC fiber channel attached storage). The general test idea was simple – see how the platforms stacked up for a query that required a full scan of a large table. More specifically, what Tim wanted to see was the relative speed at which the various storage platforms could return data. The expectation was that the direct attached storage would be fastest and the fibre channel storage would be slowest (especially since we only had a single 2G HBA). He tested ODA and Exadata and got basically what he expected, but when he went to test the database on the Dell he was surprised that it was actually faster than either of the other two tests. So here’s some output from the initial tests: First the Exadata. It’s an X2 quarter rack with one extra storage server. Note that we had to set cell_offload_processing to false to turn off the Exadata storage optimizations, thus giving us a measurement of the hardware capabilities without the Exadata offloading.
> !sqlp
sqlp
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 11:08:28 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@DEMO1> @uptime
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- ----------------- ----------------- ------- ----------
DEMO1 07-NOV-2011 12:37 23-NOV-2011 11:08 15.94 1377058
SYS@DEMO1> set sqlprompt "_USER'@'EXADATA'>' "
SYS@EXADATA>
SYS@EXADATA> ! cat /etc/redhat-release
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
SYS@EXADATA> ! uname -a
Linux enkdb03.enkitec.com 2.6.18-194.3.1.0.3.el5 #1 SMP Tue Aug 31 22:41:13 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
SYS@EXADATA> alter session set "_serial_direct_read"=always;
Session altered.
SYS@EXADATA> alter session set cell_offload_processing=false;
Session altered.
SYS@EXADATA> set autotrace on
SYS@EXADATA> set timing on
SYS@EXADATA> select count(*) from instructor.class_sales;
COUNT(*)
----------
90000000
Elapsed: 00:00:43.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3145879882
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 314K (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| CLASS_SALES | 90M| 314K (1)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1168567 consistent gets
1168557 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@EXADATA> set autotrace off
SYS@EXADATA> @fss
Enter value for sql_text: select count(*) from instructor.class_sales
Enter value for sql_id:
SQL_ID CHILD EXECS AVG_ROWS AVG_ETIME AVG_CPU AVG_PIO AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------- ------------- ------------ ----------------------------------------
b2br1x82p9862 0 1 1 43.00 3.16 1,168,557.00 1,168,567 select count(*) from instructor.class_sa
Elapsed: 00:00:00.08
So the test on the Exadata took 43 seconds to read and transport roughly 1 million 8K blocks. The same test on the ODA looked like this: Continue reading ‘Tuning Oracle to Make a Query Slower’ »
September 21, 2011, 3:59 pm
Oracle today announced a new database appliance product. It’s called Oracle Database Appliance (ODA). I’m not crazy about the name, but I really like the product. Here’s a picture of the one in Enkitec’s lab:
|
|
The project was code named “Comet” – thus the yellow sticky note. 😉
I really like that name better than ODA, so I think I will just stick with Comet.
Enkitec participated in the beta test program for the product and we were very impressed, particularly with the speed at which the product could be deployed and configured. There is a new tool called the “OAK Configurator” that is sort like the Exadata OneCommand for configuring the system. Keep an eye out for Karl Arao‘s upcoming post with screen shots of the tool in action.
I’m sure there will be plenty of people talking about the specs so I won’t get carried away with that. But I will tell you that it’s basically 4 terrabytes of usable storage, 2 node RAC with up to 24 cores and a SSD component that is used for improving redo write speeds (more on that later), all in a 4U chassis. Andy Colvin has already got a really good post on the hardware components that are included in the Oracle Database Appliance (along with pictures of the bits and bobs inside the chassis).
I should point out that while I have heard people refer to Comet as a “Baby Exadata”, I really don’t view it that way. That’s because it DOES NOT have separate storage and compute tiers. So there is no Exadata Smart Scan / Offloading secret sauce here. It also does not provide the ability to utilize Exadata’s Hybrid Columnar Compression. On the other hand, like Exadata, it is a pre-configured and tested system that can be dropped in a data center and be ready for use almost immediately (it took us only a couple of hours to set it up and create a database). Pretty unbelievable really.
So much like my favorite Bill Clinton quote, whether ODA is a “Baby Exadata” or not, really depends on your definition of the word “is”. It is a hardware platform that is built specifically to run Oracle databases, but it does not embed any of the unique Exadata software components. Nevertheless, it is an extremely capable platform that will appeal to wide variety of companies running Oracle databases.
And best of all, the list price for this puppy is only $50K. I predict this thing is going to sell like hot cakes!
