Blog List

• June 2026 (2)
  
  1. Oracle single session "library cache lock" self deadlock: ORA-04020
  1. One More Case of DBMS_ALERT deadlock
• April 2026 (1)
  
  1. One Oracle TDE Performance Test
• January 2026 (4)
  
  1. Oracle ORA-01426: numeric overflow and PL/SQL Runtime MCODE
  2. Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1
  3. Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2
  4. "Disk file operations I/O" with (FileOperation: 8, fileno: 0, filetype: 8) Test
• December 2025 (1)
  
  1. Three Oracle cursor error Tests
• September 2025 (3)
  
  1. ORA-06502: NULL index table key value and plsql_optimize_level
  2. "ORA-00001: unique constraint violated" Debugging
  3. Oracle Database Stuck on DB Writer Wait Event: "oradebug request completion"
• August 2025 (1)
  
  1. Reading CLOB Line by Line with SQL and Plsql: ChatGPT and Gemini Implementations
• July 2025 (3)
  
  1. Reading CLOB Line by Line with SQL and Plsql
  2. "library cache: bucket mutex X" on V$ Fixed Views: Case Test
  3. Oracle dbms_hprof: Uncatchable Time in PL/SQL Nested Program Units
• June 2025 (1)
  
  1. ORA-01002: fetch out of sequence: One Case Test
• May 2025 (1)
  
  1. Oracle ORA-00600 [pfrsfm: stack mismatch] and [pfrsfm: stack mismatch] Reproducing
• February 2025 (1)
  
  1. One Oracle CLOB Space Usage Test
• November 2024 (1)
  
  1. Oracle Self-tune checkpointing lag of log switch
• October 2024 (1)
  
  1. Find the skipped rows in select for update skip locked
• September 2024 (1)
  
  1. One OracleJVM TIME_MODEL Test
• April 2024 (3)
  
  1. Oracle AQ Dequeue 'KTC latch subh' Memory Leak and Dequeue Long Running Test
  2. One Test of Oracle Object Collection MEMBER OF Function
  3. In Memoriam of Prof. Niklaus Wirth: One Email on Program Debugging
• January 2024 (1)
  
  1. One Test on the Different Errors of Oracle Global Temporary Tables vs. Private Temporary Tables
• October 2023 (1)
  
  1. ORA-28268: Exceeded the maximum allowed size for Context information in a session
• September 2023 (3)
  
  1. Oracle Index Root Modification CBC Latch Contention: Workaround Studies
  2. Oracle 19.18 Wait Event 'latch: cache buffers chains' P3: block number
  3. Oracle Index Range Scan with LIKE Condition on Wildcard '_'
• August 2023 (1)
  
  1. Oracle SYS_GUID Composition
• June 2023 (1)
  
  1. One Deadlock of 'row cache lock' and 'library cache lock'
• April 2023 (3)
  
  1. Oracle Multi-Consumer AQ dequeue with dbms_aq.listen Wait Events (I)
  2. Oracle Multi-Consumer AQ dequeue with dbms_aq.listen Performance Model (II)
  3. Oracle Multi-Consumer AQ dequeue with dbms_aq.listen Performance Analysis and Workaround (III)
• March 2023 (2)
  
  1. One Test of Oracle JSON Plsql PGA Memory Leak
  2. One Test of ORA-00600: [KGL-heap-size-exceeded] With AQ Subscriber
• February 2023 (1)
  
  1. Oracle Scalar Subquery Caching and Non-deterministic Functions
• January 2023 (1)
  
  1. Oracle dbms_crypto.randombytes and Enhancement Suggestion
• December 2022 (2)
  
  1. OracleJVM JAVA_JIT_ENABLED Linux /dev/shm mount noexec EPERM and Performance
  2. One Test of Oracle too many parse errors with ORA-12850 and ORA-12872
• October 2022 (2)
  
  1. How many times SYSDATE is called in SQL and Plsql ?
  2. Oracle WITH subquery_factoring_clause TEMP TABLE TRANSFORMATION and log file sync
• September 2022 (2)
  
  1. Update Restart and Undo Extends
  2. Proposal for ORA-04030 incident/trace files Enhancement to list Plsql package Memory Usage
• August 2022 (2)
  
  1. Oracle Global Temporary Table ORA-01555 and Undo Retention
  2. Oracle Global Temporary Table ORA-01555 and XML Data Size
• July 2022 (1)
  
  1. Plsql ORA-00600 by JDBC Call Exception Catch
• June 2022 (1)
  
  1. Tests of Oracle ORA-01866: the datetime class is invalid
• May 2022 (2)
  
  1. How volatile is ORA-00600 [qernsRowP] ?
  2. One Case of ORA-00036: Maximum Number Of Recursive SQL Levels (50) Exceeded
• April 2022 (2)
  
  1. Oracle Write Consistency and ORA-30926: "unable to get a stable set of rows in the source tables"
  2. Oracle Write Consistency and ORA-00600: [13030], [20]
• March 2022 (1)
  
  1. Index Skip Scan With First Column of Composite Index Specified
• February 2022 (2)
  
  1. Oracle Kill Session Crashes DB Instance
  2. How to determine session's CURRVAL of an oracle sequence without error and without modify ?
• January 2022 (1)
  
  1. Oracle SQL Monitoring Shared Pool "keomg: entry list " Memory Leak
• December 2021 (1)
  
  1. How to count the inserts and updates from merge ?
• November 2021 (1)
  
  1. Oracle dbms_aq.dequeue_array Shared Pool "KTC Latch Subh" Memory Leak
• October 2021 (2)
  
  1. Oracle Temp Space Usage Monitoring and View v$tempseg_usage
  2. Oracle sessiontimezone Format Changed After Calling dbms_scheduler/dbms_job Subprograms
• July 2021 (1)
  
  1. Oracle Program and Table dc_objects "row cache lock"
• June 2021 (1)
  
  1. Oracle dbms_alert and its Wait Events
• May 2021 (1)
  
  1. Oracle 19c new shared pool "SO private sga" and "SO private so latch" Performance Impacts
• April 2021 (3)
  
  1. Oracle PLITBLM "library cache: mutex X"
  2. The Third Test of ORA-04025: maximum allowed library object lock
  3. Oracle forall DML save exceptions and ORA-04030: out of process memory (callheap,psdalf:RPI allocations)
• March 2021 (2)
  
  1. Oracle Temp File Read Time Stats Problem
  2. Oracle deallocate_unused_clause and its effect
• January 2021 (1)
  
  1. DBMS_SCHEDULER Job Not Running and Used Slaves
• November 2020 (3)
  
  1. OracleJVM integer Array Size Limit and Out of Memory Error
  2. Oracle LOB Chunksize and Buffersize
  3. One case of dbms_alert.signal deadlock
• September 2020 (1)
  
  1. The Second Test of ORA-600 [4156] Rolling Back To Savepoint
• August 2020 (1)
  
  1. Row Cache Object and Row Cache Mutex Case Study
• May 2020 (3)
  
  1. 12cR2 Index Usage Tracking Manual Flushing
  2. One "row cache lock" Test Case
  3. ORA-600 [504] LATCH HIERARCHY ERROR Deadlock
• April 2020 (3)
  
  1. Index Service ITL and Recursive Transaction
  2. Index Block Split Point Distribution
  3. Cache Buffer Chains Latch Contention Case Study-3: index root block Modification Blocking
• February 2020 (2)
  
  1. Cache Buffer Chains Latch Contention Case Study-2: Reverse Primary Key Index
  2. Cache Buffer Chains Latch Contention Case Study-1: Reverse Key Index
• January 2020 (1)
  
  1. Oracle Unique Index Non-Consistency Read with RowCR
• December 2019 (2)
  
  1. Oracle Database and Java Datetime Conversion Differences across Daylight Saving Time (DST)
  2. Oracle 19.4 OracleJVM JAVA_JIT_ENABLED Not Working on AIX
• August 2019 (2)
  
  1. Oracle 18 New Wait Event: 'index (re)build lock or pin object'
  2. PDML Disabled on Nonpartitioned IOT
• April 2019 (1)
  
  1. LOB ORA-22924: snapshot too old and Fix
• November 2018 (5)
  
  1. Oracle row cache objects Event: 10222, Dtrace Script (I)
  2. Row Cache Objects, Row Cache Latch on Object Type: Plsql vs Java Call (Part-1) (II)
  3. Row Cache Objects, Row Cache Latch on Object Type: Plsql vs Java Call (Part-2) (III)
  4. Row Cache and Sql Executions (IV)
  5. Latch: row cache objects Contentions and Scalability (V)
• October 2018 (2)
  
  1. Oracle ROWCACHE Views and Contents
  2. Result Cache: RC Enqueue and Contention (V)
