<pre name="code" class="sql">Oracle的dbms_stats.gather_schema_stats只会收集优化器统计信息,不会检测表的记录是否存在行迁移和行链接。
要检测表的记录是否存在row chain,需要用到ORACLE
早先一点的命令:ANALYZE TABLE
值得注意的是:数据字典 USER_TABLES.CHAIN_CNT的值是包含
了行迁移和行链接的数目.请看ORACLE的解释: CHAIN_CNT* Number of rows in the table that are chained from one data block to another, or which have migrated to a
new block, requiring a link to preserve the old ROWID 也就是说这个字段的值是行迁移和行链接的总数量,至于要知道具体行迁
移有多少,行链接又是多少,ANALYZE TABLE这个命令没办法得到,只有通过DUMP BLOCK来区分(方法复杂,故没有去做).但首先我们要知道行迁移和行链接是两个不同的概念:
(1)行迁移: ORACLE一个BLOCK的DEFAULT SIZE是8K,事实上,一个BLOCK不可以存储8K的数
据.一个BLOCK可以存储多少数据,由PCTFREE,PCTUSED参数控制(对于以前的手工管理的表空间而言)
PCTFREE:是指BLOCK保留空闲空间的百分比,用于UPDATE。对于已经插入到BLOCK的行而言，
后面的UPDATE操作有可能使行的长度增加，PCTFREE就是用于容纳增加的那部分长度而保留的空闲空间。如果UPDATE时PCTFREE再也不能够容纳行增加的长度，则ORACLE会将整个行
迁移到一个新的BLOCK，行的ROWID保留(不是太明白为什么ORACLE不改变ROWID)，原来的BLOCK有一个指针指向ROW存放的新BLOCK。这就是行迁移。可见，行迁移是由于UPDATE操作
所导致。从字面上理解:
所谓迁移，肯定先有存在这一行，才能叫着迁移.
(2)行链接:是指一个BLOCK不能容纳一行(行的长度太大),而必须将此行存放于几个BLOCK.行链接一般是在Insert时产生的.一个BLOCK能否用于
insert是由PCTUSED控制. PCTUSED：是指BLOCK用于INSERT的百分比。对于INSERT操作，BLOCK可用于容纳新行的最大空间为Blocksize-pctfree-overhead.当BLOCK数据存储已高于
PCTUSED，
ORACLE会将该块从自由链表中移除，直到该块已使用空间降到PCTUSED以下，才会再次将此块重新加入到Freelist(这是ORACLE以前手工管理的表空间管理空闲块的原理,现在ORACLE
推荐使用ASSM).
行迁移和行链接的检测： 除了我写的存储过程可以检测以外，一个简单的检测方法是：
select b.NAME, a.VALUE
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.NAME like 'table fetch continued row'
当有返回值时，可以知道表的数据存在行迁移和行链接。 行迁移和行链接的清除： 能过REBUILD数据来清除行迁移：
create table MM_PM_temp as select * from MM_PM;
truncate table MM_PM;
insert into MM_PM select * from MM_PM_temp
再重新分析表： analyze table MM_PM compute statistics; 分析过后再查看：
select t.table_name,
t.num_rows,
t.chain_cnt,
t.avg_row_len,
round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %"
from user_tables t
where
t.chain_cnt > 0
如果该表的chain_cnt变为0时，表示原先的chain_cnt全部是行迁移，而不是行链接。如果REBUILD数据后chain_cnt变少，但还大于0，则可以证明，这个表即包含行迁移，又包含
真正的行
链接。事实证明，行迁移是可以通过REBUILD数据和增加PCTFREE%来清除和减少发生频率的。注意，对于ASSM，PCTUSED，FREELIST，FREELIST GROUPS参数会被忽略。 但对于真正
的行链接，只能通过将表移植到大的BLOCSIZE的表空间上。
行迁移和行链接实验:
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
);
由于设置pct_free=0 稍微update一下就产生行迁移:
drop table test_1 purge;
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
);
insert into test_1 select * from dba_objects;---执行多次
SQL> select count(*) from test_1;
COUNT(*)
----------
4649984
批量更新:
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;
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST_1',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END; 2 3 4 5 6 7 8 9
10 /
PL/SQL 过程已成功完成。
SQL> show user
USER 为 "SCOTT"
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 151 4649984 0 0
可以看到通过DBMS_STATS.GATHER_TABLE_STATS无法搜集行迁移和行链接,需要使用ANALYZE TABLE
SQL> analyze table TEST_1 compute statistics;
表已分析。
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 403 4649984 3642111 .783252372
SQL> select TABLE_NAME,TABLESPACE_NAME from dba_tables where table_name='TEST_1';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST_1 USERS
SQL> alter table test_1 move;
表已更改。
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 403 4649984 3642111 .783252372
SQL> analyze table TEST_1 compute statistics;
表已分析。
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 416 4649984 0 0