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 coded into the application, 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. [Note: at the time of writing it looks as if the problem appeared as a side effect of the new “real-time statistics” mechanism.]
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 (This case involves 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 (a) be impossible for the optimizer to ignore for these queries and (b) that is as small and efficient as possible and (c) is extremely unlikely to be used in the wrong circumstances. [Update July 2022: the implicit assumption here is that we can drop a very large and badly designed index and replace it with tiny, safe, and precise indexes. If the original index has to be retained for other reasons the benefits of the srategy are significantly reduced.]
Here, for example, is a solution involving two indexes:
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 a solution that creates a single index:
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 (i.e. one per partition) 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);