Oracle 数据压缩Compression 技术 说明.docx
- 文档编号:24671973
- 上传时间:2023-05-30
- 格式:DOCX
- 页数:33
- 大小:28.53KB
Oracle 数据压缩Compression 技术 说明.docx
《Oracle 数据压缩Compression 技术 说明.docx》由会员分享,可在线阅读,更多相关《Oracle 数据压缩Compression 技术 说明.docx(33页珍藏版)》请在冰豆网上搜索。
Oracle数据压缩Compression技术说明
一.官网说明
1.1Oracle11gAdvancedCompression
Oracle11gEE版本中只有:
BasicTableCompression,而AdvanceCompressionFeature需要单独购买。
11gAdvancedCompression有如下特性:
1.CompressionforTableData
--支持了DML语句的compress,下面会重点关注。
2.CompressionforFileData
3.CompressionforBackupData
--包括RMAN和expdp/impdp.对数据泵,compress是inline的,在impdp时不需要进行解压缩,直接导入即可。
4.CompressionforNetworkTraffic
--在DG中使用,Redo在传输中被压缩和解压缩。
关于11gAdvancedCompression,参考Oracle的compression白皮书:
这里我们只看CompressionforTableData。
TheOracleDatabase11gAdvancedCompressionOptionintroducesacomprehensivesetofcompressioncapabilitiestohelpcustomersmaximizeresourceutilizationandreducecosts.ItallowsITadministratorstosignificantlyreducetheiroveralldatabasestoragefootprintbyenablingcompressionforalltypesofdata–beitrelational(table),unstructured(file),orbackupdata.Althoughstoragecostsavingsareoftenseenasthemosttangiblebenefitofcompression,innovativetechnologiesincludedintheAdvancedCompressionOptionaredesignedtoreduceresourcerequirementsandtechnologycostsforallcomponentsofyourITinfrastructure,includingmemoryandnetworkbandwidth.
1.1CompressionforTableData
Oraclehasbeenapioneerindatabasecompressiontechnology.OracleDatabase9iintroducedBasicTableCompressionseveralyearsagothatcompresseddatathatwasloadedusingbulkloadoperations.OracleDatabase11gRelease1introducedanewfeaturecalledOLTPTableCompressionthatallowsdatatobecompressedduringalltypesofdatamanipulationoperations,includingconventionalDMLsuchasINSERTandUPDATE.Inaddition,OLTPTableCompressionreducestheassociatedcompressionoverheadofwriteoperationsmakingitsuitablefortransactionalorOLTPenvironmentsaswell.OLTPTableCompression,therefore,extendsthebenefitsofcompressiontoallapplicationworkloads.
--Oracle是数据压缩技术的先驱,在Oracle9i中就引入了BasicTableCompression。
9i中是使用bulk进行装载时进行压缩。
到Oracle11g,TableCompress特性进一步增强。
ItshouldbenotedthatBasicTableCompressionisabasefeatureofOracleDatabase11gEnterpriseEdition(EE).OLTPTableCompressionisapartoftheOracleAdvancedCompressionoption,whichrequiresalicenseinadditiontotheEnterpriseEdition.
注意:
Oracle11gEE版本中只有:
BasicTableCompression,而AdvanceCompressionFeature需要单独购买。
1.2OLTPTableCompression
Oracle’sOLTPTableCompressionusesauniquecompressionalgorithmspecificallydesignedtoworkwithOLTPapplications.Thealgorithmworksbyeliminatingduplicatevalueswithinadatabaseblock,evenacrossmultiplecolumns.Compressedblockscontainastructurecalledasymboltablethatmaintainscompressionmetadata.Whenablockiscompressed,duplicatevaluesareeliminatedbyfirstaddingasinglecopyoftheduplicatevaluetothesymboltable.Eachduplicatevalueisthenreplacedbyashortreferencetotheappropriateentryinthesymboltable.
--Oracle表压缩使用一个唯一的压缩算法。
该算法用来消除一个databaseblock中的重复值,该重复值甚至可以跨多个列。
被压缩的blocks包含一个叫作symboltable的structure,该structure用来维护压缩的元素。
当一个block被压缩时,字段值第一次该被copy到symboltable中,然后每次的重复值都是被一个shortreference代替,该reference指向symboltable中对应的entry。
Throughthisinnovativedesign,compresseddataisself-containedwithinthedatabaseblockasthemetadatausedtotranslatecompresseddataintoitsoriginalstateisstoredintheblock.Whencomparedwithcompetingcompressionalgorithmsthatmaintainaglobaldatabasesymboltable,Oracle’suniqueapproachofferssignificantperformancebenefitsbynotintroducingadditionalI/Owhenaccessingcompresseddata.
通过这张图,可以清楚的看到compressed的block比notcompressed的block多了一个symbolTable。
正式因为该structure的使用,才使数据占用的空间降低很多。
1.3BenefitsofOLTPTableCompression
Thecompressionratioachievedinagivenenvironmentdependsonthenatureofthedatabeingcompressed;specificallythecardinalityofthedata.Ingeneral,customerscanexpecttoreducetheirstoragespaceconsumptionbyafactorof2xto4xbyusingtheOLTPTableCompressionfeature.Thatis,theamountofspaceconsumedbyuncompresseddatawillbetwotofourtimeslargerthanthatofthecompresseddata.
--压缩率由被压缩的数据性质决定,特别是重复值的数量。
重复值越多,压缩率越高。
一般来说,通过压缩,可以降低2x到4x的空间的消耗。
但是在uncompress时,还是需要增加原来的空间。
ThebenefitsofOLTPTableCompressiongobeyondjuston-diskstoragesavings.OnesignificantadvantageisOracle’sabilitytoreadcompressedblocksdirectlywithouthavingtofirstuncompresstheblock.Therefore,thereisnomeasurableperformancedegradationforaccessingcompresseddata.Infact,inmanycasesperformancemayimproveduetothereductioninI/O
sinceOraclewillhavetoaccessfewerblocks.Further,thebuffercachewillbecomemoreefficientbystoringmoredatawithouthavingtoaddmemory.
--OLTPTableCompression的好处不仅仅是存储空间的节省,另一个重要的影响Oracle直接读压缩数据块的能力,因为不需要读uncompress的block,在重复值越多的情况下,读compress会降低I/O,从而提高性能,并且buffercache因为存储更多的数据而更高效。
1.4MinimalPerformanceOverhead
Asstatedabove,OLTPTableCompressionhasnoadverseimpactonreadoperations.Thereisadditionalworkperformedwhilewritingdata,makingitimpossibletoeliminateperformanceoverheadforwriteoperations.However,OraclehasputinasignificantamountofworktominimizethisoverheadforOLTPTableCompression.Oraclecompressesblocksinbatchmoderatherthancompressingdataeverytimeawriteoperationtakesplace.Anewlyinitializedblockremainsuncompresseduntildataintheblockreachesaninternallycontrolledthreshold.Whenatransactioncausesthedataintheblocktoreachthisthreshold,allcontentsoftheblockarecompressed.Subsequently,asmoredataisaddedtotheblockandthethresholdisagainreached,theentireblockisrecompressedtoachievethehighestlevelofcompression.
--正如上面锁描述,TableCompression对read没有不利的影响。
但对write操作时需要做一些附加的操作,正因如此,对compressblock不适合进行写操作。
Oracle批处理的compress要优于每次写操作时进行压缩。
当一个block初始化时会保持uncompress状态,直到数据接近block控制阀值,当某个事务导致数据达到这个threshold,block里的所有数据都会被compressed。
随后,又更多的数据被添加进来,再次接近阀值,在次被压缩,直到整个block达到最高的compression。
ThisprocessrepeatsuntilOracledeterminesthattheblockcannolongerbenefitfromfurthercompression.Onlytransactionsthattriggerthecompressionoftheblockwillexperiencetheslightcompressionoverhead.Therefore,amajorityofOLTPtransactionsoncompressedblockswillhavetheexactsameperformanceastheywouldwithuncompressedblocks.
上图显示了block不断被压缩的过程。
1.5MigrationandBestPractices
Fornewtablesandpartitions,enablingOLTPTableCompressionisaseasyassimplyCREATEingthetableorpartitionandspecifying“COMPRESSFOROLTP”.Seetheexamplebelow:
CREATETABLEemp(emp_idNUMBER,first_nameVARCHAR2(128),last_nameVARCHAR2(128))COMPRESSFOROLTP;
--对于新表或者分区,可以在创建时指定参数:
COMPRESSFOROLTP.
Forexistingtablesandpartitions,therearethreerecommendedapproachestoenablingOLTPTableCompression:
--对于已经存在的表或者分区,可以使用如下三种方法来实现TableCompress:
1.5.1.ALTERTABLE…COMPRESSFOROLTP
ThisapproachwillenableOLTPTableCompressionforallfutureDML--however,theexistingdatainthetablewillremainuncompressed.
--启动compress后,之后的所有DML操作都会进行compress,但是对于已经存在的数据,会继续保持uncompressed。
1.5.2.OnlineRedefinition(DBMS_REDEFINITION)
ThisapproachwillenableOLTPTableCompressionforfutureDMLandalsocompressexistingdata.UsingDBMS_REDEFINITIONkeepsthetableonlineforbothread/writeactivityduringthemigration.RunDBMS_REDEFINITIONinparallelforbestperformance.
--在线重定义支持以后的DML的压缩,同时也会compress已经存在的数据。
Onlineredefinitionwillclonetheindexestotheinterimtableduringtheoperation.Alltheclonedindexesareincrementallymaintainedduringthesync(refresh)operationsothereisnointerruptintheuseoftheindexesduring,orafter,theonlineredefinition.Theonlyexceptioniswhenonlineredefinitionisusedforredefiningapartition--theglobalindexisinvalidatedandneedstoberebuiltaftertheonlineredefinition.
--在线重定义操作期间会clone索引到临时表。
这个和索引的onlinerebuild类似。
但是onlineredefinition会导致分区表的全局索引失效,需要在操作完成后进行重建。
参考我的blog:
Oraclealterindexrebuild与ORA-08104说明
1.5.3.ALTERTABLE…MOVECOMPRESSFOROLTP
ThisapproachwillenableOLTPTableCompressionforfutureDMLandalsocompressexistingdata.Whilethetableisbeingmoveditisonlineforreadactivitybuthasanexclusive(X)lock–soallDMLwillbeblockeduntilthemovecommandcompletes.RunALTERTABLEMOVEinparallelforbestperformance.
--该方法支持表以后的DML的压缩,同时也会已经存在的数据进行压缩,但是在move期间会对表加上exclusive(X)锁,其他的DML操作会被block。
使用并行来执行ALTERTABLEMOVE会增加操作的效率。
TheALTERTABLE...MOVEstatementallowsyoutorelocatedataofanon-partitionedtable,orofapartitionofapartitionedtable,intoanewsegment,andoptionallyintoadifferenttablespace.ALTERTABLEMOVECOMPRESScompressesthedatabycreatingnewextentsforthecompresseddatainthetablespacebeingmovedto--itisimportanttonotethatthepositioningofthenewsegmentcanbeanywherewithinthedatafile,notnecessarilyatthetailofthefileorheadofthefile.Whentheoriginalsegmentisreleased,dependingonthelocationoftheextents,itmayormaynotbepossibletoshrinkthedatafile.
ALTERTABLEMOVEwillinvalidateanyindexesonthepartitionortable;thoseindexeswillneedtoberebuiltaftertheALTERTABLEMOVE.Alternatively,theuseofALTERTABLEMOVEwiththeUPDATEINDEXESclausewillmaintainindexes(itplacesanexclusive(X)locksoallDMLwillbeblockeduntilthemovecommandcompletes).
--MOVE操作会导致分区或者表上的所有失效,需要操作结束后rebuild索引,或者在执行MOVE操作时加上UPDATEINDEXES,来维护索引。
Belowaresomebestpracticesandconsiderationsfortheca
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据压缩Compression 技术 说明 数据压缩 Compression