Sunday, January 25, 2009

Update and rownum oddity

Take a look at the following table:
SQL> create table codes
2 (
3 code varchar2(10),
4 used number,
5 constraint pk_codes primary key (used, code)
6 ) organization index;

Table created.

SSQL> insert into codes
2 select dbms_random.string('x', 10),
3 case when level <= 5000 then 1 else 0 end
4 from dual
5 connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'codes');

PL/SQL procedure successfully completed.
This table contains a set of codes with used column representing whether the code was already used (1) or not (0). We need to return one (random) unused code from the above table and mark this code as used. This is very easy to archive using the following update statement:
SQL> variable code varchar2(10);
SQL> set autot traceonly
SQL> update codes set used=1
2 where used=0 and rownum=1
3 returning code into :code;

1 row updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1169687698

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 13 | 117 (1)| 00:00:02 |
| 1 | UPDATE | CODES | | | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | INDEX FAST FULL SCAN| PK_CODES | 50000 | 634K| 117 (1)| 00:00:02 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(ROWNUM=1)
3 - filter("USED"=0)


Statistics
----------------------------------------------------------
1 recursive calls
5 db block gets
12 consistent gets
0 physical reads
124 redo size
913 bytes sent via SQL*Net to client
874 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
However, the plan is not exactly what I would expect (I'm running this on 11.1.0.7)... Why do IFFS when we can do IRS to get only one row? This is exactly what regular select does, after all:
SQL> select *
2 from codes
3 where used=0 and rownum=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 802332609

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX RANGE SCAN| PK_CODES | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
2 - access("USED"=0)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
590 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

10053 trace

Let's take a look at 10053 trace output for both select and update statements. I'm picking up relevant sections.

UPDATE:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: CODES Alias: CODES
#Rows: 100000 #Blks: 423 AvgRowLen: 13.00
Index Stats::
Index: PK_CODES Col#: 2 1
LVLS: 1 #LB: 423 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 0.00
Access path analysis for CODES
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CODES[CODES]
Table: CODES Alias: CODES
Card: Original: 100000.000000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
Access Path: index (index (FFS))
Index: PK_CODES
resc_io: 116.00 resc_cpu: 20012369
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 116.81 Resp: 116.81 Degree: 1
Cost_io: 116.00 Cost_cpu: 20012369
Resp_io: 116.00 Resp_cpu: 20012369
OPTIMIZER PERCENT INDEX CACHING = 0


Access Path: index (IndexOnly)
Index: PK_CODES
resc_io: 213.00 resc_cpu: 11516867
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 213.47 Resp: 213.47 Degree: 1
Best:: AccessPath: IndexFFS
Index: PK_CODES
Cost: 116.81 Degree: 1 Resp: 116.81 Card: 50000.00 Bytes: 0

***************************************
Nothing else is being tried and this is what optimizer selects as the best execution plan. Note how cardinalities are being reported (highlighted in red).

SELECT:

In addition to the above, has one more section:
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
Single Table Cardinality Estimation for CODES[CODES]
Table: CODES Alias: CODES
Card: Original: 2.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: index (index (FFS))
Index: PK_CODES
resc_io: 2.00 resc_cpu: 7461
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 7461
Resp_io: 2.00 Resp_cpu: 7461
OPTIMIZER PERCENT INDEX CACHING = 0


Access Path: index (IndexOnly)
Index: PK_CODES
resc_io: 2.00 resc_cpu: 14443
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: PK_CODES
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 13

First K Rows: unchanged join prefix len = 1
Join order[1]: CODES[CODES]#0
***********************
Note how cardinalities has changed (highlighted in green) this time. What happened is rownum = 1 predicate resulted in fist_rows(1) mode (highlighted in blue), affecting how cardinalities were calculated.

We know that first_rows(n) hint is being ignored in update and delete statements, thus our update statement always goes in all_rows mode.

You can confirm that select behaves exactly the same way when in all_rows mode:
SQL> select /*+ all_rows */ *
2 from codes
3 where used=0 and rownum=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2682988822

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 117 (1)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX FAST FULL SCAN| PK_CODES | 50000 | 634K| 117 (1)| 00:00:02 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)
2 - filter("USED"=0)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
590 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Well, I guess here goes my next wish for CBO improvement regarding how update and delete statements are handled with predicates involving rownum...

