MSSQL编程：FREELISTS and FREELIST GROUPS. SCOPE...

线上或者测试环境经常出现的误操作总是让DBA同学那么闹心。application
PURPOSE

=======

ThepurposeofthisarticleistoprovideanunderstandingofFREELISTSandFREELISTGROUPS.SCOPE&APPLICATION

===================

ThisarticleisintendedtobeusedbyDBAstohelpunderstandhowFREELISTSandFREELISTGROUPwork.FREELISTSandFREELISTGROUPS=============================

Thefollowingbulletiniscomprisedofseveralparts:1.IntroductiontoOracledatablockorganization2.FreeListOverview3.FreeListTypes4.ConsiderationsforOptimizingSpaceManagement5.Algorithms6.FreeListsinOracle

1.Introduction:

=============BeforegoingintothedetailsoffreelistmanagementonemustknowhowtheOracledatablocksareorganized.EveryobjectinanOracledatabasewhichoccupiesspaceisassociatedwithasegment.Asegmentisasetofextentsthatcontainsallthedataforaspecificlogicalstoragestructurewithinatablespace.Thereare4segmenttypes:opermanentdatasegment(table,cluster)oindexsegmentorollbacksegmentotemporarydatasegmentEachsegmentismadeupofsmallerstorageunitscalledextents.Anextentisastorageunitcomposedofcontiguousblocks.Eachsegmentisinitiallycreatedinthedatabasewithatleastoneextent(controlledbyparameterMINEXTENTS).Thoughempty,thespaceisreserved.Whentheinitialspaceisfull,anewextentisdynamicallyallocated.ThenewextentsizeisdeterminedbytheparametersNEXTandPCTINCREASE.ThedynamicallocationsstopwhenthenumberofextentsreachesthevalueofparameterMAXEXTENTS.ThefirstblockofthefirstextentofeachsegmentisreservedbyOracletostoresysteminformationanditiscalledthesegmentheader.TheSegmentHeadercontains:oTheExtentsTableoTheFreeListsdescriptorsoTheHighWaterMark(HWM)TheHWMmarksalimitinthesegmentwhereeachblockbelowis"inuse",andeachblockabovehasneverbeenused.TheHWMalwaysincreasesinsize;Itisresetto"zero"(positiontothestartofthesegment)whenaTRUNCATEisissued.

2.FreeListOverview

==================StartingwithV7.3,itisnowpossibletocreateasegmenteitherwithalimitednumberofextentsorwithanunlimitednumberofextents.Toaccommodatethis,fivenewblocktypesareintroduced:14:UnlimitedRollbackSegmentHeader15:UnlimitedDeferredRollbackSegmentHeader16:UnlimitedDataSegmentHeader17:UnlimitedDataSegmentHeaderwithFLgroups18:ExtentMapBlockForeachdatasegment,OraclekeepstrackofblockshavingavailablefreespaceforincomingINSERTorUPDATE.FreeSpacecomesfrom2ways:1.AllblocksbeyondtheHWMthatareallocatedbutnotused.2.AllblocksbelowtheHWMlinkedinafreelist,andcandidatestobereused.AFreeListisconstitutedof:oFlagtoindicateifthefreelistisused(1)orunused(0)oDBAoftheblockheadoftheFreeListChain,oDBAoftheblocktailoftheFreeListChain.IneachDataBlockthereisaflagtoindicateiftheblockislinkedinaFreeListchain.IftheflagissettheblockwillalsopointtotheDBAofthenextblockinthefreelistchain.Azerovaluemeansthatthecurrentblockisthelastblockinthechain.

3.FreeListTypes:

================

TheMasterFreeListorCommonFreespacepool:===============================================Thereisonlyoneforeachsegment.Itisimplicitlyallocatedatsegmentcreationtime.Foreachdatasegmentthereisapool,commonforeachprocess,ofblockscontainingfreespaceavailableforincominginsertscalledtheMasterfreelist.Everyprocesshasaccesstothiscommonpool.So,thelevelofcontentionincreaseswhenseveralprocesssimultaneouslywanttoinsertrowsinthesamesegment.TheblocksofinitialextentsallocatedatsegmentcreationtimeandofthenewonesdynamicallyallocatedarelinkedtotheMasterfreelist.

TheProcessFreeLists:=======================ToreducecontentionproblemsontheMasterFreelist,anotherleveloffreelistsisavailablewhichiscalledtheProcessfreelists,ExplicitlycreatedwhentheFREELISTSparameterhasbeenspecifiedinaCREATE/ALTERcommand.PartitioningtheFreeSpaceinmultiplefreelistsisusedessentiallytoimprovetheperformanceofOLTPapplicationswithahighdegreeofconcurrenttransactionsperforminginsertsorupdatesrequiringnewspaceallocation.ParameterFREELISTShasbeenaddedintheSTORAGEclauseofaCREATETABLE/CLUSTERorINDEX.Example:CREATETABLEflg(....)...STORAGE(...FREELISTS10...);Thenumberofprocessfreelistsmustbeclosetothemaximumnumberofconcurrentprocessperformingsimultaneouslyinsertsinthesegment.Itisimportanttonotethatthisimprovestheinsertsperformance,butwastesdiskspaceusage.EachprocessismappedtoaProcessfreelistbyitsOraclePID(ProcessID):Processfreelistentry=(P%NFL)+1whereP:OraclePIDoftheprocess(indexinV$PROCESS),andNFL:ProcessfreelistsnumberasdefinedbyparameterFREELISTSThe

TransactionFreeLists:===========================ImplicitlyallocatedbyOracleasneeded.ATransactionFreeListisafreelistdedicatedtoonetransactionandonlyone.Thereareaminimumof16transactionsfreelistspersegment,andthisnumberincreasesaslongasitisnecessary,untilitreachesthelimitoftheSegmentHeaderblocksize.AtransactionneedstoallocateaTxFreeListsentrywhen:oItreleasesspaceinablock(DELETEorUPDATE)oAndifithasnotalreadyallocatedone.

4.ConsiderationsforOptimizingSpaceManagement:===============================================ThetwoparametersPCTFREEandPCTUSEDoftheStorageClauseinaCREATE/ALTERstatement,areusedtocontrolandoptimizespacemanagementwithinadatasegmentblock(table,cluster).PCTFREEspecifiesthepercentageofadatablocktobereserved(keptfree)forpossibleupdatestorowsthatalreadyarecontainedinthatblock.Afterablockbecomesfull,asdeterminedbyPCTFREE,OracledoesnotconsidertheblockisfortheinsertionofnewrowsuntilthepercentageoftheblockbeingusedfallsbelowtheparameterPCTUSED.Itisadvisedthatthespacecalculatedas(100%-(PCTFREE+PCTUSED))begreaterthanthemaximumsizeofatablerow.5.Algorithms:===========AblockisputonfreelistifthefreespaceintheblockisgreaterthanthespacereservedbyPCTFREE.Blockslinkedinafreelistareavailableforfutureupdatesorinserts.Ablockisunlinkedfromafreelistifthefreespaceintheblockisnotenoughtoallowanewrowinsert,andifthepercentageoftheusedspaceremainsabovePCTUSED.AblockisrelinkedtoafreelistifafterDELETEorUPDATEoperations,thepercentageoftheusedspacefallsbelowPCTUSED.Eachtimeablockisaddedtoafreelist,itislinkedattheheadofthechain.Example:~~~~~~~~Attimet0,2blocksareonfreelist:datablocks1and3:FL:B1->B3Attimet1,deletionsaredoneonblockB2suchastheblockhastoberelinkedtoFreelist:FL:B2->B1->B3AdatablockisputonFreelistonlyifitisnotalreadylinked.TransactionFreeListAlgorithms:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~TheTxfreelistallocationprocessstartsbyscanningallTxfreelistsallocatedinthesegmentHeaderblockandcheckingifaTxfreelistentryhasnotalreadybeenallocatedtothetransaction.Else,itlooksforanunusedentryoranemptyTxfreelistbelongingtoa"committed"transaction.Ifthesearchfails,anewentryintheTxfreelistsareaisallocated.WhenthereisnochancetoexpandtheTxFreelistsarea,theunfortunatetransactionhastowaitforanentryrelease.Theselectedentryisdeterminedby(PID%Txfreeliststotalnumber).PIDistheOracleprocessId.ThewaitisdonebyrequestinginSmodetheTXenqueueheldbythetransactionTowningtheTxfreelistentry.ThewaitwillendwhenTcommits.ThespacefreedinablockbyatransactionT1(DELETEorUPDATE):oisimmediatelyreusablebyT1,owillbereusableforothertransactionsonlyafterT1commitsandwhenlookingforspaceinthisblock.Example:~~~~~~~~5.1TransactionT1deletessomerowsintableT.Theseoperationsreleasesomespaceinblock10inthesegment.ThepercentageoftheremainingusedspacefallsbelowPCTUSED:blockcanbereusedforincominginserts,itisputonfreelist.Todothis,TransactionT1needstoallocateanentryintheTransactionfreelistsareaintheSegmentHeaderblock,thenlinkstheblock10inthecorrespondingfreelist.5.2.TransactionT2wantstoinsertanewrowintableT.ThespacefreedbyT1,inblock10,cannotbereused.AssumethatT2doesnothaveaTxfreelistallocatedinthesegment(becauseT2hasnotreleasedspaceinthesegment)5.2.1T2startswalkingonitsProcessFreeList(PrFL)lookingforafreeblock.(Letsassumethatthereare3blocksonthePrFLandtheydonthaveenoughfreespaceT2needs.)5.2.2Thefirstblockfailsthecheck.Theblockmustbeexchanged(movetothenextblockonthelist)withanotherone.ThisblockhastobeunlinkedfromthefreelistiftheusedspaceintheblockgoesbeyondthespacedefinedbyPCTUSEDor,ifthe"UNLINKcounter"isbelowaparticularthreshold.(Thedefaultvalueis5).Theideabehindthisisnottokeepblocksthatfailduringfreespacesearchattheheadoffreelists.TheUNLINKactionisdoneonlyifthecurrentblockisheadofthefreelistchain.5.2.3AfteralltheblocksonthePrFLarecheckedandnoblockisfound,theblockselectionintheprocessfreelistisstopped5.2.4OracletriestomoveblocksfromMasterfreelisttoProcessfreelist.ThisoperationiscalledFreeListsMerge.Thenumberofblockstomoveisdefinedbyaconstantanditis5.Searchwillcontinuefromstep5.2.1NotethatatthispointOracleWILLNOTlookattheotherPrFLeventhoughtheremightbeemptyblocksonthoselists.Thiswillcausesomespacetobewastedtemporarily.5.2.5(AssumethattherearenomoreblocksontheMasterFreeList)OracletriesnowtogetblocksfromtheotherTxfreelists(committed).TheTxfreelistsentriesintheheaderblockarescanned,lookingfornotemptyand"committed"entries.Forthoseentries,ALLtheblocksaremovedtotheMasterFreelist.Then,onlyapart(5)oftheblocksinMasterfreelistaremovedagaintoprocessfreelist.IfthereareblocksmovedfromtheTxFltotheMsFL,gotostep2.15.2.6(If2.5fails)OracletriestobumpuptheHWM.Toreducecontentiononsegmentheaderblock,OraclebumpsuptheHWMbymblocksatatime.Thenumbermis:1:iftheblockdoesnothavetogoonfreelist,1:ifweareontheinitialextent(0)andtheHWMis<=4(smalltable)min(5,unusedblocksintheextent):before7.2min(5*(NFL+1),unusedblocksintheextent):since7.2.Intheexample,weassumeHWMisattheendoftheextent.5.2.7Newextentallocation.ThenewblocksaremovedtoMasterfreelist.Freeblocksearchcontinuesfromstep2.45.3TransactionT1wantsnowtoinsertanewrowintableT.First,itlooksforavailablespaceinblockslinkedtoitstransactionfreelist.Theblock10hasavailablespace,andthefreespaceinitisenoughtothespacerequiredbythenewrow,block10isselected.ThepercentageoftheusedspaceintheblockgoesupabovePCTUSED,theblockhastobeunlinkedfromthetransactionfreelist.6.FreelistsGroups:=================Inmulti-instances(OracleParallelServer),toreducecontentiononthesegmentheaderblock,whichcontainsthefreelistsdescriptorsofthesegment,itispossibletoallocateotherspecialblockstostorefreelistsdescriptors.ThesegroupsarecalledtheFreeListsGroups.FREELISTGROUPSisintroducedinSTORAGEclauseofaCREATETABLE/CLUSTERorINDEX.Example:~~~~~~~~CREATETABLEflg(....)...STORAGE(...FREELISTGROUPS2FREELISTS10...);EachfreelistgroupisassociatedwithaspecialblockcalledtheFreeListBlock.Theseblockswillbeallocatedrightafterthesegmentheader.So,ifandobjectiscreatedwith2freelistgroups,thefirstblockofthefirstextentwillbethesegmentheaderblockandthesecondandthirdblockswillbethefreelistblocks.TheseblocksstoreaMasterfreelist(ofthegroup),andasmuchprocessfreelistsasspecifiedbyparameterFREELISTS.TherestoftheblockdedicatedtoTransactionsfreelists.InthesegmentheaderblockofanobjectwithfreelistgroupsthereisonlyonefreelistcalledTheSuperMasterFreelistortheSegmentMasterfreelist.Thefreelistgroupsreducecontentiononthesegmentheaderblock,andreduceinter-instance"pinging",atthecostofexcessivediskspaceconsumption.ThealgorithmtomapaFreelistGroup,definedonasegmentS,toaprocessPofaninstanceI,isthefollowing:where:NFB:numberofFreelistGroupsdefinedonsegmentS,P:OraclePIDoftheprocess(fromV$PROCESS),I:INSTANCE_NUMBERparameter(ifsetinINIT.ORA)orparameterTHREADvalueMAXINST:maximumofinstancessharingthesamedatabase(MAXINSTANCESparameterofCREATEDATABASE).InanonOPSenvironmentorOPSandSingle-Instanceenvironment:Freelistgroupis:(P%NFB)+1Inamulti-instanceenvironment(OPS):oIfNFB<=MAXINST:Freelistgroupis:((I-1)%NFB)+1oIfNFB>MAXINST:RANGE=NFB/MAXINST;INITIAL=(RANGE*(I-1))+1REST=NFB-(RANGE*MAXINST)if(I-1)<RESTFreelistgroupis:INITIAL+(P%(RANGE+1))if(I-1)>=RESTFreelistgroupis:INITIAL+REST+(P%RANGE)TRUNCATINGATABLE:~~~~~~~~~~~~~~~~~~~Forthegiventableandallindexassociatedtoit,theTRUNCATEoperationdoesthefollowingtasks:1.Getexclusiveaccesstothesegmentheader.IfthesegmentheaderislockedprocesshastowaititsreleasebyrequestingaTXlockinSharedmode.2.Lockthesegmentheader.3.Flushallblocksbelongingtothissegmentinthebuffercachetodisk.4.Clearallthefreelistsinthesegmentheaderblockandinallfreelistgroupsblocks(iffreelistgroupsaredefined).ThebeforeimageofallthechangesarerecordedintheRollbacksegment.5.Truncatethesegment:theHWMreturnstoitsinitialposition(extent0andblock0iftableor1ifindex).Thebeforeimageofthechangeisrecordedintherollbacksegment.6.TrimalltheextentsbeyondMINEXTENTS.7.IfOPS,flushondiskthesegmentheaderblockandfreelistgroupsblocks.NOTE:Performancevs.Space===========================Ascanbeseenfromthealgorithmsabove,usingmultiplefreelistsmaycausesomeemptyblockstogounused,causingthesegmenttoextend.Ifperformanceiscritical,multiplefreelistscanbeusedtoimproveconcurrentaccess,possiblyattheexpenseofadditionalspaceused.However,ifspaceistheprimaryconsideration,thenasinglefreelistshouldbeused,leavingFREELISTS=1,withouttheperformancebenefitsforconcurrenttransactions
优化的SQL查询算法，有效地提高查询速度

SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

如果，某一版本可以提供强大的并发响应，但是没有Oracle的相应版本稳定，或者价格较贵，那么，它就是不适合的。

始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。