Materialize

Summary – Note the recursive SQL, the association of an in-memory temporary table with a child cursor, and possible side-effects for distributed transactions.

Prompted by a recent thread on the OTN forums, if you /*+ materialize */ a subquery will you always get IO associated with that materialisation?

Short answer: Yes. For that is what materialisation is all about.

A longer answer is perhaps slightly more interesting.

In terms of materialisation, you can force it with the hint above or it will automatically kick in if you reference it at least twice. I’m not aware of this threshold being documented but Jonathan Lewis mentioned this observation here and it ties in with what I’ve seen.

And it doesn’t seem to matter how small the result set is, it will always be materialised if those materialisation criteria are met.

If we trace a new query, we can see some of the recursive sql involved.

SQL> alter session set events '10046 trace name context forever, level 12';
SQL>
SQL> with x as
  2  (select /*+ materialize */
  3          1 col1
  4   from   dual)
  5  select * from x;

      COL1
----------
         1

SQL> alter session set events '10046 trace name context off';
SQL>

Tracing older versions of Oracle can be more revealing because in 9iR2 for example the trace file explicitly lists the recursive INSERT into the temp table, whereas by the time you get to 11.2 the INSERT has disappeared and the associated waits incorporated into the SELECT.

All versions list the creation of the temporary table (if indeed it needs to be created – see below), the DDL for which includes the specifications IN_MEMORY_METADATA and CURSOR_SPECIFIC_SEGMENT.

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6610_8A97FC" ("C0" NUMBER )
   IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950928 )
  NOPARALLEL

Note in the creation of table SYS_TEMP_0FD9D6610_8A97FC that 0FD9D6610 is the hex of 4254950928, which is just the sequence-based objno. Not sure of the significance of the last part, e.g.8A97FC.

We can also see that the data is written to temp using a direct path write/direct path write temp depending on version … and selected back via the buffer cache (for efficient use of the data) using a db file sequential read or db file scattered read.

In older versions as mentioned, you should find the recursive INSERT listed separately, e.g. (different database, different version, different temp table name and if you’re interested in the control file sequential read see this post by Timur Akhmadeev):

INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO 
  "SYS"."SYS_TEMP_0FD9D662B_671BC5CD" SELECT /*+ */ 1 FROM "SYS"."DUAL" 
  "DUAL"

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                    3        0.00          0.00
  direct path write                               1        0.00          0.00
********************************************************************************

Otherwise in newer versions, no insert but the waits for the recursive statement listed as part of the main select:

with x as
(select /*+ materialize */
        1 col1
 from   dual)
select * from x 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  direct path write temp                          1        0.00          0.00
  direct path sync                                1        0.02          0.02
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                         1        0.00          0.00
  SQL*Net message from client                     2       14.45         14.45
********************************************************************************

The temp table exists in memory and our session and other sessions cannot describe it but can select from it:

SQL> desc  sys.SYS_TEMP_0FD9D6610_8A97FC;
ERROR:
ORA-04043: object sys.SYS_TEMP_0FD9D6610_8A97FC does not exist


SQL> select * from sys.SYS_TEMP_0FD9D6610_8A97FC;

no rows selected

SQL> 

Note that the temp table is associated with the child cursor. This can be observed by using multiple sessions and forcing the creation of multiple child cursors – for example by using different optimizer settings – and tracing those sessions.

Subsequent executions of this cursor – by this session or another – can reuse this existing in-memory temporary table with no need to recreate it.

So, if we ran into one of the numerous situations that exist – often caused by bugs – where there are excessive child cursors for sql statements, if these use materialised subqueries then this is something else to be slightly concerned about.

If the cursor ages out or we flush the shared pool, the table will be cleaned up along with the cursor.

SQL> alter system flush shared_pool;
SQL> select * from sys.SYS_TEMP_0FD9D6610_8A97FC;
select * from sys.SYS_TEMP_0FD9D6610_8A97FC
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 

This recursive creation of the temp table might raise some interesting questions. For example, how this (recursive DDL) might affect / be affected by transactions?

Short answer: It does and it doesn’t

