高度警惕：这个操作会导致Oracle 夯死

数据运维 2024-01-29 Escape 手机阅读

   

     首先请大家转发，让很多朋友知道这个坑！

    前几天一个老客户的核心系统出现了异常，整个故障持续了几分钟时间，虽然不长，但是仍然影响较大。从用户提供的ASH和AWR我也进行了简单分析，最后到现场排查了一下，发现了一些蛛丝马迹。

       从AWR来看，top event出现了大量的shared pool latch以及cursor pin相关等待事件，看上去数据库基本上处于夯死的状态了。进一步看简单看了一下ASH，发现故障持续时间并不长，就几分钟。

    对于shared pool latch，cursor:pin S wait in X我们都知道跟SQL解析有关，而其中latch:shared pool又跟硬解析有关。当时猜测是不是有突发大量的硬解析或者说DDL等操作，根据用户反馈该时间点并非业务高峰期，更不会有DDL等操作。

    此时如果如果要进一步诊断的话，我们通常应该去看具体是哪些用户导致的这么高的latch等待，比如这个用户在当时在干什么？这部分内容ASH是有一些数据的，不过看得不够彻底。

     实际上我们要去看这个432会话的执行SQL情况，发现部分关键SQL死活查不到，v$sqlv$sqlareadba_hist_sqltext等。询问用户有哪些监控的时候，提到只有zabbix，其中zabbix监控的内容极为简单和粗略，基本上没用。所以我在想，此时如果用户有一套针对数据库的zCloud 细粒度监控多好，最新的6.1版本功能很强，支持10多种商业开源国产数据库，支持各种细粒度下钻，非常适合排查疑难杂症。比如要分析过去某个时间段，看负载情况，直接拖拽就行，简直不要太方便：

      实际上23年我几次出差，我每次到现场的时候，客户都说李总你很久都没来了，我们刚好有几个小问题，请帮我们看看。我基本上没用CRT/xShell之类工具登录过客户生产环境，都是让用户打开zCloud直接看，期间帮用户排除到了好几个隐患。

     继续回到案例上来，这里我们可以从ASH看到实际上等待最高session的Program是sqlplus（且user是SYS);很明显这是运维人员的操作，并非应用程序。此外还有些latch:row cache objects等待，那么有没有可能是row cache objects影响了latch:shared pool呢？我分析ash裸数据发现，session 432和604 是相互阻塞的，而其中时间点最早的432执行的一个SQLID 找不到sqltext文本。

     后面用户反馈他们运维人员在故障时间点之前执行了一个巡检脚本，后面脚本停止后就恢复了，我也看了一下他们的巡检脚本，实际上并不复杂，也就100来个SQL查询而已，其中有个查询shared pool碎片的脚本，这引起了我的注意。我们都知道实际上查询x$ksmsp 风险是比较高的。我在我们交付体系内部问了下，至少之前有3个客户在Oracle 11.2.0.3/11.2.0.4 版本中通过查询x$ksmsp时候，导致出现大量shared pool latch，进而导致实例hung的情况。这里我show一个类似的脚本：

    SET lines 120
    col sga_heap FOR a30
    SELECT KSMCHIDX "SubPool",
    'sga heap(' || KSMCHIDX || ',0)' sga_heap,
    ksmchcom ChunkComment,
    decode(round(ksmchsiz 1000), 0, '0-1K', 1, '1-2K', 2, '2-3K', 3, '3-4K', 4, '4-5K', 5, '5-6k', 6, '6-7k', 7, '7-8k', 8, '8-9k', 9, '9-10k', '10K') "size",
    count(*),
    ksmchcls Status,
    sum(ksmchsiz) Bytes
    FROM x$ksmsp
    WHERE KSMCHCOM = 'free memory'
    GROUP BY ksmchidx,
    ksmchcls,
    'sga heap(' || KSMCHIDX || ',0)',
    ksmchcom,
    ksmchcls,
    decode(round(ksmchsiz 1000), 0, '0-1K', 1, '1-2K', 2, '2-3K', 3, '3-4K', 4, '4-5K', 5, '5-6k', 6, '6-7k', 7, '7-8k', 8, '8-9k', 9, '9-10k', '10K')
    ORDER BY "size";