2025oracle體系結(jié)構(gòu)指南

oraclesharedpoollargepoolstreamespool、JAVAjavapool、重做redologbufferdatabasebuffercache、in-memorydatacontrolonlineredologSGA共享內(nèi)存結(jié)構(gòu)SGAOracle數(shù)據(jù)庫實例的數(shù)據(jù)和控制信息。這個數(shù)據(jù)包SQL、重做記錄的數(shù)據(jù)。動時，PGAOracleOracle數(shù)據(jù)庫基于與內(nèi)存相關的初始化參數(shù)設置來管理內(nèi)存。例自動優(yōu)化到這個目標內(nèi)存大小，根據(jù)需要在SGA和PGA實例之間重新分配內(nèi)存。自動共享內(nèi)存管理（Automaticsharedmemorymanagement）SGAPGAPGA手工內(nèi)存管理（Manualmemorymanagement）多初始化參數(shù)，以單獨管理SGA和PGA實例中的各個組件。DisplayssummaryinformationaboutthesystemglobalareaDisplayssizeinformationabouttheSGA,includingthesizesofdifferentSGAcomponents,thegranulesize,andfreememory.Displaysdetailedinformationabouthowmemoryisallocatedwithinthesharedpool,largepool,Javapool,andStreamspool.DisplaysPGAmemoryusagestatisticsaswellasstatisticsabouttheautomaticPGAmemorymanagerwhenitisenabled(thatis,whenPGA_AGGREGATE_TARGETisset).CumulativevaluesinV$PGASTATareaccumulatedsinceinstancestartup.Displaysinformationonthecurrentsizeofallautomaticallytunedandstaticmemorycomponents,withthelastoperation(forexample,groworshrink)thatoccurredoneach.DisplaysthecurrentsizesofallSGAcomponents,andthelastoperationforeachcomponent.DisplaysinformationabouttheamountofSGAmemoryavailableforfuturedynamicSGAresizeoperations.Displaysinformationaboutresizeoperationsthatarecurrentlyinprogress.AresizeoperationisanenlargementorreductionoftheSGA,theinstancePGA,oradynamicSGAcomponent.DisplaysinformationaboutdynamicSGAcomponentresizeoperationsthatarecurrentlyinprogress.Displaysinformationaboutthelast800completedmemorycomponentresizeoperations,includingautomaticgrowandshrinkoperationsforSGA_TARGETandDisplaysinformationaboutthelast800completedSGAcomponentresizeDisplaysinformationthathelpsyoutuneMEMORY_TARGETifyouenabledautomaticmemorymanagement.DisplaysinformationthathelpsyoutuneDisplaysinformationthathelpsyoutuneDisplaysinformationaboutthestorageallocatedforallsegmentsintheIMcolumnstore.Note:ThisviewisavailablestartingwithOracleDatabase12cRelease1selectCOMPONENT,sum(CURRENT_SIZE/1024/1024)mbfromv$memory_dynamic_componentsgroupbyCOMPONENT; SYS@orcl>showparameter biginteger896MSYS@orcl>showparametermemory_target bigintegerSYS@orcl>showparameter biginteger896MSYS@orcl>showparametersga_target bigintegerSYS@orcl>showparameter biginteger0SGAPGA v$memory_dynamic_componentswhereCOMPONENTin('PGATarget','SGATarget'); SGA PGA selectCOMPONENT,sum(CURRENT_SIZE/1024/1024)mbfromv$memory_dynamic_componentsgroupbyCOMPONENT;OracleSGASGA_TARGETSYS@orcl>altersystemsetmemory_target=0;ASMMsga_targetpga_aggregate_target，OraccleSGASYS@orcl>showparameter biginteger896MSYS@orcl>showparametermemory_target biginteger0SYS@orcl>showparametersga_max_size bigintegerSYS@orcl>showparameter bigintegerSYS@orcl>showparameter bigintegerSYS@orcl>selectpool,sum(BYTES)/1024/1024mbfromV$SGASTATgroupbypool; java streams shared large SYS@orcl>selectPOOL,NAME,BYTES/1024/1024mbfromV$SGASTATwherepoolisnull; SYS@orcl>selectNAME,BYTES/1024/1024fromV$SGAINFO; FixedSGA RedoBufferCacheSharedPoolLargePoolJavaPoolStreamsPoolSharedIOPoolGranuleSYS@orcl>selectname,value/1024/1024mbfromV$PGASTATwherename='aggregatePGAtargetparameter'; aggregatePGAtarget altersystemsetmemory_target=896M;altersystemsetsga_target=0;altersystemsetSYS@orcl>showparameter biginteger896MSYS@orcl>showparametermemory_target biginteger896MSYS@orcl>showparametersga_max_size biginteger896MSYS@orcl>showparametersga_target bigintegerSYS@orcl>showparameter biginteger v$memory_dynamic_componentswhereCOMPONENTin('PGATarget','SGATarget'); SGA PGA SYS@orcl>selectNAME,BYTES/1024/1024fromV$SGAINFO; FixedSGA RedoBufferCacheSharedPoolLargePoolJavaPoolStreamsPoolSharedIOPoolGranule SYS@orcl>alterset 1）SGA_TARGETSYS@orcl>select((selectsum(value)fromv$sga)-(selectcurrent_sizefromv$sga_dynamic_free_memory))"sga_target"fromdual;2)SYS@orclaltersystemsetsga_target=422670336;3）SGA0altersystemsetstreams_pool_size=0;altersystemsetshared_pool_size=0;altersystemsetlarge_pool_size=0;altersystemsetjava_pool_size=0;altersystemsetdb_cache_size=0;共享服務器：UAGlarge數(shù)據(jù)庫高速緩沖區(qū)DatabaseBufferoracleRedoLogSharedSQLPLSQLSQLPLSQLLarge是SGASQLJavaJavaStreamsORA-00845:MEMORY_TARGETnotsupportedonthisOnLinuxsystems,iftheoperatingsystem/dev/shmmountsizeistoosmallfortheOraclesystemglobalarea(SGA)andprogramglobalarea(PGA),itresultsinthefollowingerror:ORA-00845:MEMORY_TARGETnotsupportedonthisNotethatMemorySize(SGAandPGA),whichsetstheinitializationparameterMEMORY_TARGETorMEMORY_MAX_TARGET,cannotbegreaterthanthesharedmemoryfilesystem(/dev/shm)onyouroperatingsystem.Theworkaround,ifyouencountertheORA-00845error,istoincreasethe/dev/shmmountpointsize.Forexample:#mount-ttmpfsshmfs-osize=5gTomakethischangepersistentacrosssystemrestarts,addanentryin/etc/fstabsimilartothefollowing:shmfs/dev/shmtmpfssize=7g00mount-oremount/dev/shmOracleIn-一、OracleIn-OracleBUFFERCACHE。In-Memory中所存儲的數(shù)據(jù)不是使用傳統(tǒng)行格式而是使用列式。每個列作為單獨的結(jié)構(gòu)存儲到內(nèi)存中，并且數(shù)據(jù)被優(yōu)化用于快速掃描。In-Memorybuffercache二、In-Memory00100M。通常情況下，sysSYSTEM、SYSAUXIMOcolksppinmfora30colksppstvlfora20colksppdescforselectksppinm,ksppstvl,ksppdescfromx$ksppix,x$ksppcvywherex.indxy.indxandksppinmlike'_inmemory_enable_sys';In-MemorySQL>altersystemsetinmemory_size=500m2SGAinmemroy_sizeMEMORY_TARGET參數(shù)，同時也需要檢查SGA_MAX_TARGET。從12.2開始，可以動態(tài)調(diào)整In-MemoryINMEMORY_SIZE參數(shù)值即可。4In-MemorySQLshowparameterinmemory_sizeSQL>startupORA-00838:SpecifiedvalueofMEMORY_TARGETistoosmall,needstobeatleastcd/u01/app/oracle/diag/rdbms/orcl/orcl/trace/tail-200falert_orcl.logSQL>createpfile='/tmp/inittem.ora'fromspfile;SQL>createspfilefrompfile='/tmp/inittem.ora';2：SQL>ORA-00845:MEMORY_TARGETnotsupportedonthistmpfs/dev/shmtmpfsdefaults,size=2048m[root@oracle~]mountoremount/dev/shmIn-Memory將INMEMORY_SIZE初始化參數(shù)設置為0，并重啟數(shù)據(jù)庫SQLaltersystemsetinmemory_size=0scope=spfile;IMcolumnstore1、TABLE級啟用：通過建表或修改表啟用inmemory屬性createtabletest(idnumberinmemory;altertabletestinmemory;2、COLUMNinmemorycreatetableimo_t1(idnumber,namevarchar(12),typevarchar(12));createtableimo_t2(idnumber,namevarchar(12),typevarchar(12));altertableimo_t1inmemory(id)noinmemory(name,type);altertableimo_t2inmemory(name)noinmemory(id,type);coltable_nameformata20colcolumn_nameformatselecttable_name,segment_column_idseg_col_id,column_name,inmemory_compressionfromv$im_column_levelwhereowner='SYS'andtable_namein('IMO_T1','IMO_T2')orderby1,3;inmemoryinmemory自動加載inmemory屬性，除非顯示設置對象為noinmemory。 '/u01/app/oracle/oradata/ORCL/imotest01.dbf'size100Mdefaultinmemory;altertablespaceimotestdefault tablespace_namein('IMOTEST');4v$im_column_levelIMcolumn v$im_column_levelwheretable_name='IMO_T1';altertabletestnoaltertablespaceusersdefaultnoinmemory;四、inmemoryIn-MemoryColumnStoreIn-MemoryColumnStore據(jù)庫對象的優(yōu)先級。啟用了IMO的對象，會按照一定的優(yōu)先級進入SGA中配置好的prioritynoneStoreIn-MemoryColumnStoreSQL>altertablet1inmemorymemcompressforquerylow;TablealteredIn-MemoryColumnStoreSQL>selectcount(*)fromt1;SQL>selectowner,segment_name,inmemory_priority,inmemory_compressionfrom現(xiàn)在可以看到表t1已經(jīng)被加載到In-MemoryColumnStore中了，可以看到prioritylownonelowIn-MemoryColumnStoremediumhighcriticalIn-Memoryprioritymedium在優(yōu)先級為nonelowmediumIn-MemoryStoremediumIn-MemoryColumnStoreprioritynone、lowmediumhighIn-MemoryColumnStorecriticalIn-MemoryColumnStoremediumIn-MemoryColumnStorealtertabletestinmemorypriorityhigh;colownerformata30colsegment_nameformatselectinmemory_duplicatefromuser_tableswheretable_name='TEST';prioritycriticalIn-MemoryColumnStorehighIn-MemoryColumnStoret1ColumnStoreSQL>createtablet1asselect*fromt1;Tablecreatedqueryhight1In-MemoryColumnStoreSQL>altertablet1inmemorypriorityhighmemcompressforqueryhigh;TablealteredSQL>execdbms_inmemory.populate('U1',優(yōu)先級的設置必須應用給整個表或一個表分區(qū)。對表中的不同列集合指定不同的In-MemoryColumnStoreIn-MemoryColumnStoreIn-MemoryColumnStore1objectsIMaltersessionsetcontainer=pdb1;createusertestidentifiedbytest;grantconnect,resourcetotest;alterusertestquota100Monusers;createtabletest.testasselect*fromdba_objects;altertabletest.testinmemory;setautotraceselectcount(1fromtest.test;altertabletest.testinmemory;selectcount(1fromtest.test;colownerformata10;colnameformata25;colstatusformata10;selectowner,segment_namename,populate_statusstatus,inmemory_priority,inmemory_compressionfromv$im_segmentswheresegment_name='TEST';例：execdbms_inmemory.populateSQL>execdbms_inmemory.populate('TEST'imdbms_inmemory.populateprocedureTESTIMSQL>exec通過dbms_inmemory.repopulateprcedure及FORCE=>TRUE選項來加載TEST表到IM FORCE=>TRUE選項強制進行一次完整重新載入，類似于fullrefreshSQLexecdbms_inmemory.repopulate('TEST','TESTFORCE=>TRUE);3priorityclausesqlaltertabletest.testinmemorypriorityhigh|medium|low;六、In-Memorysetautotraceoff;altersessionsetcontainer=pdb1;droptablecreateusertestidentifiedbytest;grantconnect,resourcetotest;alterusertestquota100Monusers;createtabletest.testasselect*fromdba_objects;noinmemorysettimingsetautotracesetpagesize200linesize200coltable_namefora20colinmemory_priorityfora20colinmemory_distributefora20colinmemory_compressionfora20SQL>selectcount(*)fromtest.test;ExecutionPlanPlanhashvalue:|Id| |Name|Rows|Cost(%CPU)| SELECTSORTTABLEACCESS282recursive0dbblockgets1600consistentgets1407physical0redo552bytessentviaSQL*Netto392bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from11sortssortsrowsprocessedinmemorysetautotraceoffaltertabletest.testinmemory; dba_tableswhereTABLE_NAME INMEMORY_DISTRIBUTE FORQUERY poolalloc_bytes/1024/1024used_bytes/1024/1024 1MB64KBINMEMORYV$INMEMORY_AREAsetautotraceselectcount(*)fromTEST.test;ExecutionPlanPlanhashvalue:|Id| |Name|Rows|Cost(%CPU)| 0|SELECT 1(1)|00:00:011|SORT 2 TABLEACCESSINMEMORYFULL|TEST|72363(1)|00:00:01671recursive0dbblockgets2204consistentgets1407physical0redo552bytessentviaSQL*Netto392bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from43sortssortsrows|Id| |Name|Rows|Cost(%CPU)| 0|SELECT1(0)|00:00:011|SORT 2 TABLEACCESSFULL|72363(0)|00:00:01119recursive0dbblock16consistent0physical0redo552bytessentviaSQL*Netto392bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from2sortssortsrowsprocessedinmemorysetautotraceofffromV$INMEMORY_AREA; 1MB64KBsqlconsistentphysicalobject_namesetautotracecreateindexidx_test_OBnameontest.test(OBJECT_NAME);TESTIMcolownerforcolsegment_namefora20colpopulate_statusfora20colbytes_not_populatedfor IMsetautotraceselectcount(*)fromtest.testwhere|Id| | |Rows|Bytes|Cost(%CPU)| 0SELECT135 1SORT135|*2PredicateInformation(identifiedbyoperation-2recursive0dbblock6consistent2physical0redo542bytessentviaSQL*Netto607bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from0sortssortsrowsprocessedSQL>select/*+full(s)*/count(*)fromtest.testswhere|Id| |Name|Rows|Bytes|Cost(%CPU)| 1|SORT 1 35 |*2 PredicateInformation(identifiedbyoperation-inmemory("OBJECT_NAME"='test')1recursive0dbblock10consistent0physical0redo541bytessentviaSQL*Netto607bytesreceivedviaSQL*Netfrom2SQL*Netroundtripsto/from0sortssortsrowsprocessedrowidsetautotraceoffdropusertestcreateusertestidentifiedbytestquotaunlimitedonusers;grantconnect,resourcetotest;createtabletest.diskasselect*fromdba_objects;insertintotest.diskselect*fromtest.disk;SQL>/SQL>/SQL>selectcount(*)fromcreatetabletest.memasselect*fromtest.disk;altertabletest.meminmemory;exec setautotraceupdatetest.diskset|Id| |Name|Rows|Bytes|Cost(%CPU)| UPDATETABLEACCESSrecursivedbblockconsistentphysicalredobytessentviato382bytesreceivedviaSQL*Netfrom1SQL*Netroundtripsto/from32sorts0sorts578904rowsprocessedupdatetest.memset|Id| |Name|Rows|Bytes|Cost(%CPU)| 0|UPDATE 578K| (5)|00:00:01 1| |MEM 2 TABLEACCESSINMEMORYFULL|MEM|578K|2826K| (5)|1330recursivecalls1307061dbblockgets2150consistent22611physical308157500redo195bytessentviaSQL*Netto381bytesreceivedviaSQL*Netfrom1SQL*Netroundtripsto/from63sorts0sorts578904rowsprocessed4insertinsertintotest.diskselect*from|Id| |Name|Rows|Bytes|Cost(%CPU)| 0|INSERT |72363| (1)| 1|LOADTABLECONVENTIONAL|DISK | 2 TABLEACCESS |DISK|72363| 393(1)|1041recursivecalls85856dbblockgets25198consistentgets11176physical90658616redo195bytessentviaSQL*Netto396bytesreceivedviaSQL*Netfrom1SQL*Netroundtripsto/from135sorts0sorts578904rowsprocessedinsertintotest.memselect*from|Id| |Name|Rows|Bytes|Cost(%CPU)| 0|INSERT |72363| (1)| 1|LOADTABLECONVENTIONAL|MEM | 2 TABLEACCESS |DISK|72363| (1)|518recursivecalls172183dbblockgets41857consistentgets21241physical181360948redo195bytessentviaSQL*Netto395bytesreceivedviaSQL*Netfrom1SQL*Netroundtripsto/from32sorts0sorts1157808rowsprocessed5deleteSQL>deletefromtest.diskwhererownum|Id| |Name|Rows|Cost(%CPU)| DELETECOUNTTABLEACCESSPredicateInformation(identifiedbyoperation2-693recursivecalls11490dbblockgets1576consistentgets234physical4450884redo195bytessentviaSQL*Netto392bytesreceivedviaSQL*NetfromSQL*Netroundtripsto/from113sorts0sorts9999rowsSQL>deletefromtest.memwhererownum|Id| |Name|Rows|Cost(%CPU)| DELETECOUNTTABLEACCESSINMEMORYPredicateInformation(identifiedbyoperation-498recursivecalls11435dbblockgets697consistent193physical4450016redo195bytessentviaSQL*Netto391bytesreceivedviaSQL*Netfrom1SQL*Netroundtripsto/from35sorts0sorts9999rowsprocessedselect效果好updateinsertdelete6、In-MemoryColumnStoreIn-MemoryColumnStoreSQL數(shù)據(jù)進行查詢。In-MemoryColumnStorenomemcompressfordmlDMLdmlmemcompressforquerylow將提供最佳的查詢性能。這種壓縮方法比memcompressfordml所壓縮的數(shù)據(jù)要多但比highmemcompressforqueryhighquerylowmemcompressforcapacitylowmemcompressforcapacitylowhighmemcompressforcapacityhighmemcompressforcapacitylowhighmemcompressforcapacityhigh1、nocreatetabletest.testasselect*fromdba_objects;altertabletest.testinmemorynomemcompress;selectcount(*)fromtest.test;colownerfora5colsegment_namefora10colpopulate_statusfora10colinmemory_compressionfor v$im_segmentsvwheresegment_name=2、memcompressforaltertabletest.testinmemorymemcompressforselectcount(*)from v$im_segmentsvwheresegment_name='TEST';3、memcompressforqueryaltertabletest.testinmemorymemcompressforquerylow;selectcount(*)from v$im_segmentsvwheresegment_name=4、memcompressforqueryaltertabletest.testinmemorymemcompressforqueryhigh;selectcount(*)from v$im_segmentsvwheresegment_name='TEST';5、memcompressforcapacityaltertabletest.testinmemorymemcompressforcapacitylow;selectcount(*)from v$im_segmentsvwheresegment_name='TEST';6、memcompressforcapacityaltertabletest.testinmemorymemcompressforcapacityhigh;selectcount(*)from v$im_segmentsvwheresegment_name'TEST'; NOMEMCOMPRESSMEMCOMPRESSMEMCOMPRESSMEMCOMPRESSFORCAPACITYLOW MEMCOMPRESSFORCAPACITYHIGH selectIn-MemoryColumnStoreIn-MemoryColumnStore，那么所有存儲在該表空間中的所有In-MemoryColumnStore?？梢詫⒁粋€數(shù)據(jù)庫對象的所有列或In-MemoryColumnStoreIn-MemoryColumnStoreIn-MemoryColumnStore5通常情況下，創(chuàng)建多列索引可以提高分析與報告查詢的性能。這些索引可能影響DMLIn-MemoryColumnStore

