Oracle Scratchpad

July 5, 2021

Fussy FBIs

Filed under: CBO,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 11:19 am BST Jul 5,2021

In a recent thread on the Oracle Developer Forum a user was seeing a significant increase in time spent waiting for row locks after the number of executions of a particular “select for update” had increased from a couple of hundred per hour to a thousand per hour.

It turned out that the locking was a deliberate queueing mechanism following the basic pattern:

lock a row in the "locks" table

do some work in "another table" to flag some rows (perhaps to "own" them).

commit;

The intent was to ensure that processes did not collide (and possibly deadlock) while working on “another table”. It turned out that the increased wait time was due to an increase in the time spent between the lock and the commit; and the reason for that increase was simply a change in the execution path of a key statement executed between the two steps. The core of the work was simply the execution of one or both of two statements:

UPDATE TRAN_TAB 
SET 
        PID     = :B3,
        LOCK_ID = :B2,
        STATUS  = 'I' 
WHERE
        PID    IS NULL 
AND     STATUS = 'W' 
AND     ROWNUM <= :B1
;

UPDATE TRAN_TAB 
SET 
        PID     = :B3,
        LOCK_ID = :B2,
        STATUS  = 'I' 
WHERE
        PID    IS NULL 
AND     STATUS = 'T' 
AND     ROWNUM <= :B1
;

Originally the query had been using an index range scan on an index defined as (status, id, lock_id) but it had switched to using a tablescan because the estimated cardinality had changed from 18 rows to 3.5 million rows.

When you notice that the leading column of the index is called status you might guess (correctly) that there are just a few distinct values for the status, and just a few rows each for values ‘T’ and ‘W’ and that something unexpected had happened during statistics collection that had made Oracle “lose” sight of the special cases and treat ‘T’ (or ‘W’) as an “average” case either using “total rows / num_distinct” or “half the least popular” to estimate the cardinality. [At the time of writing it looks as if the problem appears as a side effect of “real-time statistics”.]

One fix, of course, would be to ensure that the statistics for this column never ever went wrong – and there are various ways of doing that, some more complicated and fragile than others (it’s a partitioned table and needs a suitable frequency histogram in place to get good estimates – the combination isn’t nice). Another strategy would simply be to hint the code (or add an sql_plan_baseline or sql_patch) to use the relevant index.

The nicest strategy (especially given the update to two columns out of the three in the index) might be to take advantage of function-based indexes – creating an index that would be impossible for the optimizer to avoid for these queries, that is as small and efficient as possible, and is highly unlikely to be used in the wrong circumstances. For example, a “two-index” solution:

create index tt_ft on tran_tab(
        case when status = 'T' and pid is null then 0 end
);

create index tt_fw on tran_tab(
        case when status = 'W' and pid is null then 0 end
);

or “single-index” solution:

create index tt_ftw on tran_tab(
        case when status in ('W','T') and pid is null then status end
);

The indexes hold entries only for the very small number of interesting rows, and when the status is updated the entries disappear from the index (rather than being deleted from, and re-inserted to, a very large index). Given the number of partitions in the table (ca. 100) and the very small number of rows involved, and the time-critical nature of the requirement, there’s a good case for making this a global index to avoid the need for doing lots of index probes that will find no data.

The next critical issue is that the code has to be modified to use the index – and the code has to be very precisly written. Here, from a simple model (see footnote), are a couple of examples followed by their (actual) execution plans:

select  lock_id 
from    tran_tab 
where   case when status = 'T' and pid is null then 0 end = 0 
and     rownum <= 5;

select * from table(dbms_xplan.display_cursor);


-------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |       |       |     6 (100)|          |
|*  1 |  COUNT STOPKEY                       |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TRAN_TAB |     5 |    30 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | TT_FT    |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
   3 - access("TRAN_TAB"."SYS_NC00006$"=0)


select  lock_id 
from    tran_tab 
where   case when status in ('W','T') and pid is null then status end = 'W'
;

select * from table(dbms_xplan.display_cursor);


------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |       |       |    11 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TRAN_TAB |    10 |    60 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TT_FTW   |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TRAN_TAB"."SYS_NC00008$"='W')

Be Careful

