semi/anti join cache

发表于2026年2月10日由lixiangyu

之前写过一篇标量子查询缓存http://www.minniebaby.tech/2024/12/21/oracle-scalar-subquery-caching/

描述了标量子查询或者filter在执行时，会使用”_query_execution_cache_max_size”对外表关联列进行缓存，避免重复多余的扫描内表，具体原理参考之前的文章，这里就不描述了，本文的主要内容是验证对于subquery unnest之后的semi/anti join是否适用。

继续阅读 →

发表在 Oracle, Oralce performance | 标签为 , , | 留下评论

More about ITL in oracle

发表于2026年2月9日由lixiangyu

ITL(Interested Transaction List ),The Oracle database engine locks the data modified by a transaction at the row level. To implement this feature every data block contains a list of all transactions that are modifying it. This list is commonly called interested transaction list (ITL). Its purpose is twofold. First, it is used to store information to identify a transaction as well as a reference to access the undo data associated to it. Second, it is referenced by every modified or locked row to indicate which transaction it is involved. 继续阅读 →

发表在 Oracle | 标签为 | 留下评论

Oracle GoldenGate Trail File

发表于2026年2月9日由lixiangyu

Trail files are generated by both the original extract and the Data Pump (extract) process. Trail files are read by the Data Pump (extract) and Replicat (replicat) processes. 继续阅读 →

发表在 Oracle | 标签为 , | 留下评论

Goldengate extract from Oracle ADG standby side ALO and ADG mode

发表于2026年2月9日由lixiangyu

Yes, you can extract the data from a Physical Standby database. There are actually two methods available in Oracle GoldenGate., 继续阅读 →

发表在 Oracle | 标签为 | 留下评论

Troubleshooting Library cache lock (OPTIMIZER EXPRESSION HEADER ) on oracle 19c

发表于2026年2月9日由lixiangyu

The environment is Oracle 19.14 RAC 4-nodes, After the database was upgraded to 19c, there was a performance problem. From AWR, I saw a large number of library cache locks and library cache pins in the top event.namespace was OPTIMIZER EXPRESSION HEADER 继续阅读 →

发表在 Oracle, Oracle troubleshooting | 标签为 , | 留下评论

Troubleshooting oracle 19c RAC ‘gc cr block lost’ and ‘Library Cache Load Lock’

发表于2026年2月9日由lixiangyu

最近遇到这个案例大量FG prorcess堵塞，19c (19.4) 2nodes RAC, 等待Library Cache Load Lock， 最终的堵塞会话为REC0 后台进程， 该进程等待gc cr block lost. 该事件并不常见，相似的event 还可能有gc current block lost, 仅发生在ORACLE RAC环境，当前台进程发送包给远程节点的LMSn进程，并没有及时的得到反馈时记录该事件，而可能会堵塞其它会话，产生其它等待事件，也可能是ENQ SQ或gc  xx acquire等，瞬间产生连接数耗尽等现象。 继续阅读 →

发表在 Oracle, Oracle troubleshooting | 标签为 , , | 留下评论

internal block change tracking

发表于2026年2月9日由lixiangyu

Oracle RMAN was able to take incremental backups already in 9i. However, prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup. Oracle 10g new feature, block change tracking, minimizes number of blocks RMAN needs to read to a strict minimum. With block change tracking enabled RMAN accesses on disk only blocks that were changed since the latest base incremental backup. 继续阅读 →

发表在 Oracle | 标签为 | 留下评论

OGG(12.3) Extract long time lag after Oracle RAC a instance Crash

发表于2026年1月31日由lixiangyu

Oracle 12c RAC 1个实例因硬件原因突然crash,  另一实例上的goldengate  Extract 进程Lag At Chkpt 持续增加，read checkpoint并不动，开始是因为归档日志被rman备份任务备份后删除，但restore 归档日志从Recovery Checkpoint到当前确认都存在后依旧hang,  清除了BR 文件再次启动extract进程恢复正常。记录一下这个问题。
 继续阅读 →

发表在 Oracle | 标签为 | 留下评论

如何增加新表到Goldengate已存在的同步链路?

发表于2026年1月31日由lixiangyu

在日常Goldengate日常运维中最常见的是在现有的同步进程中增加新的同步表， 这里记录简单的使用class mode典型模式时，增加新表的过程。 继续阅读 →

发表在 Oracle | 标签为 | 留下评论

Troubleshooting Goldengate OGG-02171 and OGG-02191 Pump Extract process ABENDED

发表于2026年1月31日由lixiangyu

最近处理了一起Goldengate Pump Extract进程abended的案例， 进程日志提示ERROR OGG-02171 Error reading LCR from data source和 ERROR OGG-02191 Incompatible record 104 错误，属于trail文件中的事务记录损坏， 通常是跳过文件中的错误记录，重新启动进程，环境是集成模式的goldengate 18.1。 继续阅读 →

发表在 Oracle, Oracle troubleshooting | 标签为 , | 留下评论