暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 数据库大量enq：US-contention等待事件分析报告

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

IT那活儿 2021-07-02
1787
一. 问题描述

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 2000col TABLE_OWNER for a20col TABLE_NAME for a30col INDEX_NAME for a50col COLUMN_NAME for a30select 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 1MESORDERUSER MES_CLAUSE_INFO PK_MES_CLAUSE_INFO12_NEW ID 1set lines 180 pages 500col index_name for a40col index_owner for a40select 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_NO12set linesize 300col owner for a15col DEGREE for a20col table_name for a30col index_name for a40col tablespace_name for a20col partitioned for a30select 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 1MESORDERUSER MES_CLAUSE_INFO PK_MES_CLAUSE_INFO12_NEW VALID NO UNIQUE TBS_MES_DATA 1MESORDERUSER MES_CLAUSE_INFO SYS_IL0000075373C00021$$ N/A YES UNIQUE 0set pages 10000 linesize 3000col owner for a20col tablespace_name for a20col segment_type for a20col segment_name for a30col PARTITION_NAME for a30select * from (select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes)  1024  1024 size_mfrom dba_segments where segment_name in (upper('MES_CLAUSE_INFO'))group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAMEorder 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 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202201 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202112 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202111 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202110 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202109 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202108 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202107 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202106 19089MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202105 35355MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202104 37922MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202103 44079MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202102 28176MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202101 43885MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202012 46067MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202011 38519MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202010 33426MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202009 25981MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202008 57592MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202007 14080MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202006 353121 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 3000col owner for a20col tablespace_name for a20col segment_type for a20col segment_name for a30col PARTITION_NAME for a30select * from (select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes)  1024  1024 size_mfrom dba_segments where segment_name in (upper('MES_CLAUSE_INFO'))group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAMEorder 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 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202201 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202112 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202111 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202110 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202109 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202108 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202107 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202106 19025MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202105 35355MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202104 37922MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_CLAUSE_INFO P_MES_CLAUSE_INFO_202103 44079set linesize 300col owner for a15col DEGREE for a20col table_name for a30col index_name for a40col tablespace_name for a20col partitioned for a30select 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 2000col TABLE_OWNER for a20col TABLE_NAME for a30col INDEX_NAME for a50col COLUMN_NAME for a30select 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 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PLC_APPLICANT12 PLC_APPLICANT 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_JQ12 PROPOSAL_NO_JQ 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_SY12 PROPOSAL_NO_SY 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_SPP12 PROPOSAL_NO_JQ 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_SPP12 PROPOSAL_NO_SY 2MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_UPDATE_DATE12 UPDATE_DATE 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CAR_CUSTOMER12 CAR_CUSTOMER 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CREATE_DATE12 CREATE_DATE 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__FRAME_NO12 FRAME_NO 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__LICENSE_PLATE_NO12 LICENSE_PLATE_NO 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OS12 ORDER_NO 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OSU12 ORDER_STATUS 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OSU12 USER_CODE 2MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__US12 USER_CODE 1MESORDERUSER MES_ORDER_INFOS IDX_USER_CODE_UPDATE_0919 USER_CODE 1MESORDERUSER MES_ORDER_INFOS IDX_USER_CODE_UPDATE_0919 UPDATE_DATE 2MESORDERUSER MES_ORDER_INFOS PK_M_O_I_NEW12 ID 118 rows selected.set lines 180 pages 500col index_name for a40col index_owner for a40select 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_POHONEset linesize 300col owner for a15col DEGREE for a20col table_name for a30col index_name for a40col tablespace_name for a20col partitioned for a30select 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 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PLC_APPLICANT12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_JQ12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_PROPOSAL_NO_SY12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_SPP12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW_UPDATE_DATE12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CAR_CUSTOMER12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__CREATE_DATE12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__FRAME_NO12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__LICENSE_PLATE_NO12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OS12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__OSU12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_M_O_I_NEW__US12 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS IDX_USER_CODE_UPDATE_0919 VALID NO NONUNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS PK_M_O_I_NEW12 VALID NO UNIQUE TBS_MES_DATA 1MESORDERUSER MES_ORDER_INFOS SYS_IL0000077528C00092$$ N/A YES UNIQUE 0MESORDERUSER MES_ORDER_INFOS SYS_IL0000077528C00105$$ N/A YES UNIQUE 017 rows selected.set pages 10000 linesize 3000col owner for a20col tablespace_name for a20col segment_type for a20col segment_name for a30col PARTITION_NAME for a30select * from (select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes)  1024  1024 size_mfrom dba_segments where segment_name in (upper('MES_ORDER_INFOS'))group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAMEorder 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 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_2022201 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202112 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202111 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202110 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202109 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202108 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202107 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202106 4472MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202105 8168MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202104 8226MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202103 9439MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202102 6144MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202101 8925MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202012 9088MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202011 7328MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202010 6118MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202009 3392MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202008 819 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 3000col owner for a20col tablespace_name for a20col segment_type for a20col segment_name for a30col PARTITION_NAME for a30select * from (select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 size_mfrom dba_segments where segment_name in (upper('MES_ORDER_INFOS'))group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAMEorder 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 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_2022201 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202112 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202111 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202110 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202109 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202108 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202107 .0625MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202106 4472MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202105 8168MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202104 8226MESORDERUSER TBS_MES_DATA TABLE PARTITION MES_ORDER_INFOS P_MES_ORDER_INFOS_202103 9439set linesize 300col owner for a15col DEGREE for a20col table_name for a30col index_name for a40col tablespace_name for a20col partitioned for a30select 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等待事件。

  1. 特定增加了自动维护全局索引的job，即SYS.PMO_DEFERRED_GIDX_MAINT_JOB，默认是每天的凌晨两点钟钟维护所有的全局索引。

  2. 同时在dba_indexes和dba_ind_partitions视图增加了ORPHANED_ENTRIES这一列。没有清理索引条目的全局索引会被标记为孤儿状态。

  3. 对孤儿条目的清理，有三种方法：    

a.dbms_part.cleanup_gidx包  
b.alter index INDEX_NAME rebuild;    
c.alter index INDEX_NAME coalesce cleanup;

END


更多精彩干货分享

点击下方名片关注

IT那活儿

数据库
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论