• July 2018 (3)
  
  1. row cache mutex in Oracle 12.2.0.1.0
  2. plshprof command-line utility in Oracle 12.2.0.1.0
  3. 12c Online Statistics Gathering for Direct Path Insert and 3 Side Effects
• April 2018 (1)
  
  1. TM Lock and No Transaction Commit, TX & TM Locks and No Transaction Visible
• March 2018 (2)
  
  1. 'enq: RO - fast object reuse' and 'enq: KO - fast object checkpoint' Final Blocker
  2. Oracle 12.1 Wait Event: 'log file sync: SCN ordering' Demo
• February 2018 (1)
  
  1. SYS.LOADER_DIR_OBJS Query: Privileges and Performance
• January 2018 (4)
  
  1. Oracle Logical Read: Current Gets Access Path and Cost
  2. Oracle Logical Read: Consistent Gets Access Path and Cost
  3. Oracle Physical Read Access Path and Cost
  4. Oracle Latch Misses, Spin_gets, Sleeps and Recurrent_Misses
• October 2017 (2)
  
  1. ORA-04025 library object lock on DATABASE Truncate Triggers
  2. Result Cache: 0 rows updated Invalidations (IV)
• September 2017 (2)
  
  1. Result Cache: RC Latch Contention and PL/SQL Exception Handler (III)
  2. Result Cache: RC Latch Contention and RESULT_CACHE_MAX_SIZE(II)
• July 2017 (3)
  
  1. nls_database_parameters, dc_props, latch: row cache objects
  2. v$database select and control file sequential read
  3. Getting Oracle Transaction Commit SCN
• May 2017 (8)
  
  1. JDBC, Oracle object/collection, dbms_pickler, NOPARALLEL sys.type$ query
  2. PLSQL Context Switch Functions and Cost
  
  
  3. Oracle Datetime (1) - Concepts
  4. Oracle Datetime (2) - Examples
  5. Oracle Datetime (3) - Assignments
  6. Oracle Datetime (4) - Comparisons
  7. Oracle Datetime (5) - SQL Arithmetic
  8. Oracle Datetime (6) - PLSQL Arithmetic
• March 2017 (3)
  
  1. Oracle 12c PL/SQL Function in the WITH Clause: wrong result
  2. Oracle abstract_lob Memory Leak
  3. PL/SQL Function Result Cache Invalidation (I)
• February 2017 (1)
  
  1. Oracle JVM Java OutOfMemoryError and lazy GC
• January 2017 (1)
  
  1. ORA-600 [4156] SAVEPOINT and PL/SQL Exception Handling
• November 2016 (1)
  
  1. "library cache: mutex X" and Application Context
• September 2016 (2)
  
  1. PL/SQL Multidimensional Collection Memory Usage and Performance
  1. dbms_session.get_package_memory_utilization and limitations
• August 2016 (1)
  
  1. ORA-04025 library object lock on AQ queue and SQL cursor
• June 2016 (1)
  
  1. SQL Parsing in serializable transaction throws ORA-08177: can't serialize access for this transaction
• May 2016 (1)
  
  1. Oracle Fixed Table Practice
• April 2016 (1)
  
  1. sql_id and idn to shared pool subpool
• February 2016 (1)
  
  1. Native Code Files: Ora-7445 [Ioc_pin_shared_executable_object()] Reproducing
• January 2016 (3)
  
  1. Performance of Oracle Object Collection Comparisons - Part2
  2. Performance of Oracle Object Collection Comparisons - Part1
  3. Foreign Keys and Library Cache Locks: Analysis
• December 2015 (1)
  
  1. Oracle bigfile tablespace pre-allocation and session blocking
• November 2015 (1)
  
  1. Oracle 12c single session "library cache lock (cycle)" deadlock
• September 2015 (2)
  
  1. SQL DML Exceptions, Rollbacks and PL/SQL Exception Handlers
  2. Limit PGA Memory Usage
• August 2015 (1)
  
  1. Latch _SPIN_COUNT: adaptive
• July 2015 (2)
  
  1. Redo Practice
  2. UNDO Practice
• June 2015 (1)
  
  1. Temporary LOB Memory Allocation and Freeing in JAVA
• April 2015 (2)
  
  1. IBM AIX POWER7 CPU Usage and Throughput
  2. Oracle 11.2.0.4.0 AWR "Tablespace IO Stats" Column Names Shifted
• January 2015 (1)
  
  1. open_cursors, session_cached_cursors: cursordump
• December 2014 (1)
  
  1. Local prefixed vs. nonprefixed indexes: Column Null Values
• November 2014 (2)
  
  1. reverse key index: the remedy is worse than the symptoms
  2. Low High Water Mark, High High Water Mark, and Parallel Query
• May 2014 (3)
  
  1. Oracle 11g AUTO_SAMPLE_SIZE performance on Small Tables
  2. java stored procedure calls and latch: row cache objects, and performance
  3. java stored procedure calls and latch: row cache objects
• March 2014 (2)
  
  1. dbms_aq.dequeue - latch: row cache objects on AIX
  2. Hot Block Identification and latch: cache buffers chains
• November 2013 (3)
  
  1. Remove Stale Native Code Files on AIX
  2. PGA, SGA memory usage watching from UNIX
  3. ORA-04030 incident file and alert.log
• September 2013 (1)
  
  1. Execution Switching between "direct path read" and "db file scattered read"
• June 2013 (2)
  
  1. Oracle PL/SQL Collection Memory Reclaim 
  2. dbms_alert lock hash collision
• April 2013 (2)
  
  1. cursor: pin S wait on X
  2. Shared Pool - Free Memory, Fragmentation ?
• March 2013 (3)
  
  1. Shared Pool - SQLA and KGLH0
  2. Shared Pool - Memory Allocation - unbalanced
  3. Shared Pool - KKSSP
• December 2012 (1)
  
  1. Delete Restart and new Active Undo Extent
• November 2012 (2)
  
  1. One Oracle AQ Deadlock
  2. Oracle Java OutOfMemoryError
• July 2012 (1)
  
  1. One Mutex Collision Test
• May 2012 (1)
  
  1. Why shared pool is doubled ?
• April 2012 (1)
  
  1. High system time utilization on fast machine
• February 2012 (1)
  
  1. Is latch misses statistic gathered or deduced ?
• November 2011 (2)
  
  1. One Oracle 12g Pre-announce of bug fix in dbms_debug_jdwp
  2. Oracle 11gR2 single session "library cache pin"
• July 2011 (1)
  
  1. Redo/Undo explosion from thick declared table insert
• May 2011 (3)
  
  1. ORA-04027: self-deadlock during automatic validation
  2. Foreign Key on Nested Table
  3. Update Restart and new Active Undo Extent
• April 2011 (1)
  
  1. dbms_session package_memory_utilization

Oracle single session "library cache lock" self deadlock: ORA-04020

Following previous Blog Oracle 12c single session "library cache lock (cycle)" deadlock, we will look one more case:
     ORA-04020: deadlock inside one single session.

Note: Tested in Oracle 19c

1. Test Setup

drop table test_tab;

drop table test_tab_v1;

drop table test_tab_v2;

create table test_tab as select 'ABC123' name, level c0 from dual connect by level <= 10; 

create table test_tab_v1 as select level c0 from dual connect by level <= 5; 

create table test_tab_v2 as select level c0 from dual connect by level <= 10;

create or replace function test_fun (p_x number) return number is
  l_x number;
begin
  select c0 into l_x from test_tab where name = 'ABC123' and  c0 = p_x;
  return l_x;
end;
/

create or replace package test_pkg is
  procedure p1 (p_x number);
end;
/

create or replace package body test_pkg is
  procedure p1 (p_x number) is
    l_x number;
  begin
    l_x := test_fun(p_x);
  end;
end;
/

create or replace force view test_view as
  with sq1 as (select distinct c0 vc0, test_fun(c0) vc1 from test_tab_v1)
  select * from test_tab_v2 v2, sq1 v1 where v2.c0 = v1.vc0;

create or replace procedure test_ddl_table(p_cnt number) as
begin
  for i in 1..p_cnt loop
    execute immediate 'alter table test_tab add(ks_c'||i||' number)';
    dbms_session.sleep(0.1);
  end loop;
end;
/  

create or replace procedure test_ddl_view(p_cnt number) as
begin
  for i in 1..p_cnt loop
    execute immediate 'alter view test_view compile';
  end loop;
end;
/   

create or replace procedure test_exec_call(p_cnt number) as
begin
  for i in 1..p_cnt loop
    test_pkg.p1(mod(i, 10) +1);
    dbms_session.sleep(0.1);
  end loop;
end;
/   

2. Test Run

Open two Sqlplus sessions: SESS-1 and SESS-2.

