本篇内容介绍了“什么是SQL行迁移和行链接”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
行迁移和行链接(行的跨块存储)
行链接:
成因:指一行存储在多个块中的情况,即行链接是跨越多块的行。
第一次插入时,一个块存不下,insert 到多个块中。
当一行数据大于一个数据块,ORACLE会同时分配两个数据块,并在第一个块上登记第二个块的地址,从而形成行链接。
后果:导致应用需要访问更多的数据块,性能下降。
预防:针对表空间扩大数据块大小。
检查:analyze table 表名 validate structure cascade into chained_rows;
需要执行脚本$ORACLE_HOME/rdbms/admin/utlchain.sql创建chained_rows表。
行迁移:
成因:当行被Update时,如果Update更新的行大于数据块得PCTFREE值,就需要申请第2个块,从而形成行迁移。
当一个数据行由于 update 语句导致当前块被重新定位到另一个块(那里有充足的空间)中,但在原始块中会保留一个指针。
原始块中的指针是必需的,因为索引的 ROWID 项仍然指向原始位置。
行迁移是 update 语句当 pctfree 空间不足时引起的,它与 insert 和 delete 语句无关。
后果:导致应用需要访问更多的数据块,性能下降。
预防:1. 将数据块的PCTFREE调大;
2. 针对表空间扩大数据块大小
检查:analyze table 表名 validate structure cascade into chained_rows;
如何知道发生了行链接或行迁移?
对表进行analyze,然后查看 dba_tables 的 AVG_ROW_LEN 列和 CHAIN_CNT 列,若不为0,则说明发生了行迁移或者行链接。
如何确定发生了行迁移还是行链接?
这里采用move的办法:
若对表进行move,然后CHAIN_CNT的列变为0,并且blocks也会减少,则说明只发生了行迁移。
若对表进行move,然后CHAIN_CNT的列不变,并且blocks也不变,则说明只发生了行链接。
还有一种特殊情况,同时发生行迁移和行链接(比较极端):
在对表进行update时,更新的行过大,导致一个块存不下,那么会变成行迁移,但是由于过大,因此还需要更多的块来存储,发生的是行链接
若对表进行move,然后CHAIN_CNT的列不变或者减少,并且blocks减少,则说明同时发生了行链接和行链接。
优化:
行迁移:1、若表在进行update时发生了行迁移,那么需要对表进行move操作(若有索引,需要重建)
2、exp/imp方式(针对发生行迁移的表)
3、对发生行迁移的表的数据进行新建临时表,然后在把记录插回到原表
行链接:只有通过加大BLOCK块的方式才可以避免
行迁移测试:
创建表 SQL> drop table t1; Table dropped. SQL> create table t1 (c1 varchar2(20)); Table created. 插入数据 SQL> insert into t1 select '' from dual connect by level < 1000; 999 rows created. SQL> commit; Commit complete. 先分析一下 t1 表,确定无行迁移 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 SQL> analyze table t1 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 3 0 5 使用了5个块,并且chain_cnt为0,并没有发生行迁移。 update空列,再分析 t1,有了行迁移 SQL> update t1 set c1='oracle mysql'; 999 rows updated. SQL> commit; Commit complete. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 3 0 5 SQL> analyze table t1 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 21 767 13 说明 999 行中有 767 行发生了行迁移,使用的块也增加了。
消除行迁移:
这里使用 move 解决,若表上有索引,需要重建索引,move会使表上的索引失效:
SQL> alter table t1 move; Table altered. move 表后,再分析 t1,行迁移消失。 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 21 767 13 SQL> analyze table t1 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T1'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16 0 6
使用了6个块,从13块变成6,block减少,并且CHAIN_CNT由767变成0,此时已经消除了行迁移。
总结:若表在进行update时发生了行迁移,那么需要对表进行行迁移的消除,可以采用move操作来消除行迁移(若有索引,需要重建)。
行链接测试:
创建表 SQL> create table t2 (c1 varchar2(4000),c2 varchar2(4000),c3 varchar2(4000)); Table created. 插入数据 SQL> insert into t2 select 'a','b','c' from dual connect by level <= 100; 100 rows created. SQL> commit; Commit complete. 先分析一下 t1 表,确定无行链接 SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 SQL> analyze table t2 compute statistics; Table analyzed. SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 9 0 5 使用了5个块,并且chain_cnt为0,并没有发生行链接。 插入大于 8K 行,再分析 t2,有了行链接 SQL> insert into t2 values (lpad('a',4000,'a'),lpad('a',4000,'a'),lpad('a',4000,'a')); 1 row created. SQL> commit; Commit complete. SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 9 0 5 SQL> analyze table t2 compute statistics; Table analyzed. SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 128 1 5
说明 新插入的 1 行数据发生了行迁移,使用的块不变。
消除行链接:
创建非标准块大小表空间 16K SQL> show parameter cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 1G db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_cache_advice string ON db_cache_size big integer 0 db_flash_cache_file string db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 result_cache_max_result integer 5 result_cache_max_size big integer 12064K result_cache_mode string MANUAL result_cache_remote_expiration integer 0 session_cached_cursors integer 50 设置db_16k_cache_size为16m SQL> alter system set db_16k_cache_size=16m; System altered. 创建表空间 create tablespace tabspace_16k blocksize 16K datafile '/oracle/app/oracle/oradata/test/tabspace_16k.dbf' size 20M autoextend on extent management local segment space management auto; 把t2表移动到tabspace_16k表空间上 SQL> alter table t2 move tablespace tabspace_16k; Table altered. 再次进行分析查看 SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 128 1 5 SQL> analyze table t2 compute statistics; Table analyzed. SQL> select pct_free,avg_row_len,chain_cnt,blocks from user_tables where table_name='T2'; PCT_FREE AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ----------- ---------- ---------- 10 128 0 4
目前使用了4个块,并且chain_cnt为0,说明行链接已经消除了。
同时发生行迁移和行链接(比较极端):
创建表 SQL> drop table t3; Table dropped. SQL> create table t3 (c1 int,c2 varchar2(4000),c3 varchar2(4000),c4 varchar2(4000),c5 varchar2(4000)); Table created. 插入数据 SQL> insert into t3 select level,'','','','' from dual connect by level <= 100; 100 rows created. SQL> commit; Commit complete. 先分析一下 t3 表,确定无行迁移和行链接 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 SQL> analyze table t3 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 6 0 5 使用了5个块,并且chain_cnt为0,并没有发生行迁移或者行链接 对列进行更新,再分析 t1,有了行迁移或者行链接 SQL> update t3 set c2 = LPAD('1', 4000, '*'),c3 = LPAD('1', 4000, '*'),c4 = LPAD('1', 4000, '*'),c5 = LPAD('1', 4000, '*'); 100 rows updated. SQL> commit; Commit complete. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 6 0 5 SQL> analyze table t3 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16030 100 496
说明 100 行中有 100 行发生了行迁移或者行链接,使用的块也增加了。
如何确定是发生了行迁移还是行链接?
这里采用move的办法:
若对表进行move,然后CHAIN_CNT的列变为0,并且blocks也会减少,则说明只发生了行迁移。
若对表进行move,然后CHAIN_CNT的列不变,并且blocks也不变,则说明只发生了行链接。
还有一种特殊情况,同时发生行迁移和行链接(比较极端):
在对表进行update时,更新的行过大,导致一个块存不下,那么会变成行迁移,但是由于过大,因此还需要更多的块来存储,发生的是行链接
若对表进行move,然后CHAIN_CNT的列不变或者减少,并且blocks减少,则说明同时发生了行链接和行链接。
消除行迁移:
这里使用 move 解决,若表上有索引,需要重建索引,move会使表上的索引失效: SQL> alter table t3 move; Table altered. move 表后,再分析 t3 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16030 100 496 SQL> analyze table t3 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16044 100 234
使用了234个块,从496块变成234,block减少,并且CHAIN_CNT没变,说明已经消除了行迁移,但是还有行链接为消除。
因此,同时发生行迁移和行链接。
下一步需要消除行链接:
和正常消除行链接的方法一致:
创建非标准块大小表空间 16K SQL> show parameter cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 1G db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_cache_advice string ON db_cache_size big integer 0 db_flash_cache_file string db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 result_cache_max_result integer 5 result_cache_max_size big integer 12064K result_cache_mode string MANUAL result_cache_remote_expiration integer 0 session_cached_cursors integer 50 设置db_16k_cache_size为16m SQL> alter system set db_16k_cache_size=16m; System altered. 创建表空间 create tablespace tabspace_16k blocksize 16K datafile '/oracle/app/oracle/oradata/test/tabspace_16k.dbf' size 20M autoextend on extent management local segment space management auto; 把t2表移动到tabspace_16k表空间上 SQL> alter table t3 move tablespace tabspace_16k; Table altered. 再次进行分析查看 SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16044 100 234 SQL> analyze table t3 compute statistics; Table analyzed. SQL> select pct_free,pct_used,avg_row_len,chain_cnt,blocks from user_tables where table_name='T3'; PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT BLOCKS ---------- ---------- ----------- ---------- ---------- 10 16018 0 107
目前使用了107个块,并且chain_cnt为0,说明行链接已经消除了。
至此消除了行迁移和行链接。
“什么是SQL行迁移和行链接”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注云搜网网站,小编将为大家输出更多高质量的实用文章!