Kerry Osborne's Oracle Blog

I got an interesting question today from a guy who is reading through the Alpha version of our Exadata Book. I’m paraphrasing, but Ajay basically wanted to know about the interaction between the relatively new Result Cache feature and Smart Scans. The short answer is that they work well together, exactly as you would hope. But let me back up for minute.

The Results Cache feature was introduced with 11gR1. It’s a pretty cool feature – Tom Kyte calls it JIT MV (just in time materialized view).

It basically caches the result set of a query as opposed to blocks from the underlying objects that are necessary to process the query. It is designed for situations where data is relatively static and fairly long running queries are repeated many times. Think of a DW that is updated nightly with some key queries that are repeated several times a day. As you might expect, the performance impact can be dramatic.

The question is interesting because Smart Scans read result sets directly into the PGA as opposed to retrieving blocks into the buffer cache. Sound familiar? Anyway, here’s an example of executing an Offloadable statement with a RESULT_CACHE hint:

SYS@SANDBOX> -- flush the result cache in case something is still hanging around
SYS@SANDBOX> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SYS@SANDBOX> -- run an offloadable statement a few times
SYS@SANDBOX> select avg(pk_col) from kso.skew2 a where col1 > 1;

AVG(PK_COL)
-----------
   16049999

Elapsed: 00:00:14.34
SYS@SANDBOX> /

AVG(PK_COL)
-----------
   16049999

Elapsed: 00:00:12.16
SYS@SANDBOX> /

AVG(PK_COL)
-----------
   16049999

Elapsed: 00:00:13.01
SYS@SANDBOX> -- run the same statement with a result_cache hint
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;

AVG(PK_COL)
-----------
   16049999

Elapsed: 00:00:11.97
SYS@SANDBOX> -- check execution statistics
SYS@SANDBOX> @fsx
Enter value for sql_text: %skew2%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
6fduhxkpdx1mc      0 4220890033      1      11.88      0 Yes          74.57 select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1
8vwynva819s92      0 4220890033      3      13.08      0 Yes          74.56 select avg(pk_col) from kso.skew2 a where col1 > 1

Elapsed: 00:00:00.13

So first I flushed all result cache entries from the SGA (it’s stored in the shared pool). Then I ran an offloadable SELECT statement 3 times.

The admittedly simple SQL statement takes between 12 and 14 seconds (the average elapsed time over three executions is 13 seconds). And the first execution with the RESULT_CACHE hint (before the Result Cache has been built) takes roughly the same amount of elapsed time. The fsx.sql script shows that both versions were offloaded and each cut out approximately the same amount of payload. (I’ve written about the fsx.sql script previously here: How to Tune an Exadata)

So clearly, using the RESULT_CACHE hint did not disable Smart Scans.

Now let’s see if Smart Scans disabled the Result Cache. To test this I’ll just run the hinted statement a few more times.

Continue reading ‘How Oracle Result Cache and Smart Scans Work Together’ »

You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:

I was working on an external table today and fat fingered something. This is the error report-

Error report:
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don’t do that!

Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!

Apparently some of the Oracle developers have a sense of humor.

By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:

SYS@SANDBOX1> !oerr ora 30657
30657,0000, "operation not supported on external organized table"
// *Cause:  User attempted on operation on an external table which is
//          not supported.
// *Action: Don't do that!

Q: How do you tune Exadata?
A: Look for long running queries that are not using Smart Scans and then fix them so they do.

We’ve worked on a bunch of Proof of Concepts (POC’s) for customers over the last year or so. These usually involve loading a few terabytes of data and running a bunch queries or some other workload on the data. Generally speaking, anything we have thrown at Exadata has blown the doors off of the platforms that the applications were previously running on. But occasionally we run into a situation where the speed up is just not what we’ve come to expect. Generally speaking it’s because we’ve done something that has kept Exadata from doing what it does best – Smart Scans. While my lead in is obviously a tongue in cheek over simplification, it is basically true. Unfortunately, it’s not as easy as it sounds to determine whether a statement has been offloaded, because our main tool for looking at how a statement was executed (the execution plan) doesn’t tell us whether a Smart Scan was used or not. So in this post, my intent is to give you a couple of options for determining whether Smart Scans are happening or not. Here’s a quick example showing a basic execution plan:

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


INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
SANDBOX1         24-MAR-2011 16:19 25-MAR-2011 22:57    1.28     110283

