SQL & PL/SQL

A lock is for the protection of the integrity of a resource.
You now have an increase in processes wanting to access the same set of resources.
This obviously means an increase in contention for acquiring locks to access a resource.
Reducing the time waited for a lock is not going to decrease the demand for locks.
Two basic approaches to this.
Decrease the processing time that a process requires to hold a lock on the resource.
Address lock contention (processing wanting access to the same set of resources), by increasing the number of resources available, or by clever resource scheduling to reduce the likelihood of processes vying for access to the same resource at the same time.

You need to check what type of lock/wait it is. It could be with increased number of concurrent transactions multiple concurrent transactions are now updating same row. Then you need to check if update is committed right away or transaction continues and row is locked for a long(er) time and see if that time can be reduced. Or, with increased number of concurrent transactions it could be more transactions are trying to update different but residing in same block rows while MAXTRANS is less than number of concurrent transactions updating same block or there is simply insufficient free space in block to allocate another ITL slot. In such case you need to rebuild table with higher MAXTRANS/INITRANS.
SY.

This looks like the type of code that might be used to "get the next sequential number, use it, commit" to produce an unbroken list of sequential values. If so then you can't avoid queueing.
However, as SY says, you could ensure you minimise the time between the "select for update" and the "commit".
On thought that may be relevant - if you don't have to complete all the processing between the select and the commit before releasing the locked row you could look at strategies that use the dbms_lock package to serialise the working sessions while minimising the time a session has to wait. dbms_lock allows you to release its "user-defined" locks without commiting, so your code might, in outline, change to:

request exclusive "sync" lock
do some dml
release exclusive "sync" lock
do some more dml
commit;

Sessions would queue on an enqueue/lock of type 'UL', but you might be able to shorten the wait time.
Example of usage (though not an exact match for your requirement) available at this URL:
https://jonathanlewis.wordpress.com/2011/01/14/statspack-on-rac/
Regards
Jonathan Lewis

Thank You so much [Jonathan Lewis](/ords/forums/user/Jonathan Lewis) , [Solomon Yakobson](/ords/forums/user/Solomon Yakobson) . It looks like, the thought process of modifying it to 'NOWAIT' or decreasing the WAIT to lesser value , is not going to help us anyway in reducing those 'row lock contention'.
The enque type at top in the AWR is "TX-Transaction (row lock contention)", not seeing much ITL related waits during this interval AWR report.
I am not familiar with the dbms_lock but trying to understand the logic. The code doesn't seems to be fetching sequence number and using , rather doing something else. The part of the code from 'select for update' statement till 'ÇOMMIT' statement looks like below. Need to check with team, if we can really release lock before COMMIT and then perhaps we can get rid of execution of few statements. .

Thank you @billy-verreynne ,
I am not able to get it quite clear. Can you please elaborate a bit more, in this case, how can i increase the resources to make the situation better/getting rid of row lock contention?

The problem with your approach to serialisation in multi session environments with something like wait 20 and only one lock allocation ('LOCK_PRC'), is you no chance of scalability. With a handful of fairly active sessions, you will enqueue sessions almost immediately, with with waiters being blocked until the transaction is finished, especially if your delete or updates take a while. Then the first come first served contention happening all over again as soon as session 1 finishes its transaction. Your whole approach seems wrong to me, because of its synchronous and serialised nature.
You may be better utilising an advanced queue for something like this - issuing the commands via a message enqueued and just dequeueing them asynchronously in order, the issue may be any feedback to UI if your process is relying on that. Personally, I think you need to rethink your model so that sessions aren't fighting over one lock allocation or go with optimistic locking and don't bother with "for update".