2.1 In SESS-1, run:

exec test_ddl_table(300);

2.2 In SESS-2, run:

exec test_ddl_view(10000);

After a few seconds, SESS-2 throws error:

ERROR:
ORA-24344: success with compilation error
ORA-06512: at "K.TEST_DDL_VIEW", line 4
ORA-06512: at line 1

One can check object status by:

select object_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status from dba_objects 
 where object_name in ('TEST_PKG', 'TEST_FUN', 'TEST_VIEW', 'TEST_TAB', 'TEST_TAB_V1', 'TEST_TAB_V2', 'TEST_DDL_TABLE', 'TEST_DDL_VIEW', 'TEST_EXECC_CALL') 
 order by status, object_name;

OBJECT_NAME     OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE  CREATED              LAST_DDL_TIME        TIMESTAMP           STATUS
-------------- ---------- -------------- ------------ -------------------- -------------------- ------------------- ------------
TEST_FUN          6236412                FUNCTION     2026-JUN-18 11:13:56 2026-JUN-21 08:37:46 2026-06-21:08:37:46 INVALID
TEST_PKG          6236411                PACKAGE BODY 2026-JUN-18 11:11:44 2026-JUN-21 08:37:46 2026-06-21:08:37:46 INVALID
TEST_VIEW         6236423                VIEW         2026-JUN-18 11:28:39 2026-JUN-21 08:38:29 2026-06-21:08:38:29 INVALID
TEST_DDL_TABLE    6236774                PROCEDURE    2026-JUN-20 09:30:49 2026-JUN-20 09:30:49 2026-06-20:09:30:49 VALID
TEST_DDL_VIEW     6236775                PROCEDURE    2026-JUN-20 09:30:49 2026-JUN-20 09:30:49 2026-06-20:09:30:49 VALID
TEST_PKG          6236410                PACKAGE      2026-JUN-18 11:09:46 2026-JUN-20 10:12:43 2026-06-18:11:20:14 VALID
TEST_TAB          6236945        6236945 TABLE        2026-JUN-21 08:37:45 2026-JUN-21 08:38:39 2026-06-21:08:38:39 VALID
TEST_TAB_V1       6236946        6236946 TABLE        2026-JUN-21 08:37:45 2026-JUN-21 08:37:45 2026-06-21:08:37:45 VALID
TEST_TAB_V2       6236947        6236947 TABLE        2026-JUN-21 08:37:46 2026-JUN-21 08:37:46 2026-06-21:08:37:46 VALID

select name, type, '0X'||trim('0' from addr) addr, to_number(addr, 'XXXXXXXXXXXXXXXXXX') addr_num, hash_value, type 
  from v$db_object_cache v 
 where name in ('TEST_PKG', 'TEST_FUN', 'TEST_VIEW', 'TEST_TAB', 'TEST_TAB_V1', 'TEST_TAB_V2', 'TEST_DDL_TABLE', 'TEST_DDL_VIEW', 'TEST_EXECC_CALL')
 order by v.name;

NAME           TYPE         ADDR           ADDR_NUM HASH_VALUE TYPE
-------------- ------------ ------------ ---------- ---------- ------------
TEST_DDL_TABLE PROCEDURE    0X96A00E48   2527071816 2793044217 PROCEDURE
TEST_DDL_VIEW  PROCEDURE    0X6B5A398    1801075072 2396118748 PROCEDURE
TEST_FUN       FUNCTION     0X80CDF188   2160980360 2391147328 FUNCTION
TEST_PKG       PACKAGE      0X88C948C8   2294892744 3673762546 PACKAGE
TEST_PKG       PACKAGE BODY 0X737B9C48   1937480776 2937683311 PACKAGE BODY
TEST_TAB       TABLE        0X70F1F1B    1894904240 1905210899 TABLE
TEST_TAB_V1    TABLE        0X71FAA968   1912252776 2228492516 TABLE
TEST_TAB_V2    TABLE        0X97BBD6A8   2545669800  580752620 TABLE
TEST_VIEW      VIEW         0X70BF69B8   1891592632 1492527927 VIEW

To repeat the test, re-execute Test Setup at first, then make the test.

3. trc file

Following trc file shows:

   ORA-04020: deadlock detected while trying to lock object K.TEST_FUN

with object handle: 0x80cdf188, which is TEST_FUN (ADDR = 0X80CDF188 in above v$db_object_cache output).

It is a self session deadlock because waiting session and blocking session are the same (0xb89008b8 17).

Blog Blog Oracle 12c single session "library cache lock (cycle)" deadlock contains more detail analysis on this behaviour,
for example,

	waiting session (0xb89008b8   17) = blocking session (0xb89008b8   17)
	waiting mode (X)                 != blocking mode (s)
	waiting lock (0x62d127d0)        != blocking lock (0x83c3f830)
	waiting Flags=[0000]             != blocking Flags=CNB/PNC/[0003]
	waiting SavepointNum=d56d7       > blocking SavepointNum=d56ab 

Trace file /orabin/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_ora_323537.trc
Version 19.30.0.0.0
Oracle process number: 48
Unix process pid: 323537, image: oracle@testdb

*** SESSION ID:(17.58059) 2026-06-21T08:38:29.493117+02:00
 
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object K.TEST_FUN
Short stack dump of current process:
----- Abridged Call Stack Trace -----
ksedsts()+426<-kgllkde()+1182<-kglLockWait()+2503<-kgllkal()+2030<-kglLock()+1426<-kglget()+297<-kqlvld()+851<-kglgob()+3504<-kgldpo0()+726<-qcdlgpo()+772<-qcsRslvPLSQLInvoc1()+979<-qcsRslvPLSQLInvoc()+96<-qcsRslvName()+676<-qcsridn()+115<-qcsraic()+431<-qcspqbDescendents()+546
<-qcspqb()+283<-qcspqbDescendents()+4286<-qcspqb()+283<-kkmdrv()+180<-opiSem()+2167<-opiprs()+528<-kksParseChildCursor()+524<-rpiswu2()+2004<-kksLoadChild()+5339<-kxsGetRuntimeLock()+2335<-kksfbc()+19863<-opiexe()+2982<-opiosq0()+4482<-opipls()+21173<-opiodr()+1264
<-rpidrus()+198<-skgmstack()+65<-rpidru()+151<-rpiswu2()+543<-rpidrv()+1281<-psddr0()+467<-psdnal()+624<-pevm_EXIM()+282<-pfrinstr_EXIM()+43<-pfrrun_no_tool()+60<-pfrrun()+904<-plsql_run()+752<-peicnt()+279<-kkxexe()+720<-opiexe()+31613<-kpoal8()+2301<-opiodr()+1264
<-ttcpip()+1232<-opitsk()+1908<-opiino()+935<-opiodr()+1264<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+229<-_start()+46 
----- End of Abridged Call Stack Trace -----
Partial short call stack signature: 0xce99135a9a693f97
--------------------------------------------------------
 object handle   waiting session   sid  waiting lock    mode blocking session  sid  blocking lock   mode
---------------- ---------------- ----- --------------- ---- ---------------- ----- --------------- ----
 0x80cdf188      0xb89008b8        17   0x62d127d0      X    0xb89008b8        17   0x83c3f830      S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x66147068, type: LIBRARY OBJECT LOCK (118), map: 0x62d127d0
    state: LIVE (0x4532), flags: 0x1
    owner: 0xaefc0968, proc: 0xb8eebc90
    link: 0x66147088[0xa5f638e0, 0xaefc09d8]
    child list count: 0, link: 0x661470d8[0x661470d8, 0x661470d8]
    pg: 0
SOC: 0x62d127d0, type: LIBRARY OBJECT LOCK (118), map: 0x66147068
     state: LIVE (0x99fc), flags: INIT (0x1)

LibraryObjectLock:  Address=0x62d127d0 Handle=0x80cdf188 RequestMode=X 
  CanBeBrokenCount=708 Incarnation=708 ExecutionCount=0 
  
  User=0xb89008b8 Session=0xb89008b8 ReferenceCount=0 
  Flags=[0000] SavepointNum=d56d7 