SYS@SANDBOX> 
SYS@SANDBOX> set timing on
SYS@SANDBOX> @avgskew3
SYS@SANDBOX> select avg(pk_col) from kso.skew3
  2  where col1 > 0
  3  /

AVG(PK_COL)
-----------
 16093750.2

Elapsed: 00:00:34.80
SYS@SANDBOX> select sql_id, sql_text from v$sql
  2  where sql_text like 'select avg(pk_col) from kso.skew3 where col1 > 0';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------
4p62g77m9myak select avg(pk_col) from kso.skew3 where col1 > 0

Elapsed: 00:00:00.14
SYS@SANDBOX> @dplan
Enter value for sql_id: 4p62g77m9myak
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4p62g77m9myak, child number 0
-------------------------------------
select avg(pk_col) from kso.skew3 where col1 > 0

Plan hash value: 2684249835

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   535K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |   383M|  4028M|   535K  (1)| 01:47:02 |
------------------------------------------------------------------------------------

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

   2 - storage("COL1">0)
       filter("COL1">0)


20 rows selected.

Elapsed: 00:00:00.22

The storage line in the predicate section indicates that a Smart Scan is possible, but it doesn’t actually tell us that one occurred. So how can you tell. Well there are several ways.

1. You can Millsap it. (generate a 10046 trace)
2. You can Poder it. (use Tanel Poder’s snapper script to check stats and wait events)
3. You can Wolfgang it. (generate a 10053 trace) – well actually this doesn’t work since the optimizer doesn’t know whether a statement will do a Smart Scan or not.
4. Or you can look in v$sql – I wrote a little script called fsx.sql (short for Find_Sql_eXadata.sql) to do that.

I think that tracing is the most foolproof way to verify a Smart Scan (just look for “cell smart table/index scan” wait events). But it can be a little cumbersome to generate a trace and then find it. (Note: Method-R has a great tool to make this easier called MR Trace which is a plug in for Oracle’s SQL Developer). Tanel’s snapper script is an awesome tool that is very versatile – so it’s a very valid option as well. But both of these methods depend on the fact that you can catch the statement of interest while it is executing. They provide no way of looking back at statements that ran in the past. My fsx script is not nearly as comprehensive as either of these approaches, but it has an advantage in that it looks at values stored in v$sql (which are also captured in AWR by the way). This allows us to do analysis that is not limited to what is happening right now. (i.e. we don’t have to catch the query while it’s running).

So how does it work?
Continue reading ‘How to Tune an Exadata’ »

I used to get a kick out of the recollections of the slightly older guy I worked with (Rob Weinberg). He used to tell me about programming on a system where they didn’t even have a keyboard. As he explained it, the computer was programmed by flipping switches for each bit and pushing a button to store assembly instructions. This would be done repetitively until all the instructions were entered into the computer and then the “program” could be run. I thought that was really funny having started with 4GL programming. Well I ran across a couple of videos on youtube of a guy programming a Dec PDP computer which was the machine that was in use just before I started my career on Dec Vaxen. And the videos show how it was done. (you younger guys might want to sit down before you watch this, I don’t want anyone to get dizzy and hurt themselves)

The switches allowed assembly language to be input (looks like 4 – 3 bit characters to me). They also had the ability to read programs off of paper tapes as long as you could enter the program to read the tape. Here’s a guy doing just that.

If you want to see what a variac is, have a look at this one:

It’s basically a power conditioner. I’m not sure why they needed that. I have seen people use them with guitar amps which can be sensitive (from a sound perspective) to low voltage or variable voltage.

Anyway, I thought the videos were entertaining.

I’ve had several inquiries about whether our upcoming Exadata book is part of the “Alpha” program at Apress. Honestly, I wasn’t even familiar with the program so I asked our editor, Jonathan Gennick, and found out that our book is part of the program. Tanel already did a post explaining how it works here:

• Tanel’s Alpha Program Post

I just wanted to follow up with a quick post since I’d had a few questions about it already. It’s actually pretty cool if you are anxious to get your hands on the material. It’s also interesting because you can see how the book writing process goes. You basically have access to draft versions of chapters as the authors turn them in – warts and all. Then you’ll get emails when the chapters are updated as they go through the reviewing and editing process. Here’s a link to the Apress page where you can see all the details:

• Apress Exadata Book Alpha Program Page

So welcome to the future where everyone can see everything you’re doing. Next thing you know we’ll all be wearing jet packs.