nls_database_parameters, dc_props, latch: row cache objects

This Blog will demonstrate latch: row cache objects on Dictionary Cache dc_props when querying nls_database_parameters.

Note: all tests are done in Oracle 12.1.0.2.0.

Reference: NLS understanding in this Blog is acquired and cited from:
    Oracle MOS: The Priority of NLS Parameters Explained (Where To Define NLS Parameters) (Doc ID 241047.1)

1. latch: row cache objects

At first, find v$rowcache.cache# mapping to v$latch_children.child#, then query nls_database_parameters, collecting statistics before and after test:

select r.kqrstcid cache#, r.kqrsttxt parameter, c.child#  --, r.*, c.* 
from x$kqrst r, v$latch_children c 
where r.kqrstcln = c.child# 
  and r.kqrsttxt = 'dc_props'
  and c.name     = 'row cache objects';

 CACHE# PARAMETER  CHILD#
------- --------- -------
     15 dc_props       18

select r.cache#, r.type, r.parameter, r.gets, r.count, c.name, c.child#, c.gets latch_gets
from v$rowcache r, v$latch_children c
where r.parameter = 'dc_props' and c.name = 'row cache objects' and c.child# = 18;  

 CACHE#   TYPE PARAMETER     GETS  COUNT              NAME  CHILD#  LATCH_GETS
------- ------ --------- -------- ------ ----------------- ------- -----------
     15 PARENT dc_props    140920     60 row cache objects      18      422820
                                                                    
select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
  VALUE   
  --------
  AL32UTF8
  
select r.cache#, r.type, r.parameter, r.gets, r.count, c.name, c.child#, c.gets latch_gets
from v$rowcache r, v$latch_children c
where r.parameter = 'dc_props' and c.name = 'row cache objects' and c.child# = 18;  

 CACHE#   TYPE PARAMETER     GETS  COUNT              NAME  CHILD#  LATCH_GETS
------- ------ --------- -------- ------ ----------------- ------- -----------
     15 PARENT dc_props    140980     60 row cache objects      18      423000

We can see that dc_props GETs increased 60 (140980 - 140920), and LATCH_GETS increased 180 (423000 - 422820).

nls_database_parameters is defined by:

create or replace force view sys.nls_database_parameters (parameter, value) as
  select name, substr (value$, 1, 64) from x$props where name like 'NLS%';

A query on v$rowcache_parent for 'dc_props' shows:

select cache_name, existent, count(*) cnt from v$rowcache_parent 
 where cache_name = 'dc_props' group by cache_name, existent;

  CACHE_NAME  EXISTENT   CNT
  ----------- --------- ----
  dc_props    Y           37
  dc_props    N           23

It contains 60 rows, of which 37 are existing objects, 23 are not. Total number of 'dc_props' Cache entries: v$rowcache.COUNT = 60 is corresponding to 60 parent objects of v$rowcache_parent.

37 existing objects can be confirmed by:

select count(*) from x$props; 
   COUNT(*)
  ---------
         37

These 37 existing objects are probably originated from:

select * from sys.props$;
select * from database_properties;

Since the query on nls_database_parameters is a TABLE FULL scan on X$PROPS.

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    28 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$PROPS |     1 |    28 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

and v$rowcache_parent contains 60 rows for 'dc_props', a TABLE FULL scan makes 60 GETs in v$rowcache, although the underlying X$PROPS contains only 37 EXISTENT objects. It seems that NON-EXISTENT objects also require the similar handling.

For each rowcache GET, it requires 3 "latch: row cache objects" GETs, that is why we have 180 LATCH_GETS.
(see Oracle Core: Essential Internals for DBAs and Developers Page 167:
    there was a common pattern indicating three latch gets for each dictionary cache get)

Oracle 12c V$ROWCACHE_PARENT wrote:

V$ROWCACHE_PARENT displays information for parent objects in the data dictionary. There is one row per lock owner, and one waiter for each object. This row shows the mode held or requested. For objects with no owners or waiters, a single row is displayed.

In case of acute wait of latch: row cache objects, we can try to find the lock owner or waiter by:

select s.sid, s.program, r.* 
  from v$rowcache_parent r, v$session s
 where cache_name = 'dc_props' 
   and (lock_mode != '0' or lock_request != '0')
   and r.saddr = s.saddr(+);

