Re: Locking issue

What version? I've hit a known issue after upgrading to 19.11. I had to open SR, but there is also an article in public domain here:

https://mikedietrichde.com/2021/05/04/ora-1422-and-ora-6512-from-sys-dbms_stats-in-post-upgrade/

You are not getting errors but there is also a possibility of different behavior.

We are seeing some strange locking situations because of statistics gathering. While the stats gathering was running on an object and at same time a SELECT on that object getting 'row cache lock', We want to understand why it's happening so? Has it anything to do with the 'dynamic statistics usage' which we are seeing in the note section of the plan?

Below is the occurrence of the issue..

Stats gather started non partition table -TAB1 as part of auto gather job (i.e. gather_database_stats_job_proc) and it started around 10:08PM. And multiple internal sqls were running as part of that stats gather and around 11:06PM we last saw the below statement executed in that session. 

select /*+ opt_param('_optimizer_use_auto_indexes' 'on')  no_parallel_index(t, "XXX") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad no_expand index_ffs(t,"XXXX") */ count(*) as nrw,count(distinct sys_op_lbid(228647,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("XXX")||sys_op_descend("XXX")||sys_op_descend("XXX"))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "USER1"."TAB1" sample block (  .0413656293,1) t where "XXX" is not null or "XXX" is not null or "XXXX" is not null

Then at around 11:07:00.687 PM, we have below application query started running from multiple sessions and started experiencing "row cache lock" with P1(cache id=62) as "dc_realtime_colst" , P2(mode) as 3 i.e. 'share mode' and P3(request) as 3 i.e. 'share mode'. Nothing found in the blocking session.  Subsequently few other session of same query went into "row cache lock" with P1(cache id=63) as "dc_realtime_tabst" , P2(mode) as 0 i.e. 'null mode - not locked' and P3(request) as 3 i.e. 'share mode'.

 Then we noticed at around 11:07:00.695 PM , the same stats gather session itself started experiencing "row cache lock" with P1(cache id=62) as "dc_realtime_colst" , P2(mode) as 0 i.e. 'null mode - not locked' and P3(request) as 5 i.e. 'exclusive mode'.

SELECT /*+ FULL(P) +*/ * FROM "TAB1" P

------------------------------------------------------------------------------------------------------------------

| Id | Operation              | Name           | Rows | Bytes | Cost (%CPU)| Time   |

------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT           |              |    |    | 3777K(100)|     |

|  1 | TABLE ACCESS STORAGE FULL FIRST ROWS| TAB1           |  733M|  169G| 3777K (1)| 00:02:28 |

------------------------------------------------------------------------------------------------------------------

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(_at_"SEL$1")

   FULL(_at_"SEL$1" "P"_at_"SEL$1")

   END_OUTLINE_DATA

 */

 

Note

-----

  - dynamic statistics used: statistics for conventional DML 

 

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com