ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【索引】分区表索引重建过程的10704事件跟踪

【索引】分区表索引重建过程的10704事件跟踪

原创 Linux操作系统 作者:杨奇龙 时间:2011-02-25 17:35:23 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 修改oracle字符集
请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7828976