MYSQL网站制作之Oracle Freelist和HWM道理切磋及相干性...

MySQL的支持者们却认为，MySQL所保留的功能都是精华，并且是你部署、MySQL学习教程配置和维护这个数据库所必不可少的一些功能。oracle|功能|优化
OracleFreelist和HWM道理切磋及相干功能优化

复兴通信重庆研讨所游波



关头词：Freelist，HWM，存储参数，段，块，dump，优化

文章择要：

近期来，FreeList的主要感化渐渐为OracleDBA所熟悉，网上也呈现一些相干的会商。本文以FreeList为线索对Oracle的存储办理的道理举行较深切的切磋，触及Oracle段区块办理的道理，FreeList算法等。而与FreeList亲切相干的一个重用特征HWM，与sql功能亲切相干，本文也作了道理剖析先容。在道理切磋的基本上，先容了经常使用的存储参数剖析办法，并对所触及的存储优化、HWM的优化和Freelist合作优化作了申明。

缩略语：

ASSM：autosegementspacemanagement

HWM：highwatermark

DBA：datablockaddress

OLTP：onlinetransactionprocess

OPS：oracleparallelserver
1.简介
Oracle的空间办理和存储参数办理是Oracle办理及优化的主要部分。FreeList作为Oracle底层存储参数中的中心参数，其举动体例对Oracle的存储办理及功能优化有严重影响，而现有的Oracle文档对此方面的内容对照缺少。固然Oracle9i已呈现了ASSM，可是作为深切调优对FreeList熟悉还是需要的。

近期来，FreeList的主要感化渐渐为OracleDBA所熟悉，网上也呈现一些相干的会商。本文以FreeList为线索对Oracle的存储办理的道理举行较深切的切磋，触及Oracle段区块办理的道理，FreeList算法等。而与FreeList亲切相干的一个重用特征HWM，与sql功能亲切相干，本文也作了道理剖析先容。在道理切磋的基本上，先容了经常使用的存储参数剖析办法，并对所触及的存储优化、HWM的优化和Freelist合作优化作了申明。

这些道理剖析和功能优化都创建在切磋的基本上，限于篇幅和自己履历大概存在范围、偏向或错误。

为了正确文中部分布局和字段的申明间接用英文形貌。

限于篇幅本文不合错误一样很主要的block布局作更深切的会商，对OPS功能有主要影响的freelistgroup本文也未说起，因而本文在单一freelistgroup下会商。关于block的深切会商、freelistgroup的先容与优化和PCTUSED和PCTFREE等主要参数的优化请拜见参考文献和材料。
2.道理切磋
FreeList作为一个Oracle存储办理的中心参数。其举动体例由Oralce外部把持，我们一样平常不必要把握和把持。可是我们大概会碰到这些成绩，当拔出一笔记录，会拔出到谁人块中？是利用新块，仍是拔出无数据的老块？段是甚么时分扩大的，怎样扩大的？表中只要一笔记录，可是作一次select时期价倒是上千个块，为何？假如我们从道理上分明了Oracle的存储办理体例，对相干这些成绩的办理及功能优化就明晰天然了。
2.1Oracle的逻辑贮存布局
Oralce的逻辑存储布局按表空间，段，区，块举行办理。块是Oracle用来办理存储空间的最基础单位，Oracle数据库在举行输出输入操纵时，都是以块为单元举行逻辑读写操纵的。区由一系列一连的块构成，Oralce在举行空间分派、接纳和办理时是以区为基础单元的。段由多个区构成，这些区能够是一连的也能够是不一连的，一样平常情形下一个工具具有一个段。表空间中包容段和区。

