【索引】分割槽表索引重建過程的10704事件跟蹤

楊奇龍發表於2011-02-25
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

--生成測試表.

create table yang_part (

             id number,

             create_time date,

             value varchar2(20),

             name varchar2(30)

)

partition by range (create_time) (

             partition p2010 values less than (to_date('20110101','yyyymmdd')),

             partition p201101 values less than (to_date('20110401','yyyymmdd')),

             partition p201102 values less than (to_date('20110701','yyyymmdd')),

             partition p201103 values less than (to_date('20111001','yyyymmdd')),

             partition p201104 values less than (to_date('20120101','yyyymmdd'))

);

/*create index id_ind_yang  on yang_part(id) global partition by range(create_time)

partition by range (create_time) (

             partition p2010 values less than (to_date('20110101','yyyymmdd')),

             partition p201101 values less than (to_date('20110401','yyyymmdd')),

             partition p201102 values less than (to_date('20110701','yyyymmdd')),

             partition p201103 values less than (to_date('20111001','yyyymmdd')),

             partition p201104 values less than (to_date('20120101','yyyymmdd'))

);*/

 

--生成測試用資料.

--使用隨機數生成200w的資料, 時間分佈在從今天往過去的500天內.字串資料的內容完全為隨機數

insert into yang_part

select rownum id,sysdate - dbms_random.value(1,500) create_time,

           dbms_random.string('l',15) value,

           dbms_random.string('l',20) name

         from dual

         connect by level <= 2e7;

commit;

--建立全域性索引。

create index id_ind_yang  on yang_part(id);        

--開啟enqueue locktrace跟蹤.

alter session set events '10704 trace name context forever,level 12'; 

--執行truncate partition xx update global indexes語句.

--截斷分割槽表p201102

alter table yang_part truncate partition p201102 update global indexes;

--刪除分割槽表p201103

alter table yang_part drop partition p201103 update global indexes;

--交換分割槽表

alter table yang_part exchange partition p201104 with table exch_with_p201104 update  global indexes;

----關閉

alter session set events '10704 trace name context off';

--查詢跟蹤檔案。

select value from v$diag_info where name='Default Trace File';

---

sys@rac1>select object_name,subobject_name,object_id,data_object_id from dba_objects where object_name='YANG_PART';

 

OBJECT_NAME   SUBOBJECT_NAME   OBJECT_ID  DATA_OBJECT_ID 十六進位制

------------- ---------------- ---------- ----------     ----------

YANG_PART                       129028                    1F804

YANG_PART     P201104           129033         129033     1F809

YANG_PART     P201103           129032         129032     1F808

YANG_PART     P201102           129031         129031     1F807

YANG_PART     P201101           129030         129030     1F806

YANG_PART     P2010             129029         129029     1F805

6 rows selected.

Elapsed: 00:00:00.01

================================跟蹤檔案分析=======================

oracle@rac1:rac1 />more /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_9020.trc | grep TM |more

ksqgtl *** TM-00010187-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,10187,0 mode=3 timeout=21474836

ksqrcl: TM,10187,0

ksqcmi: TM,10187,0 mode=0 timeout=0

---有上面的查詢和轉換可知 1F804對應於基表 yang_part

---在刪除資料時,首先會請求基表的share lock  SX(Row-X)行級排它鎖

ksqgtl *** TM-0001f804-00000000 mode=3 flags=0x401 timeout=0 ***

ksqcmi: TM,1f804,0 mode=3 timeout=0

---129031=>> 1F807   truncate分割槽P201102,此時對P201102表分割槽加排他鎖。

select object_name,subobject_name,object_id,data_object_id from dba_objects where object_id=129031

OBJECT_NAME   SUBOBJECT_NAME   OBJECT_ID  DATA_OBJECT_ID 十六進位制

------------- ---------------- ---------- ----------     ----------

YANG_PART     P201102           129031         129031     1F807

ksqgtl *** TM-0001f807-00000000 mode=6 flags=0x401 timeout=0 ***

ksqcmi: TM,1f807,0 mode=6 timeout=0

ksqgtl *** TM-0000027b-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,27b,0 mode=3 timeout=21474836

