该问题由MES平台的一个客户提出，某些sql会等待event enq: JZ – Join group dictionary，该等待事件比较冷门，也不确认是否会有隐患。所以让我们来分析一下。
通常我分析不熟悉的enqueue时，通常都会查询v$lock_type，可以简要的了解到该队列的含义。
|
1 2 3 4 5 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
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去分析。