This discussion is archived
11 Replies Latest reply: Jan 13, 2012 8:59 AM by Dom Brooks RSS

+materialize causes I/O ?

xerces8 Newbie
Currently Being Moderated
We use a query like this on Oracle 11.2g (in a PL/SQL stored procedure):
WITH x AS ( SELECT /*+materialize*/ FK1ID, FK2Id, FK3Id  FROM SOME_VIEW  WHERE some_ID = p_some_parameter) -- an IN parameter of the stored procedure
  SELECT  foo, bar, baz FROM t1, t2, ...
  WHERE exists (
    select 1 from  x where 
        x.FK1ID= 42
      or
        x.FK1ID = 34 and
        x.FK2ID = t2.xyz)
    AND some other conditions
This is much faster (50 times) than without the +materialize hint or if we put that (sub)query in line.

But apparently it causes extra I/O (writing the subquery result to temporary table and reading it back).

Is that true? If yes, what else to use instead then?

Regards,
David

PS: The subquery would have 10-20 rows, while the main query would return tens of thousands of rows in our test case.
  • 1. Re: +materialize causes I/O ?
    Centinul Guru
    Currently Being Moderated
    xerces8 wrote:

    This is much faster (50 times) than without the +materialize hint or if we put that (sub)query in line.

    But apparently it causes extra I/O (writing the subquery result to temporary table and reading it back).

    Is that true? If yes, what else to use instead then?
    I'd be interested in seeing the unhinted execution plan output from this query run with the GATHER_PLAN_STATISTICS hint. See {message:id=9360003} for details.
  • 2. Re: +materialize causes I/O ?
    Lakmal Rajapakse Expert
    Currently Being Moderated
    Are you sure the SQL is structured properly - should it not be lilke this instead:
    WITH x AS ( SELECT /*+materialize*/ FK1ID, FK2Id, FK3Id  FROM SOME_VIEW  WHERE some_ID = p_some_parameter) -- an IN parameter of the stored procedure
    SELECT  foo, bar, baz FROM t1, t2, ...  
    WHERE exists (
        select 1 from  x 
        where  ( x.FK1ID= 42      or        x.FK1ID = 34)     --- > the OR condition is put in brackets
        and x.FK2ID = t2.xyz)    
    AND some other conditions
    I which case you might not need the materialize hint...

    Edited by: Lakmal Rajapakse on 13-Jan-2012 05:44
  • 3. Re: +materialize causes I/O ?
    Dom Brooks Guru
    Currently Being Moderated
    But apparently it causes extra I/O (writing the subquery result to temporary table and reading it back).
    Is that true?
    Yes, it's true that writing the result to a memory table causes IO

    If yes, what else to use instead then?
    Sounds like you already have an answer:
    This is much faster (50 times) than without the +materialize hint or if we put that (sub)query in line.
    The materialize hint forces the materialization of the subquery.
    Without hinting it, this is something that will happen automatically usually when you reference it twice in subsequent selects.
    As ever, there's a balance.

    Why would we want to force a materialisation of a subquery referenced once?
    In general, it seems like something that should be avoided and treated with a no_merge or a no_unnest or whatever is appropriate for the issue that the materialize hint was trying to solve.

    As Centinul suggests, execution plans would be a useful addition to the post.

    I would double check those predicates in the subquery as well.

    You have :
            x.FK1ID= 42
          or
            x.FK1ID = 34 and
            x.FK2ID = t2.xyz
    Do you not want this?
           (x.FK1ID= 42
          or
            x.FK1ID = 34) and
            x.FK2ID = t2.xyz
    or even

           (x.FK1ID IN (42,34) and
            x.FK2ID = t2.xyz
    At the moment, you have something more akin to:
    WITH x AS ( SELECT /*+materialize*/ FK1ID, FK2Id, FK3Id  FROM SOME_VIEW  WHERE some_ID = p_some_parameter) -- an IN parameter of the stored procedure
      SELECT  foo, bar, baz FROM t1, t2, ...
      WHERE exists (
        select 1 from  x where 
            x.FK1ID= 42
       union all
        select 1 from  x where 
            x.FK1ID = 34 and
            x.FK2ID = t2.xyz)
    Even if the code is correct, the latter wording is at least perhaps clearer about it.
  • 4. Re: +materialize causes I/O ?
    xerces8 Newbie
    Currently Being Moderated
    Without the materialize hint the query is much slower.

    The (possible) problem here is, that in some cases (input parameters, table content,...) the extra I/O due to the materialize hint might slow down instead of speed up a query.

    The subquery is referenced only once, but without the hint Oracle chooses a much worse execution.

    The subquery would typically have 10 to 100 rows, the values are INTEGER, so it should fit just fine in RAM.

    So, the question is: How to make Oracle execute the subquery only once (per procedure call, it only contains the mentioned query and the result goes out via a sys_refcursor OUT parameter), but not make I/O access for it (to store it, of course read I/O for the query itself is unavoidable).

    Some other hint? (are they documented anywhere?)


    Currently I can not get an execution plan, as I get:
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    User has no SELECT privilege on V$SESSION
    What I did was to remove the materialize hint in the WITH subquery and add a /*+ gather_plan_statistics */ hint to the main SELECT, upload the new procedure definition, call it, read the cursor (PL/SQL with FECTH in a LOOP over the returned cursor) and then the above SELECT to show the results. Is that correct?
  • 5. Re: +materialize causes I/O ?
    xerces8 Newbie
    Currently Being Moderated
    PS: The conditions are correct.

    Here with extra parenthesis:
    x.FK1ID= 42
          or
    (x.FK1ID = 34 and x.FK2ID = t2.xyz)
  • 6. Re: +materialize causes I/O ?
    Dom Brooks Guru
    Currently Being Moderated
    Without the materialize hint the query is much slower.
    Ok. I misread what the issue was first time round.

    I don't understand the problem.
    Materialize gives you good performance gains (50x).
    But you want the benefit of materialize without the mechanism it uses to achieve that?


    The absence of any execution plan makes it hard to comment further.
    You might want to try a no_unnest in your subquery.
    It's possible you might need a no_expand to stop the OR predicates being expanded.

    e.g.
      SELECT /*+ no_unnest (subq) */ foo, bar, baz FROM t1, t2, ...
      WHERE exists (
           SELECT /*+ qb_name(subq) */ 1  FROM SOME_VIEW  WHERE some_ID = p_some_parameter
           AND ((
            x.FK1ID= 42)
          or
            (x.FK1ID = 34 and
            x.FK2ID = t2.xyz)))
        AND some other conditions
  • 7. Re: +materialize causes I/O ?
    €$ħ₪ Expert
    Currently Being Moderated
    Looks like issue is with the iterations when you do Exists ...
    Can't you join directly the sub query to get the desired results ...probably no_unnest may not give you what you expect but try with hint such as leading on the small dataset ...

    also check when you use the hint materialize you get the output like first 500 rows or the entire data set for that query.
  • 8. Re: +materialize causes I/O ?
    xerces8 Newbie
    Currently Being Moderated
    Dom Brooks wrote:
    Without the materialize hint the query is much slower.
    Ok. I misread what the issue was first time round.

    I don't understand the problem.
    Materialize gives you good performance gains (50x).
    But you want the benefit of materialize without the mechanism it uses to achieve that?
    No, I just want to clarify the following:
    - does materialize always save the temporary table contents to external storage? (and rereads it?)

    If yes:
    - how to avoid that I/O? As mentioned, the subquery result is like 100 or less rows of 3 integers, so not even a single disk sector, so what benefit is there in saving it to permanent storage (disk)? I am looking for a way to just keep it in RAM for the duration of the query.

    If no: Then the thread is solved and can be closed.

    Edited by: xerces8 on Jan 13, 2012 4:55 PM
  • 9. Re: +materialize causes I/O ?
    Dom Brooks Guru
    Currently Being Moderated
    I've not tested this extensively nor checked if there are any changes across versions.
    does materialize always save the temporary table contents to external storage? (and rereads it?)
    Yes.

    Regardless of size - i.e. even with one row & one column - typically what you will find with usage of the materialize hint, is that a temporary table will be created.

    Typically the data will be inserted into the temp table using a direct path write operation then re-read back via the buffer cache.

    The temporary table does not need to be created with every execution of the query.
    It can be reused by subsequent re-executions of the query if it exists, whether those re-executions are by the same or a different session.
    But at some point - possibly/probably in relation to the parent cursor - it will be cleaned up

    You should be able to verify this behaviour using a 10046 trace.
    how to avoid that I/O?
    Don't use materialize. Try a different approach, for example no_unnest as mentioned.

    Edited by: Dom Brooks on Jan 13, 2012 4:38 PM
  • 10. Re: +materialize causes I/O ?
    xerces8 Newbie
    Currently Being Moderated
    Both no_merge and no_unnest are slow (more or less like the original unoptimized query, takes minutes).

    So I guess we'll live with materialize and a few extra I/O.
  • 11. Re: +materialize causes I/O ?
    Dom Brooks Guru
    Currently Being Moderated
    Both no_merge and no_unnest are slow
    I would be very surprised if there wasn't another way but, as mentioned, execution plans are essential to be able to comment any further.

    If no_unnest or no_merge are not being obeyed, it may be because the hint has been specified incorrectly to be valid after the query transformation stage.
    See http://jonathanlewis.wordpress.com/2011/06/08/how-to-hint-1/

    But it all depends on the actual plans.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points