[20260416]奇怪的latch free等待事件3(latch: undo global data).txt

--//继续前面的分析,分析latch: undo global data等待事件：
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.

--//接着看latch: undo global data等待事件。

SYS@book> @ ev_namezpr "^latch: undo global data$"
==============================
EVENT#                        : 1146
EVENT_ID                      : 2221529869
NAME                          : latch: undo global data
PARAMETER1                    : address
PARAMETER2                    : number
PARAMETER3                    : why
WAIT_CLASS_ID                 : 1893977003
WAIT_CLASS#                   : 0
WAIT_CLASS                    : Other
DISPLAY_NAME                  : latch: undo global data
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> @ ashtop sql_id,p1raw,p2,p3  "event='latch: undo global data'"  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         P3 FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------- ----------------- ---------- ---------- ------------------- ------------------- ---------- -------- -----------
       55      .9   67% | 1q3n0uxkhyp0m 000000006F93A150         556          0 2026-04-16 16:20:22 2026-04-16 16:21:11         55        4          55
       10      .2   12% | 1q3n0uxkhyp0m 000000006F938F80         556          0 2026-04-16 16:20:35 2026-04-16 16:20:58         10        2          10
        6      .1    7% | 1q3n0uxkhyp0m 000000006F93A0A0         556          0 2026-04-16 16:21:13 2026-04-16 16:21:13          6        1           6
        4      .1    5% | 1q3n0uxkhyp0m 000000006F93A150         556          1 2026-04-16 16:20:22 2026-04-16 16:21:11          4        3           4
        2      .0    2% | 1q3n0uxkhyp0m 000000006F938F80         556          2 2026-04-16 16:20:35 2026-04-16 16:20:35          2        1           2
        1      .0    1% | 1q3n0uxkhyp0m 000000006F939030         556          3 2026-04-16 16:20:39 2026-04-16 16:20:39          1        1           1
        1      .0    1% | 1q3n0uxkhyp0m 000000006F93A0A0         556          5 2026-04-16 16:20:48 2026-04-16 16:20:48          1        1           1
        1      .0    1% | 1q3n0uxkhyp0m 000000006F93A150         556          6 2026-04-16 16:20:53 2026-04-16 16:20:53          1        1           1
        1      .0    1% | 1q3n0uxkhyp0m 000000006F93A150         556         11 2026-04-16 16:20:50 2026-04-16 16:20:50          1        1           1
        1      .0    1% | 1q3n0uxkhyp0m 000000006F9C1580         556         14 2026-04-16 16:20:39 2026-04-16 16:20:39          1        1           1
10 rows selected.

SYS@book> @ ashtop sql_id,p1raw,p2  "event='latch: undo global data'"  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
--------- ------- ------- ------------- ----------------- ---------- ------------------- ------------------- ---------- -------- -----------
       61     1.1   74% | 1q3n0uxkhyp0m 000000006F93A150         556 2026-04-16 16:20:22 2026-04-16 16:21:11         61        4          61
       12      .2   15% | 1q3n0uxkhyp0m 000000006F938F80         556 2026-04-16 16:20:35 2026-04-16 16:20:58         12        2          12
        7      .1    9% | 1q3n0uxkhyp0m 000000006F93A0A0         556 2026-04-16 16:20:48 2026-04-16 16:21:13          7        2           7
        1      .0    1% | 1q3n0uxkhyp0m 000000006F939030         556 2026-04-16 16:20:39 2026-04-16 16:20:39          1        1           1
        1      .0    1% | 1q3n0uxkhyp0m 000000006F9C1580         556 2026-04-16 16:20:39 2026-04-16 16:20:39          1        1           1
--//30个并发，集中使用5个latch 地址。

SYS@book> @ la 000000006F93A150
ADDR                 LATCH#   CHLD NAME                   GETS      IGETS     MISSES    IMISSES   SPINGETS     SLEEPS  WAIT_TIME
---------------- ---------- ------ ------------------- ------- ---------- ---------- ---------- ---------- ---------- ----------
000000006F93A150        556      2 undo global data    1093351      22416       2822        270       2434        389   25292609
--//latch# 对应P2.

YS@book> @ col_list v$latch_children child|addr|name|gets|misses|sleeps

SELECT
 addr
,child#
,name
,gets
,misses
,sleeps
,immediate_gets
,immediate_misses
,spin_gets
FROM v$latch_children

