Kerry Osborne's Oracle Blog

Just a quick post on a new Exadata feature called Zone Maps. They’re similar to storage indexes on Exadata, but with more control (you can define the columns and how the data is refreshed for example). People have complained for years that storage indexes provided no control mechanisms, but now we have a way to exert our God given rights as DBA’s to control yet another aspect of the database. Here’s a link to the 12.1.0.2 documentation which resides in the Data Warehousing Guide: Zone Map Documentation

Zone Maps are restricted to Exadata storage by the way (well probably they work on ZFS and Pillar too). Have a look at the Oracle error messages file:

>grep -i "storage type" $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i "not supported"

/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:31969, 00000, "ZONEMAP not supported for table stored in tablespace of this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:64307, 00000, " Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type" 
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:64309, 00000, " Hybrid Columnar Compression with row-level locking is not supported for tablespaces on this storage type."
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:65425, 00000, "CLUSTERING clause not supported for table stored in tablespace of this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:65451, 00000, "Advanced index compression is not supported for tablespaces on this storage type."

So according to the messages file, there are a handful of features that are restricted in this fashion (Zone Maps, HCC, Attribute Clustering and Advanced Index Compression).

As a bit of totally irrelevant history, zone maps were actually included in the 12.1.0.1 release, but the documentation on them was removed. So they worked, but they were undocumented.

Here’s an example on a 12.1.0.1 DB on a non-Exadata platform.

SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 13 15:41:46 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1211          13-AUG-2014 09:54 13-AUG-2014 15:41     .24      20820

SYS@LAB1211> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2);
create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2)
*
ERROR at line 1:
ORA-65425: CLUSTERING clause not supported for table stored in tablespace of this storage type


SYS@LAB1211> create table kso.junk1 (col1 number, col2 number);

Table created.

SYS@LAB1211> create materialized zonemap skew_zonemap on kso.junk1(col1);
create materialized zonemap skew_zonemap on kso.junk1(col1)
                                                          *
ERROR at line 1:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type

Note that both zone maps and attribute clustering were disallowed with the “not supported for table stored in tablespace of this storage type” error message.

By the way, attribute clustering is another interesting new feature of 12g that allows you to declaratively instruct Oracle to store data on disk in a sorted order. This physical ordering can have big benefit for storage indexes or zone maps (or any btree index where clustering factor is important for that matter). Oracle’s new In-Memory column store also has a min/max pruning feature (storage indexes) which means physical ordering on disk is important with that feature as well.

Anyway, here’s a link to the 12.1.0.2 documentation on attribute clustering which also resides in the Data Warehousing Guide: Attribute Clustering Documentation

And here’s another example using 12.1.0.2 on an Exadata.

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 13 15:42:18 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
INMEM            24-JUL-2014 18:35 13-AUG-2014 15:42   19.88    1717600

Elapsed: 00:00:00.00
SYS@INMEM> @test_zonemap
SYS@INMEM> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2);

Table created.

Elapsed: 00:00:00.01
SYS@INMEM> create materialized zonemap skew_zonemap on kso.junk1(col1);

Materialized zonemap created.

Elapsed: 00:00:00.15
SYS@INMEM> 
SYS@INMEM> -- so as expected, we're able to create an attribute clustered table and a zone map on Exadata
SYS@INMEM> 
SYS@INMEM> -- Let's try creating a tablespace that is not on Exa storage (even though the DB is on EXA platform)
SYS@INMEM> 
SYS@INMEM> create tablespace KSO_NON_EXA datafile '/home/oracle/KSO_NON_EXA.dbf' size 100M;

Tablespace created.

Elapsed: 00:00:00.38
SYS@INMEM> @tablespaces

TABLESPACE_NAME STATUS    CONTENTS  LOGGING   EXTENT_MGT ALLOC_TYP SPACE_MGT BLOCK_SIZE PREDICA
--------------- --------- --------- --------- ---------- --------- --------- ---------- -------
CLASS_DATA      ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 STORAGE
EXAMPLE         ONLINE    PERMANENT NOLOGGING LOCAL      SYSTEM    AUTO            8192 STORAGE
KSO_NON_EXA     ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 HOST      <=== 
SYSAUX          ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 STORAGE
SYSTEM          ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    MANUAL          8192 STORAGE
TEMP            ONLINE    TEMPORARY NOLOGGING LOCAL      UNIFORM   MANUAL          8192 STORAGE
UNDOTBS1        ONLINE    UNDO      LOGGING   LOCAL      SYSTEM    MANUAL          8192 STORAGE
USERS           ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    AUTO            8192 STORAGE