As I mentioned, you can try to reduce the time the process needs to lock the resource, or increase number of resources.
I have no idea what resources you have defined in your LOCK_TAB table, and what the LOCK_PRC resource is.
But the options I mentioned is to make the process using LOCK_PROC requiring less time for its lock duration by reducing and optimising the workload that needs to be done while LOCK_PRC is locked. Alternatively design a LOCK_PROC_01 to LOCK_PROC_10 for example in order to increase the number of LOCK_PROC resources and support up to 10 locks to be placed on LOCK_PROC.
Whether one of these options are possible, depends on the reasons why there is currently only 1 LOCK_PROC resource, and just what is done workload wise while this resource is locked.
The code snippet you posted seems to point to basic serialisation - ensuring that the LOCK_PROC resource workload can only ever be executed one at a time. Thus this indicates the workload is not thread safe (cannot be run in parallel).
Serialisation destroys and kills performance when scaling a multi-process environment. Which is exactly what you done by increasing this resource usage from 200 to 300 times per hours to a 1000 times per hour.

Thank you @paulzip .
I read in past 'for update' is really required for certain processing like say bank transaction, in which if we allow multiple session to get hold of same row/amount and work on them ,then they may endup updating to some wrong value which they should not. But as this code doesn't seems to be doing such , is that why you are suggesting of optimistic locking? and do you mean that in this case the optimistic locking i.e just the SELECT without any 'for update' is going to give use better performance as compared to 'for update nowait' or 'for update wait 10 or 5' etc?

thank you @billy-verreynne
Still trying to understand this one and what exact resource its holding up, but actually the LOCK_PRC is the name of the procedure itself which is having this code, which i posted. So its gets called iteratively in the NO_DATA_FOUND exception. So it seems its only doing these insert/update/delete whatever is there in this code. but yes after commit it returns a refcursor something as below as output. But as this out refcursor is returned after commit(and no other code is there after), so should probably be considered not part of the locked resource i believe. Please correct me if wrong.
SELECT * FROM tran_tab WHERE status = 'I' AND pid = hid AND lock_id = lockid ORDER BY creat_date;.

My comments are from a s/w engineering perspective - why locking of resources is needed, and how one can deal with it ito short optimal workloads to reduce resource contention, and increasing the amount of resources.
Had a quick look at the code you posted. But beyond seeing the serialisation of LOCK_PRC, it is difficult to provide suggestions on how to scale this. The bigger picture is needed. What business flow processing is addressed? What are the business requirements and constraints?
Serialised access to a resource is important if the resource is not multi-thread capable. This is not an error.
What can be the error is when the resource can be designed as multi-thread capable, or the requirements can be better addressed differently than via serialised access.
There is no technical solution to your resource contention problem, without knowing and addressing the bigger picture.

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.

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