SYS@book> 9999 where name='undo global data' order by 1
SYS@book> /
ADDR                 CHILD# NAME                                           GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
---------------- ---------- ---------------------------------------- ---------- ---------- ---------- -------------- ---------------- ----------
000000006F938F80          3 undo global data                            1098523       2878        430          15871              593       2450
000000006F939030          4 undo global data                            1173616       3433        398          13173              416       3035
000000006F93A0A0          1 undo global data                             729310       1384        238          65043              963       1147
000000006F93A150          2 undo global data                            1093387       2822        389          22416              270       2434
000000006F9C1580          5 undo global data                            1199855       3557        385           9782              131       3174
00000000705DF2F0          1 undo global data                                  0          0          0              0                0          0
00000000705DF3A0          2 undo global data                                  0          0          0              0                0          0
00000000705DF450          3 undo global data                                  0          0          0              0                0          0
00000000705DF500          4 undo global data                                  0          0          0              0                0          0
00000000705DF5B0          5 undo global data                                  0          0          0              0                0          0
00000000796E82C0          1 undo global data                               1210          0          0              0                0          0
00000000796E8370          2 undo global data                               1510          0          0              0                0          0
00000000796E8420          3 undo global data                               1125          0          0              0                0          0
00000000796E84D0          4 undo global data                               1067          0          0              0                0          0
00000000796E8580          5 undo global data                               1210          0          0              0                0          0
15 rows selected.
--//从gets的数量上看可以发现仅仅使用5个latch，怀疑仅仅这5个latch是可用。其他根本不会使用。
--//从latch名字上看与前面buffer busy waits等待事件修改段头的事务表相关。

小结：
--//可以看出如果密集执行这类语句可能出现的等待事件。latch free,buffer busy waits,latch: undo global data.
--//在11g重复类似测试：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ zzdate ; seq 30 | xargs -IQ -P 30 sqlplus scott/book@book01p @ m22.txt 6e4 test Q >/dev/null ; zzdate
trunc(sysdate)+17/24+51/1440+23/86400 -1776333083.168077837
trunc(sysdate)+17/24+54/1440+18/86400 1776333258.269040595
--//Sum = 175.100962758

SYS@book> @ ashtop event 1=1 trunc(sysdate)+17/24+45/1440+32/86400 trunc(sysdate)+17/24+48/1440+24/86400
    Total                                                                                                      Distinct Distinct    Distinct
  Seconds     AAS %This   EVENT                                      FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
     4228    24.6   79% | direct path write temp                     2026-04-16 17:45:33 2026-04-16 17:48:23       4228      159        4228
      341     2.0    6% |                                            2026-04-16 17:45:32 2026-04-16 17:48:22        292      151         337
      337     2.0    6% | log buffer space                           2026-04-16 17:46:00 2026-04-16 17:48:09        337       13         337
      169     1.0    3% | log file parallel write                    2026-04-16 17:45:33 2026-04-16 17:48:22          1      169         169
      101      .6    2% | db file sequential read                    2026-04-16 17:45:33 2026-04-16 17:48:23        101       77         101
       60      .3    1% | log file switch completion                 2026-04-16 17:45:42 2026-04-16 17:46:07         60        2          60
       34      .2    1% | Disk file operations I/O                   2026-04-16 17:45:43 2026-04-16 17:48:20          1       34          34
       34      .2    1% | db file async I/O submit                   2026-04-16 17:45:54 2026-04-16 17:48:23          1       34          34
       17      .1    0% | log file sync                              2026-04-16 17:45:33 2026-04-16 17:48:22          1        4           4
        5      .0    0% | control file parallel write                2026-04-16 17:46:05 2026-04-16 17:48:21          1        4           4
        3      .0    0% | latch: row cache objects                   2026-04-16 17:47:38 2026-04-16 17:48:01          3        3           3
        2      .0    0% | Log archive I/O                            2026-04-16 17:47:06 2026-04-16 17:48:06          1        2           2
        2      .0    0% | cursor: pin S                              2026-04-16 17:46:27 2026-04-16 17:46:27          1        1           1
        2      .0    0% | latch: object queue header operation       2026-04-16 17:45:36 2026-04-16 17:47:05          1        2           2
        1      .0    0% | ADR block file read                        2026-04-16 17:45:33 2026-04-16 17:45:33          1        1           1
        1      .0    0% | buffer busy waits                          2026-04-16 17:48:15 2026-04-16 17:48:15          1        1           1
        1      .0    0% | db file single write                       2026-04-16 17:47:40 2026-04-16 17:47:40          1        1           1
        1      .0    0% | direct path sync                           2026-04-16 17:45:33 2026-04-16 17:45:33          1        1           1
        1      .0    0% | log file sequential read                   2026-04-16 17:45:55 2026-04-16 17:45:55          1        1           1
19 rows selected.
--//要写临时表，产生的redo比前面的测试要大，毕竟21c多数情况采用纯内存操作(PGA 容纳)，加上temp_undo_enabled=true，产生的
--//日志相对较小。查看产生的archivee，seq从78到91 共14个,占用磁盘空间2042344 ,相当于2042344/1024/1024 = 1.9G。