ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 行迁移检测及解决一例

行迁移检测及解决一例

原创 Linux操作系统 作者:oracle_kai 时间:2008-03-31 11:14:33 0 删除 编辑

行迁移检测及解决一例

行迁移(row migration)由于在table pctfree 设置过小,在update 这个table并使其行长增大的时候,就有可能因为块的剩余空间不够存储该行,oracle会把该行数据迁移到另外一个有足够空闲空间的block中,此即发生了行迁移;发生行迁移时,行rowid并不变,原先存储该行的地方增加了一个新的指针,该指针指向迁移后的block id,所以我们在访问发生了行迁移的行时,会要读取迁移前和迁移后的2block,一个表如果有大量的行发生了row migration,那么就有必要查看该tablestorage 设置了,看看pctpree是否可以调整的更大一点。

链接(row chained)和行迁移相对应的是行链接 row chained ,行连接常发生在行很大的情况下,如有longrawlob列,当insert 一条新的记录时,一个block空间不够容纳一条记录,oracle会链接若干个block用来存储此记录,此即称为行链接;行链接是应为行的长度太长,要想避免,只能增加爱block size,在oracle9i 之前,创建好数据库后您无法修改block size,要求你在建库的时候就要规划好,在oracle9i,提供了多块大小的技术,您可以设置2k,4k,8k,16k,32k大小的block,若想使用,您还必须设置相应的db_nk_cache_size 缓冲池的大小。

 

下面是应用中客户维表行迁移的检测和消除步骤

检查user_tables 信息

Select table_name,pct_free,num_rows,blocks,chain_cnt From user_tables Where table_name='CUST_DIMT0';

TABLE_NAME                       PCT_FREE   NUM_ROWS     BLOCKS  CHAIN_CNT

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

CUST_DIMT0                             20     363338       8846       7449

 

利用oracle自带的脚本utlchain.sq,新增表chained_rows,用于存储发生行迁移记录信息

SQL> @ $ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

SQL> grant all on chained_rows to dwh;

Grant succeeded.

analyze 命令分析该表

SQL> analyze table cust_dimt0 list chained rows into sys.chained_rows;

Table analyzed

Chained_rows 用于存放产生行迁移的记录信息,其中head_rowid表示产生行迁移记录的rowid

从该表中任意找一个head_rowed,看看行迁移消除前的执行计划

SQL> set autotrace traceonly;

SQL> select * from cust_dimt0 where rowid='AAAF9UAARAAAAWGAA3';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=172)

   1    0   TABLE ACCESS (BY USER ROWID) OF 'CUST_DIMT0' (Cost=1 Card=1 Bytes=172)

Statistics

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

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

       1467  bytes sent via SQL*Net to client

        275  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

可以看到,取一条记录需要2consistent read ;

 

接下来,来消除该表上的行迁移

修改表的pct_free,增大为25,日后需再观察,是否有大量的行迁移情况,如有,则说明还需要调整该参数。

SQL> alter table cust_dimt0 pctfree 25;

建立临时表,存放发生行迁移的记录

CREATE TABLE CUST_DIMT0_080331 AS SELECT * FROM CUST_DIMT0 WHERE ROWID IN (SELECT HEAD_ROWID FROM SYS.CHAINED_ROWS WHERE TABLE_NAME='CUST_DIMT0');

查看是否有外键约束应用于此表上,如果有。则先禁用此约束,操作结束后再启用

SQL> select constraint_name,constraint_type,r_owner,r_constraint_name from dba_constraints where table_name='CUST_DIMT0' and wner='DWH';

CONSTRAINT_NAME                CONSTRAINT_TYPE R_OWNER                        R_CONSTRAINT_NAME

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

CUST_DIMT0_PK                  P                                             

SYS_C006476                    C                                             

SYS_C006477                    C                                             

SYS_C006478                    C                                             

SYS_C006479                    C     

 

SQL> SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='CUST_DIMT0_PK';

未选定行

如有相关表建有外键指向该表,需要先禁用约束

alter table xxx disable constraint yyyy

 

删除发生行迁移的记录

SQL> DELETE  CUST_DIMT0 WHERE ROWID IN (SELECT HEAD_ROWID FROM SYS.CHAINED_ROWS WHERE TABLE_NAME='CUST_DIMT0');

从临时表中重新插入记录

SQL> INSERT INTO CUST_DIMT0 SELECT * FROM CUST_DIMT0_080331;

看看执行行迁移消除后的执行计划

SQL>  select * from cust_dimt0 where rowid='AAAF9UAARAAAAWGAA3';

未选定行

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=172)

   1    0   TABLE ACCESS (BY USER ROWID) OF 'CUST_DIMT0' (Cost=1 Card=1 Bytes=172)

 

Statistics

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

          0  recursive calls

          0  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

       1222  bytes sent via SQL*Net to client

        234  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

0         rows processed

可以发现,执行行迁移消除后,consistent gets 1block了。

 

最后,drop 临时表

SQL> drop table  CUST_DIMT0_080331;

 

Table dropped

 

注:行迁移主要是由于update 的时候,块剩余空间满足不了而导致的,对于行迁移的消除,除了上面介绍的方法外,采用exp/imp ,先把表导出,然后再导入,或者采用move命令,同样,可以达到行迁移消除的目的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10159839/viewspace-220330/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    172739