Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Panos.

Asked: April 27, 2016 - 5:36 pm UTC

Last updated: June 20, 2018 - 12:16 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Dear Tom,

I am thinking to use the new feature "RESULT_CACHE" to optimize some search queries for my paginated pages.

So far, for a search page I have :
1.) a count query and
2.) the query that returns a page from the result set

Both 1 and 2 queries use a common subquery that all the filters are applied.
This query returns e.g the ids of a table e.g. CUSTOMERS.
For example:
1.)
SELECT count(1) FROM
(
SELECT /*+ RESULT_CACHE */ id
FROM CUSTOMERS
WHERE PHONE like '30%'
)

and
2.)
SELECT * FROM
(SELECT /*+ first_rows(25) */
PgdRESView.*,
ROWNUM QRYRESRNUM
FROM
(
SELECT c.NAME, c.PHONE, cd.SOME_TEXT, ...
FROM
(
SELECT /*+ RESULT_CACHE */ id
FROM CUSTOMERS
WHERE PHONE like '30%'
) filtered_rs
JOIN CUSTOMER c ON c.ID = filtered_rs.ID
JOIN CUSTOMER_DETAILS cd ON cd.CUSTOMER_ID = filtered_rs.ID
ORDER BY c.NAME ASC
) PgdRESView
WHERE ROWNUM <= 10
)
WHERE QRYRESRNUM >= 1;

First of all, I want to know if you see any problem with this approach.
I suppose that the second query there's a 99% chance (I hope so) to find the results in the cache (because the count query runs first).
But, the problem is that I see 2 cached objects (with the same name and different cache_key) after executing those queries.
Why? How I can force the second query to get always the results from the first cached object?

and Connor said...

Can you send me a test case based on the above ? Using a simpler test case, I see the result cached set being re-used as expected, ie


SQL> set autotrace off
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> set autotrace on
SQL> select count(*)
  2  from
  3   ( select /*+ result_cache */ owner, count(*) cnt
  4     from   t
  5     group by owner
  6     order by owner
  7   )
  8
SQL> /

  COUNT(*)
----------
        29


Execution Plan
----------------------------------------------------------
Plan hash value: 19060921

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |     1 |       |   447   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                            |     1 |       |            |          |
|   2 |   VIEW                |                            |    29 |       |   447   (1)| 00:00:01 |
|   3 |    RESULT CACHE       | 1kbrtdwcszttxdkk3m4v7pypgm |       |       |            |          |
|   4 |     SORT GROUP BY     |                            |    29 |   203 |   447   (1)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T                          | 95089 |   650K|   444   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - column-count=2; dependencies=(MCDONAC.T); parameters=(nls); name="select /*+ result_cache */ owner, count(*) cnt
   from   t
   group by owner
   order by owner
 "



Statistics
----------------------------------------------------------
         74  recursive calls
          0  db block gets
       1632  consistent gets
       1596  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

  COUNT(*)
----------
        29


Execution Plan
----------------------------------------------------------
Plan hash value: 19060921

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |     1 |       |   447   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                            |     1 |       |            |          |
|   2 |   VIEW                |                            |    29 |       |   447   (1)| 00:00:01 |
|   3 |    RESULT CACHE       | 1kbrtdwcszttxdkk3m4v7pypgm |       |       |            |          |
|   4 |     SORT GROUP BY     |                            |    29 |   203 |   447   (1)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T                          | 95089 |   650K|   444   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - column-count=2; dependencies=(MCDONAC.T); parameters=(nls); name="select /*+ result_cache */ owner, count(*) cnt
   from   t
   group by owner
   order by owner
 "



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

SQL>
SQL> select *
  2  from
  3   ( select /*+ result_cache */ owner, count(*) cnt
  4     from   t
  5     group by owner
  6     order by owner
  7   )
  8  where rownum < 5
  9
SQL> /

OWNER                                 CNT
------------------------------ ----------
APEX_040200                          3417
APEX_050000                          3600
APPQOSSYS                               5
AUDSYS                                 12


Execution Plan
----------------------------------------------------------
Plan hash value: 2490108855

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |     4 |   316 |   447   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY        |                            |       |       |            |          |
|   2 |   VIEW                |                            |    29 |  2291 |   447   (1)| 00:00:01 |
|   3 |    RESULT CACHE       | 1kbrtdwcszttxdkk3m4v7pypgm |       |       |            |          |
|   4 |     SORT GROUP BY     |                            |    29 |   203 |   447   (1)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T                          | 95089 |   650K|   444   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<5)

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

   3 - column-count=2; dependencies=(MCDONAC.T); parameters=(nls); name="select /*+ result_cache */ owner, count(*) cnt
   from   t
   group by owner
   order by owner
 "



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