在天生段的时分，会同时分派初始区(initialextents)，初始区的第一个块就格局化为segmentheader，并被用来纪录freelist形貌信息、extents信息，HWM信息等。
2.2freelist观点
freelist是一种单向链表用于定位能够吸收数据的块，在字典办理体例的表空间中，Oracle利用freelist来办理未分派的存储块。Oracle纪录了有余暇空间的块用于insert或Update。余暇空间来历于两种体例：1.段中一切凌驾HWM的块，这些块已分派给段了，可是还未被利用。2.段中一切在HWM下的且链进了freelist的块，能够被重用。freelist具有以下属性

lflag唆使freelist被利用(1)或未利用(0)

lfreelist链的首块的地点DBA(datablockaddress)

lfreelist链的尾块的地点DBA

freelist的信息一般保存在segmentheader中,这里给出segmentheaderblockdump片断加以申明：

nfl=3,nfb=1typ=1nxf=0

SEGLST::flg:UNUSEDlhd:0x00000000ltl:0x00000000

SEGLST::flg:USEDlhd:0x03c00233ltl:0x03c00233

SEGLST::flg:USEDlhd:0x03c00234ltl:0x03c00234

SEGLST::flg:UNUSEDlhd:0x00000000ltl:0x00000000



SegmentHeader:

==>nfl:numberoffreelists/block

==>nfb:numberoffreelistblocks+segmentheader

==>typ:blocktype

==>nxf:numberoftransactionfreelists

SegmentList:

==>flg:flagUSEDorUNUSEDthefreelist

==>lhd:headoffreelist

==>ltl:tailoffreelist



在每个块中都有一个标志flg用来标明块是不是链进了freelist链中。假如这个标记置上，该块中后向指针指向freelist链中下一个块的DBA。假如以后块是链的最开端的块，该后向指针值为0。

这里给出位于freelist上的blockdump的片断

Blockheaderdump:0x03c00235

ObjectidonBlock?Y

seg/obj:0xe2d8csc:0x00.6264c61itc:1flg:Otyp:1-DATA

fsl:1fnx:0x3c00234ver:0x01



==>Seg/objObjectIDindictionary

==>cscSCNoflastblockcleanout

==>itcNumberofITLslots

==>flgO=Onfreelist,-=Notonfreelist

==>typ1=DATA2=INDEX

==>fslITLTXfreelistslot

==>fnxDBAofNEXTblockonfreelist



举例来讲假如有五个块在freelist中，分离为A,B,C,D,E

就会构成segmentheader->A->B->C->D->E--|

同时segmentheader->E
2.3freelist种别
在段中存在3类freelist,即MasterFreelists(MFL),ProcessFreelists(PrFL),和TransactionFreelists.
2.3.1MasterFreeList(公用余暇空间池):
每个段中有一个Masterfreelist，在段创立的时分主动天生。关于每个段来讲都有如许一个余暇空间池,对每一个历程都是公用的，余暇空间就是位于masterfreelist的块上。因为Masterfreelist是公用的，因而当多个历程同时拔出行到统一个段上,masterfreelist合作利用水平就会增添。
2.3.2ProcessFreeLists
为了削减MasterFreelist的合作成绩,引进了另外一种freelist叫做Processfreelists,依据sql命令CREATE/ALTER中的参数FREELISTS创立.如许多个freelist就能够分摊余暇空间的办理，以进步OLTP使用作高度并发拔出和更新事件时空间分派办理的功能。经由过程指定CREATETABLE/CLUSTERorINDEX的子句STORAGE的参数FREELISTS来创立，比方:CREATETABLEflg(....)...STORAGE(...FREELISTS10...)。缺省的FREELISTS为1，此时不会创立Processfreelists。当FREELISTS>=2时，创立Processfreelists。

历程在利用processfreelist是依据历程的OraclePID(ProcessID)来选择的，公式以下:

selectlistentry=(PID%NFL)+1