The longer answer is that it only seems to affect distributed transactions and this effect is apparently a bug or bugs, separately listed in both 10.2 – bug 9399589 – and 11.1/11.2 – bug 9706532.

I’ve not tested the proposed patches to the issue, but certainly what happens in 11.2.0.3 is that if you hard-parse the statement as part of a distributed transaction, then the materialisation is silently bypassed (fixed in 12c)

SQL> alter system flush shared_pool;
SQL> -- distributed transaction
SQL> insert into t1@test values(1);
SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> commit;
SQL> -- no distributed transaction
SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1
SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> 


Whereas if it’s a local transaction that does the hard-parse then materialisation can be used and subsequent executions of that cursor in a distributed transaction can make use of that plan and the existing temp table.

SQL> alter system flush shared_pool;

System altered.

SQL> -- no distributed transaction
SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 3267439756

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |       |       |     4 (100)|
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |
|   2 |   LOAD AS SELECT           |                           |       |       |            |
|   3 |    FAST DUAL               |                           |     1 |       |     2   (0)|
|   4 |   VIEW                     |                           |     1 |     3 |     2   (0)|
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6604_8B16D2 |     1 |    13 |     2   (0)|
---------------------------------------------------------------------------------------------


18 rows selected.

SQL> -- distributed transaction
SQL> insert into t1@test values(1);

1 row created.

SQL> with x as
  2  (select /*+ materialize */
  3          1
  4   from   dual)
  5  select * from x;

         1
----------
         1

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID  0x0a8cyg22wz7, child number 0
-------------------------------------
with x as (select /*+ materialize */         1  from   dual) select *
from x

Plan hash value: 3267439756

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |       |       |     4 (100)|
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |
|   2 |   LOAD AS SELECT           |                           |       |       |            |
|   3 |    FAST DUAL               |                           |     1 |       |     2   (0)|
|   4 |   VIEW                     |                           |     1 |     3 |     2   (0)|
|   5 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6604_8B16D2 |     1 |    13 |     2   (0)|
---------------------------------------------------------------------------------------------


18 rows selected.

SQL> 

I saw on Twitter last week and this week that @Boneist had an interesting experience with this sort of thing.

Finally, as a quick related distraction, note that if you try to get a real time sql monitoring report within a distributed transaction – I mean, why would you? but anyway I found this whilst investing the distributed behaviour above – then it will bomb out with ORA-32036: unsupported case for inlining of query name in WITH clause.

About these ads

