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.