Wednesday, October 28, 2009

Three new "hints"

In version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. These are not normal hints like we know them. Oracle writes:

Note:
The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

That's strange. Why does Oracle mix up the concept of a hint by introducing three new hints that have a semantic effect? Now you have two kind of hints. You have optimizer hints and you have semantic hints. Both are silently ignored when you make a syntax error. But there is no way to tell with which kind you are dealing, unless you know them by heart. And it becomes harder to explain. It means you cannot advice to just gather statistics and remove all hints anymore. Couldn't this be solved by adding an extra keyword? Or were they reluctant to introduce another deviation from the ANSI standard? On the other hand, we now have some extra options available, and that's good. Let's explore them.


IGNORE_ROW_ON_DUPKEY_INDEX

From the documentation: "When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row."

An example:

rwijk@ORA11GR2> create table mytable
2 ( id number(2) primary key
3 , name varchar2(3) not null
4 )
5 /

Table created.

rwijk@ORA11GR2> insert into mytable values (4,'old')
2 /

1 row created.

rwijk@ORA11GR2> insert into mytable values (9,'old')
2 /

1 row created.

rwijk@ORA11GR2> select * from mytable
2 /

ID NAM
---------- ---
4 old
9 old

2 rows selected.

rwijk@ORA11GR2> insert /*+ ignore_row_on_dupkey_index(mytable(id)) */ into mytable
2 ( id
3 , name
4 )
5 select level
6 , 'new'
7 from dual
8 connect by level <= 10
9 /

8 rows created.

rwijk@ORA11GR2> select *
2 from mytable
3 order by id
4 /

ID NAM
---------- ---
1 new
2 new
3 new
4 old
5 new
6 new
7 new
8 new
9 old
10 new

10 rows selected.

So the insert statement with the ignore_row_on_dupkey_index was specified to insert 10 rows, but due to the current contents of the table, only 8 rows were actually inserted, since 4 and 9 already exist. In 10g Release 2 you could achieve a similar effect by using the DBMS_ERRLOG package. Using the DBMS_ERRLOG package is a bit more cumbersome and it is different since it logs ALL errors, not just the unique key violations.


CHANGE_DUPKEY_ERROR_INDEX

From the documentation: "The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.". An example with a table containing a primary key constraint and a unique key constraint:

rwijk@ORA11GR2> create table t
2 ( id number(2)
3 , name varchar2(3) not null
4 , constraint t_pk primary key (id)
5 , constraint t_uk unique(name)
6 )
7 /

Table created.

rwijk@ORA11GR2> insert into t values (1,'bla')
2 /

1 row created.


Whether the primary key constraint or the unique key constraint is violated, you'll always get an ORA-00001 error message:

rwijk@ORA11GR2> insert into t values (1,'abc')
2 /
insert into t values (1,'abc')
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.T_PK) violated


rwijk@ORA11GR2> insert into t values (2,'bla')
2 /
insert into t values (2,'bla')
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.T_UK) violated



With the change_dupkey_error_index hint you can reroute one of the ORA-00001 error messages to an ORA-38911 error. Which unique key violation is rerouted, depends on the columns or unique index name specified after the table name in the hint.

rwijk@ORA11GR2> insert /*+ change_dupkey_error_index (t(name)) */ into t
2 values (2,'bla')
3 /
insert /*+ change_dupkey_error_index (t(name)) */ into t
*
ERROR at line 1:
ORA-38911: unique constraint (RWIJK.T_UK) violated


rwijk@ORA11GR2> insert /*+ change_dupkey_error_index (t(id)) */ into t
2 values (1, 'abc')
3 /
insert /*+ change_dupkey_error_index (t(id)) */ into t
*
ERROR at line 1:
ORA-38911: unique constraint (RWIJK.T_PK) violated


This can be handy in PL/SQL to distinguish one unique key violation from the other, without having to parse the error message for the constraint name:

rwijk@ORA11GR2> declare
2 e_name_uk_violated exception;
3 pragma exception_init(e_name_uk_violated,-38911);
4 begin
5 insert /*+ change_dupkey_error_index (t,t_uk) */ into t values (1,'abc');
6 exception
7 when e_name_uk_violated then
8 dbms_output.put_line('Name should be unique, and this name already exists.');
9 when dup_val_on_index then
10 dbms_output.put_line('Some other unique constraint was violated');
11 end;
12 /
Some other unique constraint was violated

PL/SQL procedure successfully completed.

rwijk@ORA11GR2> declare
2 e_id_pk_violated exception;
3 pragma exception_init(e_id_pk_violated,-38911);
4 begin
5 insert /*+ change_dupkey_error_index (t,t_pk) */ into t values (1,'abc');
6 exception
7 when e_id_pk_violated then
8 dbms_output.put_line('Id should be unique, and this Id already exists.');
9 when dup_val_on_index then
10 dbms_output.put_line('Some other unique constraint was violated');
11 end;
12 /
Id should be unique, and this Id already exists.

