行链接
产生原因：当一行数据大于一个数据块，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',EMAIL = LPAD('1', PHONE_NUMBER = LPAD('1','*');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/ljbdrop 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