8 rows selected.

Elapsed: 00:00:00.02
SYS@INMEM> 
SYS@INMEM> -- note that tablespace KSO_NON_EXA is on local disk, not Exadata storage servers, so PREDICATE_EVALUATION is set to HOST.
SYS@INMEM> 
SYS@INMEM> drop table kso.junk1;

Table dropped.

Elapsed: 00:00:00.01
SYS@INMEM> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2) tablespace kso_non_exa;

Table created.

Elapsed: 00:00:00.01
SYS@INMEM> select owner, table_name, tablespace_name from dba_tables where table_name like 'JUNK1';

OWNER                TABLE_NAME                     TABLESPACE_NAME
-------------------- ------------------------------ ---------------
KSO                  JUNK1                          KSO_NON_EXA

Elapsed: 00:00:00.01
SYS@INMEM> 
SYS@INMEM> -- wow - that's a bit of a surprise, clustered table create worked on non-Exa storage
SYS@INMEM> -- maybe the check is done on some other level than the tablespace
SYS@INMEM> 
SYS@INMEM> create materialized zonemap skew_zonemap on kso.junk1(col1);
create materialized zonemap skew_zonemap on kso.junk1(col1)
                                                          *
ERROR at line 1:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type


Elapsed: 00:00:00.00

So as you can see, attempting to create the zone map on non-Exa storage failed as expected. But I was able to create a clustered table on non-Exa storage, which is a little weird. So while the error message for attribute clustering exists in the messages file, it doesn’t appear that there is a check in the code, at least at the tablespace level. I don’t have a 12.1.0.2 install on a non-Exadata platform at the moment to test it out, but if you do, please let me know.

That’s it for now. I hope to do some more detailed posts on In-Memory, Zone Maps, Attribute Clustering in the near future. As always, your comments are welcomed.

Just a quick note to remind you that the call for papers for E4 is closing in a few days (on April 30).  So if you have anything you think is interesting related to Exadata that you’d like to share we’d love to hear from you. By the way, you don’t have to be a world renowned speaker on Oracle technology to get accepted. You just need to have an interesting story to tell about your experience. Implementations, migrations and consolidation stories are all worthy of consideration. Any interaction between Exadata and Big Data platforms will also be of great interest to the attendees. Of course the more details you can provide the better.

Here’s a link to the submission page:

Submit an Abstract for Enkitec’s Extreme Exadata Expo

Feel free to shoot me an email if you have ideas for a talk that you want to discuss before formally submitting an abstract.

I got a look a new prototype for the next generation Exadata last week while doing some work with a company in Europe. Apparently there is a big push to be environmentally friendly there now and so Oracle is trying to come up with a model that uses less power and is biodegradable. The word on the street is that it won’t be available until after release 2 of the 12c database.

The new model has a few drawbacks though. For one thing, it only lasts a few weeks before you must either replace it or higher some rocket surgeon consultants to come in and tune it. From the early version of the prototype I saw, it does appear to be smaller and more tasty than previous models though.

That’s a picture of the lead designer (JP) showing off the prototype. The code name for the project is “Exanana” by the way. The new model should be available in select supermarkets after lunch (err launch).  Here’s another picture of JP and one of the other designers (Paul) hamming it up for the camera.

I probably should have saved this post for April 1st!

The 2012 Enkitec Extreme Exadata Expo is behind us now. Our video guy (Bob) has been working diligently for the last week or so to get the presentations edited. They will be made available to the attendees shortly. We have already posted a video of the opening session. It is me interviewing Cary Millsap about his impressions of Exadata. One of the things I have found most interesting about Exadata is how it makes very experienced Oracle performance guys re-think things. It’s fun watching them being exposed to Exadata in an intimate way (not just Power Point). The reactions are interesting. There is usually a desire to try to break it although  it’s generally harder than it appears, at least initially. I got to watch Cary for a few days (along with several of his Method-R guys) when he had his first exposure and we talk about that during the video. Cary made a profound impact on me and my career and I think his insight into the Exadata architecture is worth watching. Hopefully the fact that I have a great face for radio will not deter you (too much). The video has the obligatory Enkitec logo and endless loop, non-descript music intro, but other than that, I certify it as marketing free! 😉

Be sure and check out enkitec.tv for other videos too.