SQL> /

OWNER                                 CNT
------------------------------ ----------
APEX_040200                          3417
APEX_050000                          3600
APPQOSSYS                               5
AUDSYS                                 12


Execution Plan
----------------------------------------------------------
Plan hash value: 2490108855

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |     4 |   316 |   447   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY        |                            |       |       |            |          |
|   2 |   VIEW                |                            |    29 |  2291 |   447   (1)| 00:00:01 |
|   3 |    RESULT CACHE       | 1kbrtdwcszttxdkk3m4v7pypgm |       |       |            |          |
|   4 |     SORT GROUP BY     |                            |    29 |   203 |   447   (1)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T                          | 95089 |   650K|   444   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<5)

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

   3 - column-count=2; dependencies=(MCDONAC.T); parameters=(nls); name="select /*+ result_cache */ owner, count(*) cnt
   from   t
   group by owner
   order by owner
 "



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

SQL>
SQL>
SQL>
SQL>


but your approach is sound...in the general, a result cache query is a bit like an "on demand materialized view" so it *can* be used as a 'component' of larger queries.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Panos Grigoropoulos, May 04, 2016 - 8:33 am UTC


Panos Grigoropoulos, May 04, 2016 - 8:42 am UTC


sadly this doesnt work in 11gR2

Malcolm, June 18, 2018 - 10:48 am UTC

I replicated Connor's example, and yes if you repeat the same query, you see 0 gets in the autotrace stats.

However, I find that if I simply add a comment to the outer query, we no longer get the result fetched from the cache. I don't have a 12c to hand at the moment to try it there - can you verify that it does work in 12c but not in 11gR2

select /* connor123 */ count(*) from (select /*+ result_cache .....

followed by

select /* connor456 */ count(*) from (select /*+ result_cache .....

I have a Java app - that handles 120 different slices of a set of data - and I would like to build the full set once, and have each of the ora_hash'd slices fetched from the cache. It would be rather nice if the result_cache could hold a subquery result in 11gR2

Connor McDonald
June 19, 2018 - 3:51 am UTC

No, it works but it take a little bit of care (with any version).

Typically the database will apply transformations to a query to simplify it before execution. As such your inline view (and hence the result cache) may evaporate before the query is even run, eg when I did an optimizer trace on:

select /* connor456 */ count(*) from 
     ( select /*+ result_cache */ owner, count(*) cnt
       from   t
       group by owner
       order by owner
     )


I see this in the trace file

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OWNER" "OWNER",COUNT(*) "CNT" FROM "MCDONAC"."T" "T" GROUP BY "T"."OWNER" ORDER BY "T"."OWNER"


We got rid of all the "redundant" inlining etc to make things "better"...

So if we know better, and we to keep that inline view as a standalone thing, we can tell the optimizer this, 

SQL> set autotrace on stat
SQL> select /*+ no_merge(@rc) connor123 */ count(*) from
  2       ( select /*+ qb_name(rc) result_cache  */ owner, count(*) cnt
  3         from   t
  4         group by owner
  5         order by owner
  6       )
  7  /

  COUNT(*)
----------
        31


Statistics
----------------------------------------------------------
         64  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> /

  COUNT(*)
----------
        31


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


and our result caching is back again

great - one more remaining doubt

Malcolm, June 19, 2018 - 11:43 am UTC

Thanks Connor, I tried in another example, with a larger underlying query - and its obviously saving a lot of gets.

I still find it interesting that I observe

100+ gets reported when fetching from the cached_result with a different comment

0 gets reported when I re-use a previous comment - see below

Why would that be?

SQL> select /*+ no_merge(@rc) connor123 */ count(*) from ( select /*+ qb_name(rc) result_cache */ owner, count(*) cnt from t group by owner order by owner);
.....
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets

SQL> select /*+ no_merge(@rc) connor999 */ count(*) from ( select /*+ qb_name(rc) result_cache */ owner, count(*) cnt from t group by owner order by owner);

COUNT(*)
----------
73
.....
Statistics
----------------------------------------------------------
115 recursive calls
4 db block gets
141 consistent gets

Connor McDonald
June 20, 2018 - 12:16 am UTC

Its a new query, so we have to parse it. So there'll potentially be a few queries to dictionary tables.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions