Oracle-性能调整内存篇课件

Oracle性能调整Oracle内存优化SGAStreampoolSharedPoolDataDictionary

CacheLibraryCacheServer

processPGADatabase

BufferCacheJavaPoolLargePoolRedoLog

Buffer内存优化系统全局区数据缓冲区共享池日志缓冲区程序全局区PGAUGA内存最基本的优化方法增加内存的大小增加内存是最简单的优化方法增加内存不是万金油增加内存到足够大，内存对性能的增益不是线性递增的内存增加到多大合适？增加到性能增益曲线的拐点或者平滑点平衡性能和资源的需求两种方法判断内存大小：尝试不断的增大使用9i的内存大小建议数据高速缓存区的大小建议数据缓存区的参考特性可以启用或者禁用统计信息的收集,以预测不同缓存大小时数据库的性能.这些统计提供的信息可以帮助DBA根据系统的负载量定义合适的数据缓存区大小.数据缓存区的参考通过DB_CACHE_ADVICE参数启用.5数据高速缓存区大小的建议值数据缓存区的参考值被收集到V$DB_CACHE_ADVICE视图.这是视图包含了预测的在不同缓存大小的情况下,估计的物理读次数.行里也计算了物理读因子(physicalreadfactor),这个值来源于实际的缓存区在周期测量期间估计的读数与实际的读数之比.6数据高速缓存区的内存调整建议长期稳定运行状态下(db_cache_advice=ON)columnsize_for_estimateformat999,999,999,999heading'CacheSize(m)'columnbuffers_for_estimateformat999,999,999heading'Buffers'columnestd_physical_read_factorformat999.90heading'EstdPhys|ReadFact'columnestd_physical_readsformat999,999,999heading'EstdPhys|Reads'SELECTsize_for_estimate,buffers_for_estimate, estd_physical_read_factor,estd_physical_readsFROM V$DB_CACHE_ADVICEWHEREname='DEFAULT'AND block_size= (SELECTvalueFROMV$PARAMETER WHEREname='db_block_size') AND advice_status='ON';共享池的内存调整建议Select SHARED_POOL_SIZE_FOR_ESTIMATE"poolsize", SHARED_POOL_SIZE_FACTOR"poolsizefactor", ESTD_LC_SIZE"estdlcsize", ESTD_LC_MEMORY_OBJECTS"estdlcmemobj", ESTD_LC_TIME_SAVED"timesaved", ESTD_LC_TIME_SAVED_FACTOR"timesavedfactor", ESTD_LC_MEMORY_OBJECT_HITS"memobjhits"from v$shared_pool_advice;poolsizepoolsizefactorestdlcsizeestdlcmemobjtimesavedtimesavedfactormemobjhits

40.52043895961139726348.62043895961139726356.72043895961139726364.82043895961139726372.92043895961139726380120438959611397263881.120438959611397263961.2204389596113972631041.3204389596113972631121.4204389596113972631201.5204389596113972631281.620438959611397263……PGA的内存调整建议Select PGA_TARGET_FOR_ESTIMATE"pgasize", PGA_TARGET_FACTOR"pgafactor",BYTES_PROCESSED"bytesproc", ESTD_EXTRA_BYTES_RW"estdbyteRW", ESTD_PGA_CACHE_HIT_PERCENTAGE"setdpgahit", ESTD_OVERALLOC_COUNT"estdoveralloccnt"from v$pga_target_advice;pgasizepgafactorbytesprocestdbyteRWsetdpgahitestdoveralloccnt

12582912.5631649280353075299318874368.756316492800100225165824163164928001002301987841.263164928001002352317441.463164928001002402647041.663164928001002452976641.8631649280010015033164826316492800100175497472363164928001000……数据高速缓冲区DB_BLOCK_SIZEDB_BLOCK_BUFFERSDB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDatafilesSGADBbuffercacheDirtylist....LRUlist....DBWnServer在Oracle9i中定义数据缓存区的大小数据缓存区由多个独立的子缓存区和多种块大小的缓存区组成.DB_BLOCK_SIZE

参数决定了主块大小,这被用于SYSTEM表空间.下列参数决定由主块大小决定的各个子缓存区的大小:DB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZE11不建议使用的参数这三个参数已经不建议使用,只是为了保持向后兼容性而保留.DB_BLOCK_BUFFERSBUFFER_POOL_KEEPBUFFER_POOL_RECYCLE这些参数不能与动态参数混用.12SGADBbuffercacheDirtylist....LRUlist....DBWnServerLGWR管理数据库的数据高速缓存区DBbuffercache1....342Datafiles52调优目的和技术调优目的:服务器能够在缓存区内找到数据OLTP系统90%的命中率(hitratio)诊断测量缓存区的命中率V$DB_CACHE_ADVICE调优技术:增大缓存区的大小使用多缓存池将表存放在缓存中V$SYSSTAT诊断工具V$SESSTATSGALRUlistDirtylistBuffercacheKeepbufferpoolRecyclebufferpoolV$BUFFER_POOLDB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEV$SYSTEM_EVENTV$SESSION_WAITV$BUFFER_POOL_STATISTICSSp_m_n.lst测量缓存区的命中率SQL>SELECT1-(phy.value–lob.value–dir.value)

/ses.value"CACHEHITRATIO"2FROMv$sysstatses,v$sysstatlob,

3v$sysstatdir,v$sysstatphy3WHERE='sessionlogicalreads'4AND=‘physicalreadsdirect'5AND='physicalreadsdirect(lob)'6AND='physicalreads';Statistic Total Per Per Trans Logon Second

physicalreads 15,238 13.0 15,238.0physicalreadsdirect 863 0.7 863.0Physicalreadsdirect(lob) 0 0 0sessionlogicalreads 119,376 101.8 119,376.0

V$SYSSTAT:

STATSPACKreport:99.999%就一定表示很好的性能吗？一个性能很糟糕的数据库的命中率同样可以达到99%以上命中率只能表示是否有大多数数据访问在内存中进行真正要确定性能好坏仍然依赖于等待事件v$session_waitv$session_eventv$system_event如果有非常高的命中率，但是性能非常差，那么需要调整的不是数据高速缓冲区，而是应用的SQL语句增大数据高速缓存区的提示增大缓存区大小遵循下面的规则:如果是9i，通过advice获取较为准确的大小如果是8i，尝试合适的大小如果OLTP系统的命中率低于0.9.没有重作分页错误(unduepagefaulting).上一次的增大是有效的.使用多池SGADBbuffercachesLRUlistsKEEPpoolRECYCLEpoolDEFAULTpool定义多池Oracle9i,每个池有独立的大小和空间.由下面的参数定义DB_CACHE_SIZE,DB_KEEP_CACHE_SIZE,andDB_RECYCLE_CACHE_SIZE.这些参数是动态的.锁存器由OracleRDBMS自动分配.Oracle8i,多个池的总块数为DB_BLOCK_BUFFERS.总的锁存器数为DB_BLOCK_LRU_LATCHES.每个锁存器至少分配50块.启用多池CREATEINDEXcust_idx…STORAGE(BUFFER_POOLKEEP…);ALTERTABLEcustomerSTORAGE(BUFFER_POOLRECYCLE);ALTERINDEXcust_name_idxSTORAGE(BUFFER_POOLKEEP);KEEP池的使用

调优目的:将块保持在内存中大小:保持所有或者几乎所有的块工具:ANALYZE...ESTIMATESTATISTICSSQL>ANALYZETABLEhr.countriesESTIMATESTATISTICS;SQL>SELECTtable_name,blocks2FROMdba_tables3WHEREowner='HR'4ANDtable_name='COUNTRIES';TABLE_NAMEBLOCKS

COUNTRIES 14RECYCLE池的使用调优目的:当事务结束后减少在内存中保存的块大小:仅保持激活的块工具:V$CACHE...SQL>SELECTowner#,name,count(*)blocks2FROMv$cache3GROUPBYowner#,name;OWNER#NAMEBLOCKS

5CUSTOMER147...计算多池的命中率SQL>SQL>SELECTname, 1-(physical_reads/(db_block_gets+ consistent_gets))"HIT_RATIO"2FROM sys.v$buffer_pool_statistics3WHEREdb_block_gets+consistent_gets>0;NAME HIT_RATIO

KEEP .983520845RECYCLE .503866235DEFAULT .790350047将表保存在缓存中通过下面的方法将全表扫描的表保存在缓存中:用CACHE