The title of this piece is “Fussy FBI” – and the reason for writing it is a reminder that it’s nicer to create and index virtual columns rather than creating function-based indexes. And, if you’re on any recent version of Oracle (12c onwards) it’s a good idea to make the virtual columns invisible so that lazy code (select *, or insert without a specified list of columns, or pl/sql “insert row”) doesn’t result in an error due to the virtual column.

Take the two where clauses I’ve used above and change them slightly – in one case swapping the order of predicates, in the other swapping the order of the IN lists – and the execution paths change from index ranges scans to tablescans.

where   case when status = 'T' and pid is null then 0 end = 0 -- index range scan
where   case when pid is null and status = 'T' then 0 end = 0 -- tablescan


where   case when status in ('W','T') and pid is null then status end = 'W' -- index range scan
where   case when status in ('T','W') and pid is null then status end = 'W' -- tablescan

When you create the function-based index Oracle may rewrite the definition into a “normalised” form – for example when I query user_ind_expressions for my tt_ftw index it turns out that the stored definition is:

CASE  WHEN (("STATUS"='W' OR "STATUS"='T') AND "PID" IS NULL) THEN "STATUS" END

But when you write a query that looks as if it should match the predicate that’s visible in user_ind_expressions the optimizer won’t necessarily notice the match.

Summary

When you create a function-based index the expression you use in your queries must be a very good match for the expression that you used when creating the index. This is just one reason why it may be better to create a virtual column using the expression – then no-one has to remember exactly what the expression was in their queries.

Defining the virtual column as invisible is then a sensible strategy to avoid problems due to code that doesn’t specify explicit column names in all the cases where they should appear.

Footnote

The following script will create the table and indexes used in this note:

rem
rem     Script:         fussy_fbi.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2021
rem
rem     Last tested 
rem             19.3.0.0
rem
rem     Notes:
rem     You have to be careful with FBI definitions and usage.
rem     the match has to be very good.
rem

create table tran_tab (
        pid             number,
        id              number,
        lock_id         number,
        status          varchar2(1),
        padding         varchar2(100)
);

insert into tran_tab
select
        case when mod(rownum,10) = 0 then to_number(null) else rownum end,
        rownum,
        rownum,
        chr(65 + 8 * mod(rownum,4)),
        rpad('x',100)
from
        all_objects
where
        rownum <= 1e4
;

update tran_tab set status = 'T' where mod(lock_id,1000) = 0;
update tran_tab set status = 'W' where mod(lock_id, 990) = 0;

create index tt_ft on tran_tab(
        case when status = 'T' and pid is null then 0 end
);

create index tt_fw on tran_tab(
        case when status = 'W' and pid is null then 0 end
);

create index tt_ftw on tran_tab(
        case when status in ('W','T') and pid is null then status end
);

commit;

execute dbms_stats.gather_table_stats(user,'tran_tab')

set serveroutput off

prompt  ===========
prompt  Correct use
prompt  ===========

select  lock_id 
from    tran_tab 
where   case when status = 'T' and pid is null then 0 end = 0 
and     rownum <= 5;

select * from table(dbms_xplan.display_cursor);

select  lock_id 
from    tran_tab 
where   case when status in ('W','T') and pid is null then status end = 'W'
;

select * from table(dbms_xplan.display_cursor);

prompt  ==========
prompt  Failed use
prompt  ==========

select  lock_id 
from    tran_tab 
where   case when pid is null and status = 'T' then 0 end = 0 
and     rownum <= 5;

select * from table(dbms_xplan.display_cursor);

select  lock_id 
from    tran_tab 
where   case when status in ('T','W') and pid is null then status end = 'W'
;

select * from table(dbms_xplan.display_cursor);

2 Comments »

  1. I once encountered a predicate
    to_char (cr_date, ‘YYYYMMDD’)
    Or
    to_char (cr_date, ‘yyyyMMDD’),
    I have to build 2 indexes.

    Comment by zhwsh — July 8, 2021 @ 2:46 am BST Jul 8,2021 | Reply

    • zhwsh,

      Thanks for the comment.
      That’s a nice example of the problem – and a warning to handle dates properly as dates.

      I wonder if it was a case of developers not knowing about using the trunc() function with dates, and using to_char() to make sure they were using a date-only value.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — July 8, 2021 @ 9:49 am BST Jul 8,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.