In essence, dc_props seems the Dictionary Cache (Row Cache) of Sys Table: sys.props$.

2. NLS_CHARACTERSET semantics and Solutions

Oracle stores NLS settings in 3 levels: database/instance/session:

  nls_database_parameters
  nls_instance_parameters
  nls_session_parameters
  
and the precedence is ordered from low to high.
(Note: Any setting explicit used in SQL will always take precedence about all other settings)

In application semantics, session specific NLS settings should be selected because of precedence.

Additionally, it defines:

  v$nls_parameters 

A view that shows the current session parameters and the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.

MOS Doc ID 241047.1 Section C) The Database Parameters - NLS_DATABASE_PARAMETERS wrote:

These parameters are "overruled" by NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS.
The only exception are the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET settings.
The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be overruled by instance or session parameters.
They are defined by the value specified in "create database ..." and are not intended to be changed afterwords dynamically.

Look the definition of nls_session_parameters:

CREATE OR REPLACE FORCE VIEW SYS.NLS_SESSION_PARAMETERS (PARAMETER, VALUE) AS
SELECT SUBSTR (parameter, 1, 30), SUBSTR (VALUE, 1, 64)
  FROM v$nls_parameters
 WHERE     parameter != 'NLS_CHARACTERSET'
       AND parameter != 'NLS_NCHAR_CHARACTERSET';

and definition of gv$nls_parameters (underlying view):

SELECT inst_id,
       parameter,
       VALUE,
       con_id
  FROM x$nls_parameters
 WHERE parameter != 'NLS_SPECIAL_CHARS'

and count each by:

select count(*) from nls_session_parameters;  
   COUNT(*)
  ---------
         17
  
select count(*) from v$nls_parameters;             
   COUNT(*)
  ---------
         19  

The discrepancy are exactly the two excluded NLS_(NCHAR)_CHARACTERSET parameters, nls_session_parameters is a subset of v$nls_parameters.

select parameter, value from v$nls_parameters 
minus
select parameter, value from nls_session_parameters;

  PARAMETER              VALUE
  ---------------------- ---------
  NLS_CHARACTERSET       AL32UTF8
  NLS_NCHAR_CHARACTERSET AL16UTF16

Therefore, in order to avoid latch: row cache objects on dc_props, we can run the equivalent:

  select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

instead of query:

  select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

The later generates 60 rowcache GETs, and 180 latch: row cache objects on dc_props for each run. Therefore, severe row cache objects latch contention occurs if we frequently query nls_database_parameters.

We can verify this solution by:

select r.cache#, r.type, r.parameter, r.gets, r.count, c.name, c.child#, c.gets latch_gets
from v$rowcache r, v$latch_children c
where r.parameter = 'dc_props' and c.name = 'row cache objects' and c.child# = 18;  

 CACHE#   TYPE PARAMETER    GETS  COUNT              NAME  CHILD#  LATCH_GETS
------- ------ --------- ------- ------ ----------------- ------- -----------
     15 PARENT dc_props   141046     60 row cache objects      18      423198
                                                                    
select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
  VALUE   
  --------
  AL32UTF8
   
select r.cache#, r.type, r.parameter, r.gets, r.count, c.name, c.child#, c.gets latch_gets
from v$rowcache r, v$latch_children c
where r.parameter = 'dc_props' and c.name = 'row cache objects' and c.child# = 18;  

 CACHE#   TYPE PARAMETER    GETS  COUNT              NAME  CHILD#  LATCH_GETS
------- ------ --------- ------- ------ ----------------- ------- -----------
     15 PARENT dc_props   141046     60 row cache objects      18      423198

Obviously there are neither rowcache GETs, nor latch: row cache objects, because NLS session parameters (v$nls_parameters) are stored in session memory UGA and are not visible for other sessions, whereas nls_database_parameters is setup in a DB wide globally shared space (SGA) and have to be protected by latch: row cache objects.

In general, latch: row cache objects is documented as protecting data dictionary cache, but we saw that it is also used to safeguard X$PROPS.

Similar to v$nls_parameters, database NLS_CHARACTERSET can also be gotten by:

