Mohamed Houri’s Oracle Notes

February 9, 2012

/*+ materialized */ hint and ORA-04091 error

Filed under: Uncategorized — hourim @ 12:12 pm

It is funny to know that the /*+ materialized */ hint is, behind the scene, materialized by Oracle with a creation of a global temporary table holding the data generated by the corresponding “with subquery”. What is also funny for me is, that when trouble-shooting oracle issues, I am always cross checking, all what I have read in the past that could have any relation to the problem I am confronted to at the present time. Dom Brook post is what I have recently read and is what “my cross checking” work pointed out to be eventually related to the situation explained below.

So, recently a question came up on an Oracle French forum about an insert/select statement that was working well in 10g while it started failing in error following an upgrade to 11g. There was an excellent discussion in that French forum with valuable remarks from different posters. But my curiosity was catalyzed by the fact that no one was able to clearly show which release is correct? 10g or 11g? Before coming back to the /*+ materialized */ hint which motivates this blog let me reproduce the insert/select test case here below:

10gR2 > create table t_read_consistency (id number, vc varchar2(15));
Table created.
10gR2 > insert into
2        t_read_consistency
3      select
4        rownum id,
5        rpad('a',15,'a')
6      from
7        dual
8      connect by
9        level <= 1000;
1000 rows created.

10gR2 > create type t_read_cs as object (id number,vc varchar2(15));
/
Type created.
mhouri.world > create type t_read_cs_tab  as table of t_read_cs;
/
Type created.
10gR2 > create or replace function f_read_consistency_tab
  2  return t_read_cs_tab
  3    as
  4      lc_t_read_cs_tab t_read_cs_tab := t_read_cs_tab();
  5      j  binary_integer := 0;
  6    begin
  7        for x in (select
  8                      id,
  9                      vc
 10                   from  t_read_consistency trs
 11                  where trs.id <= 10
 12       ) loop
 13 
 14          j := j +1;
 15          lc_t_read_cs_tab.extend;
 16          lc_t_read_cs_tab(j) := t_read_cs(x.id, x.vc);
 17     end loop;
 18     RETURN lc_t_read_cs_tab;
 19   end f_read_consistency_tab;
 20  /

 Function created.

This is the model. Lets now start the insert/select operations both in 10g and 11g

10gR2 > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0    Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

10gR2 > insert into t_read_consistency
2         (id,vc)
3      select id,vc
4   from table(f_read_consistency_tab)
5  ;
10 rows created.

11gR2 > select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

11gR2 > insert into t_read_consistency
2            (id,vc)
3          select id,vc
4      from table(f_read_consistency_tab)
5     ;
from table(f_read_consistency_tab)
*
ERROR at line 4:
ORA-04091: table MOHAMED.T_READ_CONSISTENCY is mutating, trigger/function may
not see it
ORA-06512: at "MOHAMED.F_READ_CONSISTENCY_TAB", line 7

Spot the difference. What is working well in 10g is throwing an error in 11g!!! Starting from this point I was religiously trying to persuade myself that the 11g release is where the correct situation is in contrast to what were claiming several posters in the French forum. From otn, I got the link to the related documentation:

which clearly states this:

  • When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement. (10g)
  • When invoked from an INSERT, UPDATE, or DELETE statement, the subprogram cannot query or modify any database tables modified by that statement (11g)

The 11g release is, at least, in accordance with its corresponding documentation.

Then the /*+ materialized */ hint entered the scene. From ask tom, I knew that the following slightly modified insert/select is not throwing any error in 11g:


11gR2> insert into t_read_consistency(id,vc)
2          with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
3           select * from data
4          ;

10 rows created.

Consequently, if the above insert is working well this means that the insert is

  1. Either not selecting from the pipelined function and hence from the inserted table
  2. Or it is selecting from this pipelined function but before the start of the insert (exactly if I have used a PL/SQL block in which I would have first declared a local t_read_cs_tab type in which I would have then sent the content of the pipelined function and finally  would have used a select from this local type in my insert/select operation)
11gR2> explain plan for
2  insert into t_read_consistency(id,vc)
3          with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
4           select * from data
5          ;

Explained.

11gR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1931462686
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                             |  8168 |   175K|    27   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                    | T_READ_CONSISTENCY          |       |       |            |          |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| F_READ_CONSISTENCY_TAB      |       |       |            |          |
|   4 |   VIEW                              |                             |  8168 |   175K|     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D6622_C1854848 |  8168 | 16336 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

 

According to the above explain plan, the first executed operation is operation 3(read from the pipelined function) which feeds back its parent operation 2 followed by a read from that famous temporary table (SYS_TEMP_0FD9D6622_C1854848) created on the fly in response to the /*+ materialized */ hint. The insert is so that, before the first row is inserted, the function is read in its entirety and its returned rows are stored into the temporary table which is used by the insert statement. This is why the insert is not failing when the /*+ materialize */ hint is used.

Well, here below are the chronological steps

  1. I read Dom Brooks blog about the /*+ materialize */ hint where I learn that there is a creation of a temporary table behind the scene
  2. I was confronted to an issue about insert/select that is working well in 10g and not in 11g
  3. I knew that the insert/select will work in 11g if the hint /*+ materialized */ is used
  4. I cross check what I read recently and linked that to Dom Brooks article
  5. Wrote this blog article to put these chronological steps in action
About these ads

3 Comments »

  1. Hi Mohamed,

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

    Before that, 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 because we’re no longer inserting into t_read_consistency in the same statement.

    But it sounds here as if materialize is a workaround to something which perhaps should be discouraged anyway?

    Personally I’m happier with the 11g behaviour in terms of tracking the violation although I do wonder what the tracking mechanism is.

    P.S. In your demo, I don’t think you want the PK on t_read_consistency? it causes an dup_val_on_index error – or at least it does for me. If dropped it runs as shown.

    Cheers,
    Domini

    Comment by Dom Brooks — February 9, 2012 @ 2:13 pm | Reply

  2. Dom,

    I totally agree with you that in this particular situation it’s better to let the error happens instead of “working around” it with the materialize hint.
    I have also dropped the primary key constraint which, as you have pointed out, will cause a primary key violation.

    If you change a little bit the table t_read_consistency and add to it an extra attribute like an id and repete the insert in 11g it will work

    11gR2 > desc tr_read_consistency
     Name                Type
     ----------------------------------------------------
     RN                  NUMBER
     ID                  NUMBER
     VC                  VARCHAR2(15)
    
    11gR2 > insert into tr_read_consistency
      2  select rownum,
      3         id
      4         ,vc
      5  from table(f_read_consistency_tab);
    
    10 rows created.
    

    Cheers

    Comment by hourim — February 9, 2012 @ 2:28 pm | Reply

  3. [...] I have already written something about pipelined function, materialize hint and read consistency here. I thought it is worth investigating the OTN poster problem by mean of 10046 trace events in [...]

    Pingback by Materialize hint (again) « — July 23, 2012 @ 12:57 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 96 other followers

%d bloggers like this: