Search

Top 60 Oracle Blogs

Recent comments

Recursive queries on IM_DOMAIN$ at each cursor execution

At POUG 2018 conference I explained join methods by putting gdb breakpoints on the qer (Query Execution Rowsource) functions that are behind the execution plan operations. I was a bit annoyed by several calls when running a Hash Join because of recursive, internal queries on the dictionary. There are a lot of queries on the dictionary during hard parse, but this was at execution time on a query that had already been parsed before. This is new in 12.2 and seems to be related to In-Memory Global Dictionary Join Groups feature, the execution checking and setting up the Join Group aware Hash Join.

However, I must mention that even if this seems to be related with In-Memory I don’t have it enabled here:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0
Version 18.3.0.0.0
SQL> show parameter inmemory_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
inmemory_size big integer 0

I run the following query on the SCOTT schema to be sure that it is parsed. I force HASH JOIN with hints:

select /*+ leading(EMP DEPT) USE_HASH(DEPT) USE_BASH(BONUS) */ * from DEPT natural join EMP natural join BONUS;

Now starting SQL_TRACE:

column value new_value tracefile
select value from v$diag_info where name='Default Trace File';
VALUE
------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1/CDB1/trace/CDB1_ora_4116.trc
alter session set sql_trace=true;

Then run the same query 10 times:


select /*+ leading(EMP DEPT) USE_HASH(DEPT) USE_BASH(BONUS) */ * from DEPT natural join EMP natural join BONUS;
/
/
/
/
/
/
/
/
/

And tkprof the trace:

alter session set sql_trace=false;
mv &tracefile last.trc ; tkprof last.trc last.txt sort=(execnt)

Here are my 10 executions. The 10 parse calls were soft parses only (no misses in library cache):

select /*+ leading(EMP DEPT) USE_HASH(DEPT) USE_BASH(BONUS) */
* from DEPT natural join EMP natural join BONUS
call     count       cpu    elapsed disk query current  rows
------- ------ -------- ---------- ---- ----- ------- ----
Parse 10 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 120 0 0
------- ------ -------- ---------- ---- ----- ------- ----
total 30 0.00 0.00 0 120 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 130
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ---------- ---------------------------
0 0 0 HASH JOIN (cr=12 pr=0 pw=0
4 4 4 HASH JOIN (cr=12 pr=0 pw=
4 4 4 TABLE ACCESS FULL EMP (cr
4 4 4 TABLE ACCESS FULL DEPT (c
0 0 0 TABLE ACCESS FULL BONUS (c

Next to it, I can see 10 executions of a SELECT on SYS.IM_DOMAIN$ which is recursive:

SQL ID: 0b639nx4zdzxr Plan Hash: 2321277860
select domain#
from
sys.im_domain$ where objn = :1 and col# = :2
call     count       cpu    elapsed disk query current  rows
------- ------ -------- ---------- ---- ----- ------- ----
Parse 10 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---- ----- ------- ----
total 30 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
 (1st) Rows (avg) Rows (max)  Row Source Operation
------ ---------- ---------- ----------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID IM_DOMAIN$
0 0 0 INDEX UNIQUE SCAN IM_DOMAIN_UK (cr=0

I’ve no idea why this is executed even when IM is disabled. There are probably no bad consequences in performance, especially given that we do no logical reads here (I don’t know by which magic by the way). It is just a surprise to see recursive executions on the dictionary during execution.