Earlier this year I participated in a Total Cost of Ownership (TCO) study that was run by a company called the FactPoint Group. The goal was to compare the cost of purchasing and running Oracle on Exadata vs. the cost of purchasing and running Oracle on IBM P-Series hardware. The findings are published here:

Exadata vs. IBM P-Series

Fair warning, the study was funded by Oracle and the first 10 minutes are a sales pitch for Exadata, but the data collected by FactPoint for the TCO study fills the rest of the presentation.. That part was very interesting (to me anyway). They interviewed people from 5 companies which had been running in production on Exadata for at least 6 months and 5 companies that had production systems running on IBM P-Series machines. As a bonus, 2 of the Exadata customers had previously run their systems on IBM P-Series hardware or currently had other systems running on P-Series machines.

The TCO calculations were based on equivalent systems from a CPU count basis. That is, they priced out an IBM P7 system that had the same number of cores as an Exadata Half Rack and used that for the calculations. I didn’t think that was really a fair comparison since the performance of the two systems was vastly different. In fact, one of the customers that had moved from IBM to an Exadata had the following to say:

“This data warehouse was originally on 12 a P570 and the nightly load took 5 hours. With Exadata it takes 30 minutes with the Exadata 1⁄4 rack running at only 5% CPU utilization. Weekly stats updating went from 60 hours to 35 minutes. Backups reduced from 14 hours to 45 minutes.”

This is only one of many quotes from the customers included in the presentation. The quotes were quite revealing, and to my way of thinking, were the most interesting part of the presentation. Another oddity of the cost calculations was that they included RAC licenses in the Exadata costs while not including RAC in the IBM costs. Again I felt that this was an unfair comparison as RAC provided HA capabilities to the Exadata platform that the IBM platform simple didn’t have. Nevertheless, the study found that Exadata was about 35% less expensive. I wonder what it would have been if they had tried to cost an IBM system that performed as well as an Exadata and provided the same HA capabilities.

Updated 11/23/2012 – Note that the official white paper produced as a result of this study is now available here: Cost Comparison – Oracle Exadata Database Machine vs. IBM Power Systems

Well the Enkitec Extreme Exadata Expo (E4) is now officially over. I thoroughly enjoyed the event. I personally think Richard Foote stole the show with his clear and concise explanation of why a full table scan is not a straight forward operation on Exadata, and why that makes it so difficult for the optimizer to properly cost it. But Maria Colgan came out with a fiery talk on the optimizer that gave him a good run for his money (she actually had the highest average rating from the attendees that filled out evaluation forms by the way – so congratulations Maria!). Of course there were many excellent presentations from many very well known Oracle practitioners. Overall it was an excellent conference (in my humble opinion) due in large part to the high quality of the speakers and the effort they put into the presentations. I am also thankful for the fact that Intel agreed to sponsor the event and that Oracle supported the event by allowing so many of their technical folks to participate.

While I felt that the overall message presented at the conference was pretty balanced, I did leave with a couple of general impressions that didn’t really feel quite right. Of course having the ability to express one’s opinion is one of the founding principals of our country, so I am going to do a series of posts on generalities I heard expressed that I didn’t completely agree with.

The first was that I got the impression that some people think Exadata isn’t good at OLTP. No one really said that explicitly. They said things like “it wasn’t designed for OLTP” and “OLTP workloads don’t take advantage of Exadata’s secret sauce” (I may have even made similar comments myself). While these types of statements are not incorrect, they left me with the feeling that some people thought Exadata just flat wasn’t good at OLTP.

I disagree with this blanket sentiment for several reasons:

1. While it’s true that OLTP workloads generally don’t make the best use of the main feature that makes Exadata so special (namely offloading), I have to say that in my experience it has shown itself to be a very capable platform for handling the single block access pattern that characterizes what we often describe as OLTP workloads. I’ve observed many systems running on Exadata that have average physical single-block read times in the sub-1ms range. These are very good times and compare favorably to systems that store all their data on SSD storage. So the flash cache feature actually works very well, which is not too surprising when you consider that Oracle has been working on caching algorithms for several decades.

 

2. I think part of the reason for the general impression that OLTP doesn’t work well on Exadata is the human tendency to make snap judgements based on reality vs. expectations, rather than actually thinking through the relevant facts. For example, when you go to a movie that has been hyped as being on of the best of the year and a great cinematic achievement, you are more likely to come away feeling that the movie was not that great, simply because it didn’t live up to your expectations. Whereas a little known movie is more likely to impress you simply because you weren’t expecting that much. When you sit down and actually evaluate the movies side by side, you will probably come to the conclusion that hyped movie was indeed better (people don’t usually bestow accolades on totally worthless stuff). I think that, at least to some degree, OLTP type work loads on Exadata suffer from the same issue. The expectations are so high for the platform in general that even good to excellent results fall short of the massive expectations that have been created based on the some of the impressive results with Data Warehouse type work loads. But that doesn’t mean that the platform is not capable of matching the performance of any other platform you could build at a similar price point.

 

