UPDATE T_user_table 僵死

最新推荐文章于 2022-05-19 16:21:18 发布

1ihj2015

最新推荐文章于 2022-05-19 16:21:18 发布

阅读量575

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle Troubleshooting

本文链接：https://blog.youkuaiyun.com/itdba/article/details/37694877

Oracle Troubleshooting 专栏收录该内容

37 篇文章

订阅专栏

本文详细分析了使用SQL查询获取等待信息的过程，并通过跟踪文件和查询结果定位到了问题原因在于T_SM_DOWNQUEUEHIS表的索引异常。通过检查索引状态确认了问题所在，并最终通过开发人员的介入解决了该问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---获取等待信息
SQL> select session_id, event, sql_id, blocking_session
2    from dba_hist_active_sess_history
3   where sample_time > to_date('20140703 080000', 'yyyymmdd hh24miss')
4     and sample_time < to_date('20140703 093000', 'yyyymmdd hh24miss')
5     and sql_id in ('c3vpdk7dvmhpd') --sql id 重awr报告中获取
6     group by session_id, event, sql_id, blocking_session;

SESSION_ID EVENT SQL_ID BLOCKING_SESSION
---------- ---------------------------------------------------------------- ------------- ----------------
5434 enq: TX - row lock contention c3vpdk7dvmhpd 4826

--获取4026 等待信息
SQL> select session_id, event, sql_id, blocking_session
2    from dba_hist_active_sess_history
3   where sample_time > to_date('20140703 080000', 'yyyymmdd hh24miss')
4     and sample_time < to_date('20140703 093000', 'yyyymmdd hh24miss')
5     and session_id = 4826
6   group by session_id, event, sql_id, blocking_session
7 ;

SESSION_ID EVENT                                                            SQL_ID        BLOCKING_SESSION
---------- ---------------------------------------------------------------- ------------- ----------------
      4826 gc current grant buzy                                           7tfuyk4myy4z9
      4826 gc current grant buzy                                           6z2gm4ujyh9tv
--通过跟踪文件发现------------------------------------------------------------
    O/S info: user: oracle, term: UNKNOWN, ospid: 20204, machine: db2
              program: oracle@db2 (J000)

    last wait for 'gc current request' wait_time=0.000180 sec, seconds since wait started=9
                file#=1b, block#=c084b, id#=2010001
                blocking sess=0x(nil) seq=40172
    Dumping Session Wait History
     for 'gc current grant busy' count=1 wait_time=0.000180 sec
                =1b, =c084b, =2010001
     for 'gc current grant busy' count=1 wait_time=0.000239 sec

--我猜测是两个节点共同访问该表造成 gc current grant buzy 等待，
file#=1b, block#=c084b, id#=2010001 跟踪文件的数据库块信息：转化10进制：file#=27, block#=788555, id#=2010001 27
select segment_name from dba_extents where file_id=27 and 788555 between block_id and block_id+blocks-1
SQL> select segment_name from dba_extents where file_id=27 and 788555 between block_id and block_id+blocks-1;
SEGMENT_NAME

---------------------------------------------------------------------------------
T_SM_DOWNQUEUEHIS
SQL>
select * from gv$access where object='T_SM_DOWNQUEUEHIS'
-通过查询结果是两个实例访问，另外一个实例是客户端 plsql deverloper访问，退出该客户端，问题依然存在
SQL> select * from dba_hist_sqltext where sql_id in ('7tfuyk4myy4z9','6z2gm4ujyh9tv');

      DBID SQL_ID        SQL_TEXT                                                                         COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
2626348494 6z2gm4ujyh9tv UPDATE SMAPP.T_SM_DOWNQUEUEHIS A SET A.DOWNSTATUS=:B4 ,A.RPT_MATCHTIME=SYSDATE,            6
2626348494 7tfuyk4myy4z9 SELECT MAX(A.DOWNSTATUS)FROM SMAPP.T_SM_DOWNQUEUEHIS A WHERE A.SERIALNO=:B3 AND            3

--该语句执行缓慢
--且当时定时JOB被唤起
--吃饭去了
--后来经过开发人员确认过程中T_SM_DOWNQUEUEHIS 该表的索引异常
由于：

---------- ------------- -------------------------------------------------------------------------------- ------------
2626348494 6z2gm4ujyh9tv UPDATE SMAPP.T_SM_DOWNQUEUEHIS A SET A.DOWNSTATUS=:B4 ,A.RPT_MATCHTIME=SYSDATE, 6
2626348494 7tfuyk4myy4z9 SELECT MAX(A.DOWNSTATUS)FROM SMAPP.T_SM_DOWNQUEUEHIS A WHERE A.SERIALNO=:B3 AND 3

这两条语句加开发人员指出的过程中有问题的地方，可以确定问题的原因是：T_SM_DOWNQUEUEHIS 该表的索引有问题

select index_name, partition_name, status
from dba_ind_partitions
where index_name in
       (select index_name
          from dba_part_indexes
         where table_name = 'T_SM_DOWNQUEUEHIS') and status='UNUSABLE'