Materialize hint (again)

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?

About these ads