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?

July 16, 2012

Virtual column as a foreign key

Filed under: Deadlock — hourim @ 7:30 am

Recently a thread on the otn forum prompted me to review what I have already learned about the deadlock threat an unindexed foreign key can put in a mutli-user concurrent OLTP application. Particularly when the foreign key is represented by a virtual column.

In a comment of Charles ‘Hooper blog article I wrote (a) first that a function based index cannot cover the deadlock threat induced by a foreign key constraint (b) and then, a month or so after, I enriched my first comment saying that if the function based index starts by the foreign key column then it can cover that deadlock threat. How a about a foreign key represented by a virtual column? Will an index on that virtual column help?

The data model proposed by Dominic Brooks is re-used as follows:

create table t1
 (col1 number primary key);

create table t2
 (col1    number primary key
 ,status  varchar2(12) not null
 ,col2    number
 ,col2v   number generated always as (case when status = 'ACTIVE' then col2 end) VIRTUAL
  ,constraint t2_fk foreign key (col2v) references t1(col1)
  ,constraint t2_ck check (status in ('ACTIVE','INACTIVE') and (status = 'INACTIVE' or col2 is not null))
 );

insert into t1
select rownum
from dual 
connect by level <=100;

commit;

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);

declare
pragma autonomous_transaction;
begin
  delete from t1
  where col1 = 99;
  commit;
end;
/

Error report:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
00060. 00000 -  "deadlock detected while waiting for resource";
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000137b4-00000000        19     138    SX             19     138           S
 
session 138: DID 0001-0013-00000018	session 138: DID 0001-0013-00000018 
 
Rows waited on:
  Session 138: obj - rowid = 000137B4 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 79796, file - 0, block - 0, slot - 0)
 
----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=0tqwpunag33fh) -----
DELETE FROM T1 WHERE COL1 = 99
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
28F75504         4  anonymous block

I didn’t created any index on the foreign key virtual column (col2v) and this is why when I tried to delete from the parent table having an unindexed foreign key, I have been deadlocked.

Let’s now create an index on col2v virtual column and repeat the same operations

create index t2_col2v_fk on t2(col2v);

index T2_COL2V_FK created.

select index_name, index_type
from user_indexes
where table_name = 'T2';

INDEX_NAME                     INDEX_TYPE
------------------------------ ----------------------
T2_COL2V_FK                    FUNCTION-BASED NORMAL
SYS_C0013917                   NORMAL

select index_name, column_expression
from user_ind_expressions
where index_name = 'T2_COL2V_FK';

INDEX_NAME    COLUMN_EXPRESSION
------------  ------------------ ----------------------
T2_COL2V_FK	CASE "STATUS" WHEN 'ACTIVE' THEN "COL2" END 

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);
1 rows inserted.

declare
pragma autonomous_transaction;
begin
  delete from t1
  where col1 = 99;
  commit;
end;
/
anonymous block completed

Thanks to this new function based index created on the virtual column I succeed to avoid the deadlock threat.

Bottom line: there are two conclusions I can made from this blog
 When a virtual column is declared as a foreign key, indexing it (function based index) will cover the corresponding deadlock threat.
 Whatever this function based index expression is if the corresponding index starts with the foreign virtual column then it will cover the deadlock threat

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)