createaltercreatealtercreatematerializedaltermaterialized為了判斷那個數(shù)據(jù)庫對象被加載到In-MemoryColumnStore中，可以查詢v$im_segmentscreatetabletest_inmem(idnumber(5)primarykey,test_colvarchar2(15))啟用表（memcompressforqueryprioritynone）sql>altertablesh.salesinmemory;sql>duct_informationnosql>duct_informationinmemoryprioritysql>createtablespaceusers01datafile'users01.dbf'size40monlinedefaultSQL>altertablespaceusers01defaultnoIn-MemoryColumnStore八、In-MemoryColumnStoreIn-MemoryColumnStore0，這意味著00100M。ColumnStoreCDBCDBIn-MemoryIn-MemoryColumnStoreinmemorynoinmemoryIn-MemoryColumnIn-MemoryColumnStore這個參數(shù)或者設置為一個空字符串來指定對于新表與物化視圖不存在缺省的In-MemoryIn-MemoryColumnStoreinmemoryIn-MemoryColumnStoredisableIn-MemoryColumnStoreIn-MemoryColumnStore大數(shù)量，tricklerepopulationtriclerepopulationIn-MemoryColumnStore。將這個參falseSQLin-memoryIn-MemoryColumnIn-MemoryColumnStoreIn-MemoryColumnStoreIn-MemoryColumnStorealtersystemscope=spfile100M。SQL>showparameter biginteger1GIn-MemoryColumnStorecreatetablealtertableinmemoryIn-MemoryIn-MemoryColumnStore。In-MemoryColumnStoreIn-MemoryColumncreatetablealtertableinmemorynoinmmeoryIn-MemoryColumnStoretest_inmemoryIn-MemoryColumnStoreSQLcreatetabletest_inmemory(idnumber(5primarykey,test_colvarchar2(15))TableinmemorymemcompressforquerypriorityIn-MemoryCduct_informationIn-MemoryColumnStore:SQL>altertableduct_informationinmemory;TableinmemorymemcompressforquerypriorityIn-MemoryColumnStoreforcapacityforcapacitylow:SQL>duct_informationinmemorymemcompressforcapacitylow;Tablealtered.priorityprioritynone。In-MemoryColumnStorehighprioritySQL>duct_informationinmemorypriorityhig