NFL:FREELISTS界说的Processfreelist个数
2.3.3TransactionFreeLists
当Oracle必要时静态创立。一个TransactionFreeList是一种专门给某一个事件利用的freelist.每一个段最少有16个transactionsfreelists,而且这个值在必要时会增加，直抵达到SegmentHeader块的巨细限定。一个事件只要上面情形下会必要分派一个TxFreeListsentry:块中开释空间时(DELETEorUPDATE)而且还不存在TxFreeListsentry时。
2.4Freelist举动2.4.1FreelistLinkandUnlink操纵
Freelist按落后先出行列(LIFO)体例办理。也就是说最初被link到freelist的块具有开始unlink的时机。

当块中余暇空间增添到年夜于PCTFREE时，块放进freelist中。freelist中的块可用来作update或insert。当块中没有充足的空间用于insert操纵时而且利用空间年夜于PCTUSED，块就会从freelist中移出。

在块在DELETEorUPDATE操纵以后，假如利用空间落到PCTUSED下，块再次link到freelist中。每次块到场freelist时，都是link到链表的头部。

比方：思索段中有120个块编号由1到120。个中有6个块在freelist上并假定HWM是80。（block实践利用DBA编号）

10->24->45->46->65->80-|

如今作INSERT操纵,必要400bytes空间。假定块10上空间不敷，但块24上空间可用。如今数据拔出到块24，如今块24的残剩空间小于该表的PCTUSED。因而块24从freelist链表中移出。PCTFREEandPCTUSED参数的目标就是用来把持数据块从freelist的链表中移进/移出举动的。如今freelists象如许：

10->45->46->65->80-|

然后在统一事件中作DELETE统一个段的数据，使块54和67落到PCTUSED下。如今这些块到场到freelist链中。freelist链如今象如许:

67->54->10->45->46->65->80-|
2.4.2TransactionFreeList算法
扫描segmentHeader块中一切的Txfreelist,反省是不是还没有Txfreelistentry分派给transaction，怎样没有，将寻觅未利用的entry或已提交了事件的空的Txfreelist。假如上述搜刮历程失利,新的entry会在segmentHeader块中Txfreelists地区中启示。假如没有空间来天生,事件就必需守候entry的开释。

segmentheader中的最年夜freelist个数:

BlockSizeMax#Freelists

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

2K24

4K50

8K101

16k204

事件T1开释出来的余暇块(DELETEorUPDATE)的利用:

l当即被T1所重用

l当T1commit后被别的必要余暇块的事件重用，历程举比方下:




2.5HMW观点
HIGHWATERMARK代表一个表利用的最年夜的(toplimit)块。2.1中已提到HIGHWATERMARK纪录在segmentheader中，而且在Oracle拔出数据时一样平常增加5个blocks（并不是老是5个块，详细拜见2.4.2中流程图中HMW增加体例）。

segmentheaderblock中与HWM相干信息申明以下:

EXTENTCONTROL:

ExtentHeader::spare1:0space2:0#extents:13#blocks:1429

lastmap0x00000000#maps:0offset:4128

Highwater::0x020004d0ext#:12blk#:275extsize:475

#blocksinseg.hdrsfreelists:5

#blocksbelow:1229

mapblk0x00000000offset:12

Unlocked

