行链接和行迁移引起数据库性能下降的原因:
引起性能下降的原因主要是由于引起多余的I/O造成的。当通过索引访问已有行迁移现象的行时,数据库必须扫描一个以上的数据块才能检索到改行的数据。这主要有以下两种表现形式:
1) row migration 或row chaining 导致 INSERT 或 UPDATE语句的性能比较差,因为它们需要执行额外的处理
2) 利用索引查询已经链接或迁移的行的select语句性能比较差,因为它们要执行额外的I/O。
行链接与行迁移的消除
对于行链接只能增大db_block_size来清除。
测试行迁移,并消除行迁移(CTAS)
SQL> desc t Name Type Nullable Default Comments ---- -------------- -------- ------- -------- ID INTEGER Y COL CHAR(2000) Y COL2 VARCHAR2(4000) Y SQL> BEGIN 2 FOR i IN 1..20 LOOP 3 INSERT INTO t VALUES(i,'a'||i,'b'||i); 4 END LOOP; 5 COMMIT; 6 END; 7 / PL/SQL procedure successfully completed SQL> update t set col2=lpad('La','2000','*'); 20 rows updated SQL> commit; Commit complete SQL> analyze table t list chained rows; Table analyzed SQL> select t2.owner_name,t2.table_name,t2.head_rowid from chained_rows t2; OWNER_NAME TABLE_NAME HEAD_ROWID ------------------------------ ------------------------------ ------------------ SYS T AAAMlJAABAAAOraAAB SYS T AAAMlJAABAAAOrbAAB SYS T AAAMlJAABAAAOrcAAB SYS T AAAMlJAABAAAOrdAAB SYS T AAAMlJAABAAAOreAAB SYS T AAAMlJAABAAAOrfAAB 6 rows selected SQL> create index idx_tid on t(id); Index created SQL> select di.status from dba_indexes di where di.index_name='IDX_TID'; STATUS -------- VALID SQL> create table t_temp as select * from t where rowid in 2 (select cr.head_rowid from chained_rows cr where cr.table_name='T' and cr.owner_name='SYS'); Table created SQL> select count(*) from t_tmp; select count(*) from t_tmp ORA-00942: table or view does not exist SQL> select count(*) from t_temp; COUNT(*) ---------- 6 --当该表被引用时,不能被删除 SQL> delete from t where rowid in (select cr.head_rowid from chained_rows cr where cr.table_name='T' and cr.owner_name='SYS'); 6 rows deleted SQL> commit; Commit complete SQL> insert into t select * from t_temp; 6 rows inserted SQL> select count(*) from t; COUNT(*) ---------- 20 SQL> commit; Commit complete SQL> select di.status from dba_indexes di where di.index_name='IDX_TID'; STATUS -------- VALID SQL> truncate table chained_rows; Table truncated --重新分析并查看分析结果,分析结果为空,说明行迁移已经消除。 SQL> analyze table t list chained rows; Table analyzed SQL> select t2.owner_name,t2.table_name,t2.head_rowid from chained_rows t2; OWNER_NAME TABLE_NAME HEAD_ROWID ------------------------------ ------------------------------ ------------------ SQL> drop table t_temp; Table dropped SQL> select di.status from dba_indexes di where di.index_name='IDX_TID'; STATUS -------- VALID
其实,通过CTAS能够消除的,其实就是行迁移,要消除行链接只能够通过增大块大小来实现。
引起性能下降的原因主要是由于引起多余的I/O造成的。当通过索引访问已有行迁移现象的行时,数据库必须扫描一个以上的数据块才能检索到改行的数据。这主要有以下两种表现形式:
1) row migration 或row chaining 导致 INSERT 或 UPDATE语句的性能比较差,因为它们需要执行额外的处理
2) 利用索引查询已经链接或迁移的行的select语句性能比较差,因为它们要执行额外的I/O。
行链接与行迁移的消除
对于行链接只能增大db_block_size来清除。
对于行迁移的清除,一般来说分为两个步骤:第一步,控制住行迁移的增长,使其不在增多,需要设置合理的pctfree参数;第二步,清除掉以前存在的行迁移。
测试行迁移,并消除行迁移(CTAS)
SQL> desc t Name Type Nullable Default Comments ---- -------------- -------- ------- -------- ID INTEGER Y COL CHAR(2000) Y COL2 VARCHAR2(4000) Y SQL> BEGIN 2 FOR i IN 1..20 LOOP 3 INSERT INTO t VALUES(i,'a'||i,'b'||i); 4 END LOOP; 5 COMMIT; 6 END; 7 / PL/SQL procedure successfully completed SQL> update t set col2=lpad('La','2000','*'); 20 rows updated SQL> commit; Commit complete SQL> analyze table t list chained rows; Table analyzed SQL> select t2.owner_name,t2.table_name,t2.head_rowid from chained_rows t2; OWNER_NAME TABLE_NAME HEAD_ROWID ------------------------------ ------------------------------ ------------------ SYS T AAAMlJAABAAAOraAAB SYS T AAAMlJAABAAAOrbAAB SYS T AAAMlJAABAAAOrcAAB SYS T AAAMlJAABAAAOrdAAB SYS T AAAMlJAABAAAOreAAB SYS T AAAMlJAABAAAOrfAAB 6 rows selected SQL> create index idx_tid on t(id); Index created SQL> select di.status from dba_indexes di where di.index_name='IDX_TID'; STATUS -------- VALID SQL> create table t_temp as select * from t where rowid in 2 (select cr.head_rowid from chained_rows cr where cr.table_name='T' and cr.owner_name='SYS'); Table created SQL> select count(*) from t_tmp; select count(*) from t_tmp ORA-00942: table or view does not exist SQL> select count(*) from t_temp; COUNT(*) ---------- 6 --当该表被引用时,不能被删除 SQL> delete from t where rowid in (select cr.head_rowid from chained_rows cr where cr.table_name='T' and cr.owner_name='SYS'); 6 rows deleted SQL> commit; Commit complete SQL> insert into t select * from t_temp; 6 rows inserted SQL> select count(*) from t; COUNT(*) ---------- 20 SQL> commit; Commit complete SQL> select di.status from dba_indexes di where di.index_name='IDX_TID'; STATUS -------- VALID SQL> truncate table chained_rows; Table truncated --重新分析并查看分析结果,分析结果为空,说明行迁移已经消除。 SQL> analyze table t list chained rows; Table analyzed SQL> select t2.owner_name,t2.table_name,t2.head_rowid from chained_rows t2; OWNER_NAME TABLE_NAME HEAD_ROWID ------------------------------ ------------------------------ ------------------ SQL> drop table t_temp; Table dropped SQL> select di.status from dba_indexes di where di.index_name='IDX_TID'; STATUS -------- VALID
其实,通过CTAS能够消除的,其实就是行迁移,要消除行链接只能够通过增大块大小来实现。