Mohamed Houri’s Oracle Notes

July 23, 2012

Materialize hint (again)

Filed under: Oracle — hourim @ 12:57 pm

Recently a question on the OTN SQL and PL/SQL Forum comes up where someone has presented a test case in which he showed a specific issue when using a pipelined function together with the materialize hint. 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 attempt to understand what makes Oracle working as such.

First, I will start by reproducing the test case given by the OTN Forum Original poster.

SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> create table dummy (name varchar2(50 byte));

Table created.

SQL> create or replace package pkg_dummy
  2  as
  3  type dummy_record is record (name varchar2(50 byte));
  4  type dummy_records is table of dummy_record;
  5  
  6  function func_get_dummy_name
  7    return dummy_records pipelined;
  8  end pkg_dummy;
  9  /

Package created.

SQL> create or replace
  2  package body pkg_dummy
  3  as
  4  function func_get_dummy_name
  5    return dummy_records pipelined
  6  as
  7  begin
  8    for cur in( select * from dummy)
  9    loop
 10      pipe row(cur);
 11    end loop;
 12  end func_get_dummy_name;
 13  end pkg_dummy;
 14  /

Package body created.

Then I will insert a record in dummy table without committing

SQL> INSERT INTO DUMMY VALUES('Original name');

1 row created.

So far so good. Trouble will start soon.

SQL> with dummy_name as
  2    ( select "NAME" from table(pkg_dummy.func_get_dummy_name())
  3    )
  4  select "NAME" from dummy_name;

NAME
----------------
Original name

SQL> with dummy_name as
  2  (
  3  select /*+ materialize */ "NAME"
  4  from table(pkg_dummy.func_get_dummy_name())
  5  )
  6  select "NAME"
  7  from dummy_name;

no rows selected

Ooops!!! The materialize hint is giving a wrong result. When I traced the hinted query using 10046 events I observed the following information (restricted to the main one)

1) The part that corresponds to the temp table creation

SQL ID: 7p67a3p9mg7pd
Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6603_12AFAA9" ("C0" 
  VARCHAR2(50) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 
  4254950915 ) NOPARALLEL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.02          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.04          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

2) The query part

WITH DUMMY_NAME AS
(
SELECT /*+ materialize */ "NAME"
FROM TABLE(PKG_DUMMY.FUNC_GET_DUMMY_NAME())
)
SELECT "NAME"
FROM DUMMY_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.18          0        192          0           0
Execute      1      0.00       0.05          0          0          2           0
Fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.23          0        192          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TEMP TABLE TRANSFORMATION  (cr=9 pr=0 pw=0 time=0 us)
      0   LOAD AS SELECT  (cr=9 pr=0 pw=0 time=0 us)
      0    COLLECTION ITERATOR PICKLER FETCH FUNC_GET_DUMMY_NAME (cr=9 pr=0 pw=0 time=0 us cost=29 size=16336 card=8168)
      0   VIEW  (cr=0 pr=0 pw=0 time=0 us cost=3 size=220536 card=8168)
      0    TABLE ACCESS FULL SYS_TEMP_0FD9D6603_12AFAA9 (cr=0 pr=0 pw=0 time=0 us cost=3 size=16336 card=8168)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.55          5.55
********************************************************************************

SELECT * 
FROM
 DUMMY 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.05          0          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.05          0          9          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)
********************************************************************************

Why the following operation is generating 0 row instead of 1 row?


Rows     Row Source Operation
-------  ---------------------------------------------------
      0    TABLE ACCESS FULL SYS_TEMP_0FD9D6603_12AFAA9 (cr=0 pr=0 pw=0 time=0 us cost=3 size=16336 card=8168)

Is it because the temp table is created by the SYS user while the hinted query is executed by a different user (parsing user id: 84) ?

Thought that I tried the same operations (create of dummy table and select with and without materialize hint) under user SYS and have observed the same bug.

Any clue?

4 Comments »

  1. Good catch! But this hint is not documented and is not expected to work as you think it has to.

    PS:
    type dummy_record is record (name dummy.name%type );

    Comment by Alex — July 24, 2012 @ 12:14 am | Reply

  2. But, despite it is a non documented hint I see it used abusively. May be, from now and on, one have to think carrefully before using the materialize hint. Anyway my goal is to know why and up to know I have only suppositions.

    Update 25/07/2012
    To see a subquery using a WITH clause being materialized (creation of a TEMP TABLE TRANSFORMATION), you don’t have to include explicitly the materialize hint into it. If the subquery is used more than once it can be materialized.

    Best Regards

    Comment by Mohamed Houri — July 24, 2012 @ 11:02 am | Reply

  3. It becomes more interesting when the record is committed, and then updated without a commit.

    In that state, the first SQL shows the session state, while the materialized one shows the older committed state.

    PL/SQL, because of the context switch can operate at a different SCN from the calling SQL.
    It reads the block from the table, finds the block is uncommitted and rolls back to a consistent point.
    Then it needs to roll forward the record that is locked by the current transaction.
    The materialize is forcing it into a different transaction (which can see if you put a
    dbms_output.put_line( dbms_transaction.LOCAL_TRANSACTION_ID);

    in the package before the loop.
    Because it is a different transaction, it only sees the committed state.

    Comment by Gary — July 25, 2012 @ 11:12 am | Reply

  4. Gary Fantastic. You’ve found the reason

    SQL> insert into dummy(name) values ('Second name');
    
    1 row created.
    
    SQL> with dummy_name as
      2    ( select "NAME" from table(pkg_dummy.func_get_dummy_name())
      3   )
      4  select "NAME" from dummy_name;
    
    NAME                                                                            
    ------------------------                           
    Second name                                                                     
    Original name                                                                   
    
    the transaction id is : 47.30.271447     
                                                 
    SQL> with dummy_name as
      2   (
      3   select /*+ materialize */ "NAME"
      4  from table(pkg_dummy.func_get_dummy_name())
      5  )
      6  select "NAME"
      7  from dummy_name;
    
    NAME                                                                            
    ------------------------                           
    Original name                                                                   
    
    the transaction id is : 11.9.4994197                                                
    

    That is a new transaction id is used when the view is materialized (temp table transformation).

    I know now that I have a lot of things to test in this context

    Comment by hourim — July 25, 2012 @ 11:41 am | 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

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

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

OraStory

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

%d bloggers like this: