Kerry Osborne's Oracle Blog

I finally took a little time to update the page with some of the presentations I’ve done over the last couple of years. You can get there via the menu bar above or by clicking here: White Papers/Presentations

Maybe some day I’ll have time to organize all the scripts.

One of the best new features of 11g from a diagnostic standpoint is the Real Time SQL Monitoring capabilities. I did a post about it a couple of years ago here: Oracle 11g Real Time SQL Monitoring In that post I talked about a procedure (DBMS_SQLTUNE.REPORT_SQL_MONITOR) that provides a very nicely formatted explain plan type output which contains quite a bit of useful information. Well, it has recently come to my attention that the report contains a column that shows cell offloading. That’s pretty cool. Here’s a script to call that procedure, report_sql_monitor.sql, and an example (note the format is really wide so be sure and use the scroll bar at the bottom to see the columns on the right of the output):

Continue reading ‘Realtime SQL Monitoring – Designed with Exadata in Mind’ »

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