数据库大量enq：US-contention等待事件分析报告

IT那活儿发布于2023-01-11 13:20 / 487人阅读

一. 问题描述

ora event 有大量1091 enq: US – contention等待事件，并且kill之后重复出现。

二. 问题分析

ora plan 86bbdpkp00cvn看到有索引维护操作：

索引维护job与表的dml产生资源争用，导致大量1091 enq: US – contention等待事件

6月17日对表MESORDERUSER. TBS_MES_DATA做了删分区的变更，操作文档：

▼▼▼
################################################################删除MESORDERUSER.MES_CLAUSE_INFO##################################################################################
set line 200 pages 2000
col TABLE_OWNER for a20
col TABLE_NAME for a30
col INDEX_NAME for a50
col COLUMN_NAME for a30
select table_owner,table_name,index_name,column_name,column_position from dba_ind_columns where table_name = upper(MES_CLAUSE_INFO) and table_owner=MESORDERUSER order by 3,5;

TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- ------------------------------ -------------------------------------------------- ------------------------------ ---------------
MESORDERUSER MES_CLAUSE_INFO IDX_MES_CLAUSE_INFO__ORDER_NO12 ORDER_NO 1
MESORDERUSER MES_CLAUSE_INFO PK_MES_CLAUSE_INFO12_NEW ID 1



set lines 180 pages 500
col index_name for a40
col index_owner for a40
select distinct index_owner,index_name from dba_ind_partitions where index_owner =MESORDERUSER and index_name in (IDX_MES_CLAUSE_INFO__ORDER_NO12,PK_MES_CLAUSE_INFO12_NEW);


INDEX_OWNER INDEX_NAME
---------------------------------------- ----------------------------------------
MESORDERUSER IDX_MES_CLAUSE_INFO__ORDER_NO12


set linesize 300
col owner for a15
col DEGREE for a20
col table_name for a30
col index_name for a40
col tablespace_name for a20
col partitioned for a30
select owner,table_name,index_name,status,partitioned,uniqueness,tablespace_name,DEGREE from dba_indexes where owner=MESORDERUSER and table_name=MES_CLAUSE_INFO order by 3;

OWNER TABLE_NAME INDEX_NAME STATUS PARTITIONED UNIQUENES TABLESPACE_NAME DEGREE
--------------- ------------------------------ ---------------------------------------- -------- ------------------------------ --------- -------------------- --------------------
MESORDERUSER MES_CLAUSE_INFO IDX_MES_CLAUSE_INFO__ORDER_NO12 N/A YES NONUNIQUE 1
MESORDERUSER MES_CLAUSE_INFO PK_MES_CLAUSE_INFO12_NEW VALID NO UNIQUE TBS_MES_DATA 1
MESORDERUSER MES_CLAUSE_INFO SYS_IL0000075373C00021$$ N/A YES UNIQUE 0


set pages 10000 linesize 3000
col owner for a20
col tablespace_name for a20
col segment_type for a20
col segment_name for a30
col PARTITION_NAME for a30
select * from (
select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_m
from dba_segments 
where segment_name in (upper(MES_CLAUSE_INFO))
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 5 desc) ; 

OWNER TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME SIZE_M
-------------------- -------------------- -------------------- ------------------------------ ------------------------------ ----------
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_MAX .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202201 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202112 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202111 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202110 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202109 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202108 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202107 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202106 19089
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202105 35355
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202104 37922
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202103 44079
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202102 28176
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202101 43885
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202012 46067
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202011 38519
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202010 33426
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202009 25981
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202008 57592
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202007 14080
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202006 3531

21 rows selected.


----删除分区（3月份之前的，不包括3月份）
set timing on 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202006 UPDATE GLOBAL INDEXES ; 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202007 UPDATE GLOBAL INDEXES ; 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202008 UPDATE GLOBAL INDEXES ;
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202009 UPDATE GLOBAL INDEXES ; 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202010 UPDATE GLOBAL INDEXES ; 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202011 UPDATE GLOBAL INDEXES ;
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202012 UPDATE GLOBAL INDEXES ; 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202101 UPDATE GLOBAL INDEXES ; 
alter table MES_CLAUSE_INFO drop partition P_MES_CLAUSE_INFO_202102 UPDATE GLOBAL INDEXES ;


----删除完检查，应该为
set pages 10000 linesize 3000
col owner for a20
col tablespace_name for a20
col segment_type for a20
col segment_name for a30
col PARTITION_NAME for a30
select * from (
select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_m
from dba_segments 
where segment_name in (upper(MES_CLAUSE_INFO))
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 5 desc) ; 

OWNER TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME SIZE_M
-------------------- -------------------- -------------------- ------------------------------ ------------------------------ ----------
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_MAX .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202201 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202112 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202111 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202110 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202109 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202108 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202107 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202106 19025
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202105 35355
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202104 37922
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202103 44079


set linesize 300
col owner for a15
col DEGREE for a20
col table_name for a30
col index_name for a40
col tablespace_name for a20
col partitioned for a30
select owner,table_name,index_name,status,partitioned,uniqueness,tablespace_name,DEGREE from dba_indexes where owner=MESORDERUSER and table_name=MES_CLAUSE_INFO order by 3;


####################################################删除MESORDERUSER.MES_ORDER_INFOS##########################################################################################################

set line 200 pages 2000
col TABLE_OWNER for a20
col TABLE_NAME for a30
col INDEX_NAME for a50
col COLUMN_NAME for a30
select table_owner,table_name,index_name,column_name,column_position from dba_ind_columns where table_name = upper(MES_ORDER_INFOS) and table_owner=MESORDERUSER order by 3,5;


TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- ------------------------------ -------------------------------------------------- ------------------------------ ---------------
MESORDERUSER MES_ORDER_INFOS IDX_MOI_CUSTOMER_POHONE CUSTOMER_PHONE 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PLC_APPLICANT12 PLC_APPLICANT 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_JQ12 PROPOSAL_NO_JQ 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_SY12 PROPOSAL_NO_SY 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_SPP12 PROPOSAL_NO_JQ 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_SPP12 PROPOSAL_NO_SY 2
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_UPDATE_DATE12 UPDATE_DATE 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CAR_CUSTOMER12 CAR_CUSTOMER 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CREATE_DATE12 CREATE_DATE 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__FRAME_NO12 FRAME_NO 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__LICENSE_PLATE_NO12 LICENSE_PLATE_NO 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OS12 ORDER_NO 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OSU12 ORDER_STATUS 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OSU12 USER_CODE 2
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__US12 USER_CODE 1
MESORDERUSER MES_ORDER_INFOS IDX_USER_CODE_UPDATE_0919 USER_CODE 1
MESORDERUSER MES_ORDER_INFOS IDX_USER_CODE_UPDATE_0919 UPDATE_DATE 2
MESORDERUSER MES_ORDER_INFOS PK_M_O_I_NEW12 ID 1

18 rows selected.


set lines 180 pages 500
col index_name for a40
col index_owner for a40
select distinct index_owner,index_name from dba_ind_partitions where index_owner =MESORDERUSER and index_name in (IDX_MOI_CUSTOMER_POHONE,IDX_M_O_I_NEW_PLC_APPLICANT12,IDX_M_O_I_NEW_PROPOSAL_NO_JQ12,IDX_M_O_I_NEW_PROPOSAL_NO_SY12,IDX_M_O_I_NEW_SPP12,IDX_M_O_I_NEW_SPP12,IDX_M_O_I_NEW_UPDATE_DATE12,IDX_M_O_I_NEW__CAR_CUSTOMER12,IDX_M_O_I_NEW__CREATE_DATE12,IDX_M_O_I_NEW__FRAME_NO12,IDX_M_O_I_NEW__LICENSE_PLATE_NO12,IDX_M_O_I_NEW__OS12,IDX_M_O_I_NEW__OSU12,IDX_M_O_I_NEW__OSU12,IDX_M_O_I_NEW__US12,IDX_USER_CODE_UPDATE_0919,IDX_USER_CODE_UPDATE_0919,PK_M_O_I_NEW12);


INDEX_OWNER INDEX_NAME
---------------------------------------- ----------------------------------------
MESORDERUSER IDX_MOI_CUSTOMER_POHONE

set linesize 300
col owner for a15
col DEGREE for a20
col table_name for a30
col index_name for a40
col tablespace_name for a20
col partitioned for a30
select owner,table_name,index_name,status,partitioned,uniqueness,tablespace_name,DEGREE from dba_indexes where owner=MESORDERUSER and table_name=MES_ORDER_INFOS order by 3;

