oracle 11g sql优化之行迁移处理（加大BLOCK块）

行链接

产生原因：当一行数据大于一个数据块，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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!