9 comments:

  1. Hi Alex,
    We know that first_rows(n) hint is being ignored in update and delete statements, thus our update statement always goes in all_rows mode.
    Is this also true for OPTIMIZER_MODE spfile parameter?

    ReplyDelete
  2. "We know that first_rows(n) hint is being ignored in update and delete statements"
    SELECT has the option of multiple FETCHes, so it can make sesne to optimize the time to return the first FETCH at the expense of subsequent FETCHes. UPDATE and DELETE do all the processing in the ExECUTE step, no fetches involved. There's no sense in a FIRST_ROWS hint.

    That said, it does look like they've piggy-backed ROWNUM processing on First Rows processing.
    Given the stopkey (so it won't process the entire fast full scan) I do wonder if there is any differnce in runtime. A range scan must start at the root block and navigate a couple of branch blocks to a leaf block. There's a pretty good chance that a fast full scan will find a leaf block quicker.

    ReplyDelete
  3. Gary,

    what we had on a real system was a much bigger table (around 10M rows) and codes were separated by types so the table looked like this:

    codes (code varchar2(10), code_type number, used number, constraint pk_codes primary key (used, type, code)) organization index;

    the problem with IFFS is that it has to scan the segment from the beginning in order to find that first "free" row and, in case we were looking for a free code with types at the end of the segment, that was resulting in a huge performance penalty.

    Martin, I think Gary's post pretty much answers your question.

    ReplyDelete
  4. On 10g+, the default optimizer mode is "ALL_ROWS". So, the plans of select statement and update statement should be same. But as in your case, when used with ROWNUM predicate, the plans get different.

    It's due to _optimizer_rownum_pred_based_fkr parameter enabled by default. This means that Oracle converts ROWNUM predicate to first_rows mode.

    ReplyDelete
  5. Hello, Alex. Have you tried to tell Oracle to use IRS? It worked for me:

    13:33:38 TIM@test> variable code varchar2(10)
    13:33:40 TIM@test> set autot traceonly
    13:33:48 TIM@test> update /*+ index_rs(codes pk_codes) */ codes set used=1 where used=0 and rownum=1
    13:34:09 2 returning code into :code;

    1 row updated.

    Elapsed: 00:00:00.04

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1108178351

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 1 | 13 | 211 (1)| 00:00:03 |
    | 1 | UPDATE | CODES | | | | |
    |* 2 | COUNT STOPKEY | | | | | |
    |* 3 | INDEX RANGE SCAN| PK_CODES | 49938 | 633K| 211 (1)| 00:00:03 |
    -------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter(ROWNUM=1)
    3 - access("USED"=0)


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    5 db block gets
    2 consistent gets
    1 physical reads
    828 redo size
    719 bytes sent via SQL*Net to client
    679 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    13:34:11 TIM@test> disc
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

    PS. sorry, I don't know how to format code here.

    ReplyDelete
  6. Timur,

    the hint would work, yes (as your test case shows).

    When it comes to hinting, I like Jonathan Lewis' approach (http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting).

    The important thing lies in understanding why the optimizer behaves one way and not the other.

    ReplyDelete
  7. Well, there are many ways to tweak the optimizer in this particular case:

    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>
    SQL> variable code varchar2(10)
    SQL>
    SQL> set autot traceonly
    SQL>
    SQL> update /*+ opt_param('_db_file_optimizer_read_count', 1) */
    2 codes set used=1
    3 where used=0 and rownum=1
    4 returning code into :code;

    1 row updated.


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1108178351

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 1 | 13 | 216 (1)| 00:00:03 |
    | 1 | UPDATE | CODES | | | | |
    |* 2 | COUNT STOPKEY | | | | | |
    |* 3 | INDEX RANGE SCAN| PK_CODES | 50000 | 634K| 216 (1)| 00:00:03 |
    -------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter(ROWNUM=1)
    3 - access("USED"=0)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    5 db block gets
    2 consistent gets
    0 physical reads
    116 redo size
    728 bytes sent via SQL*Net to client
    739 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL>
    SQL> update /*+ opt_param('optimizer_index_cost_adj', 1) */
    2 codes set used=1
    3 where used=0 and rownum=1
    4 returning code into :code;

    1 row updated.


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1108178351

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
    | 1 | UPDATE | CODES | | | | |
    |* 2 | COUNT STOPKEY | | | | | |
    |* 3 | INDEX RANGE SCAN| PK_CODES | 50000 | 634K| 2 (0)| 00:00:01 |
    -------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter(ROWNUM=1)
    3 - access("USED"=0)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    3 db block gets
    2 consistent gets
    0 physical reads
    424 redo size
    729 bytes sent via SQL*Net to client
    743 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL>
    SQL> update /*+ opt_param('optimizer_index_caching', 100) */
    2 codes set used=1
    3 where used=0 and rownum=1
    4 returning code into :code;

    1 row updated.


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1108178351

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 1 | 13 | 2 (100)| 00:00:01 |
    | 1 | UPDATE | CODES | | | | |
    |* 2 | COUNT STOPKEY | | | | | |
    |* 3 | INDEX RANGE SCAN| PK_CODES | 50000 | 634K| 2 (100)| 00:00:01 |
    -------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter(ROWNUM=1)
    3 - access("USED"=0)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    3 db block gets
    2 consistent gets
    0 physical reads
    424 redo size
    730 bytes sent via SQL*Net to client
    744 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SQL>
    SQL>

    But I think Alex is right: The crucial point is that the optimizer doesn't consider the special case of the ROWNUM predicate, and always goes for a ALL_ROWS optimization in case of DML.

    And this isn't legal, unfortunately:

    SQL>
    SQL> update (
    2 select *
    3 from codes
    4 where used=0 and rownum<=1
    5 )
    6 set used=1
    7 returning code into :code;
    update (
    *
    ERROR at line 1:
    ORA-01732: data manipulation operation not legal on this view

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/

    ReplyDelete
  8. Randolf, see my reply above about hinting...

    ReplyDelete
  9. Alex,

    I know and agree, see my closing thoughts in my initial comment...

    Best Regards,
    Randolf

    ReplyDelete