OWNER TABLE_NAME INDEX_NAME STATUS PARTITIONED UNIQUENES TABLESPACE_NAME DEGREE
--------------- ------------------------------ ---------------------------------------- -------- ------------------------------ --------- -------------------- --------------------
MESORDERUSER MES_ORDER_INFOS IDX_MOI_CUSTOMER_POHONE N/A YES NONUNIQUE 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PLC_APPLICANT12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_JQ12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_SY12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_SPP12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_UPDATE_DATE12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CAR_CUSTOMER12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CREATE_DATE12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__FRAME_NO12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__LICENSE_PLATE_NO12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OS12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OSU12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__US12 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS IDX_USER_CODE_UPDATE_0919 VALID NO NONUNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS PK_M_O_I_NEW12 VALID NO UNIQUE TBS_MES_DATA 1
MESORDERUSER MES_ORDER_INFOS SYS_IL0000077528C00092$$ N/A YES UNIQUE 0
MESORDERUSER MES_ORDER_INFOS SYS_IL0000077528C00105$$ N/A YES UNIQUE 0

17 rows selected.



set pages 10000 linesize 3000
col owner for a20
col tablespace_name for a20
col segment_type for a20
col segment_name for a30
col PARTITION_NAME for a30
select * from (
select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_m
from dba_segments 
where segment_name in (upper(MES_ORDER_INFOS))
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 5 desc) ; 

OWNER TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME SIZE_M
-------------------- -------------------- -------------------- ------------------------------ ------------------------------ ----------
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_MAX .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_2022201 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202112 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202111 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202110 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202109 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202108 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202107 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202106 4472
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202105 8168
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202104 8226
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202103 9439
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202102 6144
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202101 8925
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202012 9088
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202011 7328
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202010 6118
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202009 3392
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202008 8

19 rows selected.


----删除分区（3月份之前的，不包括3月份）
set timing on 
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202008 UPDATE GLOBAL INDEXES ; 
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202009 UPDATE GLOBAL INDEXES ;
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202010 UPDATE GLOBAL INDEXES ; 
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202011 UPDATE GLOBAL INDEXES ; 
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202012 UPDATE GLOBAL INDEXES ;
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202101 UPDATE GLOBAL INDEXES ; 
alter table MES_ORDER_INFOS drop partition P_MES_CLAUSE_INFO_202102 UPDATE GLOBAL INDEXES ; 


----删除完检查，应该为
set pages 10000 linesize 3000
col owner for a20
col tablespace_name for a20
col segment_type for a20
col segment_name for a30
col PARTITION_NAME for a30
select * from (
select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_m
from dba_segments 
where segment_name in (upper(MES_ORDER_INFOS))
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 5 desc) ; 

OWNER TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME SIZE_M
-------------------- -------------------- -------------------- ------------------------------ ------------------------------ ----------
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_MAX .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_2022201 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202112 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202111 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202110 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202109 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202108 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202107 .0625
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202106 4472
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202105 8168
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202104 8226
MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202103 9439



set linesize 300
col owner for a15
col DEGREE for a20
col table_name for a30
col index_name for a40
col tablespace_name for a20
col partitioned for a30
select owner,table_name,index_name,status,partitioned,uniqueness,tablespace_name,DEGREE from dba_indexes where owner=MESORDERUSER and table_name=MES_ORDER_INFOS order by 3;

oracle12c的新特性：

异步全局索引维护。之前drop分区或者truncate分区的时候，会使得全局索引不可用，update indexes和update global indexes虽然可以维护索引的可用性，但是索引的维护是当时立刻发生的，业务高峰时刻会争夺性能。全局索引的异步维护就可以解决这个矛盾点。配合update索引的语句，表中的数据会当时就删除，但是需要被清理的索引条目是不会释放的，这样可以实现既保证全局索引可用性，又延后对索引的维护，错开高峰时间，避免了高峰时间性能争用问题。并且后续维护也是oracle自动进行的，不需要dba手动干预，当然手动干预也是可行的。

索引类型：

索引维护的job及语句：

关闭job：

BEGIN dbms_scheduler.stop_job(job_name => PMO_DEFERRED_GIDX_MAINT_JOB);END;

重建索引：