PL/SQL procedure successfully completed.



RETRY_ON_ROW_CHANGE

This one puzzles me. The documentation says: "When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.".

Ok, I can reproduce that. First create a table t with 50,000 rows and add a row level trigger that logs autonomously into a table called logtable. This way we can monitor the progress of the update statement in a separate session. And it slows down the update considerably, which is a good thing for the test.

rwijk@ORA11GR2> create table t
2 ( id number(9) primary key
3 , col number(9) not null
4 )
5 /

Table created.

rwijk@ORA11GR2> insert /*+ append */ into t
2 select level
3 , level
4 from dual
5 connect by level <= 50000
6 /

50000 rows created.

rwijk@ORA11GR2> create table logtable
2 ( last_id number(9)
3 , number_of_processed_ids number(9)
4 )
5 /

Table created.

rwijk@ORA11GR2> insert into logtable values (null,0)
2 /

1 row created.

rwijk@ORA11GR2> create procedure p (p_id in t.id%type)
2 is
3 pragma autonomous_transaction;
4 begin
5 update logtable
6 set last_id = p_id
7 , number_of_processed_ids = number_of_processed_ids + 1
8 ;
9 commit;
10 end;
11 /

Procedure created.

rwijk@ORA11GR2> create trigger t_aru
2 after update on t
3 for each row
4 begin
5 p(:new.id);
6 end;
7 /

Trigger created.


And now we do an update with the retry_on_row_change hint:

rwijk@ORA11GR2> update /*+ retry_on_row_change */ t
2 set col = col * -1
3 /

And while this statement is running, open a second session and run this:

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
14754 14754

1 row selected.

rwijk@ORA11GR2> update t
2 set col = 888888
3 where id = 40000
4 /

1 row updated.

rwijk@ORA11GR2> commit
2 /

Commit complete.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
17507 17508

1 row selected.

So the row with id 40000 is updated and committed, while our statement from session 1 has not reached this row yet.

After session 1 completed the update it says:

50000 rows updated.


But here is what happened (from session 1):

rwijk@ORA11GR2> select *
2 from t
3 where id between 39999 and 40001
4 /

ID COL
---------- ----------
39999 -39999
40000 -888888
40001 -40001

3 rows selected.

Session 2 has set COL to 888888, and session 1 has set this value to -888888.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
50000 89922

1 row selected.

By the number_of_processed_ids column you can deduce that the statement was restarted once it has fetched the block with ID 40000 in it. I also checked it in session 2 by reexecuting the last statement several times. The last_id column dropped back to 1 and the entire statement was re-executed. This leads to a total of 89,922 (50,000 + almost 40,000) number of processed ID's. So the statement was retried on detecting a row change. And the table doesn't need the ROWDEPENDENCIES clause to make this happen.

But what's the point? Oracle has its write consistency mechanism that makes sure a statement gets restarted under the covers if during a DML a row has been changed that has not been locked yet. For example, if we add a where clause to the update statement saying "id = col", then the statement sets out to update 50,000 rows. Now, if another session update the col value of ID 40000 to 888888, the update statement of session 1 will detect that automatically and restarts the statement. Without the need for a RETRY_ON_ROW_CHANGE hint:

From session 1 with the same DDL as above:

rwijk@ORA11GR2> update t
2 set col = col * -1
3 where id = col
4 /

And while this statement runs, update the ID 40000 record in session 2:

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
10783 10783

1 row selected.

rwijk@ORA11GR2> update t
2 set col = 888888
3 where id = 40000
4 /

1 row updated.

rwijk@ORA11GR2> commit
2 /

Commit complete.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
12822 12823

1 row selected.


And then from session 1:

49999 rows updated.

rwijk@ORA11GR2> commit
2 /

Commit complete.

rwijk@ORA11GR2> select *
2 from t
3 where id between 39999 and 40001
4 /

ID COL
---------- ----------
39999 -39999
40000 888888
40001 -40001

3 rows selected.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
50000 89999

1 row selected.

So here Oracle's write consistency mechanism made the statement rollback and re-execute. Which, by the way, is a good reason to not use autonomous transactions in database triggers: they might execute twice for a single row.

If someone can enlighten me with a use case for the RETRY_ON_ROW_CHANGE hint, I would be happy.

