Dear "ASKTOM" experts

Firstly = Very best wishes for the New Year 2023 to you ALL

+

I have recently stumbled upon a rather bizarre case of a long running SQL.
Here is the summary of findings
1) SQL = Select owner, count(*) from dba_tables where owner in (Select OWNNAM from ASZ_DB_SCHEMA_GTT001) and tablespace_name is not null
group by owner;
2) This SQL is getting stuck for approx. 22 minutes
3) This behavior DOES reproduce more than 80-90% (i.e. there are days / times when it executes immediately = i.e. within a few milliseconds)
4) This behavior is ONLY on one PDB of the 22 currently running PROD PDB's deployed on 12 CDB's (I was unable to reproduce this on any other)
5) This only happens when retrieving data from DBA_TABLES = i.e. it does not happen when dba_tables is replaced with objects or extents
6) I have tried to trace it with DBMS_MONITOR - but was unable to reproduce this behavior when trying to trace within the current session or executing MONITOR from a different session ... as xxx_enable ( sid => 123, serial# => 987, waits => true, binds => true) where supplying the current values of SID + SERIAL#
7) If - however - I start the SQL as a script as = Select systimestamp from dual; << this SQL >> Select systimestamp ... and then initiate trace from a different session then it gets stuck for 22 mins as usual... while the trace file shows the SQL statement followed by the next query to get the systimestamp
... but it does NOT output any WAIT events and the timestamps are only milliseconds apart
8) I was testing this behavior over and over and was able to determine that as long as this SQL started even 0.1 sec before I started tracing on the second session it would get stuck
9) This behavior would be the same regardless of whatever other SQL statements I would place before the one being diagnosed
10) When I tried to pause with DBMS_LOCK.sleep(3) the execution would be immediate
11) Then I tried to run with 2 SQL queries = one against OBJECTS and the next against EXTENTS and then this SQL against TABLES - same result
12) As the query to EXTENTS takes longer than a few milliseconds (over 1 second) - this time delay allowed me to start tracing from the second putty session with MONITOR - and then the query would execute immediately
13) I repeated the process several times toggling between tracing started right away (i.e. while query against EXTENTS was still running) and after it completed and was able to confirm that just because tracing had been activated BEFORE query to TABLES started the query executed immediately // when tracing started AFTER query against EXTENTS returned results then it got stuck for 22 mins
+
Naturally this is NOT an issue of any major concern in this particular case = however it might be at some other instances.
More importantly = as I had managed to reproduce this behavior more than 30 times (different days and times) AND ... this behavior does NOT occur when a session is being traced ... it might cause significant performance issues ... issues that could NOT be captured on trace files

Any thoughts ...?

I will really appreciate your feedback.
Also - I would be happy to perform some additional tests - if you can possibly think of any additional test scenarios...

Cheers
Andre

Select /+  gather_plan_statistics */ owner, count(*) from dba_tables 
where owner in (Select OWNNAM from ASZ_DB_SCHEMA_GTT001) and tablespace_name is not null group by owner

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST +PEEKED_BINDS'))