子句创建表用CACHE子句修改表在查询时,使用CACHE提示(hint)Select/*+cache*/*fromemployees;提示:不要使缓存过于拥塞.其他数据高速缓存区的性能判断依据SQL>SELECTevent,total_waits2FROMv$system_event3WHEREeventin4 ('freebufferwaits','bufferbusywaits');EVENTTOTAL_WAITS

freebufferwaits337bufferbusywaits3466V$SYSTEM_EVENT:SQL>SELECTname,value2FROMv$sysstat3WHEREname='freebufferinspected';NAMEVALUE

freebufferinspected183V$SYSSTAT:自由列表--FreeLists一个对象的自由列表维护着一个可以插入数据的数据块的列表.一个对象的自由列表数不能被动态设置.单CPU系统使用多自由列表不会带来性能的提高.调优的目的是确保一个对象有足够的自由列表以减少竞争.诊断自由列表竞争V$WAITSTAT的列:CLASS“segment header”COUNTTIMESGADatabuffercacheV$SYSTEM_EVENT的列:EVENT

TOTAL_WAITS-buffer

busywaitsServer

processServer

processV$SESSION_WAIT的列:EVENT

P1

“FILE”

P2

“BLOCK”

P3

“ID”DBA_SEGMENTS的列:SEGMENT_NAMESEGMENT_TYPEFREELISTSHEADER_FREELISTS解决自由列表竞争查询V$SESSION_WAIT.通过DBA_SEGMENTS确定对象和段的自由列表数.可以:修改有问题的对象,或者将对象移动到自动管理的表空间上.自动段空间管理CREATETABLESPACEBIT_SEG_TSDATAFILE'$HOME/ORADATA/u04/bit_seg01.dbf'SIZE1MEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;CREATETABLEBIT_SEG_TABLE(IDNUMNUMBER)TABLESPACEBIT_SEG_TS;创建一个自动管理(auto-managed)的表空间:创建一个使用自动管理方法管理自由空间的表:自动段空间管理(ASSM)管理数据库内部自由空间的方法.使用BITMAPS而不是FREELISTS去跟踪段内的自由和使用的空间.提供:轻松的管理更好的空间利用率对于并发的插入有更好的性能限制:不能在包含Lob型数据的表空间中使用自动段空间管理位图段包含描述每个块可用空间的位图位图存在一个单独的块中，被称为位图块(BMBs)当插入一个新行的时候，数据库会搜索位图看block是否有足够的空间当一定量的可用空间的块改变了，位图会改变到一个新的状态共享池大小由SHARED_POOL_SIZE定义.库高速缓存中包括了语句文本,分析代码,和执行计划.数据字典高速缓存包括了从数据字典中得到的表的定义,列,权限等信息.当大池没有被配置时,UGA中包括了Oracle共享服务器用户的会话信息.SharedpoolLibrary

cacheData

dictionary

cacheUGASharedpoolSharedpool

共享的SQL,PL/SQL区域库高速缓存SELECT语句2的内容SELECT语句1的内容SELECT语句2SELECT语句1SELECT语句1Datadictionary

cacheUGASharedpool调优库高速缓存的诊断工具sp_m_n.lstV$SGASTATV$LIBRARYCACHEV$SQLV$SQLAREAV$SQLTEXTV$DB_OBJECT_CACHE影响组件的参数SHARED_POOL_SIZE,OPEN_CURSORSSESSION_CACHED_CURSORS,CURSOR_SPACE_FOR_TIMEreport.txtLibrarycacheSharedSQLandPL/SQLViews库高速缓存性能判断依据找到正在运行的语句:SQL>selectsql_text,executions,loads2fromv$sqlarea;SQL>select*fromv$sqltext2wheresql_textlike3'select*fromhr.employeeswhere%';SQL>selectgethitratio2fromv$librarycache3wherenamespace=‘SQLAREA’;

检查V$LIBRARYCACHE中的GETHITRATIO:ExecutesPROC1—>1stpin,1loadExecutesPROC1

—>2ndpin,noreloadExecutesPROC1

—>3rdpin,noreloadExecutesPROC1

