资源描述：
《oracle深度学习笔记——性能调优汇总》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、Oracle深度学习笔记——性能调优汇总数据字典高速缓存命中率数据字典高速缓存命中率如下：select(sum(gets-getmisses-fixed))/sum(gets)"datadictionaryhitratio"fromv$rowcache;datadictionaryhitratio-------------------------.975728791参考95~99%1.硬解析过程altersessionsetsql_trace=true;altersystemflushshared_pool;selecttable_namefromuser_tableswher
2、erownum<=5;打开TRC文件如下：PARSINGINCURSOR#140195096140104len=50dep=0uid=0oct=3lid=0tim=700865302hv=17921204ad='97a9d708'sqlid='83dx9sc0j2x5n'selecttable_namefromuser_tableswhererownum<=5ENDOFSTMTPARSE#140195096140104:c=106000,e=131031,p=2,cr=727,cu=0,mis=1,r=0,dep=0,og=1,plh=5472353,tim=700865301
3、….再执行相同的命令如下，查看TRC文件如下：***2016-01-3017:55:48.379CLOSE#140195096140104:c=0,e=100,dep=0,type=0,tim=1039278193=====================PARSINGINCURSOR#140195096140104len=50dep=0uid=0oct=3lid=0tim=1039279623hv=17921204ad='97a9d708'sqlid='83dx9sc0j2x5n'selecttable_namefromuser_tableswhererownum<=5END
4、OFSTMTPARSE#140195096140104:c=1000,e=937,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=5472353,tim=1039279622……发现命中。使用tkprof处理一下：具体使用，可以直接输入tkprof打开结果文件如下：selecttable_namefromuser_tableswhererownum<=5callcountcpuelapseddiskquerycurrentrows------------------------------------------------------------
5、-----------Parse20.050.060500Execute20.000.000000Fetch40.000.000140010-----------------------------------------------------------------------total80.060.070145010Missesinlibrarycacheduringparse:11.库高速缓存命中率selectsum(pinhits)/sum(pins)Library_cache_hit_ratiofromv$librarycache;LIBRARY_CACHE_HIT
6、_RATIO-----------------------.927807473参考大于99%确定库高速缓存的效率selectnamespace,pins,pinhits,reloadsfromv$librarycacheorderbynamespace;NAMESPACEPINSPINHITSRELOADS----------------------------------------------------------------------------------------------AUDITPOLICY66560BODY1301109716CLUSTER1318129
7、80CONTEXTPOLICY200DBINSTANCE000DBLINK000DIRECTORY51410EDITION3092960INDEX4516308272OBJECTID000PDB000QUEUE1060RULESET320SCHEMA000SQLAREA528694508982641SQLAREABUILD000SQLAREASTATS64062620TABLE/PROCEDURE57487446913245TEMPORARYINDEX481045TEMPORARYTABLE
