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.

SYS@SANDBOX> 
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;

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

Elapsed: 00:00:00.08

. . .

SYS@SANDBOX> /

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

Elapsed: 00:00:00.09
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     10       1.19      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.12

So as you can see, the execution time dropped from over 10 seconds to less than 0.1 seconds. The average elapsed time for the 10 executions was about 1 second, including the first execution that took about 12 seconds. The OFFLOAD column returned by the fsx script is somewhat misleading in this case, as described in my previous post. It basically tells us if any of the executions was offloaded. Let’s clear the entry from the cursor cache and check the stats again. I’ll do this using a script called flush_sql.sql which uses the DBMS_SHARED_POOL.PURGE procedure. Then I’ll re-run the statement a few times.

SYS@SANDBOX> @flush_sql
Enter value for sql_id: 6fduhxkpdx1mc

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;

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

Elapsed: 00:00:00.10

. . . 

SYS@SANDBOX> /

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

Elapsed: 00:00:00.07
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      5        .00      0 No             .00 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.12

SYS@SANDBOX> @dplan
Enter value for sql_id: 6fduhxkpdx1mc
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6fduhxkpdx1mc, child number 0
-------------------------------------
select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1

Plan hash value: 4220890033

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |       |       |   178K(100)|          |
|   1 |  RESULT CACHE               | 2gq0a1pgm8yk88cqvfxtxhchny |       |       |            |          |
|   2 |   SORT AGGREGATE            |                            |     1 |    11 |            |          |
|*  3 |    TABLE ACCESS STORAGE FULL| SKEW2                      |   127M|  1342M|   178K  (1)| 00:35:42 |
----------------------------------------------------------------------------------------------------------

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

   3 - storage("COL1">1)
       filter("COL1">1)

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 -

So it’s clear from the elapsed time and the fsx script and the xplan output that the statement used the Result Cache.

Cool, Smart Scan the first time, Result Cache after that.

Let’s see if it behaves as expected after the data has been changed (it should revert to a Smart Scan which should also build the Result Cache which will be used on subsequent executions). And while we’re at it, let’s see how long it takes to execute the statement without Smart Scan (I’ll do that first actually).

SYS@SANDBOX> -- turn offloading off
SYS@SANDBOX> alter session set cell_offload_processing=false;

Session altered.

Elapsed: 00:00:00.06
SYS@SANDBOX> select avg(pk_col) from kso.skew2 a where col1 > 1;

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

Elapsed: 00:00:52.09
SYS@SANDBOX> -- turn offloading back on
SYS@SANDBOX> alter session set cell_offload_processing=true;

Session altered.

Elapsed: 00:00:00.06
SYS@SANDBOX> @flush_sql
Enter value for sql_id: 6fduhxkpdx1mc

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
SYS@SANDBOX> -- change the data
SYS@SANDBOX> update kso.skew2 set col1 =col1*1 where rownum < 10
SYS@SANDBOX> /

9 rows updated.

Elapsed: 00:00:00.07
SYS@SANDBOX> commit;

Commit complete.

Elapsed: 00:00:00.06
SYS@SANDBOX>  -- first run after data changed
SYS@SANDBOX> select /*+ result_cache */ avg(pk_col) from kso.skew2 a where col1 > 1;

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

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

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

Elapsed: 00:00:00.09

So the statement takes almost a minute when executed without Smart Scan (52 seconds). I then updated a few records and re-ran the statement. As expected the Result Cache entry was invalidated and the statement reverted to a Smart Scan. I didn’t show any proof that a Smart Scan was used, but take my word for it (the execution time was only 10.6 seconds, not 52) and apparently it rebuilt the Result Cache because the next execution took about 0.1 seconds.

So there you have it. The result cache appears to work well with Smart Scans.