—>4thpin,noreload提示:库高速缓存的重装载Reloads应该小于pins的1%:4pinsandnoreloadsSQL>selectsum(pins)"Executions",sum(reloads) 2"CacheMisses",sum(reloads)/sum(pins)3fromv$librarycache;如果reloads-to-pins的比值大于1%,增大SHARED_POOL_SIZE

参数的值.Invalidations对象的命名空间被标记为不可用而导致重新装载的次数:SQL>selectcount(*)fromhr.employees;SQL>selectnamespace,pins,reloads,invalidations2fromv$librarycache;SQL>selectcount(*)fromhr.employees;SQL>selectnamespace,pins,reloads,invalidations2fromv$librarycache;SQL>ANALYZETABLEhr.employeesCOMPUTESTATISTICS;调优库高速缓存通过保持分析次数最少以减少丢失:确保用户可以共享代码防止语句由于分配空间的不足而过期避免由于失效引起的重新分析调优库高速缓存通过下面的方法防止碎片:为大的内存需求保留空间将被频繁访问的大对象保留(pin)在内存中减少大的匿名PL/SQL块的使用为Oracle的共享服务连接启用大池足够大的库高速缓存SQL语句:共享对象,例如包和视图:SQL>selectsum(sharable_mem)2fromV$DB_OBJECT_CACHE;SUM(SHARABLE_MEM)

379600SQL>selectsum(sharable_mem)2fromV$SQLAREAwhereexecutions>5;SUM(SHARABLE_MEM)

381067大的内存需求满足大的连续的内存需求在共享池中保留连续的内存区V$SHARED_POOL_RESERVEDSHARED_POOL_SIZESHARED_POOL_RESERVED_SIZE`Datadictionary

cacheUGASharedpoolLibrarycacheSharedSQLandPL/SQL在内存中保留大对象将大对象保留(pin)在内存中:SQL>EXECUTEdbms_shared_pool.keep(‘package_name’);

找到那些没有保留在库高速缓存中的PL/SQL对象:SQL>select*fromv$db_object_cache2wheresharable_mem>100003and(type=‘PACKAGE’ortype=‘PACKAGEBODY’or4type=‘FUNCTION’ortype=‘PROCEDURE’)5andKEPT=‘NO’;清空共享池见flush_shared_pool_sample.txt注意：并不建议使用这种方法，只是在某些情况下考虑使用（例如频繁出现的ORA-04031错误）匿名的PL/SQL块找到匿名的PL/SQL块并转化为小的可以调用包的函数的匿名PL/SQL块:

SQL>selectsql_textfromv$sqlarea2wherecommand_type=473andlength(sql_text)>500;影响库高速缓存的的其他参数OPEN_CURSORSCURSOR_SPACE_FOR_TIMESESSION_CACHED_CURSORS数据字典高速缓存的调优内容:对象的定义术语:GETS:对对象的请求访问次数GETMISSES:请求没有找到的次数调优:避免数据字典高速缓存的丢失调优数据字典高速缓存的诊断工具V$ROWCACHE:PARAMETERGETSGETMISSESSp_1_2.lstSHARED_POOL_SIZEDatadictionary

cacheUGASharedpoolLibrarycacheSharedSQLandPL/SQL调优数据字典高速缓存保证GETMISSES的总数与GETS的总数之比小于15%:SQL>selectparameter,gets,getmisses2fromv$rowcache;PARAMETERGETS GETMISSES

dc_objects143434171dc_synonyms140432127如果有太多的内存丢失,增大参数SHARED_POOL_SIZE.UGA和Oracle的共享服务器SharedpoolStackspacePGA专用服务器连接:Oracle共享服务器连接:SharedpoolPGAStackspaceUsersessiondataCursorstateUGAUsersessiondataCursorstateUGAV$STATNAMEV$SESSTATV$MYSTATOPEN_CURSORSSESSION_CACHED_CURSORS定义合适的用户全局区大小SQL>selectSUM(value)||'bytes'

"Totalsessionmemory"2fromV$SESSTAT,V$STATNAME3wherename='sessionugamemory'4andv$sesstat.statistic#=v$statname.statistic#;

被用户当前连接使用的UGA空间:SQL>selectSUM(value)||'bytes'

"Totalsessionmemory"2fromV$MYSTAT,V$STATNAME3wherename='ses