[20260416]奇怪的latch free等待事件1.txt
--//在例行检查发现，生产系统前5个等待事件latch free等待事件(排在第4位)，大约400秒上下。开始以为space management
--//background task相关，查看参数_enable_spacebg=false，明显不是原来遇到的情况。仔细看对应的sql语句，发现一个特点，就是
--//采用with开头，开始不理解为什么会出现latch free等待，通过latch address查询latch name，发现是SQL memory manager
--//workarea list latch,明显与sql执行计划中LOAD AS SELECT (CURSOR DURATION MEMORY)，说明采用CURSOR DURATION MEMORY方式与
--//以前不同。
--//顺便提一下使用with注意一个细节问题，如果要形成中间结果集类似临时表，类似使用提示materialize要产生小量redo，如果对应
--//sql语句执行频率非常高，累计起来的redo产生量还是非常巨大的，建议修改sql语句规避这类写法，当然这类写法有一个明显的好处
--//，阅读理解sql语句会非常清晰,如果sql语句执行频率不是很高，可以采用。
--//以前没做过这类SQL语句密集执行的测试，看看主要集中出现那些等待事件。写一个例子加强记忆与理解。
--//首先转抄CURSOR DURATION MEMORY相关信息,便于大家理解相关信息：
Oracle 的 CURSOR DURATION MEMORY 主要涉及游标持续时间内存管理，包括两个核心层面：Cursor-Duration 临时表(内存中临时表)使
用的 PGA 内存，以及游标生命周期内 Private SQL Area 的内存分配。
以下是详细的技术解析：
1. Cursor-Duration Temporary Tables (CDT) 内存机制
从 Oracle 12.2 开始引入的 "In-Memory Cursor-Duration Temporary Table" 是一种查询优化技术，用于物化中间结果(如 CTE、星型
转换、分组集合操作)。
核心特性
---------------------------------------------------------------------
| 特性 | 说明 |
|----------|--------------------------------------------------------|
| 存储位置 | 本质上是 PGA 内存(UGA 子堆)，而非 SGA 或磁盘临时表空间 |
| 生命周期 | 与游标生命周期绑定，游标关闭即释放 |
| 溢出机制 | PGA 不足时自动溢出到磁盘临时段(类似排序操作) |
| 可见性 | 仅对执行该游标的会话可见，元数据不存储在数据字典中 |
| 授权 | 无需 In-Memory Option 许可 |
---------------------------------------------------------------------
工作流程
1. 优化器选择使用 cursor-duration 临时表的执行计划
2. 在 PGA 中创建唯一命名的临时表结构
3. 加载数据到内存(PGA)，内存不足时写入磁盘临时段
4. 查询执行完毕后 truncate 并释放内存/磁盘段
2. 游标内存结构(Private SQL Area)
每个游标在 PGA 中占用 Private SQL Area，分为两个区域：
. Persistent Area(持久区)
- 存储绑定变量、数据类型转换等游标结构信息
- Cursor 关闭时释放
. Run-Time Area(运行时区)
- SQL 执行过程中使用(排序、哈希、位图合并等)
- 大小取决于处理数据量、行数、每行大小
- DML：语句执行完即释放
- SELECT：记录全部传给客户端或取消查询后释放
--//补充，开始以为采用这类方式如果纯内存操作(PGA 容纳)，不会产生redo，实际测试发现还是产生小量redo，估计与在PGA中创建唯
--//一命名的临时表结构相关，另外如果溢出到磁盘临时段，肯定会产生日志，另外12c以后建议设置temp_undo_enabled=true，这样产
--//生的undo redo日志记录在临时表空间，大大减少产生日志的大小。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试环境：
SCOTT@book01p> create table job_times (sid number, serial# number,time_ela1 number,time_ela2 number ,start_date timestamp,end_date timestamp,method varchar2(20));
Table created.
SCOTT@book01p> create table t as select * from all_objects;
Table created.
SCOTT@book01p> create unique index pk_t on t (object_id);
Index created.
SCOTT@book01p> alter table t add constraint pk_t primary key (object_id);
Table altered.
$ cat m22.txt
set verify off
variable v_method varchar2(20);
exec :v_method := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,&3,dbms_utility.get_time ,null , localtimestamp,null ,:v_method) ;
commit ;
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
with t1 as ( select /*+ materialize */ 2 id from dual ) select count(*) into l_count from t where t.object_id in (select id from t1) ;
END LOOP;
END;
/
update job_times set time_ela2 = dbms_utility.get_time ,end_date=localtimestamp where sid=sys_context ('userenv', 'sid') and serial#= &3 and method = :v_method ;
commit;
quit
--//例子写的非常牵强，使用提示materialize。主要目的为了使用Cursor-Duration Temporary Tables。
--//参数1 循环次数 参数2 测试方法 参数3 执行序号。
--//其中sq语句的执行计划如下：
Plan hash value: 3663140541
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 4 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 1 |00:00:00.01 | 4 |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6606_CDED8F3 | 1 | | | | | 0 |00:00:00.01 | 1 |
| 3 | FAST DUAL | | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 1 | 1 | 8 | | | 1 |00:00:00.01 | 2 |
| 5 | NESTED LOOPS | | 1 | 1 | 8 | 3 (34)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 6 | VIEW | VW_NSO_1 | 1 | 1 | 3 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 |
| 7 | HASH UNIQUE | | 1 | 1 | 3 | | | 1 |00:00:00.01 | 0 |
| 8 | VIEW | | 1 | 1 | 3 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_CDED8F3 | 1 | 1 | 13 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 0 |
|* 10 | INDEX UNIQUE SCAN | PK_T | 1 | 1 | 5 | 0 (0)| | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$BE5C8E5F
2 - SEL$1
3 - SEL$1 / "DUAL"@"SEL$1"
6 - SEL$291F8F59 / "VW_NSO_1"@"SEL$BE5C8E5F"
7 - SEL$291F8F59
8 - SEL$D67CB2D2 / "T1"@"SEL$3"
9 - SEL$D67CB2D2 / "T1"@"SEL$D67CB2D2"
10 - SEL$BE5C8E5F / "T"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("T"."OBJECT_ID"="ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
2 - SEL$1
- materialize
3.测试：
$ zzdate ; seq 30 | xargs -IQ -P 30 sqlplus scott/book@book01p @ m22.txt 6e4 test Q >/dev/null ; zzdate
trunc(sysdate)+16/24+20/1440+16/86400 -1776327616.141770335
trunc(sysdate)+16/24+21/1440+14/86400 1776327674.080298985
--//Sum = 57.93852865
SYS@book> @ ashtop event 1=1 trunc(sysdate)+16/24+20/1440+16/86400 trunc(sysdate)+16/24+21/1440+14/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- --------------------------- ------------------- ------------------- ---------- -------- -----------
1084 18.7 58% | 2026-04-16 16:20:16 2026-04-16 16:21:13 883 56 934
310 5.3 17% | latch free 2026-04-16 16:20:18 2026-04-16 16:21:12 307 32 310
106 1.8 6% | buffer busy waits 2026-04-16 16:20:23 2026-04-16 16:21:10 106 23 106
82 1.4 4% | latch: undo global data 2026-04-16 16:20:22 2026-04-16 16:21:13 82 9 82
63 1.1 3% | log file parallel write 2026-04-16 16:20:17 2026-04-16 16:21:13 1 45 45
50 .9 3% | LGWR any worker group 2026-04-16 16:20:18 2026-04-16 16:21:13 1 50 50
38 .7 2% | cursor: pin S 2026-04-16 16:20:19 2026-04-16 16:21:10 1 10 10
25 .4 1% | LGWR worker group ordering 2026-04-16 16:20:19 2026-04-16 16:21:12 1 25 25
18 .3 1% | latch: enqueue hash chains 2026-04-16 16:20:21 2026-04-16 16:21:10 18 7 18
15 .3 1% | latch: redo allocation 2026-04-16 16:20:23 2026-04-16 16:21:11 8 7 11
14 .2 1% | latch: cache buffers chains 2026-04-16 16:20:56 2026-04-16 16:21:09 14 4 14
14 .2 1% | log file switch completion 2026-04-16 16:20:32 2026-04-16 16:20:32 14 1 14
12 .2 1% | log file sync 2026-04-16 16:20:17 2026-04-16 16:21:13 1 5 5
11 .2 1% | LGWR wait for redo copy 2026-04-16 16:20:20 2026-04-16 16:21:07 1 11 11
10 .2 1% | db file async I/O submit 2026-04-16 16:20:33 2026-04-16 16:21:11 1 10 10
4 .1 0% | latch: redo copy 2026-04-16 16:20:35 2026-04-16 16:21:06 4 3 4
2 .0 0% | control file parallel write 2026-04-16 16:20:32 2026-04-16 16:20:39 1 2 2
1 .0 0% | LGWR all worker groups 2026-04-16 16:20:34 2026-04-16 16:20:34 1 1 1
1 .0 0% | direct path write 2026-04-16 16:21:05 2026-04-16 16:21:05 1 1 1
1 .0 0% | enq: SQ - contention 2026-04-16 16:20:16 2026-04-16 16:20:16 1 1 1
1 .0 0% | latch: redo writing 2026-04-16 16:20:23 2026-04-16 16:20:23 1 1 1
21 rows selected.
--//排名第1等待事件就是latch free。
SYS@book> @ ev_namezpr "^latch free$"
==============================
EVENT# : 558
EVENT_ID : 3474287957
NAME : latch free
PARAMETER1 : address
PARAMETER2 : number
PARAMETER3 : why
WAIT_CLASS_ID : 1893977003
WAIT_CLASS# : 0
WAIT_CLASS : Other
DISPLAY_NAME : latch free
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> @ ashtop sql_id,p1raw,p2 "event='latch free'" trunc(sysdate)+16/24+20/1440+16/86400 trunc(sysdate)+16/24+21/1440+14/86400
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID P1RAW P2 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ----------------- ---------- ------------------- ------------------- ---------- -------- -----------
26 .4 8% | 1q3n0uxkhyp0m 0000000075BD4618 842 2026-04-16 16:20:43 2026-04-16 16:21:02 26 2 26
26 .4 8% | 1q3n0uxkhyp0m 0000000075BD4C58 842 2026-04-16 16:20:38 2026-04-16 16:21:00 26 2 26
22 .4 7% | 1q3n0uxkhyp0m 0000000075BD4B90 842 2026-04-16 16:20:56 2026-04-16 16:20:57 22 2 22
20 .3 6% | 1q3n0uxkhyp0m 0000000075BD55B8 842 2026-04-16 16:21:01 2026-04-16 16:21:01 20 1 20
20 .3 6% | 1q3n0uxkhyp0m 0000000075BD5810 842 2026-04-16 16:20:54 2026-04-16 16:20:54 20 1 20
20 .3 6% | 1q3n0uxkhyp0m 0000000075BD60A8 842 2026-04-16 16:20:37 2026-04-16 16:20:37 20 1 20
18 .3 6% | 1q3n0uxkhyp0m 0000000075BD63C8 842 2026-04-16 16:20:52 2026-04-16 16:20:55 18 2 18
17 .3 5% | 1q3n0uxkhyp0m 0000000075BD54F0 842 2026-04-16 16:20:27 2026-04-16 16:20:27 17 1 17
16 .3 5% | 1q3n0uxkhyp0m 0000000075BD6DF0 842 2026-04-16 16:20:40 2026-04-16 16:20:40 16 1 16
14 .2 5% | 1q3n0uxkhyp0m 0000000075BD51D0 842 2026-04-16 16:20:31 2026-04-16 16:20:40 14 2 14
13 .2 4% | 1q3n0uxkhyp0m 0000000075BD5D88 842 2026-04-16 16:20:18 2026-04-16 16:20:18 13 1 13
11 .2 4% | 1q3n0uxkhyp0m 0000000075BD5E50 842 2026-04-16 16:20:19 2026-04-16 16:20:51 11 2 11
8 .1 3% | 1q3n0uxkhyp0m 0000000075BD5428 842 2026-04-16 16:20:41 2026-04-16 16:20:41 8 1 8
8 .1 3% | 1q3n0uxkhyp0m 0000000075BD58D8 842 2026-04-16 16:20:18 2026-04-16 16:20:48 8 2 8
6 .1 2% | 1q3n0uxkhyp0m 0000000075BD3E48 842 2026-04-16 16:20:59 2026-04-16 16:20:59 6 1 6
6 .1 2% | 1q3n0uxkhyp0m 0000000075BD71D8 842 2026-04-16 16:20:25 2026-04-16 16:21:02 6 2 6
5 .1 2% | 1q3n0uxkhyp0m 0000000075BD4550 842 2026-04-16 16:20:28 2026-04-16 16:20:28 5 1 5
5 .1 2% | 1q3n0uxkhyp0m 0000000075BD59A0 842 2026-04-16 16:20:42 2026-04-16 16:21:12 5 2 5
5 .1 2% | 1q3n0uxkhyp0m 0000000075BD5A68 842 2026-04-16 16:21:08 2026-04-16 16:21:08 5 1 5
5 .1 2% | 1q3n0uxkhyp0m 0000000075BD5BF8 842 2026-04-16 16:20:58 2026-04-16 16:21:08 5 2 5
5 .1 2% | 1q3n0uxkhyp0m 0000000075BD6940 842 2026-04-16 16:21:07 2026-04-16 16:21:07 5 1 5
5 .1 2% | 1q3n0uxkhyp0m 0000000075BD6C60 842 2026-04-16 16:21:12 2026-04-16 16:21:12 5 1 5
4 .1 1% | 1q3n0uxkhyp0m 0000000075BD4230 842 2026-04-16 16:21:09 2026-04-16 16:21:09 4 1 4
4 .1 1% | 1q3n0uxkhyp0m 0000000075BD4A00 842 2026-04-16 16:20:51 2026-04-16 16:20:51 4 1 4
3 .1 1% | 1q3n0uxkhyp0m 0000000075BD5B30 842 2026-04-16 16:20:25 2026-04-16 16:20:33 3 2 3
3 .1 1% | 1q3n0uxkhyp0m 0000000075BD6170 842 2026-04-16 16:21:10 2026-04-16 16:21:10 3 1 3
2 .0 1% | 1q3n0uxkhyp0m 0000000075BD4DE8 842 2026-04-16 16:20:23 2026-04-16 16:20:23 2 1 2
2 .0 1% | 1q3n0uxkhyp0m 0000000075BD5748 842 2026-04-16 16:21:07 2026-04-16 16:21:07 2 1 2
2 .0 1% | 1q3n0uxkhyp0m 0000000075BD5F18 842 2026-04-16 16:21:09 2026-04-16 16:21:09 2 1 2
1 .0 0% | 1q3n0uxkhyp0m 0000000075BD4D20 842 2026-04-16 16:20:41 2026-04-16 16:20:41 1 1 1
1 .0 0% | 1q3n0uxkhyp0m 0000000075BD4F78 842 2026-04-16 16:21:02 2026-04-16 16:21:02 1 1 1
1 .0 0% | 1q3n0uxkhyp0m 0000000075BD6490 842 2026-04-16 16:20:59 2026-04-16 16:20:59 1 1 1
1 .0 0% | 1q3n0uxkhyp0m 0000000075BD6F80 842 2026-04-16 16:21:11 2026-04-16 16:21:11 1 1 1
1 .0 0% | 0000000075BD4C58 842 2026-04-16 16:20:38 2026-04-16 16:20:38 1 1 1
1 .0 0% | 0000000075BD4DE8 842 2026-04-16 16:20:23 2026-04-16 16:20:23 1 1 1
1 .0 0% | 0000000075BD5810 842 2026-04-16 16:20:54 2026-04-16 16:20:54 1 1 1
1 .0 0% | 0000000075BD5E50 842 2026-04-16 16:20:19 2026-04-16 16:20:19 1 1 1
37 rows selected.
--//出现37行。至少使用37个latch address。
SYS@book> @ la 0000000075BD4618
ADDR LATCH# CHLD NAME GETS IGETS MISSES IMISSES SPINGETS SLEEPS WAIT_TIME
---------------- ------ ------ -------------------------------------- ------ ----- ------ ------- -------- ------ ----------
0000000075BD4618 842 11 SQL memory manager workarea list latch 107422 0 191 0 85 108 6199765
--//p2 = latch# ,也就是P2对应LATCH#。
SYS@book> select count(*) from v$latch_children where name='SQL memory manager workarea list latch';
COUNT(*)
----------
67
--//对应数量的latch有67个。
SYS@book> @ col_gvlist v$latch_children latch#|name min(gets),max(gets),min(MISSES),max(MISSES) "LATCH#=842"
SELECT
latch#
,name
,min(gets),max(gets),min(MISSES),max(MISSES)
FROM v$latch_children
WHERE LATCH#=842
group by
latch#
,name
SYS@book> /
LATCH# NAME MIN(GETS) MAX(GETS) MIN(MISSES) MAX(MISSES)
---------- ---------------------------------------- ---------- ---------- ----------- -----------
842 SQL memory manager workarea list latch 106981 109019 118 240
--//可以发现oracle均匀使用各个latch地址，gets数量非常接近。
SYS@book> @ ashtop sid,p1raw "event='latch free' and sid=312" trunc(sysdate)+16/24+20/1440+16/86400 trunc(sysdate)+16/24+21/1440+14/86400
Total Distinct Distinct Distinct
Seconds AAS %This SID P1RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ---------- ----------------- ------------------- ------------------- ---------- -------- -----------
2 .0 13% | 312 0000000075BD4618 2026-04-16 16:20:43 2026-04-16 16:21:02 2 2 2
2 .0 13% | 312 0000000075BD58D8 2026-04-16 16:20:18 2026-04-16 16:20:48 2 2 2
2 .0 13% | 312 0000000075BD63C8 2026-04-16 16:20:52 2026-04-16 16:20:55 2 2 2
1 .0 7% | 312 0000000075BD4B90 2026-04-16 16:20:56 2026-04-16 16:20:56 1 1 1
1 .0 7% | 312 0000000075BD4C58 2026-04-16 16:21:00 2026-04-16 16:21:00 1 1 1
1 .0 7% | 312 0000000075BD54F0 2026-04-16 16:20:27 2026-04-16 16:20:27 1 1 1
1 .0 7% | 312 0000000075BD59A0 2026-04-16 16:21:12 2026-04-16 16:21:12 1 1 1
1 .0 7% | 312 0000000075BD5E50 2026-04-16 16:20:19 2026-04-16 16:20:19 1 1 1
1 .0 7% | 312 0000000075BD60A8 2026-04-16 16:20:37 2026-04-16 16:20:37 1 1 1
1 .0 7% | 312 0000000075BD6940 2026-04-16 16:21:07 2026-04-16 16:21:07 1 1 1
1 .0 7% | 312 0000000075BD6DF0 2026-04-16 16:20:40 2026-04-16 16:20:40 1 1 1
1 .0 7% | 312 0000000075BD71D8 2026-04-16 16:20:25 2026-04-16 16:20:25 1 1 1
12 rows selected.
--//顺便查询其中一个sid，可以发现并不是每个会话固定使用1个latch address，不知道每次如何选择。至少说明均匀使用，每个会话不固定。
--//这样就好理解为什么出现这类等待事件，在执行这类sql语句时，会话持有该latch，而其他会话执行类似语句CURSOR DURATION
--//MEMORY时也需要申请该latch，这样需要等待它释放。如果密集执行这类语句就会在该latch上出现争用。
--//补充产生redo大小：
SYS@book> @ curr_redo
Current log sequence No Percent Full bytes used exclude header Left space logfile size low_rba on_disk_rba MEMBER
----------------------- ------------ ------------------------- ---------- ------------ -------------------- -------------------- ----------------------------
486 15.62 24564224 132721664 157286400 486.46376.0 486.47978.0 /u01/oradata/BOOK/redo03.log
$ zzdate ; seq 30 | xargs -IQ -P 30 sqlplus scott/book@book01p @ m22.txt 6e4 test Q >/dev/null ; zzdate
trunc(sysdate)+18/24+05/1440+46/86400 -1776333946.237108808
trunc(sysdate)+18/24+06/1440+44/86400 1776334004.890784693
--//Sum = 58.653675885
SYS@book> @ curr_redo
Current log sequence No Percent Full bytes used exclude header Left space logfile size low_rba on_disk_rba MEMBER
----------------------- ------------ ------------------------- ---------- ------------ -------------------- -------------------- ----------------------------
492 65.78 103461888 53824000 157286400 491.586.0 492.202075.0 /u01/oradata/BOOK/redo03.log
--//132721664+103461888+(492-486-1)*157286400 = 1022615552,相当于1022615552/1024/1024 = 975M。
--//另外11g测试差不多1.9G。
--//另外写剩下2个等待事件。buffer busy waits，latch: undo global data。
