Oracle数据库中行迁移行迁移专题.docx
- 文档编号:29797015
- 上传时间:2023-07-27
- 格式:DOCX
- 页数:16
- 大小:22.82KB
Oracle数据库中行迁移行迁移专题.docx
《Oracle数据库中行迁移行迁移专题.docx》由会员分享,可在线阅读,更多相关《Oracle数据库中行迁移行迁移专题.docx(16页珍藏版)》请在冰豆网上搜索。
Oracle数据库中行迁移行迁移专题
Oracle数据库中行迁移/行链接
一、行迁移/行链接的介绍
在实际的工作中我们经常会碰到一些Oracle数据库性能较低的问题,当然,引起Oracle数据库性能较低的原因是多方面的,我们能够通过一些正确的设计和诊断来尽量的避免一些Oracle数据库性能不好,RowMigration(行迁移)&RowChaining(行链接)就是其中我们可以尽量避免的引起Oracle数据库性能低下的潜在问题。
通过合理的诊断行迁移/行链接,我们可以较大幅度上提高Oracle数据库的性能。
那究竟什么是行迁移/行链接呢,先让我们从Oracle的block开始谈起。
操作系统的最小读写操作单元是操作系统的block,所以当创建一个Oracle数据库的时候我们应该讲数据库的blocksize设置成为操作系统的blocksize的整数倍,Oracleblock是Oracle数据库中读写操作的最小单元,Oracle9i之前的Oracle数据库版本中Oracleblock一旦在创建数据库的时候被设定后就没法再更改。
为了在创建数据库之前确定一个合理的Oracleblock的大小,我们需要考虑一些因素,例如数据库本身的大小以及并发事务的数量等。
使用一个合适的Oracleblock大小对于数据库的调优是非常重要的。
一个Oracleblock由三个部分组成,分别是数据块头、自由空间、实际数据三部份组成。
数据块头:
主要包含有数据块地址的一些基本信息和段的类型,以及表和包含有数据的实际行的地址。
自由空间:
是指可以为以后的更新和插入操作分配的空间,大小由PCTFREE和PCTUSED两个参数影响。
实际数据:
是指在行内存储的实际数据。
当创建或者更改任何表和索引的时候,Oracle在空间控制方面使用两个存储参数:
PCTFREE:
为将来更新已经存在的数据预留空间的百分比。
PCTUSED:
用于为插入一新行数据的最小空间的百分比。
这个值决定了块的可用状态。
可用的块时可以执行插入的块,不可用状态的块只能执行删除和修改,可用状态的块被放在freelist中。
当表中一行的数据不能在一个数据block中放入的时候,这个时候就会发生两种情况,一种是行链接,另外一种就是行迁移了。
行链接产生在第一次插入数据的时候如果一个block不能存放一行记录的情况下。
这种情况下,Oracle将使用链接一个或者多个在这个段中保留的block存储这一行记录,行链接比较容易发生在比较大的行上,例如行上有LONG、LONGRAW、LOB等数据类型的字段,这种时候行链接是不可避免的会产生的。
当一行记录初始插入的时候事可以存储在一个block中的,由于更新操作导致行长增加了,而block的自由空间已经完全满了,这个时候就产生了行迁移。
在这种情况下,Oracle将会迁移整行数据到一个新的block中(假设一个block中可以存储下整行数据),Oracle会保留被迁移行的原始指针指向新的存放行数据的block,这就意味着被迁移行的ROWID是不会改变的。
当发生了行迁移或者行链接,对这行数据操作的性能就会降低,因为Oracle必须要扫描更多的block来获得这行的信息。
下面举例来具体说明行迁移/行链接的产生过程。
先创建一个pctfree为20和pctused为50的测试表:
createtabletest(
col1char(20),
col2number)
storage(
pctfree20
pctused50);
当插入一条记录的时候,Oracle会在freelist中先去寻找一个自由的块,并且将数据插入到这个自由块中。
而在freelist中存在的自由的块是由pctfree值决定的。
初始的空块都是在freelist中的,直到块中的自由空间达到pctfree的值,此块就会从freelist中移走,而当此块中的使用空间低于pctused的时候,此块又被重新放到freelist中。
Oracle使用freelist机制可以大大的提高性能,对于每次的插入操作,Oracle只需要查找freelist就可以了,而不是去查找所有的block来寻找自由空间。
具体我们通过下面的一个试验来查看行链接和行迁移是如何产生并在数据文件中体现出来的。
先查看ALLAN这个表空间的数据文件号,为了便于测试,我只建立了一个数据文件。
SQL>selectfile_idfromdba_data_fileswheretablespace_name='ALLAN';
FILE_ID
----------
23
创建一个测试表test:
SQL>createtabletest(xintprimarykey,
achar(2000),bchar(2000),cchar(2000),
dchar(2000),echar(2000))tablespaceallan;
Tablecreated.
因为我的数据库的db_block_size是8K,所以我创建的表有五个字段,每个占2000个字节,这样一行记录大约10K,就能超过一个block的大小了。
然后插入一行记录,只有一个字段的:
SQL>insertintotest(x)values
(1);
1rowcreated.
SQL>commit;
Commitcomplete.
查找这行记录所在的block,并dump出来:
SQL>selectdbms_rowid.rowid_block_number(rowid)fromtest;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
34
SQL>altersystemdumpdatafile23block34;
Systemaltered.
在udump目录下查看trace文件的内容如下:
Startdumpdatablockstsn:
34file#:
23minblk34maxblk34
buffertsn:
34rdba:
0x05c00022(23/34)
scn:
0x0000.013943f3seq:
0x01flg:
0x02tail:
0x43f30601
frmt:
0x02chkval:
0x0000type:
0x06=transdata
Blockheaderdump:
0x05c00022
ObjectidonBlock?
Y
seg/obj:
0x3ccdcsc:
0x00.13943efitc:
2flg:
Otyp:
1-DATA
fsl:
0fnx:
0x0ver:
0x01
ItlXidUbaFlagLckScn/Fsc
0x010x000a.02e.00000ad70x00800036.03de.18--U-1fsc0x0000.013943f3
0x020x0000.000.000000000x00000000.0000.00----0fsc0x0000.00000000
data_block_dump,dataheaderat0xadb505c
tsiz:
0x1fa0
hsiz:
0x14
pbl:
0x0adb505c
bdba:
0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:
pti[0]nrow=1offs=0
0x12:
pri[0]offs=0x1f9a
block_row_dump:
tab0,row0,@0x1f9a
tl:
6fb:
--H-FL--lb:
0x1cc:
1
col0:
[2]c102
end_of_block_dump
Enddumpdatablockstsn:
34file#:
23minblk34maxblk34
对其中的一些信息做一些解释:
Fb:
H是指行记录的头,L是指行记录的最后一列,F是指行记录的第一列。
Cc:
列的数量。
Nrid:
对于行链接或者行迁移来说的下一个rowid的值。
由上面的dump信息我们可以看出来当前表test是没有行链接或者行迁移的。
然后更新test表,并重新dump出来:
SQL>updatetestseta='test',b='test',c='test',d='test',e='test'wherex=1;
1rowupdated.
SQL>commit;
Commitcomplete.
此时应该有行迁移/行链接产生了。
SQL>altersystemdumpdatafile23block34;
Systemaltered.
在udump目录下查看trace文件的内容如下:
Startdumpdatablockstsn:
34file#:
23minblk34maxblk34
buffertsn:
34rdba:
0x05c00022(23/34)
scn:
0x0000.0139442bseq:
0x01flg:
0x02tail:
0x442b0601
frmt:
0x02chkval:
0x0000type:
0x06=transdata
Blockheaderdump:
0x05c00022
ObjectidonBlock?
Y
seg/obj:
0x3ccdcsc:
0x00.1394429itc:
2flg:
-typ:
1-DATA
fsl:
0fnx:
0x0ver:
0x01
ItlXidUbaFlagLckScn/Fsc
0x010x000a.02e.00000ad70x00800036.03de.18C---0scn0x0000.013943f3
0x020x0004.002.00000ae00x0080003b.0441.11--U-1fsc0x0000.0139442b
data_block_dump,dataheaderat0xadb505c
===============
tsiz:
0x1fa0
hsiz:
0x14
pbl:
0x0adb505c
bdba:
0x05c00022
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x178a
avsp=0x177c
tosp=0x177c
0xe:
pti[0]nrow=1offs=0
0x12:
pri[0]offs=0x178a
block_row_dump:
tab0,row0,@0x178a
tl:
2064fb:
--H-F--Nlb:
0x2cc:
3
nrid:
0x05c00023.0
col0:
[2]c102
col1:
[2000]
74657374202020202020202020202020202020202020202020
20202020202020202020202020202020202020202020202020
…………
col2:
[48]
74657374202020202020202020202020202020202020202020
2020202020202020202020202020202020202020202020
end_of_block_dump
Enddumpdatablockstsn:
34file#:
23minblk34maxblk34
我们不难看出,nrid出现了值,指向了下一个rowid,证明刚刚的update操作使这行记录产生了行链接或者行?
二、行迁移/行链接的检测
通过前面的介绍我们知道,行链接主要是由于数据库的db_block_size不够大,对于一些大的字段没法在一个block中存储下,从而产生了行链接。
对于行链接我们除了增大db_block_size之外没有别的任何办法去避免,但是因为数据库建立后db_block_size是不可改变的(在9i之前),对于Oracle9i的数据库我们可以对不同的表空间指定不同的db_block_size,因此行链接的产生几乎是不可避免的,也没有太多可以调整的地方。
行迁移则主要是由于更新表的时候,由于表的pctfree参数设置太小,导致block中没有足够的空间去容纳更新后的记录,从而产生了行迁移。
对于行迁移来说就非常有调整的必要了,因为这个是可以调整和控制清除的。
如何检测数据库中存在有了行迁移和行链接呢?
我们可以利用Oracle数据库自身提供的脚本utlchain.sql(在$ORACLE_HOME/rdbms/admin目录下)生成chained_rows表,然后利用ANALYZETABLEtable_nameLISTCHAINEDROWSINTOchained_rows命令逐个分析表,将分析的结果存入chained_rows表中。
从utlchain.sql脚本中我们看到chained_rows的建表脚本,对于分区表,cluster表都是适用的。
然后可以使用拼凑语句的办法生成分析所需要的表的脚本,并执行脚本将具体的分析数据放入Chained_rows表中,例如下面是分析一个用户下所有表的脚本:
SPOOLlist_migation_rows.sql
SETECHOOFF
SETHEADINGOFF
SELECT'ANALYZETABLE'||table_name||
'LISTCHAINEDROWSINTOchained_rows;'FROMuser_tables;
SPOOLOFF
然后查询chained_rows表,可以具体查看某张表上有多少的行链接和行迁移。
SELECTtable_name,count(*)fromchained_rowsGROUPBYtable_name;
当然,也可以查询v$sysstat视图中的’tablefetchcontinuedrow’列得到当前的行链接和行迁移数量。
SELECTname,valueFROMv$sysstatWHEREname='tablefetchcontinuedrow';
可以使用如下的脚本来直接查找存在有行链接和行迁移的表,自动完成所有的分析和统计。
acceptownerprompt"Entertheschemaname
tocheckforRowChaining(RETURNforAll):
"
prompt
prompt
accepttableprompt"Enterthetablename
tocheck(RETURNforAlltablesownedby&owner):
"
prompt
prompt
setheadoffserveroutontermonfeedoffverioffechooff
!
clear
prompt
declare
v_ownervarchar2(30);
v_tablevarchar2(30);
v_chainsnumber;
v_rowsnumber;
v_countnumber:
=0;
sql_stmtvarchar2(100);
dynamicCursorINTEGER;
dummyINTEGER;
cursorchainsis
selectcount(*)fromchained_rows;
cursoranalyzeis
selectowner,table_name
fromsys.dba_tables
whereownerlikeupper('%&owner%')
andtable_namelikeupper('%&table%')
orderbytable_name;
begin
dbms_output.enable(64000);
openanalyze;
fetchanalyzeintov_owner,v_table;
whileanalyze%FOUNDloop
dynamicCursor:
=dbms_sql.open_cursor;
sql_stmt:
='analyzetable'||v_owner
||'.'||v_table||'listchainedrowsintochained_rows';
dbms_sql.parse(dynamicCursor,sql_stmt,dbms_sql.native);
dummy:
=dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
openchains;
fetchchainsintov_chains;
if(v_chains!
=0)then
if(v_count=0)then
dbms_output.put_line(CHR(9)||CHR(9)||CHR(9)
||'<<<<
v_count:
=1;
endif;
dynamicCursor:
=dbms_sql.open_cursor;
sql_stmt:
='Selectcount(*)v_rows'||'From'||v_owner||'.'||v_table;
dbms_sql.parse(dynamicCursor,sql_stmt,dbms_sql.native);
dbms_sql.DEFINE_COLUMN(dynamicCursor,1,v_rows);
dummy:
=dbms_sql.execute(dynamicCursor);
dummy:
=dbms_sql.fetch_rows(dynamicCursor);
dbms_sql.COLUMN_VALUE(dynamicCursor,1,v_rows);
dbms_sql.close_cursor(dynamicCursor);
dbms_output.put_line(v_owner||'.'||v_table);
dbms_output.put_line(CHR(9)||'--->Has'
||v_chains||'ChainedRowsand'||v_rows||'Num_Rowsinit!
');
dynamicCursor:
=dbms_sql.open_cursor;
sql_stmt:
='truncatetablechained_rows';
dbms_sql.parse(dynamicCursor,sql_stmt,dbms_sql.native);
dummy:
=dbms_sql.execute(dynamicCursor);
dbms_sql.close_cursor(dynamicCursor);
v_chains:
=0;
endif;
closechains;
fetchanalyzeintov_owner,v_table;
endloop;
if(v_count=0)then
dbms_output.put_line('NoChainedRowsfound
inthe'||v_owner||'ownedTables!
');
endif;
closeanalyze;
end;
/
setfeedonheadon
prompt
三、行迁移和行链接的清除
由于对于行链接来说只能增大db_block_size来清除,而db_block_size在创建了数据库后又是不能改变了的,所以这里对行链接的清除不做过多的叙述了,主要是针对行迁移来谈谈在实际的生产系统中如何去清除。
对于行迁移的清除,一般来说分为两个步骤:
第一步,控制住行迁移的增长,使其不在增多;第二步,清除掉以前存在的行迁移。
众所周知,行迁移产生的主要原因是因为表上的pctfree参数设置过小导致的,而要实现第一步控制住行迁移的增长,就必须设置好一个正确合适的pctfree参数,否则即使清除了当前的行迁移后马上又会产生很多新的行迁移。
当然,这个参数也不是越大越好的,如果pctfree设置的过大,会导致数据块的利用率低,造成空间的大量浪费,因此必须设置一个合理的pctfree参数。
如何去确定一个表上合理的pctfree参数呢,一般来说有两种方法。
第一种是定量的的设定方法,就是利用公式来设定pctfree的大小。
先使用ANALYZETABLEtable_nameESTIMATESTATISTICS命令来分析要修改pctfree的表,然后查看user_tables中的AVG_ROW_LEN列值,得到一个平均行长AVG_ROW_LEN1,然后大量的对表操作之后,再次使用上述命令分析表,得到第二个平均行长AVG_ROW_LEN2,然后运用公式100*(AVG_ROW_LEN2-AVG_ROW_LEN1)/(AVG_ROW_LEN2-AVG_ROW_LEN1+原始的AVG_ROW_LEN)得出的结果就是定量计算出来的一个合适的pctfree的值。
这种方法因为是定量计算出来的,可能不一定会很准确,而且因为要分析表,所以对于使用RBO执行计划的系统不是很适用。
例如:
avg_row_len_1=60,avg_row_len_2=70,则平均修改量为10,PCTFREE应调整为100*10/(10+60)=16.7%。
第二种是差分微调的方法,先查询到当前表的pctfree的值,然后监控和调整pctfree参数,每次增加一点pctfree的大小,每次增加的比例不要超过5个百分点,然后使用ANALYZETABLETABLE_NAMELISTCHAINEDROWSINTOchained_rows命令分析每次所有的行迁移和行链接的增长情况,对于不同的表采取不同的增长比例,对于行迁移增长的比较快的表pctfree值就增加的多点,对于增长慢的表就增加的少点,直到表的行迁移基本保持不增长了为止。
但是注意不要把pctfree调的过大,一般在40%以下就可以了,否则会造成空间的很大浪费和增加数据库访问的IO。
使用上述的方法控制住了当前表的行迁移的增长之后,就可以开始清除之前表上
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 中行 迁移 专题