ALTER INDEX "MESORDERUSER"."PK_MES_CLAUSE_INFO12_NEW" rebuild parallel 16 ;

索引重建完成后等待事件恢复正常：

三. 总结

12C新特性drop分区之后索引维护job与表的dml产生资源争用，导致大量1091 enq: US – contention等待事件。

特定增加了自动维护全局索引的job，即SYS.PMO_DEFERRED_GIDX_MAINT_JOB，默认是每天的凌晨两点钟钟维护所有的全局索引。
同时在dba_indexes和dba_ind_partitions视图增加了ORPHANED_ENTRIES这一列。没有清理索引条目的全局索引会被标记为孤儿状态。
对孤儿条目的清理，有三种方法：

a.dbms_part.cleanup_gidx包

b.alter index INDEX_NAME rebuild;

c.alter index INDEX_NAME coalesce cleanup;

END

更多精彩干货分享

点击下方名片关注

IT那活儿

云服务器 GPU云服务器等待事件大量数据分析大量数据大数据大量数据

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129871.html

CountDownLatch的await和countDown方法简单分析

摘要：如果一个调用已经出现了，这里只计数。为表示永不过期当为时，是相对于新纪元之后的毫秒。否则这个值就是超时前的纳秒数。要解除阻塞的线程 await 调用sync.acquireSharedInterruptibly public void await() throws InterruptedException { sync.acquireSharedInterruptibly(1)...

fou7 2019-08-16 15:12 评论0 收藏0
逐行分析AQS源码(1)——独占锁的获取

摘要：本篇我们将以的公平锁为例来详细看看使用获取独占锁的流程。本文中的源码基于。由于本篇我们分析的是独占锁，同一时刻，锁只能被一个线程所持有。由于在整个抢锁过程中，我们都是不响应中断的。前言 AQS（AbstractQueuedSynchronizer）是JAVA中众多锁以及并发工具的基础，其底层采用乐观锁，大量使用了CAS操作，并且在冲突时，采用自旋方式重试，以实现轻量级和高效地获取锁...

call_me_R 2019-08-19 10:47 评论0 收藏0
深入分析AQS实现原理

摘要：更新成功返回，否则返回这个操作是原子的，不会出现线程安全问题，这里面涉及到这个类的操作，一级涉及到这个属性的意义。简单解释一下J.U.C，是JDK中提供的并发工具包,java.util.concurrent。里面提供了很多并发编程中很常用的实用工具类，比如atomic原子操作、比如lock同步锁、fork/join等。从Lock作为切入点我想以lock作为切入点来讲解AQS，毕竟...

sewerganger 2019-08-16 13:59 评论0 收藏0
J.U.C|AQS独占式源码分析

摘要：本章我们主要聊独占式即同一时刻只能有一个线程获取同步状态，其它获取同步状态失败的线程则会加入到同步队列中进行等待。到这独占式获取同步和释放同步状态的源码已经分析完了。一、写在前面上篇文章通过ReentrantLock 的加锁和释放锁过程给大家聊了聊AQS架构以及实现原理，具体参见《J.U.C|AQS的原理》。理解了原理，我们在来看看再来一步一步的聊聊其源码是如何实现的。本章给...

why_rookie 2019-08-16 16:54 评论0 收藏0
AbstractQueuedSynchronizer理解之一（ReentrantLock）

摘要：有了这个基础，才能发挥作用，使得在节点取消和异常时能够保证队列在多线程下的完整性。 Doug Lea是JDK中concurrent工具包的作者，这位大神是谁可以自行google。本文浅析ReentrantLock（可重入锁）的原理 Lock接口 showImg(https://segmentfault.com/img/bV2671?w=276&h=176); Lock接口定义了这几个...

learning 2019-08-16 11:01 评论0 收藏0
AbstractQueuedSynchronizer理解之一（ReentrantLock）

摘要：有了这个基础，才能发挥作用，使得在节点取消和异常时能够保证队列在多线程下的完整性。 Doug Lea是JDK中concurrent工具包的作者，这位大神是谁可以自行google。本文浅析ReentrantLock（可重入锁）的原理 Lock接口 showImg(https://segmentfault.com/img/bV2671?w=276&h=176); Lock接口定义了这几个...

bigdevil_s 2019-08-19 10:40 评论0 收藏0