Oracle 19c如何定位导致Library Cache竞争的DDL操作_分析ASH中的SQL

最可能原因是未完成的ddl长期持有library cache lock。ash可回溯一小时内等待详情，需重点排查sql_opname为ddl且持续waiting的记录，结合v$db_object_cache、x$kgllk等交叉验证锁滞留、时区错位或动态sql隐藏问题。

直接查ash里正在执行的ddl操作

library cache lock等待若集中在某个对象上，最可能的原因就是有未完成的ddl在持有锁。ash（v$active_session_history）是唯一能回溯“谁在什么时候干了什么”的实时证据源，比查v$session_wait更可靠——因为后者只反映当前瞬间状态，而ash保留过去一小时（默认）的采样快照。

关键不是看“有没有DDL”，而是看“有没有**长时间未结束的DDL**”。Oracle DDL本身很快，但CREATE OR REPLACE PACKAGE、ALTER VIEW这类操作会隐式加锁并触发依赖对象重编译，一旦中间卡住（比如依赖表被锁、统计信息刷新冲突），就会在ASH里留下连续多行相同SQL_OPNAME的记录。

用这个SQL定位嫌疑DDL：

SELECT sql_id, sql_opname, event, blocking_session, sample_time, 
       sql_text 
FROM v$active_session_history 
WHERE sql_opname IN ('CREATE', 'ALTER', 'DROP', 'GRANT', 'REVOKE') 
  AND session_state = 'WAITING' 
  AND event = 'library cache lock' 
ORDER BY sample_time DESC;

SQL_OPNAME值为CREATE但SQL_TEXT显示的是CREATE OR REPLACE PACKAGE BODY？说明包体编译卡住了，不是语法错误，而是它依赖的某个视图或表正被其他会话修改或查询中
如果BLOCKING_SESSION非空，顺着它查V$SESSION的SQL_ID和EVENT，大概率发现另一个会话正在硬解析同名对象——这是典型的“DDL未提交 + 并发查询”组合

为什么只盯SQL_OPNAME还不够

SQL_OPNAME字段只反映语句类型，不区分DDL是否真在执行还是已卡死。比如ALTER TABLE ... ADD COLUMN在19c中默认在线，但若该表上有未提交的DML，它会在ASH里持续显示ALTER + enq: TX - row lock contention，此时Library Cache Lock只是连锁反应，根因是事务阻塞。

必须交叉验证：SQL_OPNAME为ALTER且EVENT是library cache lock → 查V$DB_OBJECT_CACHE确认该对象LOADS > 0且EXECUTIONS = 0（说明加载失败卡住）
SQL_OPNAME为CREATE但SQL_TEXT里带AS SELECT → 检查V$SESSION_LONGOPS，这种CTAS可能因目标表空间不足或并行度太高卡在I/O阶段，间接拖住library cache
别忽略SQL_OPNAME = 'PARSE'的记录：它本身不是DDL，但如果和SQL_OPNAME = 'ALTER'出现在同一秒内、且SQL_ID相同，说明是DDL触发的隐式解析（如改完表结构后立刻查该表），这时锁竞争源头仍是前面那个ALTER

排查时最容易漏掉的PDB时区陷阱

19c中PDB的DEFAULT_TIMEZONE配置错误会导致统计信息任务错峰执行，而统计信息收集本身会触发大量DDL式元数据变更（如更新WRI$_OPTSTAT_HISTHEAD_HISTORY），进而引发Library Cache Lock雪崩。这种场景下，ASH里看不到显式DDL，但SQL_OPNAME会出现大量'UPDATE'或'INSERT'，且SQL_TEXT指向WRI$或OPTSTAT开头的系统表。

Crypto Sniper Oracle

机构级量化市场预言机，提供订单簿失衡（OBI）、VWAP分析、自动化报告及Telegram预警。

下载

先确认时区是否一致：
```
SELECT con_id, value FROM cdb_scheduler_global_attribute 
WHERE attribute_name = 'DEFAULT_TIMEZONE';
```
若CDB返回PRC而某个PDB返回PST8PDT，这就是BUG 30076391的典型表现
此时ASH中SQL_OPNAME = 'UPDATE'的记录会集中出现在非预期时间（比如下午1点），且EVENT为library cache lock + p1text = 'handle address'指向dc_statistics类row cache项
临时缓解：在问题PDB中执行EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('MAINTENANCE_WINDOW_GROUP','DEFAULT_TIMEZONE','PRC')，避免重启PDB

别被“无DDL记录”骗了

如果ASH里完全搜不到SQL_OPNAME为DDL的记录，不代表没有DDL——可能它早已结束，但锁没释放。常见于：用户执行DDL后异常退出（Ctrl+C、网络断开），会话变成INACTIVE但锁仍挂在GRD上；或者PL/SQL匿名块里嵌套DDL（如EXECUTE IMMEDIATE 'ALTER TABLE ...'），ASH只记录外层块的SQL_OPNAME = 'PL/SQL EXECUTE'，不展开内部动态语句。

查疑似残留锁：

SELECT kglnaobj, kglhdadr, kgllkmod, kgllkreq 
FROM x$kgllk 
WHERE kgllkmod != 0 
  AND kglhdadr IN (SELECT kglhdadr FROM x$kglob WHERE kglnaobj LIKE '%YOUR_TABLE_NAME%');

结果中kgllkmod = 3表示持有排他锁，kgllkreq = 0表示无人等待，但锁一直挂着

结合V$GES_BLOCKING_ENQUEUE（RAC专用）看跨节点锁链：若RESOURCE_NAME1匹配X$KGLOB.KGLNAOBJ的哈希值，说明是GRD协调失败导致锁滞留
终极手段：用oradebug setmypid; oradebug dump hanganalyze 3抓完整锁图，重点看LIBRARY CACHE LOCK节点是否指向一个已消失的SPID

真实环境里最麻烦的从来不是“找到DDL”，而是“确认它是否真结束了”。锁滞留、时区错位、动态SQL隐藏，这三类问题在19c PDB+RAC混合部署中高频出现，且ASH采样粒度（1秒）刚好会漏掉亚秒级的锁争用脉冲——所以看到等待下降又突然飙升，先别急着优化SQL，回头再扫一遍x$kgllk和CDB_SCHEDULER_GLOBAL_ATTRIBUTE。