Forum Stats

  • 3,853,848 Users
  • 2,264,287 Discussions
  • 7,905,479 Comments

Discussions

Row lock with for update

2

Answers

  • Paulzip
    Paulzip Member Posts: 8,755 Blue Diamond
    edited Jul 2, 2021 8:29AM

    Your approach is using a very poor implementation of pessimistic locking, pessimistic is where you attempt to lock the records you are about to change before you can make updates / deletes to them, but bizarrely in your case, someone has chosen a single lock allocation as the control. That's your problem. If two sessions were altering different records, you will still cause a lock wait. It's monolithic.

    In financial systems I've worked on, where pessimistic locking was a requirement, we simply lock the records that are going to be updated with nowait, we trap any resource busy with nowait and feedback to the UI "You cannot edit these records, they are currently locked for editing by another session". However, as we kept transactions to the absolute minimum time, a retry was usually successful.

    Optimistic locking models read an SCN before updates and simply just try to update the records. If another session is updating any of the rows, you'll be blocked and it'll wait. As soon as the blocking session ends its transaction, your updates will be issued. After updating, you compare your read consistency SCN with the SCN now and a difference suggests another session has updated since you started your updates.

    SB35
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    Reading the code it looks as if the intent is roughly:

    Find a limited number of rows from table tran_tab in state W and change them to state I setting an ID column to a value in hid; if there are no such rows then pick rows in state T and update them in the same way.

    There's no clue about where the value of hid comes from, though, but the use of table lock_tab ensures that you won't get two concurrent processes colliding in their attempts to lock rows from tran_tab, and the value for hid is used to update the lock_tab after the initial select for update. (The exception handler is pretty irrelevant, I suspect, since the recursive call to lock_prc() should never happen after the very first time the process runs. However it would be better to declare an exception - e.g. ran_out_of_time - and linked the error 30006 to it with the exception_init pragma so you could have a "when ran_out_of_time" clause instead of a "when others".)


    I would guess that after lock_prc(), or this part of it, has done its business the process that called it will query tran_tab for the rows with the hid that has just be used and do the "real" processing that they are supposed to get.

    If the number of calls to this procedure has gone up from 200-300 per hour to 1,000 per hour is this because the volume of tran_tab rows has increased over time and you need more processes running to handle the volume, or is it simply that the rate at which you've been able to process them has slowed down for some reason that has not yet been discovered?

    Two strategies:

    a) Minimal approach - make the work between the "select for update" and "commit" more efficient.

    Why is the call to get_setting AFTER the lock - it looks as if it could happen BEFORE the lock.

    What is the significance of the delete from LOCK_TAB. There are no clues in code supplied to suggest that lock_prc() is generating lock_tab rows, so why is lock_prc() deleting them. Is it something that could happen in a totally separate piece of code. If it has to be there do you have an index on "(id, create_date) compress 1" - what's the access path for the delete - is it as efficient as possible; what's the state of the table and index, the pattern of the delete suggests that there may be some scope for excess work being done during this delete.

    What's the access path for the update of tran_tab? Is it as efficient as possible. "Status" columns, which typically have a very small number of rows, often cause problems when they have a large number of rows with one value and a small number of rows scattered across other values; indexes on such columns can also degrade quite dramatically. One way of improving the efficiency of queries like this is to take advantage of "function-based indexes", or indexes on virtual columns, to index only the small number of rows that are of interest. I've got an example on my blog that might be relevant - but I can't find it at present.


    b) Whole new mechanism

    It looks as if the earlier suggestion for using Advanced Queueing (AQ) might be appropriate. Let Oracle handle the problem of avoiding lock conflicts. E.g. add a trigger to tran_tab that on insert (or update of status) puts a message into a queue that holds the primary key and status of a tran_tab row. Then have a dequeue functio (n that pulls messages off the queue and does the main processing you're interested in.


    Regards

    Jonathan Lewis

    SB35
  • SB35
    SB35 Member Posts: 106 Green Ribbon

    @Jonathan Lewis Thank you very much for guidance here and such detailed explanation.

    I found there was change in execution path happen for both the UPDATES inside which increased the response time from ~5 milli sec/exec to .5 second/execution. And it seems clear that its happening because of wrong cardinality estimation because of STATUS column. And this column is having ~6 distinct values and is significantly skewed. But the number of rows with status W or T is very less (<10).

    There is a frequency histogram present on this column both in global level and in each partition(i verified from dba_part_col_statistics). So i am wondering what must be happening such that the estimation went till ~3450K making it favorable for full scan? We have incremental stats ON on this table,and we use method_opt=> 'for all column size repeat' so ideally histogram should not get removed from global level. But the estimation for below full table scan path which is ~3450k is closely matching to num_rows/num_distinct of STATUS column= 23million/6=~3833k, so it seems somehow the histogram from the global level gets dropped by some stats collection causing bad plan. But still not able to figure out, how is it possible with Incremental stats being ON?

    Index TRAN_TAB_IDX is a composite index on column (status,pid,lockid). Column PID is having all 23million null values and column lockid having 1.1million distinct values with no null. You mentioned "One way of improving the efficiency of queries like this is to take advantage of "function-based indexes", or indexes on virtual columns, to index only the small number of rows that are of interest." Still struggling to understand how a function based index/virtual column can be more efficiently implemented here?


    UPDATE TRAN_TAB SET PID = :B3 ,LOCK_ID = :B2 ,STATUS = 'I' WHERE 
    PID IS NULL AND STATUS = 'T' AND ROWNUM <= :B1
    
    **************** Good Plan********************
    
    
    ---------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation                                    | Name           | Rows | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------------------------
    |  0 | UPDATE STATEMENT                             |                |      |      |  227 (100)|         |      |      |
    |  1 | UPDATE                                      | TRAN_TAB       |      |      |           |         |      |      |
    |  2 |  OPTIMIZER STATISTICS GATHERING             |                |   18 | 3366 |  227  (0)| 00:00:01 |      |      |
    |* 3 |   COUNT STOPKEY                             |                |      |      |           |         |      |      |
    |  4 |    PARTITION RANGE ALL                      |                |   18 | 3366 |  227  (0)| 00:00:01 |    1 |  110 |
    |  5 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TRAN_TAB       |   18 | 3366 |  227  (0)| 00:00:01 |    1 |  110 |
    |* 6 |      INDEX RANGE SCAN                       | TRAN_TAB_IDX   |   18 |      |  221  (0)| 00:00:01 |    1 |  110 |
    ---------------------------------------------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
     /*+
         BEGIN_OUTLINE_DATA
         IGNORE_OPTIM_EMBEDDED_HINTS
         OPTIMIZER_FEATURES_ENABLE('19.1.0')
         DB_VERSION('19.1.0')
         OPT_PARAM('_fix_control' '27268249:0')
         FIRST_ROWS
         OUTLINE_LEAF(@"UPD$1")
         INDEX_RS_ASC(@"UPD$1" "TRAN_TAB"@"UPD$1" ("TRAN_TAB"."STATUS" "TRAN_TAB"."ID" "TRAN_TAB"."LOCK_ID"))
         BATCH_TABLE_ACCESS_BY_ROWID(@"UPD$1" "TRAN_TAB"@"UPD$1")
         END_OUTLINE_DATA
     */
    
    Peeked Binds (identified by position):
    --------------------------------------
    
      3 - :B1 (NUMBER): 50
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      3 - filter(ROWNUM<=:B1)
      6 - access("STATUS"='T' AND "ID" IS NULL)
    
    
      ******************** Bad plan************************************
    
    
      ------------------------------------------------------------------------------------------------------------
    | Id | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------
    |  0 | UPDATE STATEMENT                |         |       |      | 94092 (100)|         |      |      |
    |  1 | UPDATE                         | TRAN_TAB |       |      |           |         |      |      |
    |  2 |  OPTIMIZER STATISTICS GATHERING |         | 3450K |  618M| 94092  (1)| 00:00:04 |      |      |
    |  3 |   COUNT STOPKEY                |         |       |      |           |         |      |      |
    |  4 |    PARTITION RANGE ALL         |         | 3450K |  618M| 94092  (1)| 00:00:04 |    1 |  110 |
    |  5 |     TABLE ACCESS STORAGE FULL  | TRAN_TAB | 3450K |  618M| 94092  (1)| 00:00:04 |    1 |  110 |
    ------------------------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
     /*+
         BEGIN_OUTLINE_DATA
         IGNORE_OPTIM_EMBEDDED_HINTS
         OPTIMIZER_FEATURES_ENABLE('19.1.0')
         DB_VERSION('19.1.0')
         OPT_PARAM('_fix_control' '27268249:0')
         FIRST_ROWS
         OUTLINE_LEAF(@"UPD$1")
         FULL(@"UPD$1" "TRAN_TAB"@"UPD$1")
         END_OUTLINE_DATA
     */
    
    Peeked Binds (identified by position):
    --------------------------------------
    
      3 - :B1 (NUMBER): 50
    
    Note
    -----
      - dynamic statistics used: statistics for conventional DML 
    
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    I still haven't found the article I wanted to point you to, but this one demonstrates the technique

    Read the whole thing, but the particular detail of creating a good enough function-based index can be found by searching for: "Let’s create a “function-based” index that ignores most of the data"

    In your case you could create an index a single-column index which held entries only for the 'W' and 'T' rows, and had about 20 index entries in total rather than 26M.


    For all columns size repeate can be dangerous because if ever one of the 6 values is not in the table when you gather stats then the num_distinct (and histogram) drops to 5 values, and then repeats, so you get a sample Top-N histogram and can lose "rare" values completely and end up with Oracle using "half the frequency of the least popular value" as the estimate for the missing value. For more detail see: https://jonathanlewis.wordpress.com/2017/06/01/histogram-upgrade-2/

    Don't use "size repeat" for this column use "size N" where N is larger than the number of distinct values that should appear (e.g. set it to 254). The fact that it's much too big doesn't matter, Oracle will create only 6 buckets and the processing won't be any different.

    Regards

    Jonathan Lewis

    SB35
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    Quick update - I ran up a demo table to show index creation and usage. Two options one for individual interesting states the other to get both states in one index (since there's only a very small number of values for each).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
    );
    
    select  lock_id 
    from    tran_tab 
    where   case when status = 'T' and pid is null then 0 end = 0 
    and     rownum <= 5;
    

    Note that the case expression in the where clause must be a near-perfect match for the expression in the index definition. This is why using a virtual column (preferably invisible from 12c) is a nice idea.

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

    Regards

    Jonathan Lewis

    SB35
  • SB35
    SB35 Member Posts: 106 Green Ribbon
    edited Jul 3, 2021 10:29AM

    Thank you @Jonathan Lewis . Something very odd is happening here.

    We have optimizer_mode set as = FIRST_ROWS. I am seeing , currently one of the UPDATE is taking the bad plan i.e showing 'TABLE ACCESS STORAGE FULL' in the path in the display_cursor output as i pasted below. But when i run the select part of the query manually its going for index scan path only. And when i forced a full scan to same SELECT query its appearing more costlier and it shows 'TABLE ACCESS STORAGE FULL FIRST ROWS'. How is that happening. How in run time optimizer reaching STORAGE FULL(which seems a all_rows plan) even we have first_rows setup done in database level?

    As i fetched the data below, The status 'T' is nowhere in the table and in the histogram now, so i was expecting a zero row estimation, but from where ~154k estimation is coming?

    Below is the current values in the table for STATUS column.


    Below is the values in DBA_TAB_HISTOGRAMS.

    With regards to the method_opt, is it good idea and safe in this running system to keep it AUTO rather having hardcoded bucket size of 254 , something like set 'for all column size repeat for columns size auto status'

    OR

    Keeping auto for all the colums with histogram present in table like 'for all columns size 1 for columns size auto status,col3,col10, col11'.

    My worry is , considering we are using INCREMENTAL stats collection approach, if this AUTO going to trigger stats collection on all historical partition and give us surprise some day?


    ************ Display_cursor output for the actual runtime UPDATE query***************
    
     ------------------------------------------------------------------------------------------------------------
    | Id | Operation                       | Name   | Rows | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------
    |  0 | UPDATE STATEMENT                |        |      |      | 9516 (100)|         |      |      |
    |  1 | UPDATE                         | TRAN_TAB |      |      |           |         |      |      |
    |  2 |  OPTIMIZER STATISTICS GATHERING |        |  154K|   27M| 9516  (8)| 00:00:01 |      |      |
    |* 3 |   COUNT STOPKEY                |        |      |      |           |         |      |      |
    |  4 |    PARTITION RANGE ALL         |        |  154K|   27M| 9516  (8)| 00:00:01 |    1 |  110 |
    |* 5 |     TABLE ACCESS STORAGE FULL  | TRAN_TAB |  154K|   27M| 9516  (8)| 00:00:01 |    1 |  110 |
    ------------------------------------------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
      1 - UPD$1
      5 - UPD$1 / [email protected]$1
    
    Outline Data
    -------------
    
     /*+
         BEGIN_OUTLINE_DATA
         IGNORE_OPTIM_EMBEDDED_HINTS
         OPTIMIZER_FEATURES_ENABLE('19.1.0')
         DB_VERSION('19.1.0')
         OPT_PARAM('_fix_control' '27268249:0')
         FIRST_ROWS
         OUTLINE_LEAF(@"UPD$1")
         FULL(@"UPD$1" "TRAN_TAB"@"UPD$1")
         END_OUTLINE_DATA
     */
    
    Peeked Binds (identified by position):
    --------------------------------------
    
      3 - :B1 (NUMBER): 50
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      3 - filter(ROWNUM<=:B1)
      5 - storage(("STATUS"='T' AND "ID" IS NULL))
          filter(("STATUS"='T' AND "ID" IS NULL))
    


    ***********Manually running SELECT part of the UPDATE query****************
    
      SQL> select ID, LOCK_ID,status from TRAN_TAB
     4 WHERE ID IS NULL AND STATUS = 'T' AND ROWNUM <= :B1 ;
    Elapsed: 00:00:00.00
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3252781178
    
    --------------------------------------------------------------------------------------------------------
    | Id | Operation           | Name           | Rows | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |                |  154K| 1664K|  776  (1)| 00:00:01 |      |      |
    |* 1 | COUNT STOPKEY      |                |      |      |           |         |      |      |
    |  2 |  PARTITION RANGE ALL|                |  154K| 1664K|  776  (1)| 00:00:01 |    1 |  110 |
    |* 3 |   INDEX RANGE SCAN | TRAN_TAB_IDX   |  154K| 1664K|  776  (1)| 00:00:01 |    1 |  110 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      1 - filter(ROWNUM<=TO_NUMBER(:B1))
      3 - access("STATUS"='T' AND "ID" IS NULL)
    
    Note
    -----
      - dynamic statistics used: statistics for conventional DML
    
    
     ********** with FULL Hint************************
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1672955918
    
    ------------------------------------------------------------------------------------------------------------------
    | Id | Operation                             | Name   | Rows | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT                      |        |  154K| 4085K| 9522  (8)| 00:00:01 |   | |
    |* 1 | COUNT STOPKEY                        |        |      |      |           |         |   | |
    |  2 |  PARTITION RANGE ALL                 |        |  154K| 4085K| 9522  (8)| 00:00:01 |    1 |  110 |
    |* 3 |   TABLE ACCESS STORAGE FULL FIRST ROWS| TRAN_TAB |  154K| 4085K| 9522  (8)| 00:00:01 |    1 |  110 |
    ------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      1 - filter(ROWNUM<=TO_NUMBER(:B1))
      3 - storage("STATUS"='T' AND "ID" IS NULL)
          filter("STATUS"='T' AND "ID" IS NULL)
    
    Note
    -----
      - dynamic statistics used: statistics for conventional DML
    
    
    
    


  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,061 Blue Diamond

    First - you should NOT be using optimizer_mode = first_rows; Oracle Corp. has been describing the option as "available for backwards compatibility only" for about the last 20 years. If you want to do something similar, but supported and easier to explain, you should use first_rows_N where N is 1, 10, 100, 1000. (10 might be a reasonable compromise for this type of code, even so the "rownum" predicate will by default operate as if the query is running first_rows(10)).

    One of the side-effects of first_rows is that the only way to work out what effect that has on the optimizer in your circumstances is to test it carefully on data sets that model different states of your actual data and stats. Bottom line - I probably can't explain EXACTLY why you're seeing what you're seeing - and there are too many guesses about how your data and stats vary to make it worth the effort.


    The cardinality estimate looks like the optimizer isn't using the histogram (and the fact that the Note says "dynamic stats used ..." is supports that suggestion. So the cardinality estimate is presumably based on:

    fraction of data (seen by sample) where id is null * 
    estimated total number of rows / number of distinct values seen in sample
    

    In first_rows optimisation, when the estimate cardinality is high, it's perfectly possible for the optimizer to decide a tablescan is the fastest option because it's assuming it will only have to do a couple of blocks of the scan to find a matching row (so the reported cost is not the actual estimated first_rows cost - first_rows(n) shows the effect of this assumption on the cost, see https://jonathanlewis.wordpress.com/2014/10/28/first-rows-2/ ) The combination of first_rows and dynamic_sampling is probably sufficient to explain any anomaly you're seeing. The more significant question is "why has Oracle decided to use dynamic sampling in your case?"


    Regarding "REPEAT" - I wouldn't use it (in 19c) for the reason given in the blog note I referenced. In this case it might not matter, but that's about the numbers and a bit of luck. When I suggested a fixed size I thought you had set a table preference for this table rather than setting a global "repeat"



    Regards

    Jonathan Lewis

  • SB35
    SB35 Member Posts: 106 Green Ribbon

    Thank You @Jonathan Lewis

    I am still trying to understand why do the NOTE section appearing(saying dynamic statistics used) when i execute the SELECT part of that query manually. Just to confirm, I checked using below but got no rows which means the stats is updated but not sure why the query is showing dynamic statistics usage here. I see for many queries in this database we see same note section. Can it be just side effect of the feature 'real-time statistics collection' which can be invoked automatically by the optimizer?

    select * from dba_tab_statistics where table_name='TRAN_TAB' and stale_stats<>'NO'

    Please correct me if wrong, Regarding the METHOD_OPT , our current method_opt is set at table level as 'For all columns size repeat' only and i understood that is problematic in current version and also 'for all columns size auto' had issue of creating lot of unnecessary histograms. Also considering we are using INCREMENTAL stats collection, "AUTO" can give us surprise and endup making the stats gather trigger for all historical partition if any new histogram pop up in some new partition. So it looks to me, at a minimum this should be safely replaced with 'For all columns size 1' just that we have to cater all the columns which currently have histogram on it i.e. we have to set it something like 'For all columns size 1 for columns size 254 STATUS, Column 2, column10.. etc'.

    But i was mainly thinking for those few histogram columns like "STATUS" column say, should we use 'for columns size AUTO STATUS' or 'for columns size 254 STATUS', considering we are using INCREMENTAL stats collection approach here and also the max number of bucket side is varying/increasing in latest releases?

  • SB35
    SB35 Member Posts: 106 Green Ribbon

    @Jonathan Lewis

    My bad , I just verified, all these plans for this UPDATE queries and also if i run those SELECT manually are showing the same NOTE section " dynamic statistics used: statistics for conventional DML", and i still to figure out why.

    And  also another thing is , as it seems both of these values(T and W) are highly volatile in nature and they are inserted/updated for very few rows only and changed to other status. It happens many times in the day. It may not be possible to gather stats so frequently to reflect exact data pattern in dba_histograms in this case. So how we should cater this scenario? s the only way is to apply hint on these query?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond

    This is, as far as I know, result of Oracle applying real time statistics - new feature introduced in 19C. Try setting _optimizer_gather_stats_on_conventional_dml and/or _optimizer_use_stats_on_conventional_dml.

    SY.