event enq: JZ – Join group dictionary when in-memory disable

该问题由MES平台的一个客户提出，某些sql会等待event enq: JZ – Join group dictionary，该等待事件比较冷门，也不确认是否会有隐患。所以让我们来分析一下。

通常我分析不熟悉的enqueue时，通常都会查询v$lock_type，可以简要的了解到该队列的含义。

SQL> select type,name,description from v$lock_type where type='JZ';

TYPE                 NAME                           DESCRIPTION
-------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
JZ                   Join group dictionary          Synchronizes modifications to im_domain$ and im_joingroup$

可以看到JZ队列是为了串行的维护im_domain$和im_joingroup$，这两个基表与in-memory有关。具体特性与功能参考https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/optimizing-queries-with-join-groups.html。

当create join group或者drop join group时，会请求JZ队列锁，这一点应该很容易理解。

但是客户的环境在in-memory并未启用，并且并未create join group的情况下，仍然会出现等待事件enq: JZ – Join group dictionary。

通过研究发现，当使用了hash join的时候，也会请求JZ队列锁。由隐藏参数_sqlexec_join_group_aware_hj_enabled控制。

test demo：

SQL> @sp join_group

-- show parameter by sp

-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%join_group%'

NAME                                     VALUE      DESC
---------------------------------------- ---------- ------------------------------------------------------------------------------------------
_sqlexec_join_group_aware_hj_enabled     TRUE       enable/disable join group aware hash join
_sqlexec_join_group_aware_hj_unencoded_r 50         minimum number of unencoded rowsets processed before adaptation
owsets_tolerated

SQL> @sp inmemory_size

-- show parameter by sp

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
inmemory_size                        big intege 0


SQL> select count(*) from im_joingroup$;

  COUNT(*)
----------
         0

SQL> select count(*) from im_domain$;

  COUNT(*)
----------
         0

SQL> explain plan for select count(*) from test.t,test.t1 where t.object_name=t1.object_name;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2895516660

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    70 |       |  1118   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    70 |       |            |          |
|*  2 |   HASH JOIN         |      | 87256 |  5964K|  3352K|  1118   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    | 73021 |  2495K|       |   396   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 73021 |  2495K|       |   396   (1)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_NAME"="T1"."OBJECT_NAME")

SQL>  select count(*) from test.t,test.t1 where t.object_name=t1.object_name;

  COUNT(*)
----------
    110615

SQL> select * from v$enqueue_stat where rownum=1  and eq_type='JZ';

   INST_ID EQ TOTAL_REQ# TOTAL_WAIT#  SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME     CON_ID
---------- -- ---------- ----------- ---------- ----------- ------------- ----------
         1 JZ          1           0          1           0             0          0

SQL> select count(*) from test.t,test.t1 where t.object_name=t1.object_name;

  COUNT(*)
----------
    110615

SQL> select * from v$enqueue_stat where rownum=1  and eq_type='JZ';

   INST_ID EQ TOTAL_REQ# TOTAL_WAIT#  SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME     CON_ID
---------- -- ---------- ----------- ---------- ----------- ------------- ----------
         1 JZ          2           0          2           0             0          0

SQL> alter session set "_sqlexec_join_group_aware_hj_enabled"=false;

Session altered.

SQL>  select count(*) from test.t,test.t1 where t.object_name=t1.object_name;

  COUNT(*)
----------
    110615

SQL> select * from v$enqueue_stat where rownum=1  and eq_type='JZ';

   INST_ID EQ TOTAL_REQ# TOTAL_WAIT#  SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME     CON_ID
---------- -- ---------- ----------- ---------- ----------- ------------- ----------
         1 JZ          2           0          2           0             0          0

如果没有出现性能问题的话，并不建议关闭_sqlexec_join_group_aware_hj_enabled。

不过个人猜测HASH JOIN请求的JZ锁应该是共享的，之所以会阻塞还是可能有排他模式的请求，分析队列锁，最好还是使用10046+10704 event去分析。