oracle19c子容器“library cache lock”排查分享

近期接到业务部门反馈，系统在某周六上午10点后、下午14点后出现卡顿，数据查询异常缓慢，持续约 30 分钟后恢复正常。为避免类似问题再次发生，进行了排查分析。

初步排查

由于数据库由上级公司统一搭建，仅提供了 schema 权限。通过导出当前 schema 的 AWR 数据，发现存在“library cache lock”事件，但未能从 AWR 报告中明确定位问题原因。基于 schema 环境的查询也未发现异常，怀疑问题可能出现在数据库其他层面。

深入分析

联系上级公司协助导出该时间段的 AWR 数据，并通过导入本地环境进行分析。

1. 导出 AWR 数据

通过 awrextr.sql 脚本导出目标数据库的 AWR 数据：

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql
Enter value for dbid: 1901052105
Enter value for num_days: 7
Enter value for begin_snap: 255
Enter value for end_snap: 412
Enter value for directory_name: ORACLE_BASE
Enter value for file_name: awrdat_255_412

导出文件路径：/u01/app/oracle/awrdat_255_412.dmp。

2. 导入 AWR 数据

通过 awrload.sql 脚本将导出的 AWR 数据导入本地数据库：

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql
Enter value for directory_name: ORACLE_BASE
Enter value for file_name: awrdat_255_412
Enter value for schema_name: C##AWR_STAGE
Enter value for default_tablespace: SYSAUX
Enter value for temporary_tablespace: TEMP

通过以下 SQL 验证数据加载成功：

SELECT dbid, COUNT(*) FROM dba_hist_snapshot GROUP BY dbid;

3. 分析“library cache lock”事件

通过历史活动会话视图和 SQL 文本视图查询相关锁等待的会话信息：
从 Oracle 数据库的历史活动会话视图（dba_hist_active_sess_history）、SQL 统计数据（dba_hist_sqlstat）和 SQL 文本（DBA_HIST_SQLTEXT）中获取与特定 SQL 语句和会话相关的信息，主要集中在发生 “library cache lock” 事件的会话。找到在给定时间点（snap_id=13511）有“library cache lock”锁等待的会话。

-- 查询所有正在等待“library cache lock”事件的会话信息
SELECT a.sample_time,
       a.sql_id,
       NVL(a.event, 'CPU') AS event,
       a.in_parse,
       a.in_hard_parse,
       a.force_matching_signature,
       q.sql_text
FROM dba_hist_active_sess_history a
LEFT JOIN dba_hist_sqlstat t ON a.sql_id = t.sql_id
JOIN DBA_HIST_SQLTEXT q ON a.sql_id = q.sql_id
WHERE t.force_matching_signature = (
    SELECT force_matching_signature
    FROM (
        SELECT s.force_matching_signature, COUNT(*) AS cnt
        FROM dba_hist_sqlstat s
        WHERE a.DBID = 1201140860
        GROUP BY s.force_matching_signature
        ORDER BY COUNT(*) DESC
    )
    WHERE ROWNUM = 1
)
AND a.DBID = 1201140860
AND a.snap_id = 13511
AND event = 'library cache lock';

查询结果输出可以看到，目前数据库正在收集统计作业，收集我们知道会带来库缓存锁，所以怀疑是收集作业导致。因为收集作业是根容器，所以没办法通过子容器awr中查看到相关作业信息

SAMPLE_TIME                       SQL_ID          EVENT                IN_PARSE   IN_HARD_PARSE     FORCE_MATCHING_SIGNATURE SQL_TEXT
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N          N                                        0 call dbms_stats.gather_database_stats_job_proc (  )
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N          N                                        0 call dbms_stats.gather_database_stats_job_proc (  )
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N          N                                        0 call dbms_stats.gather_database_stats_job_proc (  )
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N          N                                        0 call dbms_stats.gather_database_stats_job_proc (  )
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N          N                                        0 call dbms_stats.gather_database_stats_job_proc (  )
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N          N                                        0 call dbms_stats.gather_database_stats_job_proc (  )
07-DEC-24 10.14.02.810000000 AM   b6usrg82hwsa3   library cache lock   N      

分析发现，锁争用主要集中在 dbms_stats.gather_database_stats_job_proc() 系统统计收集作业，其他业务 SQL 语句的争用次数较少。进一步核实，该作业的执行时间与问题发生时间段完全吻合。

结论与改进措施

1. 原因定位
   系统卡顿的主要原因是统计信息收集作业导致的“library cache lock”锁争用，尤其是在周六白天执行的作业时间段，对业务查询产生较大影响。

2. 改进措施

• 调整统计信息收集作业的执行时间，优化统计信息收集策略（关闭白天收集信息策略），减少锁争用的影响范围。