==>spare1:thisfieldisnolongerused(oldinc#,nowalways0)

==>space2:thisfieldisnolongerused(oldts#,nowalways0)

==>#extents:numberofextentsallocatedtosegment

==>#blocks:numberofblocksallocatedtosegment



==>lastmap:addressoflastextentmapblock

0ifextentmapisentirelyinthesegmentheader

==>#maps:numberofextentmapblock

==>offset:offsettoendofextentmap



==>HWMdba:addressofblockathighwatermark

==>ext#:HWMextentnumberrelativetosegment

==>blk#:HWMblocknumberwithinextent

==>extsize:HWMextentsize(inblocks)

==>#blocksinseg.hdrsfreelists:numberofblocksinseg.hdrsfreelist

==>#blocksbelow:numberofblocksbelowHWM

==>mapblkdba:dbaofextentmapblockcontainingHWMextent

is0ifHWMisinthesegmentheader

==>offset:offsetwithinextentmapblock

istheext#ifHWMisinsegmentheader

==>Lockedby:iflockedbyatransaction,thexidisdisplayed



HWM能够说是已利用过的存储空间和未利用过的存储空间之间的分界限。在表利用过程当中，HWM一向向一个偏向挪动，拔出纪录时HWM大概会向增添的偏向挪动，可是删除纪录时HWM其实不会向相反的偏向挪动。拜见2.4.2。下图显现了某个数据段中HWM的地位情形。



.5

HIGHWATERMARK之以是主要是由于它对全表扫描功能的影响。当实行一个全表扫描时，Oracle会读取一切HIGHWATERMARK下的块即便它们是空块。当HIGHWATERMARK下有良多unusedblock时实行全表扫描会增添分外的不用要的I/O。它也会在全局共享区中添补良多良多空块。


3.剖析办法
存储参数基础上属于oracleinternal的器材，因而oralce并没有供应很好的手腕来剖析。可是关于DBA来讲，仍是能够经由过程blockdump和DBMS_SPACE等手腕来猎取部分信息。
3.1提取block和freelist信息
创立dbms_space利用的存储历程show_space

SQL>

createorreplaceprocedureshow_space

(p_segnameinvarchar2,

p_ownerinvarchar2defaultuser,

p_typeinvarchar2defaultTABLE,

p_partitioninvarchar2defaultNULL)

as

l_free_blksnumber;

l_total_blocksnumber;

l_total_bytesnumber;

l_unused_blocksnumber;

l_unused_bytesnumber;

l_LastUsedExtFileIdnumber;

l_LastUsedExtBlockIdnumber;

l_last_used_blocknumber;

procedurep(p_labelinvarchar2,p_numinnumber)

is

begin

dbms_output.put_line(rpad(p_label,40,.)||p_num);

end;

begin

dbms_space.free_blocks

(segment_owner=>p_owner,

segment_name=>p_segname,

segment_type=>p_type,

partition_name=>p_partition,

freelist_group_id=>0,

free_blks=>l_free_blks);



dbms_space.unused_space

(segment_owner=>p_owner,

segment_name=>p_segname,

segment_type=>p_type,

partition_name=>p_partition,

total_blocks=>l_total_blocks,

total_bytes=>l_total_bytes,

unused_blocks=>l_unused_blocks,

unused_bytes=>l_unused_bytes,

last_used_extent_file_id=>l_LastUsedExtFileId,

last_used_extent_block_id=>l_LastUsedExtBlockId,

last_used_block=>l_last_used_block);



p(FreeBlocks,l_free_blks);

p(TotalBlocks,l_total_blocks);

p(TotalBytes,l_total_bytes);

p(UnusedBlocks,l_unused_blocks);

p(UnusedBytes,l_unused_bytes);

p(LastUsedExtFileId,l_LastUsedExtFileId);

p(LastUsedExtBlockId,l_LastUsedExtBlockId);

p(LastUsedBlock,l_last_used_block);

end;

历程已创立。



SQL>createtablet1(achar(1000))storage(freelists3);

表已创立。

SQL>setserveroutputon;

SQL>execshow_space(T1);

FreeBlocks.............................0<==Numberofblocksonfreelist

TotalBlocks............................5<==Totaldatablocksinsegment

TotalBytes.............................20480<==Totalbytesinsegment

UnusedBlocks...........................4<==Totalunusedblocksinsegment

UnusedBytes............................16384<==Totalunusedbytesinsegment

LastUsedExtFileId....................15<==Fileidoflastusedextent

LastUsedExtBlockId...................562<==Blockidoflastusedextent

LastUsedBlock.........................1<==Lastusedblockinextent



PL/SQL历程已乐成完成。

有关show_space的进一步利用技能可参考文献5。以下使用下面失掉的数据对segmentheaderblock举行dump。

SQL>altersystemdumpdatafile15block562;

在udump/ora10792.trc中

***2004-09-0815:29:57.343

Startdumpdatablockstsn:27file#:15minblk562maxblk562

buffertsn:27rdba:0x03c00232(15/562)

scn:0x0000.064560e4seq:0x02flg:0x00tail:0x60e41002

frmt:0x02chkval:0x0000type:0x10=DATASEGMENTHEADER-UNLIMITED



ExtentControlHeader

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

ExtentHeader::spare1:0space2:0#extents:1#blocks:4

lastmap0x00000000#maps:0offset:2080

Highwater::0x03c00233ext#:0blk#:0extsize:4

#blocksinseg.hdrsfreelists:0

#blocksbelow:0

mapblk0x00000000offset:0

Unlocked

MapHeader::next0x00000000#extents:1obj#:60033flag:0x40000000

ExtentMap

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

0x03c00233length:4



nfl=3,nfb=1typ=1nxf=0

SEGLST::flg:UNUSEDlhd:0x00000000ltl:0x00000000

SEGLST::flg:UNUSEDlhd:0x00000000ltl:0x00000000

SEGLST::flg:UNUSEDlhd:0x00000000ltl:0x00000000

SEGLST::flg:UNUSEDlhd:0x00000000ltl:0x00000000

Enddumpdatablockstsn:27file#:15minblk562maxblk562



关于上述块中字段的申明，和相干实验。因为篇幅所限，本文不再枚举。可参考文献7。

对非segmentheader的datablock的dump办法和上述相似。datablock的布局和segmentheaderblock纷歧样，假如必要懂得，可查阅参考文献和材料。


3.2提取HWM信息3.2.1HWM地位
HWM地位按上面的公式盘算:

HWM=useedbyte=TotalBytes-UnusedBlocks

TotalBytes和UnusedBlocks都能够用show_space提取。

还能够经由过程ANALYZEtables失掉HWM信息.DBA_TABLES视图中包括了可用于各表空间剖析的列。个中blocks代表已利用过的块即HWM，empty_blocks代表未利用的空间。
3.2.1HWM下空间使用信息
要对照无数据行的块的块数和HIGHWATERMARK下总块数，能够用上面的公式来展现HWM下未用空间的比例。



p=1-r/h

r:无数据行的块的块数

h:HWM下的块数.

r能够经由过程以下办法取得：

Oracle7:

SELECTcount(distinctsubstr(rowid,15,4)||substr(rowid,1,8))FROMschema.table;

Oracle8andOracle9:

SELECTcount(distinctsubstr(rowid,7,3)||substr(rowid,10,6))FROMschema.table;

假如公式盘算的了局p是0，就不必要对表举行重修。假如了局p年夜于0,应当思索体系情况和使用必要来决意是不是必要总组表。
4.优化4.1手工接纳存储空间
在HIGHWATERMARK以上的块对功能没有影响，可是会泯灭空间。怎样空间巨细是一个思索的成绩，就能够决意接纳空块。

假定表T1的存储表示图如.5所示，利用ALTERTABLE...DEALLOCATEUNUSED语句能够接纳HWM以上的空间。好比：

altertablet1deallocateunused;

接纳后T1的存储表示如.1.1



.1.1

假如在ALTERTABLE...DEALLOCATEUNUSED语句中利用了KEEP关头字，则能够在HWM以后保存指定巨细的余暇空间，好比：

altertablet1deallocateunusedkeep10K;

接纳后T1的存储表示如.1.2



.1.2


4.2删减表
依据3.2.1能够失掉HWM以下块的利用情形。怎样p年夜于时，对全表扫描功能会发生影响，同时也会耗用空间。

假如可以确认使用有优秀的索引几近不会用到全表扫描，那末HIGHWATERMARK以下的空块，只管泯灭了空间，不会对会见发生影响。假如不克不及断定，那末就必要思索删减表。

删减表的操纵将删除表中一切的纪录，而且重置HWM标志。表在删减以后将成为一个空表。

在Oracle中删减表只要以下的两种举措：

1.利用drop语句

先利用drop语句删除全部表，然后再重修这个表。在删除-重修的过程当中，与表相干的一切索引、完全性束缚和触发器城市丧失，而且一切依附于该表的工具城市变成INVALID形态，同时本来争对表的受权也会生效。因而接纳这类体例删除表中的纪录价值太年夜。



2．利用TRUNCATE语句

TRUNCATE语句属于DDL语句，不会发生任何回退信息，而且被当即主动提交。在实行TRUNCATE语句时不会影响到与被删减表相干的任何数据库工具与受权，也不会触宣布中所界说的触发器。别的，在对标举行删减时，HWM将重置，已为表分派的存储空间将被接纳。

在实行TRUNCATE语句时，能够经由过程dropstorage子句和reusestorage子句来把持被开释的区是不是接纳到表空间中。怎样作在线体系的TRUNCATE，不但愿表长工夫锁住，那末可使用reusestorage子句，仅将HWM重置。


4.3freelist优化
freelist合作呈现在多个历程利用统一个freelist并试图同时修正freelist头部数据块时。能够经由过程查询视图v$waitsate的class范例为datablock的纪录来反省合作情形。

发生datablock范例合作的次要缘故原由是多个历程试图同时修正freelist头部数据块。但是，它也会呈现在当历程筹办将块读进buffercathe时，另外一个历程必要会见统一个块。假如能在V$SESSION_WAIT中恰好捕捉bufferbusywaits，就能够经由过程查询V$SESSION_WAIT中的P3来判断是那一类。A0或1014代表读范例，其他的值为修正合作的范例。

下一步必要断定合作触及那些段。假如可以在V$SESSION_WAIT捕捉waits，就能够用P1和P2的值(对应file和block)在DBA_EXTENTS中找到段名。怎样是一个表，就极可能必要重修表来创立更多的processfreelists。一种盘算必要创立几个freelist的办法是dump一些段中靠近HWM的块，反省interestedtransactionlist的个数，详细办法可拜见3.1。interestedtransactions个数的峰值加1就是必要的最小processfreelists的值。

从2.3和2.4能够看出，利用多个freelist大概招致更多的空块未被利用，也大概招致段更快地扩大。假如功能是以后所体贴的重点，那末多freelists能够用来进步并发会见才能，固然会增添一些分外空间的耗用。但是，假如空间利用巨细是起首思索的要素，那末保举利用singlefreelist，使参数FREELISTS=1,固然就不克不及提拔并发事件的功能了。

V$WAITSTAT也可显现其他范例class的合作，包含segmentheader和freelist。呈现在统一个freelistgroup中多个事件必要同时更新它们的freelistheader纪录时。有多种办法来办理这个成绩如重修表接纳更多的freelistgroups,大概增添_bump_highwater_mark_count巨细，大概调剂使用自己。



参考文献和材料：

1.《FREELISTSandFREELISTGROUPS.SCOPE&APPLICATION》

2.《INITRANS,MAXTRANS,FREELISTSandFREELISTGROUPS,PCTFREEandPCTUSED》,MikeAult

3.《FreelistInternals:AnOverviewKnowledge》,XpertforOracleAdministration

4．《Blockdump-8.xDataSegmentHeaderinOracle》

5．《AskTomdbms_space_free_space》，http://asktom.oracle.com

6．《DataBlocksandFreelists》，http://www.ixora.com.au

7．《窃看Datablock的物理布局》，http://www.itpub.net

8．《Oracle9iforwindowsnt/2000数据体系培训教程》,清华年夜学出书社

上述部分文章在我的blog网站http://blog.csdn.net/youbo2004上可找到，关于研讨freelist,freelistgroup和block等有很好的匡助。
MySQL采用双重授权(DualLicensed)，它们是GPL和MySQLAB制定的商业许可协议。

多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?

备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。

比如日志传送、比如集群。。。

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。