--日常维护命令对性能的影响已经上锁的注意事项
--1 create index 是4级锁
SQL>Create Index index_CONTACT_NAME On t_to_order_info(CONTACT_NAME)
SQL>Select * From V$LOCK Where Type='TM'
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
00000000D941FAB0 00000000D941FAD8 114 TM 112775 0 4 0 11 0
00000000D941FBB0 00000000D941FBD8 114 TM 18 0 3 0 11 0
SQL>Select aa.OBJECT_NAME,aa.OBJECT_ID,aa.OWNER From dba_objects aa Where aa.OBJECT_ID In (112775,18)
OBJECT_NAME OBJECT_ID OWNER
------------ --------- ------
OBJ$ 18 SYS
T_TO_ORDER_INFO 112775 GCBB
--2 create index online 是2级锁
SQL>Create Index index_CONTACT_NAME On t_to_order_info(CONTACT_NAME) Online
SQL> Select * From V$LOCK Where Type='TM' ;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000D941FAB0 00000000D941FAD8 155 TM 68 0 3 0 60 0
00000000D941FBB0 00000000D941FBD8 60 TM 237 0 3 0 60 0
00000000D941FCB0 00000000D941FCD8 114 TM 112775 0 2 0 40 0
00000000D941FDB0 00000000D941FDD8 114 TM 18 0 3 0 40 0
00000000D941FEB0 00000000D941FED8 160 TM 8779 0 3 0 28 0
SQL> Select aa.OBJECT_NAME,aa.OBJECT_ID,aa.OWNER From dba_objects aa Where aa.OBJECT_ID In (68,237,112775,18,8779);
OBJECT_NAME OBJECT_ID OWNER
-------------------------------------------------------------------------------- ---------- ------------------------------
WRI$_ALERT_OUTSTANDING 8779 SYS
JOB$ 237 SYS
SEQ$ 68 SYS
OBJ$ 18 SYS
T_TO_ORDER_INFO 112775 GCBB
--3 rebuild index 是4级锁
SQL>Alter Index IDX_T_TO_ORDER_1 Rebuild
SQL> Select * From V$LOCK Where Type='TM';
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000D941FAB0 00000000D941FAD8 155 TM 68 0 3 0 0 0
00000000D941FBB0 00000000D941FBD8 114 TM 112775 0 4 0 18 0
--4 rebuild index online 是2级锁
SQL> Alter Index TICKETORDER_MEMBERINFO_FK Rebuild Online
SQL> Select * From V$LOCK Where Type='TM';
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000D941FAB0 00000000D941FAD8 114 TM 116055 0 4 0 11 0
00000000D941FBB0 00000000D941FBD8 114 TM 112775 0 2 0 11 0
00000000D941FCB0 00000000D941FCD8 155 TM 68 0 3 0 0 0
Online的时候会多出一个临时的对象，在v$lock里面能看见
-----------------------------------------------------------------------
--5 shrink Space Compact 是3级锁
SQL>Alter t_to_order_info shrink Space Compact
SQL> Select * From V$LOCK Where Type='TM' ;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000D941FAB0 00000000D941FAD8 114 TM 106582 0 3 0 27 0
SQL> Select aa.OBJECT_NAME,aa.OBJECT_ID,aa.OWNER From dba_objects aa Where aa.OBJECT_ID In (106582);
OBJECT_NAME OBJECT_ID OWNER
-------------------------------------------------------------------------------- ---------- ------------------------------
T_TO_ORDER_INFO 106582 GCAA
--6 shrink Space开始的时候还是3级锁，在第二部调整HWM位置的时候上6级锁
SQL> Alter table t_to_order_info shrink Space;
SQL> Select * From V$LOCK Where Type='TM' ;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000D941FAB0 00000000D941FAD8 114 TM 106582 0 3 0 15 0
--7 exec dbms_stats.gather_table_stats 这个命令不会上锁，只是非常消耗i/o资源
Sql> exec dbms_stats.gather_table_stats('gcaa','t_to_order_info');
SQL> Select * From V$LOCK Where Type='TM' ;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
备注：
segment shrink分为两个阶段：
1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。
在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改
变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影
响比较小。
2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，
会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
shrink space compact只执行第一个阶段。 --这是3级锁
shrink space语句两个阶段都执行。 --这是6级锁
如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙
的时候再执行shrink space降低HWM释放空闲数据块。
作用：压缩释放空闲空间，加快全表扫描的速度
