行链接和行迁移

CHAIN_CNT:虽然行链接和行迁移的概念不同，但在ORACLE里其实不太分这个，CHAIN_CNT是总的数量


PCTFREE:
PCTFREE：为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；即：

当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。


create table TEST_1
(
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  TIMESTAMP      VARCHAR2(19),
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  GENERATED      VARCHAR2(1),
  SECONDARY      VARCHAR2(1),
  NAMESPACE      NUMBER,
  EDITION_NAME   VARCHAR2(30)
)
tablespace USERS
  pctfree 0
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

SQL> select
   owner              ,
   table_name         ,
   pct_free           ,
   pct_used           ,
   avg_row_len        ,
   num_rows           ,
   chain_cnt          ,
   chain_cnt/num_rows 
from
   dba_tables
where
   table_name='TEST_1'
order by
   chain_cnt desc;  2    3    4    5    6    7    8    9   10   11   12   13   14   15  

OWNER			       TABLE_NAME			PCT_FREE   PCT_USED AVG_ROW_LEN   NUM_ROWS  CHAIN_CNT CHAIN_CNT/NUM_ROWS
------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ------------------
SCOTT			       TEST_1				       0


pct_free=0  稍微update一下就产生hang迁移


DECLARE  
  CURSOR cur IS  
    SELECT   
     a.ROWID from test_1 a ;  
  V_COUNTER NUMBER;  
BEGIN  
  V_COUNTER := 0;  
  FOR row IN cur LOOP  
    UPDATE test_1  
       SET OBJECT_ID = 9999 

,OBJECT_NAME='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' 
     WHERE ROWID = row.ROWID ;
    V_COUNTER := V_COUNTER + 1;  
    IF (V_COUNTER >= 10000) THEN  
      COMMIT;  
      V_COUNTER := 0;  
    END IF;  
  END LOOP;  
  COMMIT;  
END;

批量update

SQL> analyze table TEST_1 compute statistics;


SQL> set linesize 200
SQL>  select
   owner              ,
   table_name         ,
   pct_free           ,
   pct_used           ,
   avg_row_len        ,
   num_rows           ,
   chain_cnt          ,
   chain_cnt/num_rows 
from
   dba_tables
where
   table_name='TEST_1'
order by
   chain_cnt desc;   
  2    3    4    5    6    7    8    9   10   11   12   13   14   15  
OWNER			       TABLE_NAME			PCT_FREE   PCT_USED AVG_ROW_LEN   NUM_ROWS  CHAIN_CNT CHAIN_CNT/NUM_ROWS
------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ------------------
SCOTT			       TEST_1				       0		    170    8886376    5059910	      .569400845