select sys_context('userenv','language') from dual;
select userenv ('language') from dual;    -- userenv 12c deprecated 

without rowcache GETs and latch: row cache objects.

sys_context('userenv','language') returns the language and territory currently used by the current session, along with the database character set in UNIX-like format:

   language_territory.characterset

Usually SYS_CONTEXT returns the value of parameter associated with the context namespace at the current instant for the current session. Since database NLS_CHARACTERSET is a database wide parameter, sys_context returns the correct value.

Further more, MOS Doc ID 241047.1 explained the difference between database character set and OS env NLS_LANG character set:

The database character set is not the same as the character set of the NLS_LANG that you started this connection with!
The OS environment NLS_LANG (from where you started this connection) is reflected in v$session_connect_info.CLIENT_CHARSET:

  select CLIENT_CHARSET, v.* from v$session_connect_info v;

MOS Doc ID 241047.1 wrote:

D1) sys.props$
    SQL>select name,value$ from sys.props$ where name like '%NLS%';
    This gives the same info as NLS_DATABASE_PARAMETERS. You should use NLS_DATABASE_PARAMETERS instead of props$

We saw that NLS_DATABASE_PARAMETERS is underlined by X$PROPS, whereas SYS.PROPS$ is a real table. The former select generates row cache GETs and requires latch: row cache objects, the later not. Therefore from performance point of view, they are not the same.

In summary:

(A). nls_database_parameters is underlined by x$props, which requires latch: row cache objects on Dictionary Cache dc_props.

(B). v$nls_parameters is underlined by x$nls_parameters.

(C). sys_context('userenv','language') is underlined by sys.props$, which is a real table.
     It returns the language and territory of current session in UNIX (NLS_LANG) like format: 
        language_territory.characterset

(D). nls_session_parameters is a subset of v$nls_parameters.
     v$nls_parameters has two additional rows of *DATABASE* characterset (NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET) 
     from nls_database_parameters.
   
(E). To find *DATABASE* characterset (NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET), 
     both nls_database_parameters and v$nls_parameters can be used. 
     The difference is that nls_database_parameters requires "latch: row cache objects", but v$nls_parameters not.

3. 'latch: row cache objects' Contention Test

Launch 2 Jobs (see appended Test Code), monitor "latch: row cache objects" contentions.

SQL > exec nls_select_jobs(2, 1e9);

column p1text format a10                
column p2text format a10 
column p3text format a10                                      
column event format a25 

SQL > select sid, event, p1text, p1, p1raw, p2text, p2, p2raw, p3text, p3, p3raw
        from v$session where program like '%(J0%';

    SID EVENT                     P1TEXT           P1 P1RAW            P2TEXT    P2 P2RAW            P3TEXT   P3 P3RAW
  ----- ------------------------- -------- ---------- ---------------- ------- ---- ---------------- ------- --- -----
      6 latch: row cache objects  address  6376016176 000000017C0A4930 number   411 000000000000019B tries     0 00
    287 latch: row cache objects  address  6376016176 000000017C0A4930 number   411 000000000000019B tries     0 00
  
--After Test stop all jobs by:
--SQL > exec clean_jobs;

Display the top latch misses and their locations, which are kqrpre and kqreqd. The sum of SLEEP_COUNT is close to sum of WTR_SLP_COUNT. Their data distribution could indicate certain Holder-Waiter logic.

column PARENT_NAME format a30                                                     
column WHERE format a20 

SQL > select * from (select * from v$latch_misses order by sleep_count desc)
       where rownum <= 5;

  PARENT_NAME                  WHERE                NWFAIL_COUNT SLEEP_COUNT WTR_SLP_COUNT LONGHOLD_COUNT LOCATION
  ---------------------------- -------------------- ------------ ----------- ------------- -------------- ----------------
  row cache objects            kqrpre: find obj                0       64009         94027              0 kqrpre: find obj
  row cache objects            kqreqd: reget                   0       61416         30578              0 kqreqd: reget
  row cache objects            kqreqd                          0       35943         36768              0 kqreqd
  space background task latch  ktsj_grab_task                  0           4             4              0 ktsj_grab_task
  call allocation              ksuxds                          0           3             3              0 ksuxds