LibraryHandle:  Address=0x80cdf188 Hash=8e860340 LockMode=S PinMode=S LoadLockMode=0 Status=INVL 
  ObjectName:  Name=K.TEST_FUN   
    FullHashValue=e7a045fef628c10c7c8dcf7b8e860340 Namespace=TABLE/PROCEDURE(01) Type=FUNCTION(08) ContainerId=0 ContainerUid=0 Identifier=6236412 OwnerIdn=49 
  Statistics:  InvalidationCount=674 ExecutionCount=0 LoadCount=696 ActiveLocks=1 ActivePins=1 TotalLockCount=163897 TotalPinCount=163874 
  Counters:  BrokenCount=708 RevocablePointer=708 KeepDependency=0 Version=0 BucketInUse=63688 HandleInUse=63688 HandleReferenceCount=0 
  Concurrency:  DependencyMutex=0x80cdf238(0, 326246, 0, 0) Mutex=0x80cdf2d8(0, 1479323, 6, 0) 
  Flags=PIN/TIM/VER/[40002801] Flags2=[0000] HandleIvd=6236945 
  WaitersLists:  
    Lock=0x80cdf218[0x62d12800,0x62d12800] 
    Pin=0x80cdf1f8[0x80cdf1f8,0x80cdf1f8] 
    LoadLock=0x80cdf270[0x80cdf270,0x80cdf270] 
  Timestamp:  Current=06-21-2026 08:37:46 
  HandleReference:  Address=0x80cdf358 Handle=0xa0670d70 Flags=OWN[200] 
  LibraryObject:  Address=0x903c0190 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=/CSC[4000000] Flags3=[0000] PublicFlags=NST[0001] 
    DataBlocks:  
      Block:  #='0' name=KGLH0^8e860340 pins=0 Change=NONE   
        Heap=0x80cdefc8 Pointer=0x903c0270 Extent=0x903c00d0 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.179688 Size=3.976562 LoadTime=06-21-2026 06:38:20 
      Block:  #='2' name=PLDIA^8e860340 pins=1 Change=NONE   
        Heap=0x903c0620 Pointer=0x80763550 Extent=0x80763490 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=13.593750 Size=15.820312 LoadTime=06-21-2026 06:38:20 
      Block:  #='4' name=PLMCD^8e860340 pins=1 Change=NONE   
        Heap=0x903c0778 Pointer=0x955b7e68 Extent=0x955b7da8 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.554688 Size=3.937500 LoadTime=06-21-2026 06:38:20 
------------- WAITING SESSION -------------
  sid: 17 ser: 58059 audsid: 102774880 user: 49/K
    flags: (0x8000041) USR/- flags2: (0x48009) -/DDLT2/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 48 O/S info: user: oracle, term: UNKNOWN, ospid: 323537
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 6132:51788
    machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  alter view test_view compile
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x87edd848, type: LIBRARY OBJECT LOCK (118), map: 0x83c3f830
    state: LIVE (0x4532), flags: 0x1
    owner: 0xa5f51dd0, proc: 0xb8eebc90
    link: 0x87edd868[0xb8f018c0, 0x87e13a98]
    child list count: 0, link: 0x87edd8b8[0x87edd8b8, 0x87edd8b8]
    pg: 0
SOC: 0x83c3f830, type: LIBRARY OBJECT LOCK (118), map: 0x87edd848
     state: LIVE (0x99fc), flags: INIT (0x1)

LibraryObjectLock:  Address=0x83c3f830 Handle=0x80cdf188 Mode=S 
  CallPin=0x90ab9830 CanBeBrokenCount=708 Incarnation=707 ExecutionCount=0 
  
  User=0xb89008b8 Session=0xb89008b8 ReferenceCount=3 
  Flags=CNB/PNC/[0003] SavepointNum=d56ab 
LibraryHandle:  Address=0x80cdf188 Hash=8e860340 LockMode=S PinMode=S LoadLockMode=0 Status=INVL 
  ObjectName:  Name=K.TEST_FUN   
    FullHashValue=e7a045fef628c10c7c8dcf7b8e860340 Namespace=TABLE/PROCEDURE(01) Type=FUNCTION(08) ContainerId=0 ContainerUid=0 Identifier=6236412 OwnerIdn=49 
  Statistics:  InvalidationCount=674 ExecutionCount=0 LoadCount=696 ActiveLocks=1 ActivePins=1 TotalLockCount=163897 TotalPinCount=163874 
  Counters:  BrokenCount=708 RevocablePointer=708 KeepDependency=0 Version=0 BucketInUse=63688 HandleInUse=63688 HandleReferenceCount=0 
  Concurrency:  DependencyMutex=0x80cdf238(0, 326246, 0, 0) Mutex=0x80cdf2d8(0, 1479323, 6, 0) 
  Flags=PIN/TIM/VER/[40002801] Flags2=[0000] HandleIvd=6236945 
  WaitersLists:  
    Lock=0x80cdf218[0x62d12800,0x62d12800] 
    Pin=0x80cdf1f8[0x80cdf1f8,0x80cdf1f8] 
    LoadLock=0x80cdf270[0x80cdf270,0x80cdf270] 
  Timestamp:  Current=06-21-2026 08:37:46 
  HandleReference:  Address=0x80cdf358 Handle=0xa0670d70 Flags=OWN[200] 
  LibraryObject:  Address=0x903c0190 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=/CSC[4000000] Flags3=[0000] PublicFlags=NST[0001] 
    DataBlocks:  
      Block:  #='0' name=KGLH0^8e860340 pins=0 Change=NONE   
        Heap=0x80cdefc8 Pointer=0x903c0270 Extent=0x903c00d0 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.179688 Size=3.976562 LoadTime=06-21-2026 06:38:20 
      Block:  #='2' name=PLDIA^8e860340 pins=1 Change=NONE   
        Heap=0x903c0620 Pointer=0x80763550 Extent=0x80763490 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=13.593750 Size=15.820312 LoadTime=06-21-2026 06:38:20 
      Block:  #='4' name=PLMCD^8e860340 pins=1 Change=NONE   
        Heap=0x903c0778 Pointer=0x955b7e68 Extent=0x955b7da8 Flags=I/-/P/A/-/-/- 
        FreedLocation=0 Alloc=2.554688 Size=3.937500 LoadTime=06-21-2026 06:38:20 
--------------------------------------------------------
This lock request was aborted.

One More Case of DBMS_ALERT deadlock

Following the previous Blog One case of dbms_alert.signal deadlock
< in this Blog, we will demonstrate one more case of DBMS_ALERT deadlock.

Note: Tested in Oracle 19c

Open two Sqplus sessions: SESS-1 and SESS-2.

1. At T1, login to SESS-1, register two alert interests: alert_1 and alert_2, then exit the session:

exec dbms_alert.register('alert_1');

exec dbms_alert.register('alert_2');

exit;

2. At T2, re-login to SESS-1 (sid: 552), check two registered names:

-- watch two registered names, whose session is not alive.

select * from sys.dbms_alert_info where name like 'ALERT%';

NAME            SID             CHANGED    MESSAGE
--------------- --------------- ---------- ---------------
ALERT_1         0228CD530001    N
ALERT_2         0228CD530001    N

-- determines if the specified session is active.
declare
  l_unique_sid varchar2(30) := '0228CD530001';
begin
  if not dbms_session.is_session_alive(l_unique_sid) then
    dbms_output.put_line('Not Alive: (sid: ' ||to_number(substr(l_unique_sid, 1, 4), 'XXXX')||
                                ', serial#: '||to_number(substr(l_unique_sid, 5, 4), 'XXXX')||
                                   ', inst: '||to_number(substr(l_unique_sid, 9, 4), 'XXXX')||')');
  end if;
end;
/

Not Alive: (sid: 552, serial#: 52563, inst: 1)

3. At T3, continue in SESS-1, send signal: alert_2

SQL (sid:552) > exec dbms_alert.signal('alert_2', 'alert_msg_2');

4. At T4, login to SESS-2 (sid: 16), register one alert interests: alert_3

SQL (sid:16) > exec dbms_alert.register('alert_3');

--Note that this call is with default cleanup = true to perform cleanup of any extant orphaned pipes.

5. At T5, in SESS-1, check TX lock, SESS-2 (sid:16) is blocked by SESS-1 (sid: 552)

SQL (sid:552) > select * from  v$lock where type = 'TX';

ADDR             KADDR             SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------------- ---------------- ---- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00000000B4310970 00000000B43109A0   16 TX    5505024     782554          0          6        132          0          0
00000000ADB30E70 00000000ADB30EA8  552 TX    5505024     782554          6          0        169          1          0
00000000ADBCFAB8 00000000ADBCFAF0   16 TX    6291482     236621          6          0        132          0          0

3 rows selected.

6. At T6, in SESS-1, send signal: alert_1

SQL (sid:552) > exec dbms_alert.signal('alert_1', 'alert_msg_1');

7. Both sessions throw ORA-00060

SQL (sid:16) > exec dbms_alert.register('alert_3');
BEGIN dbms_alert.register('alert_3'); END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_ALERT", line 82
ORA-06512: at "SYS.DBMS_ALERT", line 82
ORA-06512: at "SYS.DBMS_ALERT", line 103
ORA-06512: at line 1

SQL (sid:552) > exec dbms_alert.signal('alert_1', 'alert_msg_1');
BEGIN dbms_alert.signal('alert_1', 'alert_msg_1'); END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_ALERT", line 431
ORA-06512: at line 1

8. Both Deadlock trc files:

=================== testdb_ora_1851008.trc ===================

Unix process pid: 1851008, image: oracle@testdb

*** SESSION ID:(16.33150) 2026-06-09T15:35:47.444358+02:00
 
Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-0060001A-00039C4D-00000000-00000000         66      16     X        33150      63     552           X  57779
TX-00540000-000BF0DA-00000000-00000000         63     552     X        57779      66      16           X  33150
 
----- Information for waiting sessions -----
Session 16:
Holds resource TX-0060001A-00039C4D-00000000-00000000 acquired 204 seconds ago.
  sid: 16 ser: 33150 audsid: 131915016 user: 0/SYS
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 66 O/S info: user: oracle, term: UNKNOWN, ospid: 1851008
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 42636:42136
    machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  DELETE DBMS_ALERT_INFO WHERE SID = :B1 
 
Session 552:
Holds resource TX-00540000-000BF0DA-00000000-00000000 acquired 241 seconds ago.
  sid: 552 ser: 57779 audsid: 131915017 user: 0/SYS
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 63 O/S info: user: oracle, term: UNKNOWN, ospid: 1851016
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 57408:55064
    machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )


session 16: DID 0001-0042-0000011E	session 552: DID 0001-003F-000005CF 
session 552: DID 0001-003F-000005CF	session 16: DID 0001-0042-0000011E 
 
Rows waited on:
  Session 16: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAC
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 2)
  Session 552: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAB
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 1)

=================== testdb_ora_1851016.trc ===================

Unix process pid: 1851016, image: oracle@testdb

*** SESSION ID:(552.57779) 2026-06-09T15:35:50.836313+02:00

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00540000-000BF0DA-00000000-00000000         63     552     X        57779      61     196           X   9237
TX-005B0001-000BE24C-00000000-00000000         61     196     X         9237      63     552           X  57779
 
----- Information for waiting sessions -----
Session 552:
Holds resource TX-00540000-000BF0DA-00000000-00000000 acquired 245 seconds ago.
  sid: 552 ser: 57779 audsid: 131915017 user: 0/SYS
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 63 O/S info: user: oracle, term: UNKNOWN, ospid: 1851016
    image: oracle@testdb
  client details:
    O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 57408:55064
    machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
 
Session 196:
Holds resource TX-005B0001-000BE24C-00000000-00000000 acquired 3 seconds ago.
  sid: 196 ser: 9237 audsid: 131915018 user: 0/SYS
    flags: (0x10041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 61 O/S info: user: oracle, term: UNKNOWN, ospid: 1850047
    image: oracle@testdb (J006)
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 1850047
    machine: testdb program: oracle@testdb (J006)
    application name: task-911-2 hash value=1117638710
    action name: bgp: loop: init, hash value=2828460256
  current SQL:
  DELETE DBMS_ALERT_INFO WHERE SID = :B1 
 

session 552: DID 0001-003F-000005CF	session 196: DID 0001-003D-000004C1 
session 196: DID 0001-003D-000004C1	session 552: DID 0001-003F-000005CF 
 
Rows waited on:
  Session 552: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAB
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 1)
  Session 196: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAC
  (dictionary objn - 3933820, file - 446, block - 97185, slot - 2)

9. Repeat Test

To repeat test, exit both SESS-1 and SESS-2, open a third SESS-3, and register a new alert to cleanup alert_1, alert_2, alert_3. Then repeat above test steps.

exec dbms_alert.register('alert_other');

10. Reasoning

Two alert interests are inserted (registered) into sys.dbms_alert_info in the order of alert_1 and alert_2, both with the same unique_session_id: 0228CD530001.

When we register alert interest: alert_3, DBMS_ALERT first performs cleanup of not alive sessions by delete in default order: alert_1 and alert_2.

Whereas we frist send (update) signal: alert_2, then alert_1.

In this case, Updates vs Delete are interfered in crossing row order, hence Deadlock.

One Oracle TDE Performance Test

In this Blog, we will test the performance difference of SQLs on TDE table and non TDE table.

Note: Tested in Oracle 19.27 on AIX and Linux

1. Test Setup

We create one TDE tablespace and one table on it, and also one test procedure.

drop tablespace test_ts_tde including contents and datafiles;

create tablespace test_ts_tde datafile '/oratestdb/oradata/testdb/test_ts_tde.dbf' 
  size 10m autoextend on maxsize 1g extent management local segment space management auto encryption default storage (encrypt);

drop table test_tab_tde;

create table test_tab_tde(grp number, id number, name varchar2(100), ts timestamp with time zone) tablespace test_ts_tde;

create index test_tab_tde_idx1 on test_tab_tde (grp, id, name, ts) tablespace test_ts_tde;

create index test_tab_tde_idx2 on test_tab_tde (grp, name) tablespace test_ts_tde;

create or replace procedure test_tab_tde_proc (p_grp_cnt number, p_id_cnt number) as
  l_start   number := dbms_utility.get_time;
  type      t_tab is table of test_tab_tde%rowtype;
  l_tab     t_tab := t_tab();
  l_ins_cnt number := 0;
  l_del_cnt number := 0;
  l_upd_cnt number := 0;
  l_sel_cnt number := 0;
begin
  select ceil(level/p_id_cnt), level, rpad('abc_'||level, 100, 'x'), systimestamp bulk collect into l_tab from dual connect by level <= p_id_cnt;

  l_start := dbms_utility.get_time;
  for g in 1..p_grp_cnt loop
	  for i in 1..p_id_cnt loop
	    l_tab(i).grp := g;
	  end loop;
      
    forall k in l_tab.first .. l_tab.last save exceptions
      insert into test_tab_tde values l_tab(k);
	  l_ins_cnt := l_ins_cnt + p_id_cnt;
	  
	  if mod(g, 2) = 1 then
	    delete from test_tab_tde where grp = g and rownum <= p_id_cnt;
	    l_del_cnt := l_del_cnt + sql%rowcount;
	  end if;
    
	  if mod(g, 2) = 0 then
	    update test_tab_tde set name = rpad('abc_'||id, 100, 'x') where grp = g and rownum <= p_id_cnt;
	    l_upd_cnt := l_upd_cnt + sql%rowcount;
	  end if;
	  
	  for c in (select * from test_tab_tde where grp = g and rownum <= p_id_cnt)
	  loop
	    l_sel_cnt := l_sel_cnt + 1;
	  end loop;
	  
	  commit;
  end loop;
  dbms_output.put_line('test_tab_tde elapsed='||round((dbms_utility.get_time-l_start)/100, 2)||
                       ', Insert = '||l_ins_cnt||', Delete = '||l_del_cnt||', Update = '||l_upd_cnt||', Select = '||l_sel_cnt);
end;
/

Similarily, we create one NON TDE tablespace and one table on it, and also one test procedure.

drop tablespace test_ts_non including contents and datafiles;

create tablespace test_ts_non datafile '/oratestdb/oradata/testdb/test_ts_non.dbf' 
  size 10m autoextend on maxsize 1g extent management local segment space management auto;

drop table test_tab_non;

create table test_tab_non(grp number, id number, name varchar2(100), ts timestamp with time zone) tablespace test_ts_non;

create index test_tab_non_idx1 on test_tab_non (grp, id, name, ts) tablespace test_ts_non;

create index test_tab_non_idx2 on test_tab_non (grp, name) tablespace test_ts_non;

create or replace procedure test_tab_non_proc (p_grp_cnt number, p_id_cnt number) as
  l_start   number := dbms_utility.get_time;
  type      t_tab is table of test_tab_non%rowtype;
  l_tab     t_tab := t_tab();
  l_ins_cnt number := 0;
  l_del_cnt number := 0;
  l_upd_cnt number := 0;
  l_sel_cnt number := 0;
begin
  select ceil(level/p_id_cnt), level, rpad('abc_'||level, 100, 'x'), systimestamp bulk collect into l_tab from dual connect by level <= p_id_cnt;

  l_start := dbms_utility.get_time;
  for g in 1..p_grp_cnt loop
	  for i in 1..p_id_cnt loop
	    l_tab(i).grp := g;
	  end loop;
      
    forall k in l_tab.first .. l_tab.last save exceptions
      insert into test_tab_non values l_tab(k);
	  l_ins_cnt := l_ins_cnt + p_id_cnt;
	  
	  if mod(g, 2) = 1 then
	    delete from test_tab_non where grp = g and rownum <= p_id_cnt;
	    l_del_cnt := l_del_cnt + sql%rowcount;
	  end if;
    
	  if mod(g, 2) = 0 then
	    update test_tab_non set name = rpad('abc_'||id, 100, 'x') where grp = g and rownum <= p_id_cnt;
	    l_upd_cnt := l_upd_cnt + sql%rowcount;
	  end if;
	  
	  for c in (select * from test_tab_non where grp = g and rownum <= p_id_cnt)
	  loop
	    l_sel_cnt := l_sel_cnt + 1;
	  end loop;
	  
	  commit;
  end loop;
  dbms_output.put_line('test_tab_non elapsed='||round((dbms_utility.get_time-l_start)/100, 2)||
                       ', Insert = '||l_ins_cnt||', Delete = '||l_del_cnt||', Update = '||l_upd_cnt||', Select = '||l_sel_cnt);