16 comments:

  1. Man! This mixing up of optimizer and semantic hints is gonna cause soooo many troubles...
    Why oh why, didn't they just add a session parameter for each of these, to be set as needed?
    One of the most bone-headed "evolutions", and I hope I'm being really optimistic...

    ReplyDelete
  2. All three seem, to some extent, puzzling with alternatives which would be clearer to the developer.

    It's hard to see how they passed any sort of prioritisation board.
    I can only think that they helped meet some obscure internal dev requirement or to help beat some benchmark, etc.

    ReplyDelete
  3. Yeah, the mixing of the two types of hints is a bad idea.

    Handling them is nice though. Well, the first one, not breaking, is very nice. When processing data from two different sources it is quite convenient to just INSERT the new stuff without having to write a NOT EXISTS for the very same TABLE which adds to the operation.

    ReplyDelete
  4. In the same category, REWRITE_OR_ERROR appeared in 10g
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/qradv.htm#BABIAIHJ

    ReplyDelete
  5. Good catch, Laurent.

    Learnt something new. Thanks!

    ReplyDelete
  6. Back to the original question - a user case for RETRY_..., I'm guessing it will be related to editioning, seeing as the other two are also mentioned heavily in that context.

    I'm thinking something like a cross-edition trigger may need to get a "fuller" restart by using the hint rather than the default offered by standard write consistency.

    (Just a guess at this stage)

    Cheers
    Connor

    ReplyDelete
  7. Yes, the hint must have something to do with editions. The hint is mentioned in Bryn Llewellyn's white paper about editions. He writes in footnote 62:

    To do... explain the circumstances when the Retry_On_Row_Change hint is useful.

    (Did he publish the white paper unfinished?) I hope he will do his "to do" someday ...

    ReplyDelete
  8. My guess - the standard write consistency mechanism triggers a restart if the columns referenced in the statement (or triggers)change, the RETRY_ON_ROW_CHANGE, being based on the ora_rowscn, restarts if ANY column has been modified.

    ReplyDelete
  9. Yes, that's a good guess: it's true. But when would that behaviour be desirable?

    ReplyDelete
  10. I'd bet the IGNORE_ROW_ON_DUPKEY_INDEX hint was added because (1) 10gR2's DML Error Logging doesn't log unique key violations and (2) the venerable EXCEPTIONS INTO clause allows the violations in. So the hint is allows Oracle to "take the first one" and reject the rest http://tkyte.blogspot.com/2005/04/understanding.html

    ReplyDelete
  11. I also think RETRY_ON_ROW_CHANGE is useful in Edition-Based Upgrade.
    They 3 are in the same category--Online Application Upgrade Hints.

    --viadeazhu

    ReplyDelete
  12. The functionality of IGNORE_ROW_ON_DUPKEY_INDEX could already be achieved in yeat another way - with MERGE:

    http://www.colloperate.com/2009/12/oracle-is-strange.html

    MERGE would seem a much more elegant way to do the same thing, since it makes the intent more explicit and much clearer.

    Oracle's process for including new features really leaves me scratching my head sometimes...

    ReplyDelete
  13. It looks like that RETRY_ON_ROW_CHANGE is an optimization of write consistency detection
    in the block level, instead of traditional row level.

    Following query probably justify it.

    with block_stats as
    (select dbms_rowid.rowid_block_number (t.rowid) block#
    ,dbms_rowid.rowid_row_number (t.rowid) row#
    ,t.*
    from t)
    select count(id) rows_before_id_4000
    from block_stats
    where block# < (select block# from block_stats where id = 40000);


    ROWS_BEFORE_ID_4000
    -------------------
    39921

    1 row selected.


    Also this little arithmetic helps me understand where 89922 comes from.

    50000 + 39921 + 1 = 89922

    By the way, following blog further shows update restart.
    That old restart problem again...
    (http://tkyte.blogspot.com/2010/04/that-old-restart-problem-again.html)

    ReplyDelete
  14. If session 1 updates as:

    update /*+ retry_on_row_change */ t
    set col = col * -1
    where id = col and id != 40000;

    and session 2 updates before session 1 hits this data block (the blcok with id = 40000) by:

    update t
    set col = 888888
    where id = 40000;

    then the update restarted, and performance is worse:

    Sqlplus> select * from logtable;

    LAST_ID NUMBER_OF_PROCESSED_IDS
    ---------- -----------------------
    50000 89921

    so this hint would have Pros and Cons.

    ReplyDelete
  15. Thanks for these additions, ksun. I'm still not convinced as to why you would really need this hint, though.

    ReplyDelete
  16. Hint: RETRY_ON_ROW_CHANGE was mentioned in 10g "Bug 6082734 Dump (kdr9igtl) accessing a row from an IOT" for IOT. For update restart, I tried to compose a Blog: Update Restart and new Active Undo Extent (http://ksun-oracle.blogspot.com/2011/05/update-restart-and-new-undo-extent.html)

    ReplyDelete