本文共 3141 字，大约阅读时间需要 10 分钟。

行链接

产生原因：当一行数据大于一个数据块，ORACLE会同时分配两个数据块，并在第一个块上登记第二个块的地址，从而形成行链接。

预防方法：针对表空间扩大数据块大小。

检查：analyze table 表名 validate structure cascade into chained_rows;

 

--- PCTFREE试验准备之建表

DROP TABLE EMPLOYEES PURGE;CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;desc EMPLOYEES;create index idx_emp_id on employees(employee_id);

--- PCTFREE试验准备之扩大字段alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000);alter table EMPLOYEES modify EMAIL VARCHAR2(2000);alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000);

--- PCTFREE试验准备之更新表

UPDATE EMPLOYEES SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'), PHONE_NUMBER = LPAD('1', 2000, '*');COMMIT;

---行链接移优化前，先看看该语句逻辑读情况SET AUTOTRACE traceonly set linesize 1000select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0/

 

set autotrace off

 

----- 发现存在行链接的方法

--首先建chaind_rows相关表，这是必需的步骤--sqlplus "/ as sysdba"

sqlplus ljb/ljb

drop table chained_rows purge;@?/rdbms/admin/utlchain.sql----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中analyze table EMPLOYEES list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES';

---用消除行迁移的方法根本无法消除行链接!!!

drop table EMPLOYEES_TMP;

create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);Insert into EMPLOYEES select * from EMPLOYEES_TMP;delete from chained_rows ;commit;--发现用消除行迁移的方法根本无法消除行链接!analyze table EMPLOYEES list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES';

SET AUTOTRACE traceonly

set linesize 1000select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0/

 

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

---启动大小为16K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)

--行链接只有通过加大BLOCK块的方式才可以避免，如下：alter system set db_16k_cache_size=50m scope=spfile;  --由于我的是RAC,且节点2关闭，不能直接scope=both,还有就是设置的50m是根据表的总的数据量和热数据设置的，具体生产情况，需要酌情设置。

shutdown immediate;

startup；

 show parameter db_

 

DROP TABLE EMPLOYEES_BK PURGE;

CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_LJB_16K AS SELECT * FROM EMPLOYEES;delete from chained_rows ;commit;analyze table EMPLOYEES_BK list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES_BK';

***************发现用消除行迁移的方法根本无法消除行链接************SQL> analyze table EMPLOYEES list chained rows into chained_rows;表已分析。SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; COUNT(*)---------- 107 ***********行链接只有通过加大BLOCK块的方式才可以避免**********************

如下：

SQL> DROP TABLE EMPLOYEES_BK PURGE;表已删除。SQL> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_LJB_16K AS SELECT * FROM EMPLOYEES;  ---也可以直接用alter table  table_name move tablespace TBS_LJB_16K,但是索引需要重建;表已创建。SQL> delete from chained_rows ;已删除107行。SQL> commit;提交完成。SQL> analyze table EMPLOYEES_BK list chained rows into chained_rows;表已分析。SQL> select count(*) from chained_rows where table_name='EMPLOYEES_BK'; COUNT(*)---------- 0