ksqgtl *** TM-000001ca-00000000 mode=6 flags=0x401 timeout=0 ***

ksqcmi: TM,1ca,0 mode=6 timeout=0

ksqrcl: TM,1ca,0

ksqcmi: TM,1ca,0 mode=0 timeout=0

ksqgtl *** TM-00000047-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,47,0 mode=3 timeout=21474836

ksqgtl *** TM-000001ad-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1ad,0 mode=3 timeout=21474836

ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,4,0 mode=3 timeout=21474836

ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,12,0 mode=3 timeout=21474836

ksqrcl: TM,12,0 select object_name ,object_type from dba_objects where object_id in (18,4,71,458,635);

ksqcmi: TM,12,0 mode=0 timeout=0

ksqrcl: TM,4,0

ksqcmi: TM,4,0 mode=0 timeout=0

ksqrcl: TM,1ad,0

ksqcmi: TM,1ad,0 mode=0 timeout=0

ksqrcl: TM,47,0

ksqcmi: TM,47,0 mode=0 timeout=0

ksqrcl: TM,27b,0

ksqcmi: TM,27b,0 mode=0 timeout=0

ksqrcl: TM,1f807,0

ksqcmi: TM,1f807,0 mode=0 timeout=0

ksqrcl: TM,1f804,0

ksqcmi: TM,1f804,0 mode=0 timeout=0

===================================================

一下是drop 分割槽 P201103的跟蹤檔案,除了所用的系統表以外,和truncate表加的鎖相同。

ksqgtl *** TM-0001f804-00000000 mode=3 flags=0x401 timeout=0 ***

ksqcmi: TM,1f804,0 mode=3 timeout=0

ksqgtl *** TM-0001f808-00000000 mode=6 flags=0x401 timeout=0 ***

ksqcmi: TM,1f808,0 mode=6 timeout=0

ksqgtl *** TM-000001b1-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1b1,0 mode=3 timeout=21474836

ksqgtl *** TM-000001a8-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1a8,0 mode=3 timeout=21474836

ksqgtl *** TM-0000027b-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,27b,0 mode=3 timeout=21474836

ksqgtl *** TM-00000047-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,47,0 mode=3 timeout=21474836

ksqgtl *** TM-000001ad-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1ad,0 mode=3 timeout=21474836

ksqgtl *** TM-00000004-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,4,0 mode=3 timeout=21474836

=====================分割槽交換的跟蹤檔案分析==================

從物件號為,1F804 (分割槽表 yang_part),1F809(分割槽表的P201104 分割槽),1F80B(全域性索引) 所加的鎖

oracle@rac1:rac1 /tmp>more /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_29814.trc | grep TM |more

--先對基表加share lock

ksqgtl *** TM-0001f804-00000000 mode=3 flags=0x401 timeout=0 ***

ksqcmi: TM,1f804,0 mode=3 timeout=0

--P201104 分割槽加 排他鎖。

ksqgtl *** TM-0001f809-00000000 mode=6 flags=0x401 timeout=0 ***

ksqcmi: TM,1f809,0 mode=6 timeout=0

--EXCH_WITH_201104 加排他鎖

ksqgtl *** TM-0001f80b-00000000 mode=6 flags=0x401 timeout=0 ***

ksqcmi: TM,1f80b,0 mode=6 timeout=0

==============其他的為系統表。

ksqgtl *** TM-000001a8-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1a8,0 mode=3 timeout=21474836

ksqgtl *** TM-000001a6-00000000 mode=3 flags=0x401 timeout=21474836 ***

ksqcmi: TM,1a6,0 mode=3 timeout=21474836

 

----整個過程中使用的系統表:

sys@rac1>select object_name ,object_type from dba_objects where object_id in (18,4,71,458,635);

OBJECT_NAME               OBJECT_TYPE

------------------------- -------------------

TAB$                      TABLE

OBJ$                      TABLE

SUPEROBJ$                 TABLE

MON_MODS$                 TABLE

MLOG$                     TABLE                  

===================================================

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-687963/,如需轉載,請註明出處,否則將追究法律責任。

相關文章