How Oracle Result Cache and Smart Scans Work Together

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.

6 Comments

  1. Uwe Hesse says:

    Nice piece of research, Kerry, thank you! I’d like to add a little remark about how to use the result cache feature: With 11gR2, we have a third option between 1) using the hint as you did in the posting or 2) setting RESULT_CACHE_MODE=FORCE (as discussed in the AskTom thread you linked to), that is 3) “Using Result Cache Table Annotations”, as described here: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF977
    3) seems to me a quite reasonable way to deal with that feature

  2. osborne says:

    Hi Uwe,

    That’s an interesting option. I could see it being useful in some situations. I could also see it being problematic if there are very diverse queries going against a table, or the access pattern changes over time and no one remembers that the attribute has been set. Hints are a little more obvious. I’ll have to give that some thought.

    Kerry

  3. Kevin says:

    Hi Kerry,

    Nice article. I almost hate to do this, but you know I must. I’d like to point out that the product of a Smart Scan is not a result set as stated in the beginning of this article. A result set is the product of higher levels of processing above Smart Scan (the SQL layer).

    OK, enough of that. I have some chapters from your book to go off and review :-)

  4. osborne says:

    Kevin,

    As usual you are semantically correct. Would a row source be more to your liking?

  5. Victor Chen says:

    Thanks Kerry for the scripts, they are new gems in my toolbox.

Leave a Reply