Oracle 行迁移与行链接
Oracle 行迁移与行链接
行链接和行迁移引起数据库性能下降的原因:引起性能下降的原因主要是由于引起多余的I/O造成的。当通过索引访问已有行迁移现象的行时,数据库必须扫描一个以上的数据块才能检索到改行的数据。这主要有以下两种表现形式:
1) row migration 或row chaining 导致 INSERT 或 UPDATE语句的性能比较差,因为它们需要执行额外的处理
2) 利用索引查询已经链接或迁移的行的select语句性能比较差,因为它们要执行额外的I/O。
行链接与行迁移的消除
对于行链接只能增大db_block_size来清除。
对于行迁移的清除,一般来说分为两个步骤:第一步,控制住行迁移的增长,使其不在增多,需要设置合理的pctfree参数;第二步,清除掉以前存在的行迁移。
[sql]
- --查看行链接或行迁移总体情况
- SQL> SELECT name,value FROM v$sysstat WHERE name like 'table fetch continued row';
- NAME VALUE
- ---------------------------------------------------------------- ----------
- table fetch continued row 519
如何检测行链接或行迁移
[sql]- --运行utlchain.sql
- SQL> @$Oracle_HOME/rdbms/admin/utlchain.sql
- Table created.
- --对表进行分析
- SQL> analyze table t list chained rows;
- Table analyzed.
- --查询chained_rows结果
- SQL> select * from chained_rows;
- no rows selected
[sql]
- 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
- --重新分析并查看分析结果,分析结果为空,www.bkjia.com说明行迁移已经消除。
- 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
|
评论暂时关闭