ora event 有大量1091 enq: US – contention等待事件,并且kill之后重复出现。
索引维护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;
异步全局索引维护。之前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 ;
索引重建完成后等待事件恢复正常:
特定增加了自动维护全局索引的job,即SYS.PMO_DEFERRED_GIDX_MAINT_JOB,默认是每天的凌晨两点钟钟维护所有的全局索引。
同时在dba_indexes和dba_ind_partitions视图增加了ORPHANED_ENTRIES这一列。没有清理索引条目的全局索引会被标记为孤儿状态。
对孤儿条目的清理,有三种方法:
更多精彩干货分享
点击下方名片关注
IT那活儿
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129871.html
摘要:如果一个调用已经出现了,这里只计数。为表示永不过期当为时,是相对于新纪元之后的毫秒。否则这个值就是超时前的纳秒数。要解除阻塞的线程 await 调用sync.acquireSharedInterruptibly public void await() throws InterruptedException { sync.acquireSharedInterruptibly(1)...
摘要:本篇我们将以的公平锁为例来详细看看使用获取独占锁的流程。本文中的源码基于。由于本篇我们分析的是独占锁,同一时刻,锁只能被一个线程所持有。由于在整个抢锁过程中,我们都是不响应中断的。 前言 AQS(AbstractQueuedSynchronizer)是JAVA中众多锁以及并发工具的基础,其底层采用乐观锁,大量使用了CAS操作, 并且在冲突时,采用自旋方式重试,以实现轻量级和高效地获取锁...
摘要:更新成功返回,否则返回这个操作是原子的,不会出现线程安全问题,这里面涉及到这个类的操作,一级涉及到这个属性的意义。 简单解释一下J.U.C,是JDK中提供的并发工具包,java.util.concurrent。里面提供了很多并发编程中很常用的实用工具类,比如atomic原子操作、比如lock同步锁、fork/join等。 从Lock作为切入点 我想以lock作为切入点来讲解AQS,毕竟...
摘要:本章我们主要聊独占式即同一时刻只能有一个线程获取同步状态,其它获取同步状态失败的线程则会加入到同步队列中进行等待。到这独占式获取同步和释放同步状态的源码已经分析完了。 一、写在前面 上篇文章通过ReentrantLock 的加锁和释放锁过程给大家聊了聊AQS架构以及实现原理,具体参见《J.U.C|AQS的原理》。 理解了原理,我们在来看看再来一步一步的聊聊其源码是如何实现的。 本章给...
摘要:有了这个基础,才能发挥作用,使得在节点取消和异常时能够保证队列在多线程下的完整性。 Doug Lea是JDK中concurrent工具包的作者,这位大神是谁可以自行google。 本文浅析ReentrantLock(可重入锁)的原理 Lock接口 showImg(https://segmentfault.com/img/bV2671?w=276&h=176); Lock接口定义了这几个...
摘要:有了这个基础,才能发挥作用,使得在节点取消和异常时能够保证队列在多线程下的完整性。 Doug Lea是JDK中concurrent工具包的作者,这位大神是谁可以自行google。 本文浅析ReentrantLock(可重入锁)的原理 Lock接口 showImg(https://segmentfault.com/img/bV2671?w=276&h=176); Lock接口定义了这几个...
阅读 1346·2023-01-11 13:20
阅读 1684·2023-01-11 13:20
阅读 1132·2023-01-11 13:20
阅读 1858·2023-01-11 13:20
阅读 4100·2023-01-11 13:20
阅读 2704·2023-01-11 13:20
阅读 1385·2023-01-11 13:20
阅读 3597·2023-01-11 13:20