15 Responses to Materialize

  1. Boneist says:

    Yes, it was a highly interesting experience! Well, I might have used another word for it, but hey ho *{;-)

    Re. “Whereas if it’s a local transaction that does the hard-parse then materialisation can be used and subsequent executions of that cursor in a distributed transaction can make use of that plan and the existing temp table.”

    That wasn’t my experience – when the connection that was logging in with the distributed connection set, it got a different plan, even though I had run the query in a local transaction and had obtained the materialzed plan!

    It’s definitely something to watch out for!

    • Dom Brooks says:

      Same ballpark at least – distributed transactions and materialised subqueries – caveat developer/dba.

      I’ve shown above that in 11.2.0.3 at least that a distribution transaction can re-use an existing materialisation plan. It may be that things change between versions – you were 10.2 I believe? – or of course there can be other reasons why existing cursors cannot be shared.

      Your issue was good timiong for me because, whilst doing this post, I’d raised the question to myself about when this temp table creation might be an issue and had looked at a few local transactions and seen no issue. Distributed transactions hadn’t even crossed my mind.

      Ta.

      • Boneist says:

        Aah, yes, of course; the version difference might mean different behaviour! D’oh.

        It would be even more tricky to have diagnosed if the “good” plan could have been reused I think; at least the behaviour was consistent on my db – when it was called through the python script, my SQL refused point blank to materialize, even with an existing materialized plan already there!

      • Dom Brooks says:

        I can get similar behaviour on 9.2.0.8, 11.2.0.2 and 11.2.0.3.

        Not that that means anything other than materialisation may be affected inconsistently by distributed transactions depending perhaps on version and a bunch of other unknown things.

        Your bug – 9399589 – talks about an issue with a global transaction or just the setting of attributes OCI_ATTR_EXTERNAL_NAME / OCI_ATTR_INTERNAL_NAME.

        The other bug – 9706532 – is 11g specific, talks about the issue with OCI_ATTR_EXTERNAL_NAME being resolved by a separate fix in 11.2.0.1 but mentions that materialization will still fail for a PLSQL RPC over a DB LINK.

        What I showed above is not a PLSQL RPC call over a DB LINK, so it might be related, might be not.

        Conclusion – materialisation and distributed transactions – dodgy and possibly not surprising.

        As you say, having a consistent issue – i.e. with consistently different plans – was probably helpful. Then you only need to ask the question why? Rather than two questions – 1. Why? and 2. Why only sometimes?

        No-one likes an intermittent blighter.

      • Boneist says:

        “Not that that means anything other than materialisation may be affected inconsistently by distributed transactions depending perhaps on version and a bunch of other unknown things.”

        I wouldn’t mind if the decision was logged somewhere… it certainly wasn’t even mentioned in the 10053; it would have been lovely to have something that said “Can’t materialize” instead of it silently not doing it!

        “No-one likes an intermittent blighter.” *Amen* to that!

        My issue didn’t involve database links either, just python connecting to a database. I’m not convinced that those two attributes are not set, but we could not find them – they weren’t in the python scripts, or the environment/sys variables on the server!

        Fun times *{;-)

  2. Dominic,
    It took me 3-4 reading before understanding the basic problem :) Does it means that verification of plans from Distributed transactions needs extra attention as may due to hitting the bug we get wrong plan. In such scenarios , filing a bug with Oracle is only solution or something else

  3. Dom Brooks says:

    Depends – if materialization is key to a piece of code being performant and that code is used in distributed transactions, then this could be an issue for you.

    But then again combining different features often reveals edge cases and issues.

    All depends on why the materialization is effective.

    Although the main benefit of using materialisation is meant to be a reduction in IO for repeated subqierues, I think a lot of the times people see a benefit because they’re using it as a substitute for an orthodox subquery and perhaps no_merge or no_unnest depending on the code.

  4. Pingback: /*+ materialized */ hint and ORA-04091 error «

  5. hourim says:

    Hi Dom,

    Recently I was trying to understand why an insert/select that was working well in 10g starts failing in error in 11g. It is an insert into a table(t) using a function(f) that selects from this inserted table(t). Until I reached the point stating that if you use the /*+ materialize */ hint in this kind of situations the 11g insert/select statement will work without error.

    I’ve summarized all things here

    http://hourim.wordpress.com/2012/02/09/materialized-hint-and-ora-04091-error/

    It seems that, thanks to the /*+ materialize */ hint, my insert into table (t) is not selecting directly from the function (which is selecting from table t) but it is selecting from the Global Temporary table created, behind the scene, in response to the /*+ materialize */ hint.

    I hope I’ve clearly understood the situation

    • Dom Brooks says:

      With the materialize hint, the original INSERT statement effectively becomes an INSERT SELECT FROM the temp table.

      A recursive statement deals with the temp table create if it’s necessary.

      And the subsequent insert into temp table from the function does not violate the mutation rule.

  6. Igor Usoltsev says:

    Thanks for this great and exact explanation, Dominic

    My tests exactly repeated your results on 11.2.0.3 with the global transaction plan materialization on remote instance over db link – I found your note a bit late
    In addition, the baseline creation (for recursive sql in remote instance) does not help too – oracle behavior remains the same, which is expected bug result, as I understand

  7. Pingback: Bug 9706532: проблемы материализации подзапросов в рамках глобальной транзакции « Oracle mechanics

  8. Pingback: Plan_hash_value and internal temporary table names « OraStory

  9. Pingback: Fun with distributed transactions « OraStory

  10. Dom Brooks says:

    Materialization withing distributed transaction seems fixed in 12c

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 72 other followers

%d bloggers like this: