国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

數(shù)據(jù)庫大量enq：US-contention等待事件分析報告

IT那活兒 / 507人閱讀
數(shù)據(jù)庫大量enq：US-contention等待事件分析報告
一. 問題描述

ora event 有大量1091  enq: US – contention等待事件，并且kill之后重復(fù)出現(xiàn)。


二. 問題分析

ora plan 86bbdpkp00cvn看到有索引維護操作：

索引維護job與表的dml產(chǎn)生資源爭用，導(dǎo)致大量1091  enq: US – contention等待事件

6月17日對表MESORDERUSER. TBS_MES_DATA做了刪分區(qū)的變更，操作文檔：

▼▼▼
################################################################刪除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.


----刪除分區(qū)（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 ;


----刪除完檢查，應(yīng)該為
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.


----刪除分區(qū)（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 ; 


----刪除完檢查，應(yīng)該為
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分區(qū)或者truncate分區(qū)的時候，會使得全局索引不可用，update indexes和update global indexes雖然可以維護索引的可用性，但是索引的維護是當(dāng)時立刻發(fā)生的，業(yè)務(wù)高峰時刻會爭奪性能。全局索引的異步維護就可以解決這個矛盾點。配合update索引的語句，表中的數(shù)據(jù)會當(dāng)時就刪除，但是需要被清理的索引條目是不會釋放的，這樣可以實現(xiàn)既保證全局索引可用性，又延后對索引的維護，錯開高峰時間，避免了高峰時間性能爭用問題。并且后續(xù)維護也是oracle自動進行的，不需要dba手動干預(yù)，當(dāng)然手動干預(yù)也是可行的。

索引類型：

索引維護的job及語句：

關(guān)閉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 ;

索引重建完成后等待事件恢復(fù)正常：


三. 總 結(jié)

12C新特性drop分區(qū)之后索引維護job與表的dml產(chǎn)生資源爭用，導(dǎo)致大量1091  enq: US – contention等待事件。

  1. 特定增加了自動維護全局索引的job，即SYS.PMO_DEFERRED_GIDX_MAINT_JOB，默認(rèn)是每天的凌晨兩點鐘鐘維護所有的全局索引。

  2. 同時在dba_indexes和dba_ind_partitions視圖增加了ORPHANED_ENTRIES這一列。沒有清理索引條目的全局索引會被標(biāo)記為孤兒狀態(tài)。

  3. 對孤兒條目的清理，有三種方法：    

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


END


更多精彩干貨分享

點擊下方名片關(guān)注

IT那活兒

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/129871.html

相關(guān)文章

  • CountDownLatch的await和countDown方法簡單分析

    摘要：如果一個調(diào)用已經(jīng)出現(xiàn)了，這里只計數(shù)。為表示永不過期當(dāng)為時，是相對于新紀(jì)元之后的毫秒。否則這個值就是超時前的納秒數(shù)。要解除阻塞的線程 await 調(diào)用sync.acquireSharedInterruptibly public void await() throws InterruptedException { sync.acquireSharedInterruptibly(1)...

    fou7 評論0 收藏0

  • 逐行分析AQS源碼(1)——獨占鎖的獲取

    摘要：本篇我們將以的公平鎖為例來詳細(xì)看看使用獲取獨占鎖的流程。本文中的源碼基于。由于本篇我們分析的是獨占鎖，同一時刻，鎖只能被一個線程所持有。由于在整個搶鎖過程中，我們都是不響應(yīng)中斷的。 前言 AQS（AbstractQueuedSynchronizer）是JAVA中眾多鎖以及并發(fā)工具的基礎(chǔ)，其底層采用樂觀鎖，大量使用了CAS操作， 并且在沖突時，采用自旋方式重試，以實現(xiàn)輕量級和高效地獲取鎖...

    call_me_R 評論0 收藏0

  • 深入分析AQS實現(xiàn)原理

    摘要：更新成功返回，否則返回這個操作是原子的，不會出現(xiàn)線程安全問題，這里面涉及到這個類的操作，一級涉及到這個屬性的意義。 簡單解釋一下J.U.C，是JDK中提供的并發(fā)工具包,java.util.concurrent。里面提供了很多并發(fā)編程中很常用的實用工具類，比如atomic原子操作、比如lock同步鎖、fork/join等。 從Lock作為切入點 我想以lock作為切入點來講解AQS，畢竟...

    sewerganger 評論0 收藏0

  • J.U.C|AQS獨占式源碼分析

    摘要：本章我們主要聊獨占式即同一時刻只能有一個線程獲取同步狀態(tài)，其它獲取同步狀態(tài)失敗的線程則會加入到同步隊列中進行等待。到這獨占式獲取同步和釋放同步狀態(tài)的源碼已經(jīng)分析完了。 一、寫在前面 上篇文章通過ReentrantLock 的加鎖和釋放鎖過程給大家聊了聊AQS架構(gòu)以及實現(xiàn)原理，具體參見《J.U.C|AQS的原理》。 理解了原理，我們在來看看再來一步一步的聊聊其源碼是如何實現(xiàn)的。 本章給...

    why_rookie 評論0 收藏0

  • AbstractQueuedSynchronizer理解之一（ReentrantLock）

    摘要：有了這個基礎(chǔ)，才能發(fā)揮作用，使得在節(jié)點取消和異常時能夠保證隊列在多線程下的完整性。 Doug Lea是JDK中concurrent工具包的作者，這位大神是誰可以自行g(shù)oogle。 本文淺析ReentrantLock（可重入鎖）的原理 Lock接口 showImg(https://segmentfault.com/img/bV2671?w=276&h=176); Lock接口定義了這幾個...

    learning 評論0 收藏0

  • AbstractQueuedSynchronizer理解之一（ReentrantLock）

    摘要：有了這個基礎(chǔ)，才能發(fā)揮作用，使得在節(jié)點取消和異常時能夠保證隊列在多線程下的完整性。 Doug Lea是JDK中concurrent工具包的作者，這位大神是誰可以自行g(shù)oogle。 本文淺析ReentrantLock（可重入鎖）的原理 Lock接口 showImg(https://segmentfault.com/img/bV2671?w=276&h=176); Lock接口定義了這幾個...

    bigdevil_s 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<