02-01 用缓存改善性能(60 页)

使用缓存改善性能(ImprovingPerformancewithCaching),课程目标,完成本课程,你应该能做下面工作:缓存SQL结构集改善内存的使用使用提示写查询语句使用DBMS_RESULT_CACHE包在PL/SQL函数中使用缓存,课程导向,缓存SQL结果集概念使用DBMS_RESULT_CACHE包实现SQL查询缓存使用PL/SQL函数返回缓存结果结果缓存管理讨论,什么是结果缓存?,结果集缓存（ResultCache），ResultCache又可以分为：ServerResultCache和ClientResultCache可以通过客户端内存来缓存查询的结果集，缓存结果可以在所有session间共享，当查询反复执行时，查询结果可以直接从客户段的缓存中获得，从而极大地提高应用效率。对于ServerResultCache:服务器端结果集缓存使用SharedPool中的内存来进行结果缓存,OracleDatabase11gsResultCache,SGA区ServerResultCache示意图,SystemGlobalArea(SGA),SharedPool,DatabaseBufferCache,LargePool,Other,RedoLogBuffer,LibraryCache,DataDictionaryCache,Other,ServerResultCache,ReservedPool,增加结果缓存内存的大小,你可使用RESULT_CACHE_MAX_SIZE参数来修改缓存内存的大小.,SGA,Defaultresultcache,Sharedpool,Increasedresultcache,结果缓存相关参数,MEMORY_TARGET参数:MEMORY_TARGET参数是11g版本的内存设置参数这个参数控制一切与内存有关的参数，包括SGA和PGA如果这个参数设置大于0，则许多参数自动变为0(以动态方式出现)MEMORY_TARGET参数0时:RESULT_CACHE_MAX_RESULT是动态参数RESULT_CACHE_MAX_RESULT参数自动设置=011gMEMORY_TARGET参数指定了数值(实例SGA和PGA总内存)，那么Oracle将设置RESULT_CACHE_MAX_SIZE为MEMORY_TARGET的0.25%,设置Result_Cache_Max_Size,Result_Cache_Max_Size由DBA设置缓存的大小是动态的，并且修改的参数一直有效直到重启动为止,SQLALTERSYSTEMSETresult_cache_max_size=2MSCOPE=MEMORY;Systemaltered.SQLSELECTname,value2FROMv$parameter3WHEREname=result_cache_max_size;NAMEVALUE-result_cache_max_size20971521rowselected.,启用查询结果缓存,使用RESULT_CACHE_MODE初始化参数RESULT_CACHE_MODE可设置为:MANUAL(默认):可在语句采用提示RESULT_CACHE实现FORCE:结果总是存储在内存里,启用缓存-RESULT_CACHE_MODE参数,RESULT_CACHE_MODE=AUTOMANUALFORCEMANUAL(默认)只有使用RESULT_CACHE提示才能进行缓存FORCE(强制缓存)除非你用NO_RESULT_CACHE提示,课程导向,缓存SQL结果集概念使用DBMS_RESULT_CACHE包实现SQL查询缓存使用PL/SQL函数返回缓存结果结果缓存管理讨论,DBMS_RESULT_CACHE包,包共有下面的程序存储过程,使用DBMS_RESULT_CACHE包,DBMS_RESULT_CACHE提供API接口，DBA也可以使用,executedbms_result_cache.memory_report,ResultCacheMemoryReportParametersBlockSize=1KbytesMaximumCacheSize=1056Kbytes(1056blocks)MaximumResultSize=52Kbytes(52blocks)MemoryTotalMemory=5140bytes0.003%oftheSharedPool.FixedMemory=5140bytes0.003%oftheSharedPool.DynamicMemory=0bytes0.000%oftheSharedPool,使用DBMS_RESULT_CACHE包,显示分配的详细信息:只需要设置报告为”TRUE”,SETSERVEROUTPUTONEXECUTEDBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);,.FixedMemory=5132bytes0.002%oftheSharedPool.CacheMgr=108bytes.MemoryMgr=124bytes.BloomFltr=2Kbytes.StateObjs=2852bytes.DynamicMemory=98396bytes0.046%oftheSharedPool.Overhead=65628bytes.HashTable=32Kbytes(4Kbuckets).ChunkPtrs=12Kbytes(3Kslots).ChunkMaps=12Kbytes.Miscellaneous=8284bytes,使用DBMS_RESULT_CACHE包,DBMS_RESULT_CACHE:DBA用DBMS_RESULT_CACHE.FLUSH净化内存,-任何单条查询的结果占高速缓存的最大限制5-result_cache_max_result改变占有的比例(0到100)-清除内存缓存的结果：execdbms_result_cache.flush-可用下面命令：altersystemflushshared_pool；-execdbms_result_cache.memory_report();,使用DBMS_RESULT_CACHE包,DBMS_RESULT_CACHE:DBA用DBMS_RESULT_CACHE.FLUSH做管理,-使某个特定的对象告诉缓存结果无效：execdbms_result_cache.invalidate(ABC,TAB)-显示结果高速缓存的状态:colstatusfora30selectdbms_result_cache.statusfromdual;STATUS-ENABLEDSQL,RESULT_CACHE有关数据字典,缓存有关数据字典:v$result_cache_statistics内存统计数据v$result_cache_object对象跟属性v$result_cache_dependency显示结果的依赖关系v$result_cache_memory显示内存块及统计数据,RESULT_CACHE有关数据字典,DBMS_RESULT_CACHE:DBA经常要查询缓存的实际分配,-查询缓存分配的情况：SQLselectpool,name,bytes/1024/1024FROMv$sgastatwhereupper(name)like%RESULT%;POOLNAMEBYTES/1024/1024-sharedpoolResultCache:StateObjs.002719879sharedpoolResultCache:MemoryMgr.000118256sharedpoolResultCache:BloomFltr.001953125sharedpoolResultCache:CacheMgr.000102997,RESULT_CACHE有关数据字典,DBMS_RESULT_CACHE:DBA经常要查询缓存的实际分配,SQLselectname,sum(bytes)fromv$sgastatwherenamelikeResultCache%groupbyrollup(name);NAMESUM(BYTES)-ResultCache:BloomFltr2048ResultCache:CacheMgr108ResultCache:MemoryMgr124ResultCache:StateObjs28525132,课程导向,缓存SQL结果集概念使用DBMS_RESULT_CACHE包实现SQL查询缓存使用PL/SQL函数返回缓存结果结果缓存管理讨论,SQL查询结果缓存,定义:当前结果缓存或在内存中存放频繁查询的结果,使之后再查询时使用.缓存的结果存放在内存的SGA中优点:改善性能,SQL查询结果缓存,假定:你希望从大量以州分组的信用数据的平均值大量的查询但每次只分析很少的几个数据在这些查询中,数据很少变化解决:在查询中使用提示/*+result_cache*/,SELECT/*+result_cache*/AVG(cust_credit_limit),cust_state_provinceFROMsh.customersGROUPBYcust_state_province;,清除共享池和结果缓存,-flush.sql-Start清除缓冲区和共享池.-Verifythatmemorywasreleased.connect/assysdbaSETECHOONSETFEEDBACK1SETNUMWIDTH10SETLINESIZE150SETTRIMSPOOLONSETTABOFFSETPAGESIZE1000SETSERVEROUTPUTONexecutedbms_result_cache.flushaltersystemflushshared_pool/executedbms_result_cache.memory_report,检查缓存结果数据,ResultCacheMemoryReportParametersBlockSize=1KbytesMaximumCacheSize=1056Kbytes(1056blocks)MaximumResultSize=52Kbytes(52blocks)MemoryTotalMemory=5140bytes0.003%oftheSharedPool.FixedMemory=5140bytes0.003%oftheSharedPool.DynamicMemory=0bytes0.000%oftheSharedPoolPL/SQLproceduresuccessfullycompleted.,-flush.sql-Startwithacleanslate.Flushthecacheandsharedpool.-Verifythatmemorywasreleased.connect/assysdbaSETECHOONSETFEEDBACK1SETNUMWIDTH10SETLINESIZE150SETTRIMSPOOLONSETTABOFFSETPAGESIZE1000SETSERVEROUTPUTONexecutedbms_result_cache.flushaltersystemflushshared_pool/executedbms_result_cache.memory_report,检查查询的执行计划,-plan_query1.sqlconnectoe-Generatetheexecutionplan.-(ThequerynameQ1isoptional)explainplanforselect/*+result_cacheq_name(Q1)*/*fromorders;setechooff-Displaytheexecutionplan.Verifythatthequeryresult-isplacedintheResultCache.$ORACLE_HOME/rdbms/admin/utlxpls,检查另外的执行计划,-plan_query2.sqlsetechoonconnectoe-Generatetheexecutionplan.-(ThequerynameQ2isoptional)explainplanforselectc.customer_id,o.ord_countfrom(select/*+result_cacheq_name(Q2)*/customer_id,count(*)ord_countfromordersgroupbycustomer_id)o,customerscwhereo.customer_id=c.customer_id;setechooff-Displaytheexecutionplan.-usingthecodeinORACLE_HOME/rdbms/admin/utlxplssetmarkuphtmlpreformatonselectplan_table_outputfromtable(dbms_xplan.display(plan_table,null,serial);,执行两个查询,-query3.sql-Cacheresultofbothqueries,thenusethecachedresult.connectoesetechoonselect/*+result_cacheq_name(Q1)*/*fromorders/selectc.customer_id,o.ord_countfrom(select/*+result_cacheq_name(Q3)*/customer_id,count(*)ord_countfromordersgroupbycustomer_id)o,customerscwhereo.customer_id=c.customer_id/setechooff,SQLconnect/assysdbaConnected.SQLSQLcolnameformata55SQLSQLselect*2fromv$result_cache_statistics3/IDNAMEVALUE-1BlockSize(Bytes)10242BlockCountMaximum10563BlockCountCurrent324ResultSizeMaximum(Blocks)525CreateCountSuccess26CreateCountFailure07FindCount08InvalidationCount09DeleteCountInvalid010DeleteCountValid010rowsselected.SQL,浏览缓存结果建立数据,Numberofcacheresultssuccessfullycreated,再次执行两次查询,-query3.sql-Cacheresultofbothqueries,thenusethecachedresult.connectoesetechoonselect/*+result_cacheq_name(Q1)*/*fromorders/selectc.customer_id,o.ord_countfrom(select/*+result_cacheq_name(Q3)*/customer_id,count(*)ord_countfromordersgroupbycustomer_id)o,customerscwhereo.customer_id=c.customer_id/setechooff,SQLconnect/assysdbaConnected.SQLSQLcolnameformata55SQLSQLselect*2fromv$result_cache_statistics3/IDNAMEVALUE-1BlockSize(Bytes)10242BlockCountMaximum10563BlockCountCurrent324ResultSizeMaximum(Blocks)525CreateCountSuccess26CreateCountFailure07FindCount28InvalidationCount09DeleteCountInvalid010DeleteCountValid010rowsselected.,浏览找到缓存结果信息,Numberofcacheresultssuccessfullyfound,课程导向,缓存SQL结果集概念使用DBMS_RESULT_CACHE包实现SQL查询缓存使用PL/SQL函数返回缓存结果结果缓存管理讨论,PL/SQL函数结果缓存,定义:启用数据缓存使会话间能共享在SGA中缓存函数的结果，使得运行你的应用时任何会话都可用这个结果好处:改善性能改善具有可测性,PL/SQL函数结果标注,假设:你需要PL/SQL函数得到一个复杂的度量数据.这个数据是你的函数慢慢计算出来，但这个结果被频繁调用解决方法:在函数定义时用RESULT_CACHE指定当表存在依赖关系时，你还RELIES_ON子句来保证,清除共享池及其缓存结果,-flush.sql-Startwithacleanslate.Flushthecacheandsharedpool.-Verifythatmemorywasreleased.connect/assysdbaSETECHOONSETFEEDBACK1SETNUMWIDTH10SETLINESIZE150SETTRIMSPOOLONSETTABOFFSETPAGESIZE1000SETSERVEROUTPUTONexecutedbms_result_cache.flushaltersystemflushshared_pool/executedbms_result_cache.memory_report,ResultCacheMemoryReportParametersBlockSize=1KbytesMaximumCacheSize=1056Kbytes(1056blocks)MaximumResultSize=52Kbytes(52blocks)MemoryTotalMemory=5140bytes0.003%oftheSharedPool.FixedMemory=5140bytes0.003%oftheSharedPool.DynamicMemory=0bytes0.000%oftheSharedPool,CREATEORREPLACEFUNCTIONORD_COUNT(cust_nonumber)RETURNNUMBERRESULT_CACHERELIES_ON(orders)ISV_COUNTNUMBER;BEGINSELECTCOUNT(*)INTOV_COUNTFROMordersWHEREcustomer_id=cust_no;returnv_count;end;,在PL/SQL函数使用RESULT_CACHE,在函数中用RESULT_CACHE选件定义可选项：RELIES_ON子句,指定结果要进行缓存,指定表的数据是可信赖的-依赖关系保证,在查询里调用PL/SQL函数,SQLconnectoeConnected.SQLselectcust_last_name,ord_count(customer_id)no_of_orders2fromcustomers3wherecust_last_name=MacGraw;CUST_LAST_NAMENO_OF_ORDERS-MacGraw41rowselected.,验证内存的实际分配大小,SQLSETECHOONSQLSETFEEDBACK1SQLSETNUMWIDTH10SQLSETLINESIZE150SQLSETTRIMSPOOLONSQLSETTABOFFSQLSETPAGESIZE1000SQLSQLconnect/assysdbaConnected.SQLSQL-EstablishthecachecontentSQLsetserveroutputonSQLexecutedbms_result_cache.memory_reportResultCacheMemoryReportParametersBlockSize=1KbytesMaximumCacheSize=1056Kbytes(1056blocks)MaximumResultSize=52Kbytes(52blocks)MemoryTotalMemory=103536bytes0.055%oftheSharedPool.FixedMemory=5140bytes0.003%oftheSharedPool.DynamicMemory=98396bytes0.052%oftheSharedPool.Overhead=65628bytes.CacheMemory=32Kbytes(32blocks).UnusedMemory=29blocks.UsedMemory=3blocks.Dependencies=2blocks(2count).Results=1blocks.PLSQL=1blocks(1count)PL/SQLproceduresuccessfullycompleted.,浏览缓存结果的创建,SQLconnect/assysdbaConnected.SQLSQLcolnameformata55SQLSQLselect*2fromv$result_cache_statistics3/IDNAMEVALUE-1BlockSize(Bytes)10242BlockCountMaximum10563BlockCountCurrent324ResultSizeMaximum(Blocks)525CreateCountSuccess16CreateCountFailure07FindCount08InvalidationCount09DeleteCountInvalid010DeleteCountValid010rowsselected.Elapsed:00:00:05.00SQL,再次调用PL/SQL函数,SQLconnectoeConnected.SQLselectcust_last_name,ord_count(customer_id)no_of_orders2fromcustomers3wherecust_last_name=MacGraw;CUST_LAST_NAMENO_OF_ORDERS-MacGraw41rowselected.,浏览结果缓存数据,SQLconnect/assysdbaConnected.SQLSQLcolnameformata55SQLSQLselect*2fromv$result_cache_statistics3/IDNAMEVALUE-1BlockSize(Bytes)10242BlockCountMaximum10563BlockCountCurrent324ResultSizeMaximum(Blocks)525CreateCountSuccess16CreateCountFailure07FindCount18InvalidationCount09DeleteCountInvalid010DeleteCountValid010rowsselected.Elapsed:00:00:00.01SQL,确认结果是被缓存,SQLconnect/assysdbaConnected.SQLSQLcolnameformata55SQLSQLselecttype,namespace,status,scan_count,name2fromv$result_cache_objects3/TYPENAMESSTATUSSCAN_COUNTNAME-DependencyPublished0OE.ORD_COUNTDependencyPublished0OE.ORDERSResultPLSQLPublished1OE.ORD_COUNT:8.ORD_COUNT#fac892c7867b54c6#13rowsselected.SQL,课程导向,缓存SQL结果集概念使用DBMS_RESULT_CACHE包实现SQL查询缓存使用PL/SQL函数返回缓存结果结果缓存管理讨论,存储查询或函数(function)返回的结果占用共享池(SharedPool)一片FlushingSharedPool操作不受影响,11gServerResultCaches-管理,1.RESULT_CACHE_MODE结果集cache的模式，共有三种方式Hint(默认)-/*+RESULT_CACHE*/altersessionaltersystemMANUAL(default)查询提示或表注释FORCE每个结果都缓存(不推荐)AUTO?更多的选择,11g-ResultCache参数,2.RESULT_CACHE_MAX_SIZEOracle系统每个单个查询允许结果缓存数。它的默认值是整个结果缓存的5%参数限定结果集的最大值，以bytes为单位。缓存从共享池中分配，但又分别管理（比如：刷新共享池并不会清空结果集缓存）分配给serverresultcache的总数0(Disabled),0.25%(memory_target),0.5%(sga_target)及1%(shared_pool_size)3.RESULT_CACHE_MAX_RESULT参数限定了一个结果集所使用的总缓存的最大百分比,11g-ResultCache参数,4.RESULT_CACHE_REMOTE_EXPIRATION参数：如果查询依赖于一个远程数据库，则这个参数决定结果集应该保留的分钟数这个参数限定了依赖于远程对象的结果集的有效时间，默认值为0，即不cache这样的结果集默认为0分钟，表示对远程数据库表的任何改变不能在本地数据库检测到的一个提醒，因此陈旧的结果集可能会不适当地保持一段较长时间这个参数可以设置为全局(ALTERSYSTEM)或对每个会话(ALTERSESSION),11g-ResultCache参数,11g-ResultCache参数,缓存有关参数列出缓存参数：,连接到:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQLshowparameterRESULT_CACHENAMETYPEVALUE-client_result_cache_lagbiginteger3000client_result_cache_sizebiginteger0result_cache_max_resultinteger5result_cache_max_sizebiginteger3168Kresult_cache_modestringMANUALresult_cache_remote_expirationinteger0SQL,11g-ResultCache参数,缓存有关参数列出缓存参数：,连接到:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsSQLSQLALTERSYSTEMSETresult_cache_max_size=20MSCOPE=MEMORY;Systemaltered.SQLSELECTname,value2FROMv$parameter3WHEREname=result_cache_max_size;,11g-ResultCache参数,MEMORY_TARGET参数与RESULT_CACHE_MAX_SIZE参数当设置MEMORY_TARGET参数RESULT_CACHE_MAX_SIZE的设置变无效(自动为0)不设置MEMORY_TARGET而使用SGA_TARGET和PGA_AGGREGATE_TARGET，则不会导致RESULT_CACHE_MAX_SIZE被置为0值不使用MEMORY_TARGET方式设置内存参数，则RESULT_CACHE_MAX_SIZE参数设置正常,11g-ResultCache参数,MEMORY_TARGET参数内存目标参数影响到其他参数11g/12g中，如果设置了MEMORY_TARGET，则RESULT_CACHE_MAX_SIZE设置重启后被置为0：,SQLaltersystemsetmemory_target=220mscope=spfile;Systemaltered.SQLaltersystemsetresult_cache_max_size=10mscope=spfile;Systemaltered.SQLshutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQLstartupORACLEinstancestarted.TotalSystemGlobalArea209235968bytesFixedSize1298920bytesVariableSize192941592bytesDatabaseBuffers12582912bytesRedoBuffers2412544bytesDatabasemounted.Databaseopened.,SQLshowparametermemory_targetNAMETYPEVALUE-memory_targetbiginteger220MSQLshowparameterresult_cache_max_sizeNAMETYPEVALUE-result_cache_max_sizebiginteger0SQLaltersystemsetresult_cache_max_size=10mscope=both;Systemaltered.SQLshowparameterresult_cache_max_sizeNAMETYPEVALUE-result_cache_max_sizebiginteger0,V$RESULT_CACHE_STATISTICS结果缓存的设置和内存使用统计MonitorCREATESvs.FINDSV$RESULT_CACHE_MEMORY结果缓存的内存块和相关统计V$RESULT_CACHE_OBJECTS结果缓存相关对象还有它们的属性信息V$RESULT_CACHE_DEPENDENCY结果缓存的依赖信息,11g-ResultCache-Views,11g-ResultCache-监控,缓存情况监控下面查询v$result_cache_statistics视图缓存信息：,columnnameformata20selectname,valuefromv$result_cache_statistics;NAMEVALUE-BlockSize(Bytes)1024BlockCountMaximum3136BlockCountCurrent32ResultSizeMaximum(Blocks)156CreateCountSuccess2CreateCountFailure0FindCount0InvalidationCount0DeleteCountInvalid0DeleteCountValid0,11g-ResultCache-监控,缓存情况监控查询V$RESULT_CACHE_OBJECTS视图信息：,descgv$result_cache_statisticssetlinesize121colnameformata30colvalueformat999999SELECTname,valueFROMgv$result_cache_statistics