MYSQL编程：Data Blocks and Freelists (from ixor...

RDBMS并非没有局限性。它们难以扩展，需要大量的资源来配置和维护，比如时间、硬件和人力。同样，它们往往遵循峰值性能模型，这就要求系统按照峰值容量来配置可用性，而不考虑典型的数据使用情况。QuestionsandAnswersDataBlocksandFreelistsTransactionandprocessfreelists26October1998Youmentionedthattherearedifferenttypesoffreelists.Couldyoupleaseexplainabitmoreaboutallthis?Eachsegmenthasatleastamasterfreelist.Thisfreelistisimplementedasalinkedlist.Thesegmentheaderblockcontainsapointertothefirstblockonthefreelist.Intheblockheaderforthatblockisapointertothenextfreelistblockandsoon.Thefreelistpointerintheblockheaderofthelastblockonafreelistisnull.Thefreelistheaderrecordinthesegmentheaderblockalsocontainsapointertothelastblockinthefreelist.
Ifasegmentiscreatedwithmultiplefreelists,thenthesegmentheaderblockalsocontainsafreelistheaderrecordforeachoftheseprocessfreelistsinadditiontothemasterfreelist.
WhenaprocessperformsDMLthatcausesablocktofallbelowthePCTUSEDvalueforthetable/cluster,orentirelyfreesanindexleafblock(however,theindexcaseismorecomplex,becauseblocksonafreelistarenotunlinkedfromtheB*-treestructureuntilanattemptismadetoreusethem),thatblockisputontoatransactionfreelist.Transactionfreelistsaredynamicallycreatedasnecessary,andalsohaveaheaderrecordinthesegmentheaderblock.Thetransactionfreelistheaderalsorecordsthetransactionid.
Whenatransactionneedstoinsert/migratearow,itusesthefirstblockonitstransactionfreelist,ifithaspreviouslyfreedsomeblocks(except,foranindex,becauseblocksplitsalwaysoccurinarecursivetransaction).Otherwiseaprocessfreelistisused,basedontheprocessnumber(PID)modthenumberofprocessfreelists.Iftheprocessfreelistisempty,oriftherearenoprocessfreelists,thenthemasterfreelistisused.
Ifthemasterfreelistisempty,andiftherearesometransactionfreelistsfortransactionsthathavecommitted,thenthetransactionfreelistismarkedasunusedandtheblocksaremergedintothemasterfreelist.Otherwise,thehighwatermarkisraised,initiallyby1blockatatimeforthefirst5datablocksinthesegment,andthereafterbythegreaterof5blocksor_bump_highwater_mark_countblocks(whichdefaultsto0)timesthenumberofprocessfreelistsplus1(forthemasterfreelist),uptothenumberofblocksremainingintheextent.Theseblocksarenewed,whichmeansthatafreebufferisallocatedinthecacheandtheblockheaderisformattedtomaketheblockspartofthesegment.Raisingthehighwatermarkmayinvolvedynamicextension.Oncethemasterfreelistisnotempty,upto5blocksaremovedtothetargetprocessfreelistifany,fromwheretheycanbeused.
Thenumberoffreelistheadersthatcanfitintothesegmentheaderblockislimitedbythedatabaseblocksize.Atleasthalfofthefreelistslotsmustbeavailablefortransactionfreelists.Youcanseetheexactnumberwiththequery
selectkviivalfromx$kviiwherekviitag=ktsmtf;
connectedasSYS(orinternal).Onbusysegments,moretransactionfreeliststhanthatmaybedynamicallycreated,slotspermitting.
Ifasegmentiscreatedwithmultiplefreelistgroups,oneblockafterthesegmentheaderisusedforthefreelistheaderrecordsforeachfreelistgroup.Thereisstillamasterfreelistinthesegmentheader,aswellasamasterfreelistineachgroup.Freelistgroupselectionisbasedontheinstancenumbermodthenumberoffreelistgroups.
Asmentionedpreviously,freelistcontentionoccurswhenmultipleprocessesusingthesamefreelistattempttomodifythedatablockontheheadofthefreelistconcurrently.ItisshowninV$WAITSTATagainstthedatablockclass.V$WAITSTATcanalsoshowcontentionforthesegmentheaderandfreelistblocks.Thisoccurswheremultipletransactioninthesamefreelistgroupneedtoupdatetheirfreelistheaderrecordssimultaneously.Therearevariouswaysofaddressingtheseproblemssuchasrebuildingthetablewithmorefreelistgroups,orincreasing_bump_highwater_mark_count,orthenovelideaoffixingtheapplication.
Todrilldownonwhichsegmentsarecausingdatablockcontention,Isuggestedusingevent10046,level8.Thiscreatesatracefilemuchliketooneproducedbythesql_tracefacility,exceptthatforeacheventwaitalineisprintedtothetracefile.Inparticular,eachbufferbusywaitisrecordedtogetherwiththeP1andP2valueswhicharethedatafileandblocknumberofthewait.Sotofindwhichblocksaprocesshasbeenwaitingon,youjustgrepthetracefileforbufferbusywaitslinesandproduceahistogramofthefileandblocknumbersmostcommonlywaitedfor.Onceyouhavesuspectfileandblocknumbers,youcanrelatethemtoasegmentbyqueryingDBA_EXTENTS.Inthecaseoffreelistcontentiononatableitiscommontohaveseveralhotblocksjustbelowthehighwatermarkforthesegment.
ThereisnoCPUcosttohavingmultipleprocessfreelists,andatrivialcosttohavingmultiplefreelistgroups.IfyouhavetoomanyprocessfreeliststheworstyougetismorefreespacebelowtheHWMthatisnotaccessibletoanyparticularprocess.Youalsogetalowerlimitonthenumberofpossibletransactionfreelistsyoucanhave.

Datablockandfreelistwaits1April1999InqueryingtheV$WAITSTAT,theresultsshowthatdatablockhasacountof797344andfreelisthasacountof0.Ihavereadthatifthedatablockcountishigh,thenIshouldincreasethefreelistsonthetablesthataresubjecttoconcurrentinserts.However,iftherewerefreelistcontention,woulditnotshowupwithacountfortheclassoffreelist?Whatthendoesacountfordatablockmean?Unlessyoucreatemultiplefreelistgroups,youwillneverseewaitsforfreelistblocksinV$WAITSTAT.Thefreelistheadersonlygointoseparatefreelistblockiftherearemultiplefreelistgroups-otherwise,theygointothesegmentheaderblock.V$WAITSTATshowsbufferbusywaitsbythedatablockclassandiftherearenofreelistblocks,thenyoucannotwaitforthem!
However,waitsfordatablockclassblocksarecommonlyduetoinsufficientprocessfreelists.Ifmultipleinsertprocessesusethesameprocessfreelistconcurrently,thentheywillallattempttomodifythedatablockontheheadofthatfreelist.Theassociatedbufferbusywaitsarerecordedasdatablockclasswaitsbecausethatistheclassofblockonwhichtheyaretryingtoestablishabufferlock.
Discretetransactionsholdbufferlocksforthedurationofthetransactionandcanalsocausedatablockbufferbusywaits.

V$WAITSTAT5April1999Imlookingforhelponhowtoresolve"datablock"waitsthatarefoundintheV$WAITSTATtable.Whatcausesthemandwhatsthebestwaytoresolvethis?Themaincauseofthisistwoprocessestryingtomodifytheblockatthesametime.However,italsooccurswhenoneprocessisbusyreadingtheblockintothebuffercache,butitisnotyetthere,andanotherprocessneedstoaccessthesameblock.YoucantellthedifferencebylookingatP3inV$SESSION_WAITifyouareluckyenoughtocatchsomeofthebufferbusywaitsthere.A0or1014valueisthereadcase;allothervaluesarethemodificationcontentioncase.
Yournextstepshouldbetotrytoidentifywhichdatabasesegmentsareinvolved.Onceagain,ifyoucancatchsomewaitsinV$SESSION_WAITthenyoucanusetheP1&P2(file&block)valuestolookupthesegmentnameinDBA_EXTENTS.Ifitisatable,then9timesoutof10allyouneedtodoistorecreatethetablewithmoreprocessfreelists.Onewayofworkingouthowmanyfreeliststocreateistodumpsomeblocksfromthesegmentnotveryfarbelowthehigh-watermarkandtakealookattheinterestedtransactionlistsize.Thepeaknumberofinterestedtransactionsplusoneistheminimumnumberofprocessfreeliststhatyouneed.

Transactionandprocessfreelists18April1999HowcanIdeterminehowmanytransactionfreelistsareavailableandhowmuchspacetheyconsume?DoesitdependonwhethertheFREELISTSparameterisspecifiedintheSTORAGEclause?Otherthanthefreelistheadersinthesegmentheaderblock(orfreelistblockifmultiplefreelistgroupshavebeenspecified),freelistsdonotconsumeanyspaceatall.Blocksarelinkedintoafreelistusingafixedpointerintheblockheader.
ThenumberoffreelistsavailabledependsonyourdatabaseblocksizeandOracleversion.Fora2Kblocksizeunder8.0.5therecanbeupto47processandtransactionfreelistsintotal.Atleast25mustbereservedfortransactionfreelists.Youcanseethisnumberwiththefollowingquery:
selectkviivalfromx$kviiwherekviitag=ktsmtf;

Rowoverhead19April1999IwouldliketoknowiftheAVG_ROW_LENcolumninUSER_TABLESthatispopulatedbytheANALYZEcommandincludestherowdirectoryoverheadof3*UB1foreachrowstoredornot.Therowdirectoryoverheadis2bytesperrow,not3.Thereisalsoa3byteoverheadperrowinthedatasectionoftheblock:1forthetotalrowlength,1forflags,and1forthecolumncount.AVG_ROW_LENdoesnotincludetherowdirectoryoverhead,butdoesincludetheotherthreebytesofrowdataoverhead.
Blocksonfreelists25May1999Howcanyoucalculatethenumberofblocksthatareonafreelist,andhowcanyoutellwhetherornotablockisonafreelistandwhichoneitisoniftherearemorethanone?YoucanuseDBMS_SPACE.FREE_BLOCKStocounttheblocksonthefreelistsforaparticularfreelistgroup,butnotdowntothelevelofdistinctfreelists.Itisnotfeasibletodeterminewhichfreelistaparticularblockison,becausethefreelistsaresinglylinked.Thecodewouldhavetostartatthefreelistheaderblock(segmentheader)andfollowallthefreelistsoneblockatatimetodeterminethis,whichmakesitinfeasibleonperformancegrounds.
Freelistgroups24June1999Yousuggested2freelistgroups.NoweverymanualIknowofrelatesthissettingtomulti-instanceOracleandIbelievethatthegroupisjustontheinstancenumber.Sowhy2?Youhavebufferbusywaitsonthesegmentheaderblocksforyourkeytables.Byhavingtwofreelistgroups,youeffectivelymovethefreelistsoutofthesegmentheaderblockintoaseparatefreelistblock.Therewillbetwosuchfreelistblocks,oneofwhichwillneverbeused.Buttheloadthatyoupresentlyhaveonthesegmentheaderblockwillbedividedbetweenthenewsegmentheaderblockandthefirstfreelistblock.
INITRANSandMAXTRANS29June1999IfoundanOraclebookthatsaidthatthevalueofINITRANSandMAXTRANScanbelowiftheindexentriesarelarge.Couldyoutellmewhy?Whenatransactionmodifiesablock,itneedsexclusiveuseofatransactionslot,fromthatmoment,untilitcommits(orrollsback).Thisisusedtoimplementrowlevellocking.Ifatableblockonlycontainsonerow,thenonlyonetransactionslotcanbeneeded.Similarly,ifanindexblockonlyhas3keys,thenatmost4transactionslotscanbeneeded(theextraoneisforarecursivetransactionforanindexblocksplit).
Althoughthenumberofrow/keysinablocksetsthemaximumforthenumberoftransactionslotsthatmightbeneeded,itismostunusualtorequirethatmanyslots.Indeed,giventhatOraclecandynamicallyallocateextratransactionslotsfrominternalblockfreespaceasrequired,itisquestionablewhetheryoushoulduseanon-defaultINITRANSsettingatall,exceptinunusualcircumstances(suchasparallelDML).Anotherexceptioniswhererows/keysareupdatedinsequencebydistincttransactions.
SoyourauthorisrightaboutINITRANSingeneral.HoweverithasmoretodowithOraclesabilitytodynamicallyallocatetransactionslotsthanthequestionableassumptionofrandomdataaccess.IwouldalsocautionagainsttamperingwithMAXTRANS.

FirstINSERTslow30June1999Themainaccounttableisprocessedduringbatchbyanarrayfetchandupdate.ThefirstINSERTtotheaccounttabledoes55000diskreadsandtakes15minutes.SubsequentINSERTsworknormally.Thissoundslikeafreelistissue.IfyourovernightprocessfirstdropsblockspaceusagebelowPCTUSED,andthenraisesitclosetoPCTFREEagain,youwilltherebycreateaverylongtransactionfreelist.Thefirstinserthastomigratethattransactionfreelisttothemasterfreelist,andunlinktheleadingblocksonthefreelistthatdonothaveenoughfreespacefortherowtobeinserted.Youcancontrolthemaximumnumberofblocksthatwillbeunlinkedfromthefreelistbythefirstinsertwiththe_release_insert_thresholdparameter.Ithinkthisissupposedtodefaultto5blocks,butifmyhunchiscorrect,itseemstobemuchhigherinyourcase.Alternately,youcanreducePCTUSEDtoavoidtheproblementirely.
Strangeenqueuewait1October1999WhywouldOracleincuranenqueuewaitonupdatestatementsissuedbytwodatabasesessionsthattouchtwodifferentrecordsifbothrecordsareonthesameblock,butotherwisesucceedifthestatementsupdatetworecordsfromdifferentblocks?
SESSION_IDTYPEID1ID2MODE_HELDMODE_REQUESTED--------------------------------------------------------------19TX26218271967NoneShare18TX26218271967ExclusiveNone
YoucantellfromthefactthatthewaitingsessionwantsaSHAREmodelockthatitiswaitingforafreetransactionslotintheblock.Theproblemisthatthereisnotenoughfreespaceintheblocktodynamicallyallocateanothertransactionslot.
Impactofmultiplefreelists25October1999Ihavefoundoutthatfreelistsononetablewassetupto10.Generallyyousetfreeliststogreaterthan1(maybe2)forheavyinsertingtables.Myquestionis,Whatkindnegativeimpactdohighfreelistshaveonatableasidefrommorestoragerequirements?Multipleprocessfreelisthaveaminimalimpactontablestoragerequirements.Onaverageeachprocessfreelistwillhaveeither2or3blocks.Thatis,thedifferencebetween2freelistsand10freelistsintermsofstoragespaceislikelytobejust20moreblocksbelowthehighwatermark.
Thefreelistheadersthemselveshavenostoragerequirements,astheygointothefreelistgroupblockorsegmentheaderblock.HavingahighnumberofprocessfreelistsdoeslimitthespaceinthatblockforthedynamiccreationoftransactionfreeliststowhichblocksthatfallbelowPCTUSEDmaybereturned.However,thereisadatabaseblocksizebasedminimumtopreventthecreationoftoomanyprocessfreelists.Thatminimumcanbeseenwith
selectkviidsc,kviivalfromx$kviiwherekviitag=ktsmtf;

Blockheadersize25October1999Idliketoknowthespaceactuallyreservedforrowdatainablock.Isupposeits(totalblocksize-blockheadersize).Inthiscasetheproblembecomesknowingtheblockheadersize.Thecommonblockheadertakes20bytes,andthetailtakes4bytes.Thetransactioncontrolheadertakes24bytes,andthereare24bytesforeachtransactionslot.Thenthereisa14-bytecommonheaderforthedatalayer.Clusterblockshaveatabledirectory,followedbyarowdirectory.Eachslotinthetabledirectorytakes4bytes,andeachslotintherowdirectorytakes2bytes.Tableblocksarethesameasclusterblock,exceptthattherecanonlybeonetableinthetabledirectory.Indexblockshavea16-bytecommonindexblockheaderfollowingthecommondatablockheader,andtheneithera24-bytebranchblockheader,ora32-byteleafblockheader,dependingontheblocktype.Theseindexheadersarefollowedbyarowdirectory,asforclustersandtables.Thereisalsoa3-byterowheaderoneachrowinthedataarea.Sothereyouhaveit...itdepends.
Minimumrowlength17November1999InarecentarticleinOracleProfessionalmagazine,itsaysPCTFREEwillalwaysdefaulttoatleast10percent,evenifitssetlowerthan10percent
andtoconfirmthistheauthorsuggestsCreateanewtablespace;createasingletablewithPCTFREEsetto1orso,andloadshortrowsofjustafewcharacters.Itshouldbeeasytofindtheblockandsee.UseodonUnix.Anyway,thedataappearsfromthebackandmovesforwardintheblock.Iwouldsayifthereisasignificantblankspot(00)atthebeginningofthedata,thenitisindeedleavingbehindsomespace".
Canyouthrowanylightonthis?
Theauthorismistaken.Thisphenomenonthathehasobservedisduetotheminimumrowlengthrequirement.Ifheweretouserowslongerthan6bytesofrealdata,hewouldbeabletofillupablockcompletely.Itisnotnecessarytoresorttoodtoseetheavailablespace,itismuchpreferabletouseanOracleblockdumpbecauseOracleonlylogicallydeletesrowsinblocksbychangingaflaguntilsuchtimeasthespaceisneeded.
Freelists8December1999Willaninsertstatementlockthetablebeinginsertedinto?Ifso,isthereanywaytoavoidthis?Ihaveonetableintowhichalltransactionsareinserting.Ifeelthistableisactingasabottleneckandslowingdownalltheusers.Whatisthebestwaytoapproachthis?SuchatableshouldberecreatedwithmultipleprocessFREELISTS.Togettheappropriatenumberoffreelists,takeablockdumpofsomeoftheblocksinthetable,andusethemaximumnumberofinterestedtransactionlistentriesthatyouseeinanyoneblock.
Minimumrowlength9December1999Iamunawareofaminimumrowlength.Canyouenlightenmesomemorehere?Theminimumrowlengthrequirementisthatatleast11bytesofdataareaarerequiredforeachrow,toallowforthepossibilityofalltherowsintheblockneedingtobemigrated.The11bytesare2forarowdirectoryentry,3fortherowheaderand6bytesfortherowidofthemigratedrowpiece.Thereforenomorethan(dataareasize/11)rowscanbeplacedinanyoneblock.
Freelists11December1999IsthereanybenefitinincreasingthenumberoffreelistsonasingleCPUmachine?Also,isthereanypointindoingsoiftherearenodeletes,becauseallinsertswillgotothehigh-watermark,ratherthanusingfreelists?Yes,IhaveseenbenefitinmultiplefreelistsonasingleCPUmachine.Whilebufferlockdurationisbrief,itisstilllongenoughrelativetoatime-slicetomakeasignificantimpact.Thefrequencyofdeletesisnotafactor.Allinsertsusefreelists,evenwheninsertingatthehigh-watermark.
Freelists27December1999Iamtryingtounderstandtransactionandprocessfreelists.WhatistheprocedurewhenOracleneedstogetablockfromafreelist?Aprocesswillfirstcheckitsowntransactionfreelist,forfreespacereleasedearlierinthesametransaction.Failingthat,itgoestotheprocessfreelist.Iftheprocessfreelistisempty,itispopulatedwithupto5blocksfromthemasterfreelist.Ifthemasterfreelistisempty,thenifpossiblethefreelistforacommittedtransactionwillbemergedwithit-thatisalltheblocksfreedbythattransactionaremovedtothemasterfreelist.OtherwisetheHWMisraisedandtheblocksareformattedandplacedonthemasterfreelist.Thenumberofblocksformattedisnormally5,butif_bump_highwater_mark_counthasbeenset,itisthenumberofprocessfreelistsplus1timesthe_bump_highwater_mark_countsetting,orthenumberofunusedblocksintheextent,whicheverisless.Oncethemasterfreelistispopulated,agroupofupto5blocksismovedtotheprocessfreelist,fromwhereablockcanbetakenforuse.
Freelistcontention5January2000Yousuggestedthatmybufferbusywaitswereprobablyduetofreelistcontention.However,Ihaveanarticlethatexplainshowtocheckforfreelistcontentionwiththequery
select*fromv$waitstatwhereclass=freelist;
anditappearsthatIdonthaveanyfreelistcontentionatall.Whatelsecouldtheproblembe?Thatarticleiswrong.Thatqueryshowscontentiononfreelistgroupblocks.Bydefaulttablesdonthaveanyfreelistgroupblocks,soyouarenotlikelytoseecontentionforthem.Thefreelistheadersarestoredinthesegmentheaderblock.Freelistgroupblocksonlyexistifyouspecifymultiplefreelistgroupswhencreatingthetable.Eventhen,contentiononthefreelistgroupblock(orsegmentheader)isonlycontentionformodifyingthefreelistheader,notthefreelistdatablocksthemselves.
Realfreelistcontentioniswhentwoormoreprocesseslookatthefreelistheader,withoutmodifyingit,andthenattempttoinsertintotothedatablockontheheadofthefreelist.ThecontentionoccursonthedatablockitselfandisshownassuchinV$WAITSTAT.
Ifthatqueryweretoshowasignificantnumberofwaits,thenthatwouldprobablyindicatetoosmallagapbetweenPCTFREEandPCTUSEDonsometables.

INITRANS28January2000InadatabasewithalargeconcurrentuserpopulationanddefaultINITRANSsettings,canthecostofdynamicallycreatingtransactionslotsbesignificant?Thecostofdynamicallycreatingatransactionslotistrivial.Thereisanextrachangevectorintheundorecordandtwomoreintheredorecord,butnoadditionallockingorlatching.Itisbettertokeepdatadensityhigh,thantoattempttoavoidthiscost.
Numberdatatypestorage1February2000Ihavebeendoingsometestingontheinternalrepresentation/storageofthenumberdatatype.WhatIhavefoundsofarpuzzledmeandwouldlikeanexpertsanalysisoftheoutputshownbelow.
DUMP_NOCOL1VSIZE(COL1)LENGTH(COL1)=========================================================================Typ=2Len=3:194,10,2192033Typ=2Len=2:194,93920024
Thequestionis,Whywould920bestoredusing3byteswhile9200usedonly2bytes?Thebasefortheexponentis100(storedinexcess64notationwithahigh-ordersignbit).Thuseachpairofsignificantdigitsisrepresentedinonebyteofthemantissa.9200onlyrequiresonebyteinthemantissa,representing92.Whereas920requirestwo--onerepresenting09,andonerepresenting20.
bufferbusywaits9February2000Wehavesomebigtablesthatallusersinserttoandupdate.Inoticedsomewaitsondatablockandsegmentheaderblocks.IcouldrecreatethetableswithmoreFREELISTS,butIcantfindablockwithanITLcounthigherthan1togetthehighestnumberofconcurrenttransactionsonthetable.IfIincreasethenumberofFREELISTS,doesithelpevenifitisnotashighasthenumberofconcurrenttransactions?IsthereadrawbackinspecifyingmanyFREELISTS?IassumeyouvebeendoingsomeblockdumpstolookattheITLcount.Welldone.Thatmeansthatyouhaveunderstoodalotoftheissuesherealready.IncreasingthenumberofFREELISTSwillnothurt,butinthiscaseitwillprobablynothelpeither.Thereisasecondreasonfordatablockwaitsthatyoushouldconsider,namelythatofreferencedatathatisnotbeingcachedinaKEEPbufferpoolbeingsimultaneouslyrequiredbytwosessions.Youcanidentifythesefromthep3parametervalueofthewaits,whichyoucangetwithourtrace_waits.sqlscript.ThesegmentheadercontentionispossiblycausedbyhavingPCTFREEandPCTUSEDtooclosetogetheronsomeimportanttables.
Freespacenotreused18February2000WehaveastrangeproblemonOracle7.3.4.Foracertaintablewereplace(deleteandinsert)20%oftherowseachday.Butthefreespacefromthedeletesisnotre-usedbytheinserts,sothetableacquiresanewextenteachday.Thistablecontains1LONGcolumn,buttherearenochainedrows(analyzeshows:chained_rows=0andavg_row_len=650).Thedatabaseblocksizeis2K-alittlesmall,Iknow!AlsoPCTFREEis10andPCTUSEDis90soPCTFREE+PCTUSED=100.WhataretheconsequencesofhavingPCTFREE+PCTUSED=100,andisthisthecauseofourproblem?HavingPCTFREE+PCTUSED=100doesnotcausefreespacetogounused,asinyourcase,butitdoesincreasetheriskofbufferbusywaitsagainstthesegmentheaderblockifthetableismodifiedbymultipleconcurrentsessions.However,thatisadifferentproblem.
AssumingdefaultINITRANSyouhave1958bytesavailableinthedataareaperblock.Eachrowtakesanextra2bytesintherowdirectory,so3averagerowswouldrequire1956bytes.Itiseasytoimaginethatsomerowsbeingdeletedaresmallerthanaverage.Soletussaythat1%oftherowsbeingreturnedtothefreelistonlyhave600bytesfree.Whensearchingforablockfromthefreelist,Oraclewilllookatnomorethanthefirst5blocks(bydefault).Anyblockthatcannotfitthenewrow,butisstillbelowPCTUSED,isskippedoverstaysontheheadofthefreelist.Inthiscase,thedeleteoperationwillreturnalltheblocksfromwhicharowhasbeendeletedtotheheadofthefreelist,andtheinsertswillbegintousethoseblocks.However,youwillsoonhave5oftheblockswithonly600bytesoffreespaceontheheadofthefreelist.Thismeansthatmostinsertswillscanthese5blocks,skipthem,andgetafreshblockattheHWM.
Thisispartofwhyitisrecommendedthattherebegreaterthan1averagerowbetweenPCTUSEDandPCTFREE.Inyourcase,IwouldrecommendfirstlythatyoudropPCTUSEDto50%.Thatwillenabletheunusableblocksontheheadofthefreelisttobeunlinked,andyouwillsoonbeusingallthepresentlyunusablefreespace.Longterm,thiswillmeanthattworowsneedtobedeletedfromanyblockbeforeitgoesonthefreelist,butoverallthiswillbeamorespaceefficientsolution.Then,assoonasyouhaveopportunity,thisdatabaseshouldberebuiltwithan8Kor16Kblocksize.
IdontunderstandverywelltheimpactofPCTUSEDinthiscase.HowdoesasmallerPCTUSEDresolvetheproblemofthese5blocks?TheimpactofthePCTUSEDsettingisthatwhentheblockontheheadofthefreelistcannotaccommodatethenewrow,OraclecanunlinkitfromthefreelistifandonlyifitisfullerthanPCTUSED.IfOracleweretounlinkblockswithlessfreespacethanPCTUSED,thenthatblockwouldbemostunlikelytoeverenduponafreelistagain,andoncealltherowshadbeendeletedtheblockwouldbe"lost".
SettingPCTUSEDenablesblockswithrelativelylittlefreespacetobeunlinkedfromthefreelist,sothatblockswithmorespacefurtherdownthefreelistcanbereached.ThisisbecauseOraclewillonlylookatthefirst5blocksbydefaultbeforeformattinganewblockabovethehigh-watermark.Thenumberofblocksisconfigurableviathe_walk_insert_thresholdparameter.Thereisalsoa_release_insert_thresholdparameterthatalsodefaultsto5.Thislimitsthenumberblocksthatwillbeunlinked,ratherthanjustskipped(walked).Sointheworstcase,aninsertmayunlink4blocksandwalk5orviceversabeforegoingtothehigh-watermark.
Thanks.IllchangePCTUSED.WhenIdo,willittakeeffectimmediately,ordoIhavetoreorganizethetable?IfyoujustchangePCTUSEDthesituationwillnotgetanyworse.However,tofixthesituationproperly,youshouldrebuildthetableassoonaspossible(ifnotthewholedatabase).
Tuningtablestorage19February2000Imlookingforatuningutilitytohelpmedetectingblockchainingandfragmentation,tosuggestvaluesforPCTFREEandPCTUSED,andsoon.Therearetwoscriptsonthiswebsitethatmighthelpyou.Thefirst,calledrow_migration.sql,reportsthedegreeofrowmigrationasapercentageofthenumberofrows,foreachtablewithmigratedrows.ItignorestableswithLONGsorpossiblerowlengthsgreaterthantheblocksize,becauseitisnoteasytodistinguishrowmigrationfromchainingforsuchtables.AsuggestedPCTFREEiscalculatedasthefreespaceleftbyonelessthanthenumberofaveragerowsthatcanfitinablock.AnewPCTUSEDissuggestedtoallowforalittlemorethanonerowbetweenPCTFREEandPCTUSED.
Thesecond,calledsparse_tables.sql,reportsthedatadensityforsparsetablesasapercentageofthenumberofrowsthatcouldfitbelowthehigh-watermark.AnewPCTFREEof1isrecommended,ontheassumptionthatthereisnoriskofrowmigrationforsuchtables.AnewPCTUSEDissuggestedasbefore.
Warnings:Boththesescriptsusethestatisticsrecordedinthedatadictionary,soyouneedtohaveanalyzedeverythingrecentlyiftheresultsaretobetrusted.Also,theyarebasedontheassumptionofapseudo-randompatternofinsert/update/delete.TherearesometablesthatneedmoregenerousvaluesforPCTFREEandPCTUSED,becausetheyhaveanunusualDMLpattern.Nevertheless,youcancertainlytakethisasabetterstartingpointthanthedefaultsof10and40!

GapbetweenPCTFREEandPCTUSED23February2000WhydoeshavingtoosmallornogapbetweenPCTFREEandPCTUSEDincreasetheriskofbufferbusywaits?IfyouhaveaPCTFREEandPCTUSEDtooclosetogether,mostinsertsandmostdeleteswillrequirethetargetblocktobeeithertakenofforaddedtoafreelist.Thatinvolvesachangetothesegmentheaderorfreelistgroupblockthatstoresthefreelistheaders.Ifthereishighconcurrencyofinsertsanddeletesonthetable,theywillbesingle-threadedonthebufferlockforthebuffercontainingthecurrentmodecopyofthatfreelistheaderblock.ThiswillbeshownasbufferbusywaitsinV$SYSTEM_EVENT.V$WAITSTATwillclassifythesewaitsasagainstsegmentheaderclassblocks,orfreelistclassblocks,dependingonwhetherthetablehasmultiplefreelistgroups.
Rowmigration15March2000Isthereawaytofindoutwhichrowsarechained?Ihaveagoodnumberofchainedrowsinatable,andIneedtofindoutwhichrowsandwhy.Themaximumsizeoftherowscannotexceed150bytes,andthedatabaseblocksizeis16K.UpdatesoccuronlytoNUMBERcolumns(thevaluesincrease)onadailybasis,andtoCHAR&DATEcolumnsonamonthlybasis.AmIcorrectthatupdatesonNUMBERcolumnsdonotleadtoanincreaseinsize?
AssumingthatablockhasbeenfilledupentirelyandOracleneedstoexpandarowinthatblock,willtherowmigratetoanewblock(asitwoulddefinitelyfindablockinwhichtofittheentirerow)orwillitchainacrosstoanotherblock?IassumethatOraclewouldrathermigratethanchain.Ifitmigratesthenwhydoesitshowthatithassomanychainedrows?Doesitmeanthatmigrationisreflectedasrow-chaining?
UpdatingNUMBERcolumnstolargervaluescanincreasethestorageneeded.WhenOraclereports"chainedrows"itincludesmigratedrowsinthecount.Inyourcase,theyareallmigratedrows,becausetherowlengthislessthantheblocksize,andasyousay,Oraclewillmigrateinpreferencetochaining.
YoucangetthemigratedrowsusingANALYZELISTCHAINEDROWS,butIwouldjustrebuildthewholetablewithasuitablePCTFREEsetting.Youcanuseourrow_migration.sqlscripttogetabettervalueforPCTFREE.

MAXTRANS16March2000WhataretheprosandconsofsettingtheMAXTRANStothemaximumof255?Thatsthedefault,butyouactuallycannotgetthatmany,becausethereisalowerlimitimposedbytheblocksize,unlessyouhavea16Kblocksizeandveryshortrows.WheredidyoufindthisinforegardingMAXTRANS?Ihavecheckedseveralbooksandhadnoluckwithanexactdefinition.Didyoufindthisinfoinaparticularbook,ordiditcomefromawealthofknowledge?Thebitabouttheblocksizelimitisinmybook.IdidnotbothertoexplainhowmanyITLentriescanfit,becauseitisacademic.However,ifyourcurious...
Theminimumrowlengthis11bytes.Toneed255ITLentries,youwouldneed255rowsintheblock.Thatmakes2805bytesofdata.EachITLis24bytes.So255wouldneed6120bytes.Thatwillnotfitinan8192byteblock,withoutregardforheaderseven.
Orputanotherway,a16Kblockwithheadersand255ITLswouldhave10198bytesfordata.Toget255rowsintheblock,therowlengthwouldhavetobelessthan40bytes.

Chainedfetchratio29March2000InmystatisticsIamgetting
chainedfetchratio0.3099PCTFREEtoolowforatable
IsthereanywayIcanfindoutwhichtablehasPCTFREEtoolow?Thiscouldbechaining,ratherthanrowmigration.Ifitisrowmigration,andifyourschemaisanalyzed,thenourrow_migration.sqlscriptmighthelp.
Blockheaders10April2000Imstartingtotakesomeblockdumps,butitishardtoknowwhatImlookingat.Canyouhelpmetounderstandwhatallthoseheaderfieldsareusedfor?Theyseemtotakeupquitealotofspace.Therearethreesetsofheaderinformation,correspondingtothethreelayersoftheOraclekernelthathandledatablocks.Illonlyexplainthefirsttwofornow,becausethethirdistoobigaquestiontoanswer.Firstly,alldatafileblocksarewrittenandreadbythecachelayer(KCB)generallythroughthedatabaseblockbuffercache.Thereisa20-byteheaderand4-bytetailthatarereadandmaintainedbythecachelayer.Thecachelayerheaderisalsocalledthecommonblockheader.
Blocksthatcontainuserdataarefurtherinterpretedandmaintainedbythetransactionlayer(KTB).Theseblockshaveatransactionlayerheaderimmediatelyfollowingthecommonblockheader.Thetransactionheaderiscomprisedofa48-bytefixedheader,includingone24-byteinterestedtransactionslot,andavariablenumberofadditionalinterestedtransactionlistslots.
Theremainderofeachblock,thedataarea,isusedbythedatalayer(KD)tostoreuserdata.Thedataareacontainsaheader,freespaceandrowdata.Theinternalstructureofthedatalayerheaderandrowdataareaisdependentontheblocktype.Clusterblocks,tableblocks,indexbranchblocksandindexleafblockseachhavedifferentstuctures.AsIsaid,Illnotattempttoexplainthestructureofthisareaindetailfornow,butIwilltellyouhowbigtheheadersare.Hereshowitlooksinadiagram.
TheCacheHeaderandTailThisiswhatthecacheheaderandtaillooklikeinablockdump.


buffertsn:0rdba:0x004000f8(1/248)scn:0x0000.0001ab63seq:0x01flg:0x00tail:0xab630601frmt:0x02chkval:0x0000type:0x06=transdata

Thecachelayerheaderiscomprisedofthefollowingfields.
database
blockaddress4bytesThetablespacerelativedatabaseblockaddress(RDBA).Thisisconstructedfromthetablespacerelativefilenumber,andtheblocknumberofthedatablockwithinthatfile.SCN6bytesTheSCNatwhichtheblockwaslastchanged.Thelow-order4bytesarecalledtheSCNbase,andthehigh-order2bytesarecalledtheSCNwrap.sequence1byteAsequencenumberincrementedforeachchangetoablockatthesameSCN.AnewSCNisallocatedifthesequencenumberwraps.flag1byte(Idontknow.)format1byteTheformatofthecacheheaderwaschangedforOracle8.UnderOracle8,thevalueisalways2.Previously,itwas1.checksum2bytesAnoptionalchecksumoftheblockcontents.Whenablockiswritten,thechecksumiseitherclearedorsetdependingonthesettingofthedb_block_checksumparameter.Whenablockisread,thechecksumisverifiedifpresent.blocktype1byteThemostcommonblocktypesare:
1=undosegmentheaderblock
2=undodatablock
5=datasegmentheaderblock
6=datablockunused4bytesUnusedspace,possiblyforbackwardorforwardcompatibility.
Thetailiscomprisedofthelow-ordertwobytesoftheSCNbasefollowedbytheblocktypeandthesequencenumber.Theconsistencyoftheheaderandtailischeckedwheneverablockisread.Thisdetectsmostblockcorruptions,inparticularsplitblocksfromhotbackups.
TheTransactionHeader
Thetransactionheaderconsistsof24bytesofcontrolinformationfollowedbyanarrayofoneormoretransactionslots.Thisarrayiscalledtheinterestedtransactionlist(ITL),andeachslotmaycontainaninterestedtransactionentry(ITE).TheinitialnumberofITLslotsissetbytheINITRANSparameterforthesegment,exceptthatindexbranchblocksonlyhaveoneslot.ClusterandindexsegmentshaveaminimumINITRANSvalueof2.Ifthereissufficientfreespaceinthedataareaoftheblock,additionaltransactionslotscanbedynamicallyallocatedasrequired,uptoalimitdictatedbythedatabaseblocksizeortheMAXTRANSparameterforthesegment.

Thisiswhatthetransactionheaderlookslikeinablockdump.

Blockheaderdump:0x00400035ObjectidonBlock?Yseg/obj:0x2csc:0x00.21ab6itc:2flg:-typ:1-DATAfsl:0fnx:0x0ver:0x01ItlXidUbaFlagLckScn/Fsc0x01xid:0x0000.00f.00000005uba:0x0040000b.0000.12C---0scn0x0000.000000ac0x02xid:0x0002.003.00000027uba:0x00c00004.0019.01--U-1fsc0x0000.00021b7c

Thecontrolinformationinthetransactionheadercontainsthefollowingfields.
objectnumber4bytesTheobjectnumberofthesegmentinOBJ$.cleanoutSCN6bytesTheSCNatwhichthelastfullcleanoutwasperformedontheblock.ITLcount1byteNumberofentriesintheITL.flag2bytesA0indicatesthattheblockisonafreelist.Otherwisetheflagis-.blocktype1byte1=data
2=indexITL
freelistslot1byteIndextothefirstslotontheITLfreelist.nextfreelist
block4bytesTheRDBAofthenextblockonthesegmentfreelist.
Nullifthisblockisnotonafreelist.version1byte(Idontknow.)unused4bytesUnusedspace,possiblyforbackwardorforwardcompatibility.
Eachinterestedtransactionlistentrycontainsthefollowingfields.
transactionid8bytesThisiscomprisedoftherollbacksegmentnumber(2bytes),theslotnumberinthetransactiontableofthatrollbacksegment(2bytes),andthenumberoftimesuseofthattransactiontablehaswrapped(4bytes).undoblock
address8bytesThelocationoftheundoforthemostrecentchangetothisblockbythistransaction.ThisiscomprisedoftheDBAoftherollbacksegmentblock(4bytes),thesequencenumber(2bytes),andtherecordnumberforthechangeinthatundoblock(1byte),plus1unusedbyte.flags1nibble----=transactionisactive,orcommittedpendingcleanout
C---=transactionhasbeencommittedandlockscleanedout
-B--=thisundorecordcontainstheundoforthisITLentry
--U-=transactioncommitted(maybelongago);SCNisanupperbound
---T=transactionwasstillactiveatblockcleanoutSCN
locks3nibblesThenumberofrow-levellocksheldintheblockbythistransaction.SCNorfree
spacecredit6bytesIfthetransactionhasbeencleanedout,thisisthecommitSCNoranupperboundthereof.Otherwisetheleadingtwobytescontainthefreespacecreditforthetransaction-thatis,thenumberofbytesfreedintheblockbythetransaction.TheDataAreaImmediatelyfollowingthetransactionlayerheaderisthe14-bytecommonheaderforthedatalayer(KDB).Theremainderofthedatalayerheaderdiffersforcluster,tableandindexblocks.Clusterblockshaveatabledirectory,followedbyarowdirectory.Eachslotinthetabledirectorytakes4bytes,andeachslotintherowdirectorytakes2bytes.Tableblocksarethesameasclusterblock,exceptthattherecanonlybeonetableinthetabledirectory.Indexblockshavea16-bytecommonindexblockheaderfollowingthecommondatablockheader,andtheneithera24-bytebranchblockheader,ora32-byteleafblockheader,dependingontheblocktype.Theseindexheadersarefollowedbyarowdirectory,asforclustersandtables.
Thebodyofeachblockcontaintherowdataandfreespace.Ingeneralrowsareinsertedfromthebottomoftheblock.However,blockfreespaceisnotcoalescedwhenrowsaredeletedorupdatedsuchthattheoverallrowlengthisreduced.Blockfreespaceisonlycoalescedwhenmorecontiguousspaceisneededforaninsertorupdatethanthatwhichisavailable.
人力成本上的节省，MySQL的用户Spacemonkey实验室的首席执行官MitchPirtle如此表示：“维护MySQL使得你不需要一个年薪15万美元的DBA。

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

连做梦都在想页面结构是怎么样的，绝非虚言

现在是在考虑:如果写到服务器端,我一下搞他个10个存储过程导过去,那久之服务器不就成垃圾箱了吗?即便优化了我的中间层.

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

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

大侠们有推荐的书籍和学习方法写下吧。

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书