Forum Stats

  • 3,782,317 Users
  • 2,254,637 Discussions
  • 7,880,045 Comments

Discussions

Reg: SQL Result Cache -

ranit B
ranit B Member Posts: 3,268 Silver Trophy
edited Feb 20, 2016 1:15PM in SQL & PL/SQL

Hi Experts,

I was reading this article on SQL Result Cache, and trying to incorporate this in one of our application.

Problem -

We have a java application which has a selection button. On pressing the button, a pop up comes which displays a list of values. This list is fetched by executing a SQL query on the database.

It's a simple query involving 2 tables. But, one of the table contains 35M records and index is also getting appropriately used. (But, one thing- the table data gets changes once in 3 or 4 months.)

Still it takes 20s to display the result in the popup...

Now, i'm just trying to use /*+ RESULT_CACHE */ in the query.

Will it be beneficial ? Any caveats ?

Thanks and Regards,

-Ranit

( on Oracle 11.2.0.4.0 )

TubbyAndrewSayer
«1345

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Feb 12, 2016 9:53AM
    ranit B wrote:
    
    Hi Experts,
    
    I was reading this article on SQL Result Cache, and trying to incorporate this in one of our application.
    
    Problem -
    We have a java application which has a selection button. On pressing the button, a pop up comes which displays a list of values. This list is fetched by executing a SQL query on the database.
    It's a simple query involving 2 tables. But, one of the table contains 35M records and index is also getting appropriately used. (But, one thing- the table data gets changes once in 3 or 4 months.)
    
    Still it takes 20s to display the result in the popup...
    
    Now, i'm just trying to use /*+ RESULT_CACHE */ in the query.
    
    Will it be beneficial ? Any caveats ?
    
    Thanks and Regards,
    -Ranit
    ( on Oracle 11.2.0.4.0 )
    

    I think the only real caveat is that you would want to ensure things are sized properly. You say the table has 35 million records but you don't tell us how many survive the filters and are returned to the client. You also have to consider that the result cache is going to be dependent on the inputs to the query, so how many variants are you going to have? If you have a new set of filters every time you execute the query the result cache isn't going to help you any. Also, if this is an infrequently executed query and you have other things filling up the result cache it's possible these results get aged out, so execution frequency is going to be another consideration for you.

    Cheers,

  • Unknown
    edited Feb 12, 2016 10:53AM
    ranit B wrote:
    
    Hi Experts,
    
    Now, i'm just trying to use /*+ RESULT_CACHE */ in the query.
    
    Will it be beneficial ? Any caveats ?
    
    Thanks and Regards,
    -Ranit
    ( on Oracle 11.2.0.4.0 )
    
    
    

    You tell us since we don't have your tables or data.

    What happens for first session to run the query?

    >Caveats?

    Avoids hints in Production code.

    post URL where is recommends to use hint in Production code.

  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited Feb 12, 2016 9:57PM

    Hi Sol,

    *1

    The first time it will take the normal time of ~20s, but then onwards, it will be using the cache.

    *2

    Agreed that hints shouldn't be used in actual prod.

    But, as TomKyte says, there are 2 kinds of hints : good ones and bad ones.

    I guess, /*+ result_cache */ is a good hint. Can't really think of any scenario where the end user is affected.

    Your thoughts please...

  • Unknown
    edited Feb 12, 2016 10:08PM
    ranit B wrote:
    
    I guess, /*+ result_cache */ is a good hint. Can't really think of any scenario where the end user is affected.
    

    post EXPLAIN PLAN & tkprof results for query run twice; one with HINT & one run without HINT.

    I seriously doubt that /*+ RESULT_CACHE */ hint has any impact.

    If the result set is not in CACHE, the HINT does not matter.

    if the result set is in cache, the HINT is not necessary.

  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited Feb 12, 2016 10:35PM

    Hi Tubby,

    >ensure things are sized properly. 

    Didn't get this part exactly.

    >You say the table has 35 million records but you don't tell us how many survive the filters and are returned to the client. 

    Around 5K.

    >You also have to consider that the result cache is going to be dependent on the inputs to the query, so how many variants are you going to have? 

    Ok, let me quickly brief you about this part...

    There are 3 radio buttons"X", "Y" and "Z" , X on line 5 of application screen, and Y/Z on line 8.

    Button X is responsible for including the big table in query FROM clause.

    Button Y/Z is responsible for WHERE filter clause.

    So, as of now, just 2variants of that query.


    >If you have a new set of filters every time you execute the query the result cache isn't going to help you any.


    Just 2 variants as of now.

    Moreover, result_cache just stores o/p of queries, right? Not the table data directly.. So if there are 5 filters, all 5 o/ps will be cached.

  • Unknown
    edited Feb 12, 2016 10:19PM

    what is size of each of the five different result sets?

    what is the size the CACHE?

    what other results sets go into the CACHE?

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited Feb 13, 2016 12:04AM
    I seriously doubt that /*+ RESULT_CACHE */ hint has any impact.
    If the result set is not in CACHE, the HINT does not matter.
    if the result set is in cache, the HINT is not necessary.
    

    result_cache feature does not find data in buffer cache area. it actually picks results of same queue from result cache area in shared pool, which make is better and faster.

    A portion of the execution plan of query might look like the following:

    -------------------------------------------------------------

    | Id | Operation          | Name                       |Rows

    --------------------------------------------------------------

    | 0 | SELECT STATEMENT    |                            | 11

    | 1 |  RESULT CACHE       | 8fpza04gtwsfr6n595au15yj4y |

    | 2 |   HASH GROUP BY     |                            | 11

    | 3 |    TABLE ACCESS FULL| EMPLOYEES                  | 107

    --------------------------------------------------------------

    check following link

    Oracle Database 11g: The Top New Features for DBAs and Developers | Caching and Pooling

  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited Feb 13, 2016 12:33AM

    Exactly Nimish.

    I don't have any DB around me now. So, couldn't reply to Sol.

    Guess, Result Cache is another caching layer introduced which is checked even before going to Database Buffer Cache, while fetching data.

    But interestingly, the query cost doesn't get change.

    Edit:

    Also, i think, the below 2 queries will store data separately in Result Cache -

    SELECT col1, col2 FROM my_table WHERE col3 = 'X'; -- 400 rows

    SELECT col1, col2 FROM my_table WHERE col3 = 'Y'; -- 500 rows


    We can check this for cache usage ( below query might error out no DB to check currently ):

    SELECT * FROM v$parameters WHERE parameter like 'result_cache%' ;

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited Feb 13, 2016 5:22AM

    You don't need to guess, just read the article.

    Look at the stats and execution time. it almost goes down to 0

    Statistics
    ----------------------------------------------------------
      0 recursive calls
      0 db block gets
      0 consistent gets
      0 physical reads
      0 redo size
      760 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)
    rows processed

    ranit B
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Feb 13, 2016 11:57AM
    ranit B wrote:
    
    Hi Tubby,
    
    
    >ensure things are sized properly. 
    
    Didn't get this part exactly.
    >You say the table has 35 million records but you don't tell us how many survive the filters and are returned to the client. 
    
    Around 5K.
    
    >You also have to consider that the result cache is going to be dependent on the inputs to the query, so how many variants are you going to have? 
    Ok, let me quickly brief you about this part...
    There are 3 radio buttons"X", "Y" and "Z" , X on line 5 of application screen, and Y/Z on line 8. 
    Button X is responsible for including the big table in query FROM clause.
    Button Y/Z is responsible for WHERE filter clause.
    So, as of now, just 2variants of that query.
    
    >If you have a new set of filters every time you execute the query the result cache isn't going to help you any.
    Just 2 variants as of now. Moreover, result_cache just stores o/p of queries, right? Not the table data directly.. So if there are 5 filters, all 5 o/ps will be cached.

    1) you need to make sure the result cache (the thing you are taking advantage of with the RESULT_CACHE hint) has enough space allocated. This is what I mean by "things are sized properly".

    https://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#TGDBA630

    2) the number of variants of the query wasn't what I was asking for but it will be relevant. What I was asking for is the INPUTS to the query(s). If those are constantly changing from execution to execution then the benefit you see from the result cache will be 0. You need to understand your application and how the data is queried. You may find the result cache will improve things enormously, you may find there's 0 benefit because inputs are never the same between executions (or they are but very infrequently and the cache ages out the previous execution before the next occurs).

    To be clear though, sounds like more than 2 variants. Because you have with / without "big table" (button x) and then you have the predicates added by button Y/Z which I'm not sure how that works, maybe they're mutually exclusive or not, still... more than 2 variants

    3) the cache stores 

       a) the query plan

       b) the inputs to the query (bind variables)

       c) the results of the query

    Conceptually speaking at least.

    Cheers,

    ranit B
This discussion has been closed.