Forum Stats

  • 3,741,441 Users
  • 2,248,430 Discussions
  • 7,861,808 Comments

Discussions

Does Oracle provides caching for Corelated Sub-queries?

user8492264
user8492264 Member Posts: 57 Red Ribbon
edited Jun 14, 2016 2:39PM in SQL & PL/SQL

Does Oracle provides caching for Corelated Sub-queries?

So let's say I have a query like -

SELECT ..

     , NVL (( SELECT a FROM b WHERE a.a = m.a)

          , ( SELECT c FROM d WHERE c.a = m.a)

           )

     , (SELECT e FROM f WHERE e.a = m.a)

FROM m

WHERE ...;

Say there are 100 Million rows and 100 possible values for (m.a). Will Oracle cache these queries for the same run? Will this caching be available across sessions?

Or Oracle does not cache co-related subqueries?

Tagged:
Esa Samuli LaariDom BrooksBluShadow

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,102 Red Diamond
    edited Jun 13, 2016 9:00AM

    Well, the query as a whole will be effectively cached by sitting the shared pool, which saves it from being hard parsed again, assuming any session issues the exact same query, meaning that the same optimiser plan can be used.

    Aside from that, it's typically data blocks (blocks that are read from the tablespace - hence relating to physical I/O reads) that are cached, so if any part of the query requires to read the same data block that has been read elsewhere recently, it can read it from the cache, assuming it's not been aged out of the cache (which may also depend on the amount of data and cache available)

  • user8492264
    user8492264 Member Posts: 57 Red Ribbon
    edited Jun 13, 2016 9:10AM

    Thanks BluShadow!!

    I am actually concerned about the data portion only. As the number of rows returned by my query would be very large - I am assuming it would not be available in the cache unless Oracle caches it specifically. Something similar to what it does for functions in Oracle Sub-query caching.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jun 13, 2016 9:20AM
    user8492264 wrote:
    
    Something similar to what it does for functions in Oracle Sub-query caching.
    

    please post SQL  results that show above is true

  • Sven W.
    Sven W. Member Posts: 10,511 Gold Crown
    edited Jun 13, 2016 9:28AM

    Oracle has many different caching concepts, which all work together in some way.Most of the caches will work accross all sessions.

    Just to name a few:

    a) hard parse/soft parse - this is what BluShadow already mentioned

    To execute a query the optimizer will build the execution plan, how this query is executed in the most optimal way. Part of this might be query rewrites, so for example a subquery might be rewitten into a join. Once this is done, further calls for the same statement will  only result in a soft parse, not in a hard parse anymore.

    b) buffer cache - data blocks (table and index blocks) are cached in memory the first time they are loaded from disk. This is the reason why often the second execution for a query is faster than the first execution.

    c) result cache - the result for an SQL statement (can be a subquery!) can be cached and reused. This is possible for SQL statements, but also for plsql functions. In the case of functions you need to say that the function should be result cached. In the case of a SELECT it should work automatically. But you can enforce it with a hint:  /*+ result_cache */ . Whether it is working can be seen in the execution plan.

    There are some parameters that influence the size of the result cache

    SELECT name, value, description FROM v$parameter WHERE name LIKE 'result_cache%';

    I think c) is the interesting part for your select.

    Additionally you should consider to use COALESCE instead of NVL.

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited Jun 13, 2016 9:46AM

    Seems easy to test?

    Esa Samuli Laari
  • padders
    padders Member Posts: 1,053 Silver Trophy
    edited Jun 13, 2016 10:08AM

    > Does Oracle provides caching for Corelated Sub-queries?

    Yes, see http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html.

    > Will Oracle cache these queries for the same run?

    If by 'run' you mean single execution of the query then yes.

    > Will this caching be available across sessions?

    I don't believe scalar subquery caching works across sessions, or even across statements for that matter.

    Note that scalar subquery caching uses a relatively small fixed size hash table (I recall Jonathan Lewis having examples which illustrated the exact size).

    The size is probably not an issue if you have 100 values of 'a'. But then if the other relations are (by inference) small why are you not hash outer-joining to them? Scalar subqueries - although syntactically attractive - may remove options for the optimizer in earlier versions.

    Dom Brooks
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,681 Gold Crown
    edited Jun 13, 2016 11:14AM

    The mechanism is known as scalar subquery caching - here's an article I wrote about it a long time ago: https://jonathanlewis.wordpress.com/2006/11/06/filter-subqueries/

    My example uses an existence subquery to demonstrate, but the same mechanism applies to scalar subqueries in the select list.

    As padders pointed out, though the cache is very small (in the order of 1,024 entries if the inputs and outputs are numeric, fewer for strings); and as the example shows there is no collision handling so if 2 of your values collide then one of them will not be cached.

    It's also worth pointing out that the optimizer in 12c can unnest scalar subqueries in the select list, turning them into (outer) joins.  A simple guideline, though - if you've got more than one or two scalar subqueries in your select list you've probably done something wrong in developing your SQL.

    Regards

    Jonathan Lewis

    BluShadow
  • user8492264
    user8492264 Member Posts: 57 Red Ribbon
    edited Jun 14, 2016 8:35AM

    Thanks everyone for the quick response. So what I understand from the above comments is that Oracle does caches the Subquery ( I was afraid it did so only for functions) which is great. My sub-query usually return <100 rows so I hope that would work fine.

    [Jonathan]: It's also worth pointing out that the optimizer in 12c can unnest scalar subqueries in the select list, turning them into (outer) joins. 

    [Response]: I'm aware of that and in hope that Oracle would unnest my subqueries and tune it, I added more than one or two subqueries.


    [Jonathan]: A simple guideline, though - if you've got more than one or two scalar subqueries in your select list you've probably done something wrong in developing your SQL.

    [Response]: Thanks for the guideline! I do understand that now and I am trying to move my sub-queries out as Outer join.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jun 14, 2016 2:39PM
    user8492264 wrote:
    
    Thanks everyone for the quick response. So what I understand from the above comments is that Oracle does caches the Subquery ( I was afraid it did so only for functions) which is great. My sub-query usually return <100 rows so I hope that would work fine.
    
    [Jonathan]: It's also worth pointing out that the optimizer in 12c can unnest scalar subqueries in the select list, turning them into (outer) joins.  
    [Response]: I'm aware of that and in hope that Oracle would unnest my subqueries and tune it, I added more than one or two subqueries.
    
    [Jonathan]: A simple guideline, though - if you've got more than one or two scalar subqueries in your select list you've probably done something wrong in developing your SQL. [Response]: Thanks for the guideline! I do understand that now and I am trying to move my sub-queries out as Outer join.

    Your subqueries here are in the select part of the statement, they can only return 1 row per execution otherwise you are trying to fit multiple rows into one field.

    Just because the scalar subquery results are cached doesn't mean the query is automatically fast. The overhead on fetching from the cache could add up, and some results may not be cached due to collisions.
    It is usually an indicator of lazy sql so I'd always be suspicious about the performance.

    You can look at row source level statistics (google gather_plan_statistics hint) to report on the impact of your scalar subquery and other parts of the plan.

    You can tell if Oracle has unnested your scalar subquery by viewing the execution/explain plan for the query.

This discussion has been closed.