【真实经验分享】Oradebug抓取阻塞源SQL语句

客户反馈最近系统经常锁表想要找到导致锁表的语句但是数据库中查不到阻塞会话的当前SQL根本原因在于锁由事务Transaction持有而非SQL语句持有加锁的SQL通常早已执行结束而事务却因未提交或回滚而持续打开导致会话处于空闲状态因此动态性能视图中无法捕获到正在执行的SQL。最后通过oradebug工具来查找可能导致阻塞的SQL。oradebug是Oracle数据库专供高级管理员使用的内部诊断“手术刀”核心价值在于通过生成各类转储如系统状态和Hang分析来精准定位数据库挂起、崩溃或慢SQL的根源并能探查甚至修改内存结构但它属于高风险且无官方文档详细承诺的调试工具必须持有SYSDBA权限强烈建议仅在Oracle技术支持指导下使用否则操作不当极易导致实例宕机。1、查找阻塞会话信息SELECTblocking_sessionASblocker_sid,sidASblocked_sid,sql_id,eventASwaiting_eventFROMv$sessionWHEREblocking_sessionISNOTNULL;BLOCKER_SID BLOCKED_SID SQL_ID WAITING_EVENT----------- ----------- --------------------------------------- ------------------------------------------------------------7769bc8kzrzxdcvkh enq: TX-rowlockcontention2、通过SID查询ORADEBUG需要的参数– 将 77 替换为阻塞源会话的 SIDSELECTs.sid,s.serial#,s.status,p.pidASorapid,-- 这是 ORADEBUG SETORAPID 需要的参数p.spidASospidFROMv$sessions,v$process pWHEREs.paddrp.addrANDs.sid77;SIDSERIAL# STATUS ORAPID OSPID---------- ---------- ------------- ---------- -----------7733INACTIVE53483923、对阻塞源会话和被阻塞会话分别生成trc文件ORADEBUG SETORAPIDSID-- SID替换为上一步查询出来的ORAPIDSYSorcl1ORADEBUG SETORAPID53-- 53Oracle pid:53,Unix process pid:48392,image: oraclerac1SYSorcl1ORADEBUGDUMPSYSTEMSTATE10Statement processed.SYSorcl1SYSorcl1ORADEBUG TRACEFILE_NAME;/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_48392.trc在生成的trc文件中搜索被锁的表名主要找DML语句。