Why isn’t my /*+ result_cache */ working?

As I mentioned just now, I’ve got Oracle 11g up and running on my apology of a laptop but I’ve not yet got to the juicy result cache stuff that I wanted to get to.

First thing I want to do is to set up some result cache demos.

It’s getting late and I’m getting tired, distracted now by Have I Got News For You, I’m making silly mistakes and jumping to conclusions.

I thought I was going to be blogging about the result_cache initialisation parameters result_cache_max_size and result_cache_max_result and why these were preventing the efficacy of my demos.

But from my observations, actually the problem seems to be that if you’re silly enough to log in as sysdba, you won’t user the result_cache.

Witness:


SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 10 23:20:01 2007

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

Enter user-name: dom
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 120 pages 9999 autotrace on timing on
SQL> drop table table_c
2 /

Table dropped.

Elapsed: 00:00:01.76
SQL> create table table_c as select object_id from all_objects where rownum select /*+ result_cache */
2 *
3 from table_c;

OBJECT_ID
----------
20
46
28
15
29
3
25
41
54

9 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4211580995

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | bamn0a75f25nx8rd4zwa55md30 | | | | |
| 2 | TABLE ACCESS FULL| TABLE_C | 9 | 117 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

1 - column-count=1; dependencies=(DOM.TABLE_C); name="select /*+ result_cache */
*
from table_c"

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
10 consistent gets
1 physical reads
0 redo size
514 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

SQL>

That should have got it in the cache. A subsequent call should have no consistent gets:


SQL> l
1 select /*+ result_cache */
2 *
3* from table_c
SQL> /

OBJECT_ID
----------
20
46
28
15
29
3
25
41
54

9 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4211580995

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | bamn0a75f25nx8rd4zwa55md30 | | | | |
| 2 | TABLE ACCESS FULL| TABLE_C | 9 | 117 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

1 - column-count=1; dependencies=(DOM.TABLE_C); name="select /*+ result_cache */
*
from table_c"

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

SQL>

Log on as sysdba and watch it not use the result_cache


SQL>
SQL> conn dom as sysdba
Enter password:
Connected.
SQL> set lines 120 pages 9999 autotrace on timing on
SQL> l
1 select /*+ result_cache */
2 *
3* from table_c
SQL> /

OBJECT_ID
----------
20
46
28
15
29
3
25
41
54

9 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4211580995

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TABLE_C | 9 | 117 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
521 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> show user
USER is "SYS"

About these ads

4 Responses to Why isn’t my /*+ result_cache */ working?

  1. prodlife says:

    That was unexpected.

    Especially since in Oracle’s tutorial they are demonstrating this feature with sysdba:

    http://www.oracle.com/technology/obe/11gr1_db/perform/rescache/res_cache.htm

  2. dombrooks says:

    Yeah, but I was getting tired so maybe my judgement can’t be trusted. Then again my example is pretty straightforward.

    I’ll have another look tonight, including looking at any difference I get with the tutorial you highlighted.

    And if it’s still happening I’ll see if anything turns up in an extended trace. That’s another thing to look at anyway – how does result_cache usage affect the information in 10046 / 10053 event traces.

    Maybe then I’ll eventually get to blog about my thoughts of how the result_cache hint might encourage you to write your more complex SQL with WITH subqueries fetching more static lookup data using result_cache hint…

  3. dombrooks says:

    > I’ll have another look tonight
    Change of plan. I did back-to-back spinning classes at the gym and I just need to sit back and not think. Maybe tomorrow.

  4. I just tried with 11.1.0.7 and notice that it’s using Result_Cache even while logged in as SYSDBA. Possibly this has changed in 11.1.0.7 – I don’t have 11.1.0.6 handy to test and confirm this. Please see the following:

    SQL> show user
    USER is “SYS”
    SQL> Select /*+ RESULT_CACHE */ object_name from x4006.t1 where object_id between 500 and 540;

    35 rows selected.

    Elapsed: 00:00:00.01

    Execution Plan
    ———————————————————-
    Plan hash value: 3617692013

    ————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————————-
    | 0 | SELECT STATEMENT | | 12 | 948 | 288 (1)| 00:00:04 |
    | 1 | RESULT CACHE | 5rvkp48pnszcn2wzx9cvxxsa50 | | | | |
    |* 2 | TABLE ACCESS FULL| T1 | 12 | 948 | 288 (1)| 00:00:04 |
    ————————————————————————————————-

    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    1476 bytes sent via SQL*Net to client
    546 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    35 rows processed

    P.S: I know I am responding to a year old post!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: