2024Oracle In Memory应用最佳实践

OracleInMemory目录CONTENTSIn目录CONTENTSInMemoryInMemoryInMemoryInMemory太多InDBResultMemoptimizeforMemoptimizeforTimestenIn-MemoryOracleDatabaseInMemoryBufferCache

NewIn-Memory

InMemory

在行存储中处理DML目录CONTENTSIn目录CONTENTSInMemoryInMemoryInMemoryInMemoryInMemory InMemory聚Vector

HASHTable Tableallvaluesan1

InMemoryExample:ComputetotalsalespriceNet=Price+Price*TaxIn-MemoryColumnPrice+Price+Price*

In-MemoryColumnSales_Q1

InMemory

ColumnStore

securefilelobIndex

Fast

FASTSTART

InMemory：ActiveDataGuard1In-

1In-

InMemory与BUFFERCACHE访问相比性能提高7到128与磁盘命中相比性能提高62到3259混合场景提速5到9倍（通过删除多余索引实现全面基于目录CONTENTSIn目录CONTENTSInMemoryInMemoryInMemoryInMemory

DataIn-MemoryColumnIn-MemoryColumn

StagingStaging

3rdNormal

PerformanceSTARPre-Cal

星型模式和预先计算的InMemoryOLAP业务或数据库评估InMemory纯OLTPInMemoryIn-MemorySGA_TARGET=源SGA_TARGET+INMEMORY_SIZE（单实例SGA_TARGET=(源SGA_TARGET+INMEMORY_SIZE)*1.1PGA_TARGET=PARALLEL_MAX_SERVERS*如果无法避免写临时段：_smm_auto_max_io_sizeHashOutoflineCompressionAdvisor&In-Memory内存InMemory对象InMemory

SQL>SELECT*FROMV$INMEMORY_AREAWHERECON_ID=1MB64KBSQL>selectsegment_name,inmemory_size,bytes,populate_statusfrom BYTESBYTES_NOT_POPULATED 0内存InMemory对象InMemory执行计划INMEMORY

SQL>select/*+full(t)*/count(*)fromt_inmemoryt;Planhashvalue:|Id| | |Rows|Cost(%CPU)| 0|SELECT 1| (6)|00:00:01 1|SORT 1 2 TABLEACCESSINMEMORYFULL|T_INMEMORY|5307K| (6)|00:00:01内存InMemory对象InMemory执行计划INMEMORY

62recursive0dbblock18consistent5physical0redo561bytessentviaSQL*Netto410bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from0sortssortsrows内存InMemory对象InMemory执行计划INMEMORYIMscan

SQL>selectname,value,t.con_idfromv$statnamen,v$sesstatwheren.statistic#=andn.con_id=andname='IMscanandsidin(selectsidfromv$mystat); IMscan InMemory与In-Memory是sharenothing架In-Memory的表会打散到RAC数据打散方式包括ROWIDRANGE、In-Memory的DuplicateExadataInMemory与RAC环境如何高效使用InPARALLEL_FORCE_LOCAL设置为PARALLEL_DEGREE_POLICY设置为需要确保N/nodes合理规划表分区并按照PARTITION方式分布In规划业务访问SERVICE对应的节点，按照SERVICE分布InDataGuard&

In-MemoryIn-Memory对OLTP对于网络交互，登录，解析，PL/SQL，复杂函数，数据处理，ETL需要关注由于空间，连接方式，RAC架构以及BUG导致的In-MemoryIn-MemoryRAC目录CONTENTSIn目录CONTENTSInMemoryInMemoryInMemoryInMemoryIn-MemoryColumnHot CoolAuto

In-Memoryinmemory_automatic_level=INMEMORYHotTables

Hot

Cold

12c(18c/19c):Memory访问

WHEREPrice>1000SALESLEDLED

RowStore(BufferSALES20c:从BufferCache

ScanandfilterbyPriceusingcolumnstore

LEDIn-Memory连接处理速度提高5-10

JoinSalesandVectorVector

VectorIn-MemoryExternal

Object

向量处理JSONIn-Memory

分区（internalpartitions）与外部分区（external

CREATETABLEorders(order_idorder_dateDATE,…EXTERNALPARTITION(TYPEoracle_loaderDEFAULTDIRECTORYdata_dirACCESSPARAMETERS(..)REJECTLIMITunlimited)PARTITIONBY(partitionq1_2015valueslessthan(‘2015-04-EXTERNALLOCATIONpartitionq2_2015valueslessthan(‘2015-07-01’),partitionq3_2015valueslessthan(‘2015-10-01’),partitionq4_2015valueslessthan(‘2016-01-In-MemoryIn-MemoryIn-Mem