[Jonathan Lewis](/ords/forums/user/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 

I still haven't found the article I wanted to point you to, but this one demonstrates the technique
Index Engineering (0 Bytes)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

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

Thank you [Jonathan Lewis](/ords/forums/user/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 / TRAN_TAB@UPD$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

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

Thank You @jonathan-lewis2
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?

@jonathan-lewis2
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?

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.

I think both of these are modifiable at system level. But if i want to test if my query behavior changes or say the dynamic statistics usage in the note section will really change, is there anyway i can turn off the real-time statistics at session level?

Session level:

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = false;

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = false;

Session altered.

SQL>

SY.

For example:

SQL> EXPLAIN PLAN FOR
  2  SELECT  COUNT(*)
  3    FROM  activity qRoot
  4    WHERE qRoot.Retired = 0
  5  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

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

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

Note
-----
   - dynamic statistics used: statistics for conventional DML

19 rows selected.

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = false
  2  /

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = false
  2  /

Session altered.

SQL> EXPLAIN PLAN FOR
  2  SELECT  COUNT(*)
  3    FROM  activity qRoot
  4    WHERE qRoot.Retired = 0
  5  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

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

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

15 rows selected.

SQL>

You can also control it on statement level:

SQL> show parameter "_optimizer_gather_stats_on_conventional_dml"

NAME                                        TYPE        VALUE
------------------------------------------- ----------- ------------------------------
_optimizer_gather_stats_on_conventional_dml boolean     FALSE

SQL> show parameter "_optimizer_use_stats_on_conventional_dml"

NAME                                     TYPE        VALUE
---------------------------------------- ----------- ------------------------------
_optimizer_use_stats_on_conventional_dml boolean     FALSE

SQL> EXPLAIN PLAN FOR
  2  SELECT  --+ OPT_PARAM('_optimizer_use_stats_on_conventional_dml' 'true')
  3          COUNT(*)
  4    FROM  activity qRoot
  5    WHERE qRoot.Retired = 0
  6  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

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

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

Note
-----
   - dynamic statistics used: statistics for conventional DML

19 rows selected.

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = true
  2  /

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = true
  2  /

Session altered.

SQL> show parameter "_optimizer_gather_stats_on_conventional_dml"

NAME                                        TYPE        VALUE
------------------------------------------- ----------- ------------------------------
_optimizer_gather_stats_on_conventional_dml boolean     TRUE

SQL> show parameter "_optimizer_use_stats_on_conventional_dml"

NAME                                     TYPE        VALUE
---------------------------------------- ----------- ------------------------------
_optimizer_use_stats_on_conventional_dml boolean     TRUE

SQL> EXPLAIN PLAN FOR
  2  SELECT  --+ OPT_PARAM('_optimizer_use_stats_on_conventional_dml','false')
  3          COUNT(*)
  4    FROM  activity qRoot
  5    WHERE qRoot.Retired = 0
  6  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

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

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

15 rows selected.

SQL>

SY.

Statistics: On the importance of the Notes column:
Jonathan is correct: that was the post by @mohamed-houri2:
Statistics: On the importance of the Notes column

Best regards,
Sayan Malakshinov
http://orasql.org

@jonathan-lewis2 @solomon-yakobson @sayan-malakshinov Thank You so much.
Yes , i am also seeing by disabling the real time stats collection using hint (OPT_PARAM('_optimizer_use_stats_on_conventional_dml','false')) , the dynamic statistics note section disappears from the plan. But still , i am not seeing that "STORAGE FULL" path getting reproduced. So may be something odd happening at run time making "STORAGE FULL FIRST ROWS" changed to "STORAGE FULL" path.
And i see in dba_part_col_statistics all the NOTES section against each partition for that column STATUS set as "HYPERLOGLOG ", so its not really matching with the blog note which states , discard of histogram may be result of "HIST_FOR_INCREM_STATS HYPERLOGLOG".
My Apology if i miss the answer somewhere, Its regarding, on 19C what exact METHOD_OPT option we should use(mainly for partition table with INCREMENTAL stats ON) in place of "FOR ALL COLUMSN SIZE REPEAT" on an existing table having few columns already HISTOGARM created on them.
Say for example a partition table TAB1 having three columns col1, col2, col3 but having COl2 with height balanced histogram and col3 having frequency histogram already present on it. Should we use
1)"For all columsn size 1 for columns size auto col2 col3"
2)"For all columsn size 1 for columns size auto col2 FOR COLUMNS SIZE 254 col3"
3)"For all columsn size AUTO"

@sayan-malakshinov
Sayan,
Thanks for supplying a reference - unfortunately that wasn't the one I had in mind; there was something I read about a year ago.
Regards
Jonathan Lewis

[Sandeep Gandhi, Consultant](/ords/forums/user/Sandeep Gandhi, Consultant)SB35
One little detail - for 19c it ought to be hybrid, not height-balanced. You'd only get height balanced if you were setting an actual value for estimate_percent rather than auto_sample_size.
Generally my preference is to avoid "size auto" completely and then setting individual "safe" sizes for columns that I knew ought to have frequency or top-frequency histograms. I do not like hybrid or height-balanced histograms - hybrid are better than height balanced, but they are still subject to instability because the stats are sampled.
So my preliminary choice would be:

for all columns size 1 for columns size X col2 for columns size Y col3

Where X and Y would be set to sizes where I was confident they would be safe candidates for frequency or top-frequency histograms. Remember that from 12c the number of buckets can go up to 2047 (or maybe 2048) so if you think an old height-balanced would have been useful then aiming for a top-frequency by setting a large value for X would be good. As for the col3 - if there should only be a small number of values (like the 7 or 8 in your example) than I might put in Y = 15 to give comfortable room for values that might be invented in the future - the excess specification won't change the workload or the number of buckets finally produced.
Regards
Jonathan Lewis