(Note: WHERE column is obsolete, it is always equal to the value in LOCATION)

4. Blocker and Waiter Demo

As a demo, we manually create 'latch: row cache objects' blocking. In one Session (SID 123, SPID 3456), stop it before it release the held 'latch: row cache objects', so it becomes a latch Holder, then run sql. Session is stopped with a callstack.

$ > dtrace -w  -n \
'pid$target::kqreqd:entry  /execname == "oracle"/ {self->rin = 1;}
pid$target::kslgetl:return  /execname == "oracle" && self->rin > 0 / 
{@[pid, ustack(5, 0)] = count(); stop(); exit(0);}
' -p 3456 

  -- Blocker SID = 123 --
  oracle`kslgetl+0x185
  oracle`kkdlpExecSql+0x20c
  oracle`kkdlpftld+0x147
  oracle`qerfxFetch+0x125f
  oracle`opifch2+0x188b
  
$ > pstack 3456
 0000000001ec38f8 kqreqd () + d8
 00000000028ee50c kkdlpExecSql () + 20c
 000000000757a7f7 kkdlpftld () + 147
 000000000204467f qerfxFetch () + 125f
 0000000001ee765b opifch2 () + 188b  


SQL (123) > exec nls_select(1);

// one can also use:
// dtrace -w -n 'pid$target::kqrLockAndPinPo:entry {@[pid, ustack(5, 0)] = count(); stop(); exit(0);}' -p 3456 

In a second session, run the same statement. It will be blocked, and pstack prints out its requesting code path.

SQL (789) > exec nls_select(1);

$ > pstack 5678

  -- Waiter SID = 789 --
  fffffd7ffc9d3e3b semsys   (2, 1000001f, fffffd7fffdf0af8, 1, 124a)
  0000000001ab90f5 sskgpwwait () + 1e5
  0000000001ab8c95 skgpwwait () + c5
  0000000001aaae69 kslges () + 5b9
  0000000001ebd12e kqrpre1 () + 72e
  00000000028ee49b kkdlpExecSql () + 19b
  000000000757a7f7 kkdlpftld () + 147
  000000000204467f qerfxFetch () + 125f
  0000000001ee765b opifch2 () + 188b


select sid, event, p1text, p1, p1raw, p2text, p2, p2raw, p3text, p3, p3raw, blocking_session, final_blocking_session
 from v$session s where sid in (123, 789);
 
SID EVENT                        P1TEXT             P1 P1RAW     P2TEXT   P2 P2RAW  P3TEXT P3 P3RAW  bs fbs
--- ---------------------------- ---------- ---------- --------- ------ ---- ------ ------ -- ----- --- ---
123 SQL*Net message from client  driver id  1413697536 054435000 #bytes    1 000001         0 00                                       
789 latch: row cache objects     address    6376016176 17C0A4930 number  411 00019B tries   0 00    123 123

(Note: shortcut bs for BLOCKING_SESSION, shortcut fbs for FINAL_BLOCKING_SESSION)
 
select * from v$latchholder;
select * from v$session_blockers where sid in (123, 789);
select * from v$session_wait where sid in (123, 789);
select * from v$session_wait_history where sid in (123, 789) order by seq# desc, sid;

5. rowcache Gets vs. latch Gets

For each rowcache GET, it requires 3 "latch: row cache objects" GETs as revealed by Book: Oracle Core: Essential Internals for DBAs and Developers (Page 167).

With following dtrace, we can see the code path and call count. All of them stem from kkdl (dictionary lookup) subroutines.

As demonstrated at the Blog's beginning, 'dc_props' contains 60 rows, 1000 runs will result in 60'000 rowcache Gets, and 180'000 latch Gets. Interestingly the output shows that 60 'dc_props' rowcache rows seems splitted into two Buckets (kkdlpftld+0x147 and kkdlpftld+0x351), each with 30 rows.

SQL > exec nls_select(1000);

sudo dtrace -n \
'pid$target::kqreqd:entry  /execname == "oracle"/ {self->rin = 1;}
pid$target::kslgetl:entry  /execname == "oracle"/ {self->lin = 1;}
pid$target::kqreqd:return  /self->rin > 0/ {@ROWCO_CNT[ustack(6, 0)] = count(); self->rin = 0;}
pid$target::kslgetl:return /self->lin > 0/ {@LATCH_CNT[ustack(6, 0)] = count(); self->lin = 0;}  
' -p 7482 

//---- rowcache Gets ----
    oracle`kqreqd+0x359        // row cache ...
    a.out`kkdlpftld+0x351      // dictionary lookup ...
    a.out`qerfxFetch+0x125f    // fixed table Fetch
    oracle`opifch2+0x188b      // fetch main routine
    oracle`opiefn0+0x1f7       // initialize opi
    oracle`opipls+0x352c
  30000
  
    oracle`kqreqd+0x359
    a.out`kkdlpftld+0x147
    a.out`qerfxFetch+0x125f
    oracle`opifch2+0x188b
    oracle`opiefn0+0x1f7
    oracle`opipls+0x352c
  30000
  

//------- latch Gets -------  
    oracle`kslgetl+0x185         // get latch
    a.out`kkdlpExecSql+0x19b
    a.out`kkdlpftld+0x351
    a.out`qerfxFetch+0x125f
    oracle`opifch2+0x188b
    oracle`opiefn0+0x1f7
  30000
  
    oracle`kslgetl+0x185
    a.out`kkdlpExecSql+0x19b
    a.out`kkdlpftld+0x147
    a.out`qerfxFetch+0x125f
    oracle`opifch2+0x188b
    oracle`opiefn0+0x1f7
  30000
  
    oracle`kslgetl+0x185
    a.out`kkdlpExecSql+0x20c
    a.out`kkdlpftld+0x351
    a.out`qerfxFetch+0x125f
    oracle`opifch2+0x188b
    oracle`opiefn0+0x1f7
  60000
  
    oracle`kslgetl+0x185
    a.out`kkdlpExecSql+0x20c
    a.out`kkdlpftld+0x147
    a.out`qerfxFetch+0x125f
    oracle`opifch2+0x188b
    oracle`opiefn0+0x1f7
  60000

Following documents have a deep investigation of Latches and Mutexes:

  New features of Latches and Mutexes in Oracle 12c.
  Latch, mutex and beyond
  Latch, mutex and beyond(Dtrace)
  Oracle 12 and latches
  Oracle 12 and latches, part 2

The kslgetl (get the exclusive latch) and ksl_get_shared_latch functions take the following arguments:

  1-latch address
  2-immediate get (0 means yes, 1 means no)
  3-where (X$KSLLW.INDX)
  4-why   (X$KSLWSC.INDX)
  5-mode (8=shared,16=exclusive; only for ksl_get_shared_latch function)

kslgetl() performs:

  – sskgslgf (immediate latch get)
  – kslges (kernel service latch get spinning, wait latch get)
  — kskthbwt
  — kslwlmod (setup wait list)
  — sskgslgf (immediate latch get)
  — skgpwwait (sleep latch get)
  — semop

6. row cache objects Queries

When there are contentions on 'latch: row cache objects', the instant and historical associated dc objects can be found by:

select c.addr, c.latch#, c.child#, c.name, c.gets latch_gets, c.misses, c.sleeps, c.spin_gets, c.wait_time, c.gets/(nullif(r.gets, 0)) ratio
      ,r.cache#, r.type, r.parameter, r.count, r.gets rowcache_gets, r.getmisses
      ,s.sid, s.serial#, program, s.user#, s.username, s.sql_id, s.row_wait_obj#
      ,(select owner||'.'||object_name from dba_objects where object_id = s.row_wait_obj#) object_name
      ,s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw
      ,(select  owner || '.' || object_name
              || case when procedure_name is not null then '.' || procedure_name end
         from dba_procedures
        where     object_id = s.plsql_entry_object_id and subprogram_id = s.plsql_entry_subprogram_id)
         plsql_entry
      ,(select   owner || '.'|| object_name
              || case when procedure_name is not null then  '.' || procedure_name end
         from dba_procedures
        where  object_id = s.plsql_object_id and subprogram_id = s.plsql_subprogram_id)
         plsql   
      ,(select dbms_lob.substr (sql_text, 50, 1) from dba_hist_sqltext where sql_id = s.sql_id and rownum = 1)
       sql_txt   
from v$session s, v$latch_children c, x$kqrst x, v$rowcache r
where (s.event = 'latch: row cache objects' or s.p2 = c.latch#)
  and s.p1raw = c.addr 
  and c.child# = x.kqrstcln
  and x.kqrstcid = r.cache#
  and c.name = 'row cache objects' 
--  and r.parameter = 'dc_props' and c.child# = 18
order by sid; 
  
with sq as (select /*+ materialize */ * from v$active_session_history where sample_time > sysdate - 10/1440)  -- last 10 minutes
select /*+ leading(sq) */
      c.addr, c.latch#, c.child#, c.name, c.gets latch_gets, c.misses, c.sleeps, c.spin_gets, c.wait_time, c.gets/(nullif(r.gets, 0)) ratio
      ,r.cache#, r.type, r.parameter, r.count, r.gets rowcache_gets, r.getmisses
      ,s.sample_time, s.session_id, s.session_serial#, program, s.user_id, s.sql_id, s.current_obj#
      ,(select owner||'.'||object_name from dba_objects where object_id = s.current_obj#) object_name
      ,s.event, s.p1text, s.p1, to_char(s.p1, 'XXXXXXXXXXXXXXXXXXXX') p1raw
      ,s.p2text, s.p2, to_char(s.p2, 'XXXXXXXXXXXXXXXXXXXX') p2raw 
      ,s.p3text, s.p3, to_char(s.p3, 'XXXXXXXXXXXXXXXXXXXX') p3raw
      ,(select  owner || '.' || object_name
              || case when procedure_name is not null then '.' || procedure_name end
         from dba_procedures
        where     object_id = s.plsql_entry_object_id and subprogram_id = s.plsql_entry_subprogram_id)
         plsql_entry
      ,(select   owner || '.'|| object_name
              || case when procedure_name is not null then  '.' || procedure_name end
         from dba_procedures
        where  object_id = s.plsql_object_id and subprogram_id = s.plsql_subprogram_id)
         plsql   
      ,(select dbms_lob.substr (sql_text, 50, 1) from dba_hist_sqltext where sql_id = s.sql_id and rownum = 1)
       sql_txt   
from sq s, v$latch_children c, x$kqrst x, v$rowcache r
where (s.event = 'latch: row cache objects' or s.p2 = c.latch#)
  and c.addr like '%'||(trim(to_char(p1, 'XXXXXXXXXXXXXXXXXXXX')))||'%'  
  and c.child# = x.kqrstcln
  and x.kqrstcid = r.cache#
  and c.name = 'row cache objects' 
--  and r.parameter = 'dc_props' and c.child# = 18
order by s.sample_time desc, s.session_id, s.session_serial#; 

Here two respective queries in short of access to x$kqrst. Then map CHILD# to dc CACHE# with a mapping table (created from a test DB of same Oracle Version by the query at the beginning of Blog).

select c.addr, c.latch#, c.child#, c.name, c.gets latch_gets, c.misses, c.sleeps, c.spin_gets, c.wait_time
      ,s.sid, s.serial#, program, s.user#, s.username, s.sql_id, s.row_wait_obj#
      ,(select owner||'.'||object_name from dba_objects where object_id = s.row_wait_obj#) object_name
      ,s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2, s.p2raw, s.p3text, s.p3, s.p3raw
      ,(select  owner || '.' || object_name
              || case when procedure_name is not null then '.' || procedure_name end
         from dba_procedures
        where     object_id = s.plsql_entry_object_id and subprogram_id = s.plsql_entry_subprogram_id)
         plsql_entry
      ,(select   owner || '.'|| object_name
              || case when procedure_name is not null then  '.' || procedure_name end
         from dba_procedures
        where  object_id = s.plsql_object_id and subprogram_id = s.plsql_subprogram_id)
         plsql   
      ,(select dbms_lob.substr (sql_text, 50, 1) from dba_hist_sqltext where sql_id = s.sql_id and rownum = 1)
       sql_txt   
from v$session s, v$latch_children c
where (s.event = 'latch: row cache objects' or s.p2 = c.latch#)
  and s.p1raw = c.addr 
  and c.name = 'row cache objects'
--  and c.child# = 18
order by sid; 
  
with sq as (select /*+ materialize */ * from v$active_session_history where sample_time > sysdate - 10/1440)  -- last 10 minutes
select /*+ leading(sq) */
      c.addr, c.latch#, c.child#, c.name, c.gets latch_gets, c.misses, c.sleeps, c.spin_gets, c.wait_time
      ,s.sample_time, s.session_id, s.session_serial#, program, s.user_id, s.sql_id, s.current_obj#
      ,(select owner||'.'||object_name from dba_objects where object_id = s.current_obj#) object_name
      ,s.event, s.p1text, s.p1, to_char(s.p1, 'XXXXXXXXXXXXXXXXXXXX') p1raw
      ,s.p2text, s.p2, to_char(s.p2, 'XXXXXXXXXXXXXXXXXXXX') p2raw 
      ,s.p3text, s.p3, to_char(s.p3, 'XXXXXXXXXXXXXXXXXXXX') p3raw
      ,(select  owner || '.' || object_name
              || case when procedure_name is not null then '.' || procedure_name end
         from dba_procedures
        where     object_id = s.plsql_entry_object_id and subprogram_id = s.plsql_entry_subprogram_id)
         plsql_entry
      ,(select   owner || '.'|| object_name
              || case when procedure_name is not null then  '.' || procedure_name end
         from dba_procedures
        where  object_id = s.plsql_object_id and subprogram_id = s.plsql_subprogram_id)
         plsql   
      ,(select dbms_lob.substr (sql_text, 50, 1) from dba_hist_sqltext where sql_id = s.sql_id and rownum = 1)
       sql_txt   
from sq s, v$latch_children c
where (s.event = 'latch: row cache objects' or s.p2 = c.latch#)
  and c.addr like '%'||(trim(to_char(p1, 'XXXXXXXXXXXXXXXXXXXX')))||'%'  
  and c.name = 'row cache objects' 
--  and c.child# = 18
order by s.sample_time desc, s.session_id, s.session_serial#; 


LATCH_CHILD  CACHE# PARAMETER (Oracle Version 12.1.0.2.0)
----------- ------- -------------------------------------
          1       3 dc_rollback_segments
          2       1 dc_free_extents
          3       4 dc_used_extents
          4       2 dc_segments
          5       0 dc_tablespaces
          6       5 dc_tablespace_quotas
          7       6 dc_files
          8       7 dc_users
          8      10 dc_users
          9       8 dc_object_grants
          9       8 dc_objects
         10      17 dc_global_oids
         11      12 dc_constraints
         12      13 dc_sequences
         13      16 dc_histogram_data
         13      16 dc_histogram_defs
         14      54 dc_sql_prs_errors
         15      32 kqlsubheap_object
         16      19 dc_partition_scns
         16      19 dc_table_scns
         17      18 dc_outlines
         18      15 dc_props
         19      14 dc_profiles
       ....      ....

7. Test Code

create or replace procedure nls_select(p_cnt number) as
            l_val VARCHAR2 (256 Byte);
begin
for i in 1..p_cnt loop
  select value into l_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
end loop;
end;
/

create or replace procedure nls_select_jobs(p_job_cnt number, p_cnt number) as
  l_job_id pls_integer;
begin
  for i in 1.. p_job_cnt loop
    dbms_job.submit(l_job_id, 'begin while true loop nls_select('||p_cnt||'); end loop; end;');
  end loop;
  commit;
end;    
/

create or replace procedure clean_jobs as
begin
  for c in (select job from dba_jobs) loop
    begin
       dbms_job.remove (c.job);
    exception when others then null;
    end;
    commit;
  end loop;

  for c in (select d.job, d.sid, (select serial# from v$session where sid = d.sid) ser 
              from dba_jobs_running d) loop
    begin
      execute immediate
             'alter system kill session '''|| c.sid|| ',' || c.ser|| ''' immediate';
      dbms_job.remove (c.job);
    exception when others then null;
    end;
    commit;
  end loop;
  
  -- select * from dba_jobs;
  -- select * from dba_jobs_running;
end;
/

-- exec nls_select_jobs(2, 1e9);
-- exec clean_jobs; -- stop all jobs