3. I don’t think I’ve ever seen a true OLTP workload. That is to say, I can’t recall ever looking at a system that didn’t have some long running reporting component or batch process that does not fall into the simple single block access (OLTP) category. So I believe that the vast majority of systems categorized as OLTP should more correctly be called “mixed” workloads. In these types of systems, the offloading capability of Exadata can certainly make a big difference for the long running components of the system, but also can improve performance on the single-block access stuff by reducing the contention for resources caused by the long running queries that are unavoidable on standard Oracle architecture.

 

4. Very few of the Exadata systems we’ve worked on over the last few years are supporting a single application or even a single database. Consolidation has become the name of the game for many (maybe most) Exadata implementations. I did a presentation at last year’s Hotsos Symposium where I compiled statistics from 51 Exadatas that we had worked on. 67% were being used as consolidation platforms. This makes it even more likely that an OLTP type workload will benefit from running on the Exadata platform.

So does Exadata run stand alone “pure OLTP” workloads 10X faster than any other standard Oracle based system you could build yourself?

No it does not.

But it does work as well as almost any system you could build, regardless of how much money you spend on the components. By way of proof I’ll tell you a story about a system that we benchmarked on an Exadata V2 quarter rack system. The benchmark was on a batch process that updated well over a billion records, one row at a time, via an index. The system we were comparing against was an M5000 / Solaris system with 32 cores and all data was stored on an SSD SAN. The benchmark showed Exadata to be a little over 4 times faster. This was primarily because most of the work was logical i/o that was serviced by the buffer cache on both platforms. The faster CPUs in the Exadata accounted for most of the gains. Nevertheless, the system was not migrated to an Exadata. A new system was built using a faster Intel-based server which made up the CPU speed difference (and in fact exceeded specs on the V2) and a more capabile SSD based SAN was installed. The resulting system ran the benchmark in about the same time as the original V2 quarter rack (actually it was very slightly faster). Unfortunately the SAN alone cost more than the Exadata. And the real life system also did a bunch of other stuff like some long running ad hoc queries. Guess which platform dealt with those better. 😉 Here’s a slide that summarizes some of the results.

In fairness, I should point out that there is a subset of “OLTP” workloads that are very write intensive. Since writes to data in Oracle are usually asynchronous, while writes to log files usually must complete before a transaction can complete, it’s usually writes to log files that are the bottle neck in these types of systems. However, if the synchronous log file writes can be avoided (or optimized), much higher transaction rates can occur and writes to DB files can become a bottle neck. In those cases, pure write IOPS can be a limiting factor. My opinion is that such systems are relatively rare. But they do exist. Exadata is not currently the best possible option for these extremely write intensive workloads. I say currently because at the time of this writing the storage software does not include any sort of write back cache for buffering writes to data files. However, this is a feature that is expected to be released in the near future. 😉

So that’s it for the OLTP topic.

Stay tuned for Part II, where I’ll discuss another general impression with which I didn’t really agree…

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.

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. 😉

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’ »

I will be participating in an Exadata Virtual Conference which has been organized by Tanel Poder on August 3rd and 4th. This conference will follow the same format as the one Tanel and I did last year with Jonathan Lewis and Cary Millsap. It will be two half days which should provide some flexibility for people with busy schedules. The online format allows all participants to interact directly via chat while the speakers are presenting and then via voice during question and answer sessions. This is a great opportunity to talk to some guys that have done a bunch of work with Exadata. Andy Colvin will be discussing patching which has been problematic for some shops. Andy has done more patching than anyone I know. Randy Johnson will be discussing Resource Management on Exadata which is a key to successful consolidation projects. I will be talking about how Smart Scans work under the covers and covering techniques for determining when and where they are (or are not) occurring. Tanel will be covering in depth tuning and diagnostic techniques specific to Exadata. Of course, Randy, Tanel and I collaborated on the upcoming Apress book, Expert Oracle Exadata, which should be out a couple of weeks before the conference. Here’s a link to the page with all the conference details:

Exadata Virtual Conference

Note that there is a discount for early registration. I hope to see you there.