end;
/

2. Run both tests with SQL trace

We run both tests on AIX.

SQL > exec test_tab_tde_proc(1e4, 100);
      test_tab_tde elapsed=91.65, Insert = 1000000, Delete = 500000, Update = 500000, Select = 500000

SQL > exec test_tab_non_proc(1e4, 100);
      test_tab_non elapsed=42.31, Insert = 1000000, Delete = 500000, Update = 500000, Select = 500000

The elapsed time for TDE operations is more than double the time required for non-TDE operations.

Here the TDE trc:

********************************************************************************
INSERT INTO test_TAB_TDE VALUES(:B1 ,:B2 ,:B3 ,:B4 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000     20.99      37.88          8     117577    1458787     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001     20.99      37.88          8     117577    1458787     1000000

********************************************************************************
DELETE FROM test_TAB_TDE WHERE GRP = :B2 AND ROWNUM <= :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute   5000     18.87      33.92          0      24883    3593457      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5001     18.87      33.92          0      24883    3593459      500000

********************************************************************************
SELECT * FROM test_TAB_TDE WHERE GRP = :B2 AND ROWNUM <= :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute  10000      0.73       1.32          0          0          0           0
Fetch    15000      0.59       1.05          0      61687          0      500000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25001      1.33       2.38          0      61687          2      500000

********************************************************************************
UPDATE test_TAB_TDE SET NAME = RPAD('abc_'||ID, 100, 'x') WHERE GRP = :B2 AND ROWNUM <= :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          2           0
Execute   5000      7.93      14.24          0      19816     520264      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5001      7.93      14.24          0      19816     520266      500000

Here the non TDE trc:

********************************************************************************
INSERT INTO test_TAB_NON VALUES(:B1 ,:B2 ,:B3 ,:B4 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      9.57      16.73          8     119206    1430838     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      9.57      16.73          8     119206    1430838     1000000

********************************************************************************
DELETE FROM test_TAB_NON WHERE GRP = :B2 AND ROWNUM <= :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   5000      7.76      13.48          0      24835    3592775      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5001      7.76      13.48          0      24835    3592775      500000

********************************************************************************
SELECT * FROM test_TAB_NON WHERE GRP = :B2 AND ROWNUM <= :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.73       1.27          0          0          0           0
Fetch    15000      0.58       1.00          0      61647          0      500000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    25001      1.31       2.27          0      61647          0      500000

********************************************************************************
UPDATE test_TAB_NON SET NAME = RPAD('abc_'||ID, 100, 'x') WHERE GRP = :B2 AND ROWNUM <= :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   5000      3.94       6.82          0      19815     520240      500000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5001      3.94       6.82          0      19815     520240      500000

Above traces showed that although Consistent Gets ("query") and Current Gets ("current") stats are very closed, TDE doubled elaspsed time compared to non-TDE. All 4 SQLs are CPU intensive (the differences between "cpu" and "elapsed" are due to AIX special accounting).

3. Perf TOP

Here the perf top output of both tests runing on Linux.

-- TDE
SQL > exec test_tab_tde_proc(1e4, 100);
      test_tab_tde elapsed=24.86, Insert = 1000000, Delete = 500000, Update = 500000, Select = 500000

$ > perf top -p 1438084 -d 2

Overhead  Shared Object       Symbol
   5.76%  oracle              [.] l9_EncryptCFB128_RIJ128_AES_NI.key_128_s
   5.05%  oracle              [.] kcbchg1_main
   4.14%  oracle              [.] kcrfw_redo_gen_ext
   3.58%  oracle              [.] __intel_avx_rep_memcpy
   3.40%  [vdso]              [.] __vdso_clock_gettime
   2.34%  oracle              [.] kcbgcur
   2.32%  oracle              [.] __intel_avx_rep_memset
   2.03%  oracle              [.] kdkcmp1
   1.65%  oracle              [.] kcrfw_enc_redo_gen
   1.58%  oracle              [.] kcrfw_copy_cv
   1.56%  oracle              [.] lxoCpStr
   1.28%  oracle              [.] kcbget
   1.25%  oracle              [.] kcbklbc
   1.21%  oracle              [.] l9_aes128_KeyExpansion_NI

-- Non TDE
SQL > exec test_tab_non_proc(1e4, 100);
      test_tab_non elapsed=18.54, Insert = 1000000, Delete = 500000, Update = 500000, Select = 50000

$ > perf top -p 1438084 -d 2

Overhead  Shared Object       Symbol
   6.40%  oracle        [.] kcbchg1_main
   5.31%  oracle        [.] kcrfw_redo_gen_ext
   4.24%  oracle        [.] __intel_avx_rep_memcpy
   2.61%  oracle        [.] kcbgcur
   2.45%  oracle        [.] lxoCpStr
   2.31%  oracle        [.] kcrfw_copy_cv
   2.13%  oracle        [.] kcbhfix_tail
   2.12%  oracle        [.] kdkcmp1
   1.61%  oracle        [.] kcoapl
   1.58%  [vdso]        [.] __vdso_clock_gettime
   1.56%  oracle        [.] ktuchg2
   1.49%  oracle        [.] lxsCntDisp
   1.41%  oracle        [.] kcbget
   1.36%  oracle        [.] kdiins1
   1.35%  oracle        [.] kcbklbc
   1.28%  oracle        [.] kslfre
   1.17%  oracle        [.] _intel_fast_memcpy
   1.16%  oracle        [.] kslgetl
   1.09%  oracle        [.] sxorchk
   0.90%  oracle        [.] ksl_get_shared_latch_int
   

-- check AES-NI
$ > grep -o aes /proc/cpuinfo | head -n 1
    aes   

Oracle ORA-01426: numeric overflow and PL/SQL Runtime MCODE

Oracle PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

ORA-01426: numeric overflow

declare                                            
  p_pint1    pls_integer := 2147483645;            
  p_pint2    pls_integer := 13;                    
  p_pint3    pls_integer := 9;                     
  p_char     varchar2(4) := 'ABC';                 
  p_num_ret  number;                               
begin                                              
  p_num_ret := mod((p_pint1+p_pint2), p_pint3);    
end;                                               
/                                                  

ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 8

PL/SQL Runtime MCODE

----- PL/SQL Runtime State -----
ANONYMOUS BLOCK:
library unit=9849d118 line=8 opcode=8(ADDI) static link=0 scope=0
----- Begin Dump of MCODE -----
Entry #0
00000: BREAK          
Entry #1
00002: ENTER            DS[0]+136 <"__anonymous_block",ept=1,sz=208,lvl=0,prm=0>
    Static Address Registers                              
    #0000005 HS+0                                 |================ MCODE Mnemonics ==============        
    #0000006 HS+24                                |    MOVI:  Move Integer                                
    #0000007 HS+48                                |    MOVC:  Move Character                                                          
    #0000008 HS+72                                |    ADDI:  Add Integer (operand and return are Integer. Interpreted as Subroutine: pfrrexc_record_excp())             
00008: INFR             DS[0]+184                 |    RASIX: Raise Exception                                          
  Frame Desc Version = 2                          |===========================================================================================                         
    slot# = 0 start offset = 16                   | Comment                                | Test Code                                                           
    # of locals = 5                               |-------------------------------------   |---------------------------------------------------    
    TC_SSCALARi: #0, FP+16, d=FP+24               | #0 <= 2147483645                       |                                                                 
    TC_SSCALARi: #1, FP+48, d=FP+56               | #1 <= 13                               |  declare                                                        
    TC_SSCALARi: #2, FP+80, d=FP+88               | #2 <=  9                               |    p_pint1    pls_integer := 2147483645;                        
    TC_VCHARi: #3, FP+112, d=FP+136, mxl=4 cha    | #3 <= 'ABC'                            |    p_pint2    pls_integer := 13;                                
    TC_SSCALARi: #4, FP+176, d=FP+184             |                                        |    p_pint3    pls_integer := 9;                                 
00014: MOVI             #6, #0                    | #6 <= #0 (2147483645)                  |    p_char     varchar2(4) := 'ABC';                             
00020: MOVI             #7, #1                    | #7 <= #1 (13        )                  |    p_num_ret  number;                                           
00026: MOVI             #5, #2                    | #5 <= #2 (9         )                  |  begin                                                          
00032: MOVC             #8, #3                    | #8 <= #3 ('ABC      )                  |    p_num_ret := mod((p_pint1+p_pint2), p_pint3);              
00038: ADDI             #6, #7, #4                | #6 + #7 => #4 (2147483645 + 13 => #4)  |  end;                                                                
00046: RASIX            6501                      | #Throw Exception                       |  /                                                              
----- End Dump of MCODE -----  
                                                                          
FP=0x7f8432ae69c8 PC=0x628c39be Page=0 AP=(nil) ST=0x7f8432ae6a98            |                              
DL0=0x7f84319bac20 GF=0x7f84319bac68 DL1=0x7f84319bac38 DPF=0x7f84319bac58   | 
HS=0x628c3bd0 AR=0x7f84319babb8 DS=0x628c3b70                                | 
PB_PC=(nil) SV_PC=0x628c39c6                                                 | 
DS pkg desc :                                                                | 
0628C3A30                   02920314 00000040          [....@...]            | 
0628C3A40 00020003 00000000 00000000 00000001  [................]            | 
0628C3A50 00D80000 0000000A 01200000 00000204  [.......... .....]            | 
0628C3A60 00000000 00000000 00000000 00000000  [................]            | 
0628C3A70 00000000 00000000                    [........]                    | 
AR :                                                                         | 
7F84319BABB0                   32AE69D8 00007F84          [.i.2....]         | 
7F84319BABC0 32AE69F8                             [.i.2]                     | 
FP :                                                                         | 
7F8432AE69C0                   00000000 00000000          [........]         | 
7F8432AE69D0 32AE69F0 00007F84 32AE69E0 00007F84  [.i.2.....i.2....]         | 
7F8432AE69E0 32AE69F0 00007F84 00080000 00000000  [.i.2............]         | 
7F8432AE69F0 7FFFFFFD 00007F84 32AE6A00 00007F84  [.........j.2....]         | TC_SSCALARi: #0, FP+16: 7FFFFFFD = p_pint1    pls_integer := 2147483645; 
7F8432AE6A00 32AE6A10 00007F84 00080000 00000000  [.j.2............]         | 
7F8432AE6A10 0000000D 00007F84 32AE6A20 00007F84  [........ j.2....]         | TC_SSCALARi: #1, FP+48: 0000000D = p_pint2    pls_integer := 13;
7F8432AE6A20 32AE6A30 00007F84 00080000 00000000  [0j.2............]         | 
7F8432AE6A30 00000009 00000000 32AE6A50 00007F84  [........Pj.2....]         | TC_SSCALARi: #2, FP+80: 00000009 = p_pint3    pls_integer := 9;
7F8432AE6A40 01040009 00010369 00000010 00000000  [....i...........]         | 
7F8432AE6A50 32AE6A60 00007F84 00280003 00000000  [`j.2......(.....]         | TC_VCHARi: #3, FP+112:  p_char     varchar2(4) := 'ABC';
7F8432AE6A60 7F434241 00000003 00060009 00460001  [ABC...........F.]         |                         434241 = CBA             
7F8432AE6A70 00000000 00000000 32AE6A80 00007F84  [.........j.2....]         | 
7F8432AE6A80 32AE6A90 00007F84 000A0000 7FFFFFFF  [.j.2............]         | 
7F8432AE6A90 7FFFFF80 00800010                    [........]                 | TC_SSCALARi: #4, FP+176

Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1

---

(I)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1       (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2

---

In this Blog, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions, and explore their complexity.

In next Blog: (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions for nested table column.

Note: Tested in Oracle 19.27

1. MEMBER vs. IN Performance Difference

We create two nested tables of number, and run query on them with MEMBER and IN Conditions respectively:

---------------- test Number MEMBER vs. IN Comparison Conditions Performance ----------------

drop type t_num_tab;

create or replace noneditionable type t_num_tab is table of number;
/

create or replace procedure test_NUM_MEMBER_vs_IN_Conditions (p_size_1 number, p_size_2 number, p_sel varchar2 := 'A') as
  l_t_num_tab_1    t_num_tab := t_num_tab();
  l_t_num_tab_2    t_num_tab := t_num_tab();
  l_start_time     number;
  l_ret_cnt        number;
begin
  select level bulk collect into l_t_num_tab_1 from dual connect by level <= p_size_1;
  select level bulk collect into l_t_num_tab_2 from dual connect by level <= p_size_2;
 
  dbms_output.put_line('l_t_num_tab_1.count   = '||cardinality(l_t_num_tab_1));
  dbms_output.put_line('l_t_num_tab_2.count   = '||l_t_num_tab_2.count);
   
  if p_sel in ('A', 'M') then
        --================= MEMBER Comparison =================
        l_start_time := dbms_utility.get_time;
       
        select /*+ Test_NUM_MEMBER */ count(*) into l_ret_cnt
          from table(cast (l_t_num_tab_1 as t_num_tab)) t1
         where t1.column_value member of cast(l_t_num_tab_2 as t_num_tab);
        
        dbms_output.put_line('=============== MEMBER Condition ===============');
        dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
        dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
  end if;
 
  if p_sel in ('A', 'I') then
        --================= IN Comparison =================
        l_start_time := dbms_utility.get_time;
       
        select /*+ Test_NUM_IN */ count(*) into l_ret_cnt
          from table(cast (l_t_num_tab_1 as t_num_tab)) t1
         where t1.column_value in (select column_value from table(cast(l_t_num_tab_2 as t_num_tab)));
       
        dbms_output.put_line('=============== IN Condition ===============');
        dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
        dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
      end if;
end;
/

The test result shows that the elapsed time for MEMBER is about 100 times greater than for IN.

SQL > exec test_NUM_MEMBER_vs_IN_Conditions(100000, 1000);

      l_t_num_tab_1.count   = 100000
      l_t_num_tab_2.count   = 1000
      =============== MEMBER Condition ===============
      Elapsed(centi)        = 226
      l_ret_cnt             = 1000
      =============== IN Condition ===============
      Elapsed(centi)        = 2
      l_ret_cnt             = 1000

Here both xplan:

SELECT /*+ Test_NUM_MEMBER */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE MEMBER OF CAST(:B2 AS T_NUM_TAB);
  SQL_ID  9v3d6bbpjs0x2, child number 0  Plan hash value: 3309076612
  -------------------------------------------------------------------------------------------
  | Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |      |       |       |    30 (100)|          |
  |   1 |  SORT AGGREGATE                    |      |     1 |     2 |            |          |
  |*  2 |   COLLECTION ITERATOR PICKLER FETCH|      |    50 |   100 |    30   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(VALUE(KOKBF$)MEMBER OFCAST(:B2 AS "T_NUM_TAB") )
    

SELECT /*+ Test_NUM_IN */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B2 AS T_NUM_TAB)));
  SQL_ID  2z2buk4wrs0gs, child number 0  Plan hash value: 4146508332
  --------------------------------------------------------------------------------------------
  | Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                    |      |       |       |    60 (100)|          |
  |   1 |  SORT AGGREGATE                     |      |     1 |     4 |            |          |
  |*  2 |   HASH JOIN RIGHT SEMI              |      |    10 |    40 |    60   (0)| 00:00:01 |
  |   3 |    COLLECTION ITERATOR PICKLER FETCH|      |   300 |   600 |    30   (0)| 00:00:01 |
  |   4 |    COLLECTION ITERATOR PICKLER FETCH|      |  1000 |  2000 |    30   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - access(VALUE(KOKBF$)=VALUE(KOKBF$))
     

Here bpftrace on kopi2csaccess for both MEMBER and IN Comparison.
For MEMBER, count() = 10,094,950 is about (l_t_obj_tab_1.count * l_t_obj_tab_2.count), which is quadratic performance.
For IN, count() = 100,102 is about (l_t_obj_tab_1.count + l_t_obj_tab_2.count), which is linear performance.

--------------------- MEMBER Comparison ---------------------- 
SQL > exec test_NUM_MEMBE,R_vs_IN_Conditions(100000, 100, 'M');
        l_t_num_tab_1.count   = 100000
        l_t_num_tab_2.count   = 100
        =============== MEMBER Condition ===============
        Elapsed(centi)        = 1199
        l_ret_cnt             = 100
 
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kopi2csaccess+2 /pid == 1129837/ {@[ustack(2)] = count();}'
      @[
          kopi2csaccess+2
          qerocFetch+201
      ]: 100001
      @[
          kopi2csaccess+2
          expeal+59
      ]: 10094950
 
 
--------------------- IN Comparison --------------------------
SQL > exec test_NUM_MEMBER_vs_IN_Conditions(100000, 100, 'I');
        l_t_num_tab_1.count   = 100000
        l_t_num_tab_2.count   = 100
        =============== IN Condition ===============
        Elapsed(centi)        = 13
        l_ret_cnt             = 100
 
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kopi2csaccess+2 /pid == 1129837/ {@[ustack(2)] = count();}'
      @[
          kopi2csaccess+2
          qerocFetch+201
      ]: 100102
      

2. MEMBER vs. IN complexity

We create nested table of objects with map member function to record number of Comparisons in MEMBER and IN queries in order to evaluate the complexity of applied algorithm, similar to the approaches of previous Blog:
     Performance of Oracle Object Collection Comparisons - Part1
     Performance of Oracle Object Collection Comparisons - Part2

---------------- test Object MEMBER vs. IN Comparison Conditions Performance ----------------

create or replace package cmp_counter as
  p_cnt number := 0;
end;
/

create or replace package body helper as
  procedure format(p_name varchar2, p_value number) as
  begin
    dbms_output.put_line(rpad(p_name, 40) ||' = '|| lpad(p_value, 10));
  end format;
end helper;
/

drop type t_obj_tab force;

drop type t_obj force;

create or replace type t_obj as object (
  p_num number,
  map member function comp return integer);
/

create or replace type body t_obj as
  map member function comp return integer is 
    begin 
      cmp_counter.p_cnt := cmp_counter.p_cnt + 1;
      return p_num;
    end; 
end;
/

create or replace type t_obj_tab as table of t_obj;
/

create or replace procedure test_OBJ_MEMBER_vs_IN_Conditions (p_size_1 number, p_size_2 number, p_sel varchar2 := 'A') as
  l_t_obj_tab_1     t_obj_tab := t_obj_tab();
  l_t_obj_tab_2     t_obj_tab := t_obj_tab();
  l_start_time     number;
  l_ret_cnt        number;
begin
  select cast(collect(t_obj(level)) as t_obj_tab) into l_t_obj_tab_1 from dual connect by level <= p_size_1; 
  select cast(collect(t_obj(level)) as t_obj_tab) into l_t_obj_tab_2 from dual connect by level <= p_size_2; 
  
  dbms_output.put_line('l_t_obj_tab_1.count   = '||cardinality(l_t_obj_tab_1));
  dbms_output.put_line('l_t_obj_tab_2.count   = '||l_t_obj_tab_2.count);
    
  if p_sel in ('A', 'M') then
    --================= MEMBER Comparison =================
    cmp_counter.p_cnt := 0;
    l_start_time := dbms_utility.get_time;
    
    select /*+ Test_OBJ_MEMBER */ count(*) into l_ret_cnt 
      from table(cast (l_t_obj_tab_1 as t_obj_tab)) t1 
     where t_obj(t1.p_num) member cast(l_t_obj_tab_2 as t_obj_tab);
     
    dbms_output.put_line('=============== MEMBER Condition ===============');
    dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
    dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
    dbms_output.put_line('Number of Comparisons = '||cmp_counter.p_cnt);
  end if;
       
  if p_sel in ('A', 'I') then
    --================= IN Operator =================
    cmp_counter.p_cnt := 0;
    l_start_time := dbms_utility.get_time;
    
    select /*+ Test_OBJ_IN */ count(*) into l_ret_cnt 
      from table(cast (l_t_obj_tab_1 as t_obj_tab)) t1 
     where t_obj(t1.p_num) in (select t_obj(t2.p_num) from table(cast(l_t_obj_tab_2 as t_obj_tab)) t2);
    
    dbms_output.put_line('=============== IN Condition ===============');
    dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
    dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
    dbms_output.put_line('Number of Comparisons = '||cmp_counter.p_cnt); 
  end if;
end;
/

We run the test:

SQL> exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000);    

     l_t_obj_tab_1.count   = 10000
     l_t_obj_tab_2.count   = 1000
     =============== MEMBER Condition ===============
     Elapsed(centi)        = 4503
     l_ret_cnt             = 1000
     Number of Comparisons = 19001000
     =============== IN Condition ===============
     Elapsed(centi)        = 4
     l_ret_cnt             = 1000
     Number of Comparisons = 11000

The test result shows that the elapsed time for MEMBER is about 1000 times greater than for IN, and the number of Comparisons is more than 1000 times (elapsed time is proportional to number of Comparisons).

For MEMBER, the Number of Comparisons is about (product: l_t_obj_tab_1.count * l_t_obj_tab_2.count=19,001,000), which is quadratic performance.
For IN, the Number of Comparisons is about (addition: l_t_obj_tab_1.count + l_t_obj_tab_2.count=11,000), which is linear performance.

Here both xplan:

SELECT /*+ Test_NUM_MEMBER */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE MEMBER OF CAST(:B2 AS T_NUM_TAB);
  SQL_ID  9v3d6bbpjs0x2, child number 0  Plan hash value: 3309076612
  -------------------------------------------------------------------------------------------
  | Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |      |       |       |    30 (100)|          |
  |   1 |  SORT AGGREGATE                    |      |     1 |     2 |            |          |
  |*  2 |   COLLECTION ITERATOR PICKLER FETCH|      |    50 |   100 |    30   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(VALUE(KOKBF$)MEMBER OFCAST(:B2 AS "T_NUM_TAB") )
    

SELECT /*+ Test_NUM_IN */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B2 AS T_NUM_TAB)));
  SQL_ID  2z2buk4wrs0gs, child number 0  Plan hash value: 4146508332
  --------------------------------------------------------------------------------------------
  | Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                    |      |       |       |    60 (100)|          |
  |   1 |  SORT AGGREGATE                     |      |     1 |     4 |            |          |
  |*  2 |   HASH JOIN RIGHT SEMI              |      |    10 |    40 |    60   (0)| 00:00:01 |
  |   3 |    COLLECTION ITERATOR PICKLER FETCH|      |   300 |   600 |    30   (0)| 00:00:01 |
  |   4 |    COLLECTION ITERATOR PICKLER FETCH|      |  1000 |  2000 |    30   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - access(VALUE(KOKBF$)=VALUE(KOKBF$)) 
     

3. bpftrace on PICKLER FETCH Calls

The Oracle subroutine for PICKLER FETCH is implemented by "kodpunp" (kernel objects data manager (pickler) unpickle an image into an object). We can use bpftrace to count the number of such calls.

3.1 MEMBER Comparison

The bpftrace count on kodpunp (from kgmtimob): 19001000 matches Sqlplus(pid == 1128134) output: Number of Comparisons = 19001000.

SQL > exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000, 'M');

        l_t_obj_tab_1.count   = 10000
        l_t_obj_tab_2.count   = 1000
        =============== MEMBER Condition ===============
        Elapsed(centi)        = 7526
        l_ret_cnt             = 1000
        Number of Comparisons = 19001000
        
        Elapsed: 00:01:15.28

$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kodpunp+2 /pid == 1128134/ {@[ustack(2)] = count();}'
      @[
          kodpunp+2
          kpcocaup+599
      ]: 2
      @[
          kodpunp+2
          kopp2uattr+7010
      ]: 11000
      @[
          kodpunp+2
          kgmtimob+1002
      ]: 19001000
      

3.2 IN Comparison

The bpftrace count on kodpunp (from kgmtimob): 11000 matches Sqlplus(pid == 1128134) output: Number of Comparisons = 11000.

SQL > exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000, 'I');

        l_t_obj_tab_1.count   = 10000
        l_t_obj_tab_2.count   = 1000
        =============== IN Condition ===============
        Elapsed(centi)        = 5
        l_ret_cnt             = 1000
        Number of Comparisons = 11000
        
        Elapsed: 00:00:00.09

$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kodpunp+2 /pid == 1128134/ {@[ustack(2)] = count();}'
      @[
          kodpunp+2
          kpcocaup+599
      ]: 2
      @[
          kodpunp+2
          kgmtimob+1002
      ]: 11000
      @[
          kodpunp+2
          kopp2uattr+7010
      ]: 11000
      

Voila full Plsql callstack:

#0    kodpunp ()
#1    kokoupkl ()
#2    kpcocaup ()
#3    kprcdt ()
#4    kprccu ()
#5    opifcr ()
#6    qergsFetch ()
#7    opifch2 ()
#8    opiefn0 ()
#9    opipls ()
#10   opiodr ()
#11   rpidrus ()
#12   skgmstack ()
#13   rpidru ()
#14   rpiswu2 ()
#15   rpidrv ()
#16   psddr0 ()
#17   psdnal ()
#18   pevm_EXECC ()
#19   pfrinstr_EXECC ()
#20   pfrrun_no_tool ()
#21   pfrrun ()
#22   plsql_run ()