java性能测试与调优案例

目JDK优 如何修改默认Java调用的JVM.dll版本 测试实 收集Java堆的优 Tomcat性能与优 Tomcat页 内存使用调整（OutofMemery问题 连接线程数调整（cannotconnecttoserver问题 是否是整个连接处理的时间 net收工测 直接写Socket做测 acceptCount–最大排队 TomcatNative oracle性能、诊断分析与调 方 方法1：在LR的Controller中配置监视 方法3：使用Oracle企业管理器查看数据库性 计数 dbblockgets parsecount CPUusedbythis 诊断和优化SQL语 Oracle索引问题诊断与优 p6spy和SQL语 JSP、Servlet性能优 Servlet常见性能问题分析与优 Servlet中利用init()方法进行高速缓 Servlet压缩输 JSP常见性能问题分析与优 选择正确的页面包含机 PageSession 正确地确定javabean的生命周 控制Session的时 Java代码性能与性能问题定 内存泄漏检 JVM内存泄漏分 认识 JVM启动参数介 JVM性能瓶 JVM内存泄漏实例一-PermGen溢 JVM内存泄漏实例二-Heap溢 JVM内存泄漏实例三-回收时promotion 实例-JProfile内存泄 log4j性能诊断与优 代码效率性能测试与优 线程死 线程竞 算法效 JDK版本加载速度较快，server版本加载速度较慢但运行起来较快。%JAVA_HOME%/jre/bin/如何修改默Java调用JVM.dll版本javaversionjvm更改默认 调用的jvm.dll,这个由 决定。编里面第一行写的是-默认就是版本，把第二行的-serverKNOWN放到第一行，-server--hotspotALIASED_TO-native-green测试实测试应用程序测试目的测试场景主页面：在《J2EE性能测试》这本书中也得到类似的结果：JDK1.3HotSpotServer JDK1.3HotSpot926msJDK1.3Classic JDK1.22 IBM JRockit 选择合适JDK度不同。一般来说，尽可能选择发布的稳定的JDK版本。的稳定的JDK版本相对以前的JDK版本都会做一些bug的修改和性能的优化工作。JDK的版本（Sun发布公司和其它一些公司一直在为提高性能而对javaJava堆的优应用程序是否发生了内存泄漏，有一个非常优秀的工具推荐给大家——Quest公司的JProbe工具，使用它来观察程序运行期的内存变化，并可产生内存快照，从而分析并定位JDK的版本和厂家决定使用-mx和-ms或-Xmx-Xms。Java堆大小决定了回收的频度和速度，Java堆越大，回收的频度越低，速度越慢。同理，Java堆越小，回收的频度越高，速度越快。要想设置比较理想的参数，还是需要了解一些基础知识的。Java堆的最大值不能太大，这样会造成系统内存被频繁的设置-Xms等于-XmX估计内存中存活对象所占的空间的大小，设置-Xms等于此值，-Xmx四倍于此值设置-Xms等于-Xmx1/2设置-Xms介于-Xmx1/101/4使用默认的设置和-Xmx两个最重要的参数外，还有很多可能会用到的参数，这些参数通常依赖于垃圾收集的算法，所以可能JDK的版本和厂家而有所不同。但这些参数一般在Web开发中用的比较少，就不做详细介绍了。在实际的应用中注意设置-Xms和-Xmx使其尽可能的优Java虚拟机和垃圾收集算法的机制了。翻译的《深入Java虚拟机》一书。Tomcat性能与优D:\PrefTest\案例\Tomcat调优\Tomcat性能利用JDK的JConsole来TomcatJMX接口实施，具体步骤如下：首先，打开tomcat5的bin中的catalina.bat Linuxcatalina.sh -集Tomcat性能数据，具体代码如下所示：importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.HashMap;importjava.util.I importjava.util.Map;importjava.util.Set;importimportimportjavax.management.ObjectInstance;importimportjavax.manage importjavax.management.remote.JMXConnector;importjavax.management.remote.JMXServiceURL;publicclassMonitorTomcat*@parampublicstaticvoidmain(String[]{tryStringjmxURL="service:jmx:rmi:///jndi/rmi://06:8999/jmxrmi";//tomcatjmxurlJMXServiceURLserviceURL=newJMXServiceURL(jmxURL);Mapmap=newString[]credentials=newString[]{"monitorRole","QED"};map.put("jmx.remote.credentials",credentials);JMXConnectorconnector=JMXConnectorFactory.connect(serviceURL,map);MBeanServerConnectionmbsc=connector.getMBeanServerConnection(); MBeanInfombInfo=StringattrName="currentThreadCount";//tomcat的线程数对应的属性值MBeanAttributeInfo[]mbAttributes=mbInfo.getAttributes();for(intj=0;j}SetMBeanset=mbsc.queryMBeans(null,null);System.out.println("MBeanset.size():"+MBeanset.size()); torMBeansetI tor=MBeanset.i while tor.hasNext())ObjectInstanceobjectInstance=(ObjectInstance)MBeansetI ObjectNameobjectName=objectInstance.getObjectName();System.out.println("canonicalName:"+if(canonicalName.equals("Catalina:host=localhost,type=Cluster"))//GetdetailsofclusterMBeansStringcanonicalKeyPropList=}}//‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐systemObjectNameruntimeObjName=newObjectName("java.lang:type=Runtime");System.out.println("程序:"+(String)mbsc.getAttribute(runtimeObjName,"VmName"));Datestarttime=newDate((Long)mbsc.getAttribute(runtimeObjName,"StartTime"));SimpleDateFormatdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");Longtimespan=(Long)mbsc.getAttribute(runtimeObjName,"Uptime");

ObjectNameheapObjName=new longmaxMemoryheapMemoryUsage.getMax();longcommitMemory=heap longusedMemory=heapMemoryUsage.getUsed(); mitMemory+"%");//堆使用 mitMemory=nonheap longnonusedMemory=heapMemoryUsage.getUsed(); ObjectNamepermObjName=newObjectName("java.lang:type=MemoryPool,name=Perm MemoryUsage.from((CompositeDataSupport)mbsc.getAttribute(permObjName,"Usage"));longcommitted=p longused=heapMemoryUsage.getUsed();//System.out.println("perm mitted+"%");//持久堆使用//‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐Sessionfor(ObjectNameobj:s){ 最大会话数:"+ System.out.println("会话数:"+mbsc.getAttribute(objname,"activeSessions"));}//‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ThreadPool‐ObjectNamethreadpoolObjName=newObjectName("Catalina:type=ThreadPool,*");Set<ObjectName>s2=mbsc.queryNames(threadpoolObjName,null);for(ObjectNameSystem.out.println("最大线程数:"+mbsc.getAttribute(objname,"maxThreads")); 当前线程数:"+ 繁忙线程数:"+ }}catch(Exception{}}publicstaticStringformatTimeSpan(longspan){longminseconds=span%1000;span=span/1000;longseconds=span%60;span=span/60;longmins=span%60;span=span/60;longhours=span%24;span=span/24;longdays= }}内存使用调整（OutofMemery问题Tomcat128MB，在较大型的应用项目中，这点内存是不够的，需 下，在文 下，在文{tomcat_home}/bin/catalina.shremExecuteTheRequestedCommandsetJAVA_OPTS='-Xms256m-Xmx512m'256MB连接线程数调整（cannotconnecttoserver问题""Thisistheumnumberofthreadsallowed.Thisdefinestheupperboundtotheconcurrency,asTomcatwillnotcreateanymorethreadsthanthis.IftherearemorethanmaxThreadsrequests,theywillbequeueduntilthenumberofthreadsdecreases.IncreasingmaxThreadsincreasesthecapabilityofTomcattohandlemoreconnectionsconcurrently.However,threadsuseupsystemresources.Thus,settingaveryhighvaluemightdegradeperformance,andcouldevencauseTomcattocrash.Itisbettertodenysome ingconnections,ratherthataffecttheonesthatarebeingcurrentlyserviced.Thisistheumnumberofidlethreadsallowed.AnyexcessidlethreadsareshutdownbyTomcat.Settingthistoalargevalueisnotgoodforperformance;thedefault(50)usuallyworksformostWebsiteswithanaverageload.ThevalueofmaxSpareThreadsshouldbegreaterthanminSpareThreads,butlessthanmaxThreads.Thisistheminimumnumberofidlethreadsallowed.OnTomcatstartup,thisisalsothenumberofthreadscreatedwhentheConnectorisinitialized.IfthenumberofidlethreadsfallsbelowminSpareThreads,Tomcatcreatesnewthreads.Settingthistoalargevalueisnotgoodforperformance,aseachthreadusesupresources.Thedefault(4)usuallyworksformostWebsiteswithanaverageload.Typically,siteswith“bursty”trafficwouldneedhighervaluesfor正常，当超过60个时，居然全部报错，打开server.xml可以看到如下配置：<Connectorport="8080"redirectPort="8443"/> umnumberofrequestprocessingthreadstobecreatedbythisConnector,whichthereforedeterminesthe umnumberofsimultaneousrequeststhatcanbehandled.Ifnotspecified,thisattributeissetto200.Ifanexecutorisassociatedwiththisconnector,thisattributeisignoredastheconnectorwillexecutetasksusingtheexecutorratherthananinternalthreadpool.这个就产生问题了，打开Tomcat的源码发现默认值是40，而不200，所以压力测试时遇到<Connectorport="8080"Tomcat7实验一、测JSP页面VU：50RT：VU：50RT：VU：50RT：二、测JPetStore主页VU：50RT：VU：50RT：ThenumberofmillisecondsthisConnectorwillwait,afteracceptingaconnection,fortherequestURIlinetobepresented.Thedefaultvalueis60000(i.e.60seconds).<!--Defineanon-SSLHTTP/1.1Connectoronport8080--<Connectorport="8080"maxHttpHeaderSize="8192"maxThreads="150"minSpareThreads="25"enableLookups="false"redirectPort="8443"connectionTimeout="2000"disableUploadTimeout="true" net收工测直接用net连上tomcat，如果什么都不输入，socket很快回断开，输入完整GET。。。，能够获得输出。如果不保持输入，$netlocalhostGET/index.jspHTTP/1.1Connection:Keep-AliveHost:3Content-Length:Socket做测以下是Groovy代码content='''GET/index.jspHTTP/1.1Connection:Keep-AliveHost:3Content-Length:defSocketsocket=newSocket('localhost',8080)println'iskeepalive?'+socket.getKeepAlive()println'sleep'+sleepTime+'ms.'println'isclosed?'+println'sleep'+sleepTime+'println'writesocketbegin======'writeStream(content,socket.getOutputStream())println'readsocketprintlnreadStream(socket.getInputStream())[0..300]voidwriteStream(content,stream){OutputStreambuf=newBufferedOutputStream(stream);OutputStreamWriterout=newOutputStreamWriter(buf,"UTF-8");printcontent}StringStringbyte[]buffer=newintreadCount=stream.read(buffer);while(readCount!=-1){sResult+=newString(buffer,readCount,"utf-8");readCount=}return}acceptCount–最大排队Theumqueuelengthfor ingconnectionrequestswhenallpossiblerequestprocessingthreadsareinuse.Anyrequestsreceivedwhenthequeueisfullwillberefused.Thedefaultvalueis100.<Connectorport="8080"maxThreads="800"maxThreads：tomcat起动的最大线程数，即同时处理的任务个数，默认值为200maxThreads如何配cpu，2第一种情况，如果我们的操作是纯粹的计算，那么系统响应时间的主要限制就是cpu的运算能力，此时maxThreads应该尽量设的小，降低同一时间抢cpu的线程个数，可maxThreads应该尽量设的大，这样才能提高同时处理请求的个数，从tomcat同时处理的请求量会比较大，所以需要关注一下tomcat的虚拟机内存设置和linux的openfile限制。*webserver允许的最大连接数还受制于操作系统的内核参数设置，通常Windows2000个左右，Linux1000个左右。CentOSSocke最大连接数ulimit-中的openfilesvisoftnofilehardnofilesoft可以超过的配置数hard最大过的配置数nofile对描述符的配置试验修改softnofilehardnofileAction.c(6):Error-27796:Failedtoconnecttoserver"01:8080":[10060]ConnectiontimedoutControllerConnectionConnectionperSecond图中看到连接数偏少（相比起默认设置1024）:1、ConnectionsperSecond-NewConnection：28.082ConnectionsperSecond-ConnectionShutdown：28.082ConnectionsperSecond-NewConnection：114.000TomcatNative信息TheAPRbasedApacheTomcatNativelibrarywhichallowsoptimalperformanceinproductionenvironmentswasnotfoundonthejava.library.path:E:\Program 所谓的ApacheTomcatNativelibrary其实叫APR，全称为：ApachePortableRuntimeandTomcatcanusetheApachePortableRuntimetoprovidesuperiorscalability,performance,andbetterintegrationwithnativeserver.TheApachePortableRuntimeisahighlyportablelibrarythatisattheheartofApacheHTTPServer2.x.APRhasmanyuses,includingaccesstoadvancedIOfunctionality(suchassendfile,epollandOpenSSL),OSlevelfunctionality(randomnumbergeneration,systemstatus,etc),andnativeprocesshandling(sharedmemory,NTpipesandUnixsockets).ThesefeaturesallowsmakingTomcatageneralpurposewebserver,willenablemuchbetterintegrationwithothernativeweb,andoverallmakeJavamuchmoreviableasafullfledgedwebservertformratherthansimplyabackendfocusedtechnology.ApacheTomcatNativelibraryApache为了提升Tomcat的性能搞的一套本地化Socket,Thread,IO组件也就是说它有高级IO功能,操作系统级别的功能调用,以及本地进程处理等等这些Tomcat更像WebServer(Apache那样),而不是只能用来JSP,也就是说提升单独的Tomcat作为服务器的吞吐性能.Windows下安 LoadedAPRbasedApacheTomcatNativelibrarytar–xvfapr-1.2.12.tar.gz

make安装APR-tar–xvfapr-util-1.2.12.tar.gzmaketomcatnativecdtarzxvftomcat- ./configure--prefix=/tomcat/apr--with-apr=/tomcat/apr–with-java-home=/usr/jdkmakeJAVA_OPTS="$CATALINA_OPTS-Djava.library.path=加在#ExecuteTheRequested 前#vi/etc/profile添加：export6.tomcat#vim信息LoadedApacheTomcatNativelibrary信息APRcapabilities:IPv6[true],sendfiletrue],acceptfiltersfalse],randomtrue].2008-7-810:20:27org.apache.coyote.http11.Http11AprProtocolinit<Connectorport="8081"connectionTimeout="20000"<Connectorport="8081"protocol="HTTP/1.1"redirectPort="8443"/>redirectPort="8443"/>ConnectorNIOHTTPPOOL是输出一个oWorld。假设地址是我们依次对四种Connector进试，测试的客户端在另外一台机器上用ab命令来完成，测试命令为：ab-c900-n2000，最终的如下表所示(单位:平NIOHTTPPOOL281666692256440默认配置。NIO280的，NIOPNIO的基础上加入线程池，可能是程序处理更复杂了，因此性能不见得比NIOPOOL方式则波动很大，测试期间和HTTP方式一样，不时有停滞。Tomcat6.XJCPServlet2.5JSP2.1的规范，并且包括其它很多有用的功能，使它成为开发和部署web应用和web服务的坚实平台。webjava实现，tomcatweb开发者开发、测试的首选，有很多tomcat作为开发时候使用，而在部署的时候，把应tomcat作为平台的时候，而并发用户connectionrefuse的错误。但是很多商业应用部署在tomcat上运行却安然无恙。是ApacheHTTPServer2.x的，它是高度可移植的本地库，它使用高性能的UXINI/OjavaioAPRJava开发者而言可能稍稍有点难度，在很多用NIO的技术来提升tomcat的并发处理能力。NIOtomcatjavaiojavaNIO的差别。JavaIO操作都是阻塞式的(blockingI/O),socket的编程基础，你会接触过堵socket则后程序就可以执行accept、read、write等操作，一般来说，如果使用堵塞socket，通常我们通常开一个线程acceptsocket，当读完这次socket请求的时候，开一个单独的线程处理这个socketsocketselect状，当有信号的时候可以通过可以通过多路复用（Multiplexing)技术传递给一个指定的线程池来处理请求，然后原来的线程继续select状态。最简单的多路复用技术可以通过java管道(Pipe)来实java管道或者队列里面，java世界里，现在已经有很多NIOGrizzly,ApacheMina等等，如果你对如何编写高性能的网络服务器有，你可以研读这些源代码。webIO(BIO)NIOBIOwebweb请求一个单独的线程，所以并发量一旦上去了，线程数就上去了，CPUBIO不合适高吞吐量、高可伸webNIO则是使用单线程(CPU)或者只使用少量的多线程(CPU)来Socketpipe或者队列里的请求.OS可以接受TCP的连接，web服务器就可以处理该请求。大大提高了web服务器的可伸缩性。redirectPort="8443"/>息，表示NIO已经启动。*由于tomcat的html和解析功能相对其他服务器如apche等较弱，所以，一般都是集成起来 性能、诊断分析与调D:\PrefTest\Oracle调优\Oracle性能诊断与调优学习笔记方1LRController中配置监1ControllerOracle客户端3、在Controller中配置Oracle连接：Tochangethelengthofeachmonitoringsample(inseconds),thedat\monitors\vmon.cfgfileintheLoadRunnerrootfolder.Thedefaultrateis10seconds.TheminimumsamplingratefortheOracleMonitoris10seconds.Ifyousetthe10secondintervals.添加自定义[V$Monitor];Howmanysecondsforeachdata;NamemustbeuniqueName=存Description=该计数器返回当前存Query=SELECT100*((sum(pins-reloads))/sum(pins))from;Namemustbeunique SUM(CONSISTENT_GETS)),4)*100FROM(SELECTCASEWHENNAME='physicalreads'THENVALUEENDPHYSICAL_READS,CASEWHENNAME='dbblockgets'THENVALUEENDDB_BLOCK_GETS,CASEWHENNAME='consistentgets'THENVALUEENDCONSISTENT_GETSFROMV$SYSSTATWHERENameIN('physicalreads','dbblockgets','consistentgets'));NamemustbeDescription=应大于Query=Selectround(sum(pins-reloads)/sum(pins)*100,2)fromv$librarycache;NamemustbeDescription=应大于0.85Query=Selectround(sum(gets-getmisses-usage-fixed)/sum(gets)*100,2)fromv$rowcache;NamemustbeuniqueDescription=应该小于1%Query=selectround(sum(waits)/sum(gets)*100,2)fromv$rollstat;NamemustbeuniqueDescription=1%Query=selectsum(shrinks)fromv$rollstat,v$rollnamewherev$rollstat.usn=v$rollname.usn;NamemustbeName=表空间的I/O读总Description=表空间的Query=selectsum(f.phyrds)pyrfromv$filestatf,dba_data_filesdfwheref.file#=df.file_id;NamemustbeName=表空间的I/O块读总Description=表空间的Query=selectsum(f.phyblkrd)pbrfromv$filestatf,dba_data_filesdfwheref.file#=df.file_id;NamemustbeuniqueDescription=表空间的I/OQuery=selectsum(f.phywrts)pywfromv$filestatf,dba_data_filesdfwheref.file#=df.file_id方法2：使根据sitescope帮助文档，需要把oracle的jdbc驱动classes12.zip放到<SiteScoperootdirectory>\WEB-INF\lib<SiteScoperootdirectory>\java\lib\ext需注意的是：数据库用户名必须是DBA。3：使Oracle企业管理器查看数据库性方法4：使D:\PrefTest\Oracle调优\SpotlightForOracle使用说明计数Ifthenumberofdiskwritesisnon-zeroforagivensortoperation,thenthisstatisticisincremented.SortsthatrequireI/Otodiskarequiteresourceintensive.TryincreasingthesizeoftheinitializationparameterSORT_AREA_SIZE.Formoreinformation,see"SORT_AREA_SIZE".sorts(memory)andsorts(disk)：sorts(memory)是在SORT_AREA_SIZE(因此不需要在磁盘进满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计算in-memorysortratioIfthenumberofdiskwritesiszero,thenthesortwasperformedcomple yinmemoryandthisstatisticisincremented.Thisismoreanindicationofsortingactivityintheapplicationworkload.Youcannotdomuchbetterthanmemorysorts,exceptmaybenosortsatall.SortingisusuallycausedbyselectioncriteriaspecificationswithintablejoinSQLoperations.Select*fromv$sysstatwherenamelikeSort(disk):要求IO去临时表空间的排序Sort(memory)：完全在memory中完成的排序数Sort(rows)：被排序的行数合：sorts(memory)/(sorts(memory)+sorts(disk)selectfromv$sysstata,v$sysstatb,v$sysstatcwhere='sorts(memory)'='sorts(memory)'and='sorts1,2,3,4;createtablettasselect*fromall_objects;Settimingselect*fromttorderby排序诊断和优化FROMv$sysstatdisk,v$sysstatmemWHEREmem.NAME='sorts(memory)'ANDdisk.NAME='sortsSort(diskSort(memory)<5如果超过5%，增加sort_area_size的值(调整PGA)。SELECT*FROM查看bytesprocessed、extrabytesread/written的增量值和cachehitpercentage的值如果cachehitpercentage偏低，则要考虑调整PGA GlobalArea程序全局区）是一块包含一个服务进程的数据和控制信息的内调整PGA优化排序首先查看Oracle的estd_pga_cache_hit_percentageAShit_ratio,ORDERBYtarget_mb;然后调整altersystemset*在OLTP（联机事务处理）系统中，典型PGA内存设置应该是总内存的较小部分（例如）剩下80％分配给SGADSS：PGA_AGGREGATE_TARGET=(total_mem*80%)*dbblockgetsNumberofblocksaccessedinbuffercacheforINSERT,UPDATE,DELETE,andSELECTFORUPDATE.Representblocklogicalreads(fromcache).ThelogicalreadsALWAYSincludethephysicalreads.Lownumberofphysicalreadsispreferable.在Oracle的文档中有这样一段dbblockgets：NumberoftimesaCURRENTblockwasconsistentgets：Numberoftimesaconsistentreadwasrequestedforaphysicalreads：Totalnumberofdatablocksreadfromdisk.Thisnumberequalsthevalueof"physicalreadsdirect"plusallreadsintobuffercache.1、DBBlockGets（当前请求的块数目3、Physicalreads（物理读、在数据库高速缓存中不存在这逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistentgets'dbblockgets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'physicalreads'。select1-((physical.value-direct.value-lobs.value)/logical.value) fromv$sysstatphysical,v$sysstatdirect,v$sysstatlobs,v$sysstatlogicalwhere='physicalreads'='physicalreadsdirect(lob)'and='sessionlogicalreads';BufferCache优化虑调整buffercachehitratio时，需要注意：如果上次增加buffercache的大小以后，没有对提高hitratio产生很大效果的话，不要盲目增加buffercache的大小以提高性能。因为对于排序操作或并行读，oracle是绕过buffercache进行的。在调整buffercache时，尽量避免增加很多的内存而只是提高少量hitratio的情况出现。showparameter_sizeparsecountTotalnumberofparsecalls(realparses).Ahardparseisaveryexpensiveoperationintermsofmemoryuse,becauseitrequiresOracletoallocateaworkheapandothermemorystructuresandthenbuildaparsetree.Shouldbeminimized.TheratioofHardParsetoTotalshouldbelessthan20%.sharedpool或虽然在sharedpool但因为两者存在部分差异而不能被使用时产生硬解析。如果语句已经存在于sharedpool并且可以被使用则产生软解析。当语句被使用(即共享)所有数据Softparseratio：这项将显示系统是否有太多硬解析。该值可与原始统计数据对比以确确。例如，软解析率仅为0.2则表示硬解析率太高，不过，如果总解析量(parsecounttotal)偏：1-(parsecount(hard)/parsecount(total)select1-(a.value/b.value)W='parsecount(hard)'='parsecountTheparseprocessincludesthefollowingphases（解析过程包括以下阶段CheckingthattheSQLstatementissyntacticallyvalid(thatis,thattheSQLconformstotherulesoftheSQLlanguage,andthatall andoperatorsarevalidandcorrectlyused).CheckingthattheSQLissemanticallyvalid.Thismeansthatallreferencestodatabaseobjects(suchastablesandcolumns)arevalid.Checkingsecurity(thatis,thattheuserhaspermissiontoperformthespecifiedSQLoperationsontheobjectsinvolved).DetermininganexecutionnfortheSQLstatement.TheexecutionndescribestheseriesofstepsthatOracleperformsinordertoaccessandupdatethedatainvolved.Parsingcanbeanexpensiveoperation.ItsoverheadisoftenmaskedbythegreateroverheadofhighI/Orequirements.However,eliminatingunnecessaryparsingisalwaysdesirable.Theparse/executeratioreflectstheratioofparsecallstoexecutecalls.Becauseparsingisanexpensiveoperation,itisbettertoparsestatementsonceandthenexecutethemmanytimes.Highparseratios(greaterthan20%)canresultfromthefollowingIfli ls,ratherthanbindvariables,areusedasqueryparameters,theSQLmustbere-parsedoneveryexecution.Youshouldusebindvariableswheneverpossible,unlessthereisapressingreasonforusingcolumnhistograms.SomedevelopmenttoolsortechniquesresultinSQLcursorsbeingdiscardedafterexecution.Ifacursorisdiscarded,thentheparseisrequiredbeforethestatementcanbere-executed.Oraclesql的处理过程1、语法检查(syntax2、语义检查(semantic4、执行sql，返回结果(executeandreturn)Oracle利用内部的hashsqlhashlibrarycache里查找是否存在该hash值；sql与cache查找不能被充分共享利用的SQL语句（查询LibraryCache中执行次数偏低的SQL语句SELECTsql_textFROMv$sqlareaWHEREexecutions<5ORDERBYparseSELECTsql_text,parse_calls,executionsFROMv$sqlareaORDERBYsharedpoolsize,通过调整SharedPool来调整LibraryCache，invalidations的值也应该接近0selectnamespace,gethitratio,pinhitratio,reloads,invalidationsfromv$librarycache;SELECTgethitratioFROMv$librarycacheWHEREnamespace='SQLSELECTsql_text,users_executing,executions,loadsFROM进一步查询该SQL的完整信息SELECT*FOMv$sqltextWHEREsql_textLIKE'SELECT*FROMhr.employeesWHERE诊断Selectsum(pins)"executions",sum(reloads)"cachemisses",sum(reloads)/sum(pins)fromreloads/pins>1%有两种可能，一种是librarycache空间不足，一种是sql中的对象不合法查看某个session的hardparse个数selecta.sid,a.valuefromv$sesstata,v$sessionb,v$statnamecwherea.sid=b.sidanda.statistic#=c.statistic#anda.sid=137and='parsecount(hard)';SELECTshared_pool_size_for_estimateASpool_size,estd_lc_size,estd_lc_time_saved 估计librarycache的大小 调优SQL>CREATE m(xSQL>CREATEORRECEPROCEDUREproc1ExecuteENDLOOP;/ExecuteENDLOOP;/SQL>SETTIMINGSQL>execElapsed:00:00:08.93SQL>selectcount(*)fromSQL>TRUNCATETABLETabletruncated.SQL>execproc1;Elapsed:00:00:01.85SQL>selectcount(*)from2、修改cursor_sharing参数为similar，让类似的SQL语句不做 CURSOR_SHARING=SIMILAR来强制ORACLE使用绑定变量ShowparameterAltersystemset实验cursor_sharingstringEXACTSQL>select*fromtestwhereobject_id=1;norowsselectedSQL>selectsql_text,parse_callsfromv$sqlareawheresql_textlike'select*fromtest%';select*fromtestwhereobject_id=:"SYS_B_0"2select*fromtestwhereobject_id=11SQL>altersystemflushshared_pool;Systemaltered.SQL>altersystemflushshared_pool;Systemaltered.SQL>altersessionset SessionSQL>select*fromtestwhereobject_id=1;norowsselectedSQL>selectsql_text,parse_callsfromv$sqlareawheresql_textlike'select*fromtest%';select*fromtestwhereobject_id=:"SYS_B_0"1SQL>select*fromtestwhereobject_id=2;norowsselectedSQL>selectsql_text,parse_callsfromv$sqlareawheresql_textlike'select*fromtest%';select*fromtestwhereobject_id=:"SYS_B_0"2CPUusedbythisAmountofCPUtime(intensofmilliseconds)usedbyasessionfromthetimeausercallstartsuntilitends.Ifausercallcompleteswithin10milliseconds,thestart-andend-usercalltimesarethesameforpurposesofthisstatistic,and0millisecondsareaddedParseCPUtototalCPUratio：该项显示总的CPU花费在执行及解析上的比率。如果这项比率：1-(parsetimecpu/CPUusedbythisselect1-(a.value/b.value)where='parsetimecpu'and='CPUusedbythissession';V$SYSSTATshowsOracleCPUusageforallsessions.Thestatistic"CPUusedbythissession"showstheaggregateCPUusedbyallsessions.V$SESSTATshowsOracleCPUusagepersession.YoucanusethisviewtodeterminewhichparticularsessionisusingthemostCPU.Ifyoucan,determinewhytheprocessesusesomuchCPUtimeandattempttotunethem.Possibleareastoresearchinclude,butarenotlimitedto,thefollowing:ReadConsistencyWaitDetectionLatch诊断找使用CPU多的用户fromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvalue*12是cpuusedbythis再找出使用CPU多的SQL查找指定SPID正在执行的SQL语句 P.pidpid,S.sidsid,P.spidspid,S.usernameusername,S.osuserosname,P.serial#WHEREP.addr=s.paddrANDS.sql_address=a.address 或者指定SID查看正在执行的SQL语句SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,S.osuserosname,P.serial#WHEREP.addr=s.paddrANDS.sql_address=a.address ANDs.sid=诊断和优SQLPL/SQL指定某个Session的SQL语句selectSID,SERIAL#,USERNAME,OSUSER,MACHINE,TERMINAL,PROGRAMfrom 然后利用DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION包来特定的进程Oracle提交的SQL语句execute execute 在Spotlight中执行session的SQL语句，并查看执行计划还可以利用SQLTuning的调优建议功能D:\PrefTest\案例\oracle性能调优\SQL优化 索引问题诊断与优UsageofFunctions（where关键字后要尽量避免使用函数UsingfunctionsinthewhereclausesuppressestheuseofSELECT*FROMdwtable2WHEREto_number(empno)=783;Elapsed:00:00:00.15ThisquerycouldbewrittenmoreefficientlySELECT*FROMdwtable2WHERE实createtables1asselect*fromSH.SALES;createtables2asselect*fromsettimingselect*froms1whereprod_id=1;select*froms2whereprod_id=1;索引性能测试与诊断1、查看数据库Index信息SELECTA.OWNER, A.TABLESPACE_NAME,A.UNIQUENESSFROMDBA_INDEXESA,DBA_IND_COLUMNSB,DBA_TABLESCWHEREA.OWNER=UPPER('hr')ANDA.OWNER=C.OWNERANDA.TABLE_NAME=B.TABLE_NAME 2、查出没index的表FROMALL_TABLESWHEREOWNERNOTIN('SYS','SYSTEM','OUTLN','DBSNMP')ANDOWNER=FROMALL_INDEXESWHEREOWNERNOTIN3、查出建立了过index的表 FROMALL_INDEXESWHEREOWNERNOTIN('SYS','SYSTEM','OUTLN','DBSNMP')ANDOWNER=HAVINGCOUNT(*)>('4')重；建议每张表不超过5个索引。实验createtabletable1asselect*fromSH.SALES;createtabletable2asselect*fromSELECTcount(*)FROMtable1whereprod_id=30;settimingupdatetable1setcust_id=1whereprod_id=30;updatetable2setcust_id=1whereprod_id=30;4、找出全表扫描（FullScan）SidThefollowingqueryreportshowmanyfulltablescansaretakingce:SELECTname,valueFROMWHEREnameLIKE'%tableORDERBYThevaluesrelatingtothefulltablescanstablescans(longtables)-ascanofatablethathasmorethanfivedatabaseblockstablescans(shorttables)-acountoffulltablescanswithfiveorfewerblocksIfthenumberoflongtablescansissignificant,thereisastrongpossibilitythatSQLstatementsinyourapplicationneedtuningorindexesneedtobeadded.Ifyoucanidentifytheuserswhoareexperiencingthefulltablescans,youcanfindoutwhattheywererunningtocausethesescans.Belowisascriptthatallowsyoutodothis: || || ||')'"UserSUM(DECODE(NAME,'tablescans(shorttables)',VALUE))"ShortScans",SUM(DECODE(NAME,'tablescans(longtables)',VALUE))"LongScans", FROMv$sessionss,v$sesstatse,v$statname WHEREse.statistic#= AND(NAMELIKE'%tablescans(short ORNAMELIKE'%tablescans(long ORNAMELIKE'%tablescanrows ANDse.sid= ANDss.usernameISNOT GROUPBY || || ||')D:\PrefTest\案例\oracle性能调优\Oracle索引问题诊断与优p6spy和SQL语D:\PrefTest\案例\p6spy1、把P6Spy的jar包p6spy.jar放到CLASSPATH中，如果是WebYourWebApp/WEB- 、把perties放到CLASSPATH目录下，如果是WebYourWebApp/WEB- 、修改你应用系统中的数据库驱动名称 P6Spy的驱动程序名 其它的全部使用默认值，暂时先都不用修4pertiesrealdriver，把它的值改为你的应用系统的真正 下的spy.log里看到SQLSQLJSP、Servlet性能优D:\PrefTest\案例\servlet性能优 常见性能问题分析与优Servlet中利用init()方法进行高速servlet大大地提高系统性能，例如下面的Servlet代码：publicclassTestInit2extendsHttpServlet //加载JDBC驱StringdbURL="jdbc:sqlserver://localhost:1433;DatabaseName=Northwind"; StringuserName="sa"; StringuserPwd=""; ConnectiondbConn;Statementstmt=null;ResultSetrs=null;{}publicvoidtry{{}}publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{PrintWriterout=response.getWriter();try{rsstmt.executeQuery"select*fromNorthwind.dbo.Orders得到结果集out.println("out.printlnrs.getString("orderid取出列值out.println("");}out.println("}catch(Exception{}}publicvoidinit()throws{trytry{{}stmt=dbConn.createStatement();//创建}catch(ClassNotFoundException{{}}}要快点：0.465VS0.591initServletdoGet中连接数据库、执行查询语句，这样每次请求Servlet都将执行这些代码，耗费大量资源：publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{StringdriverName=" //加载StringdbURLjdbc:sqlserver://localhost:1433; StringuserName="sa"; StringuserPwd=""; ConnectionStatementstmt=ResultSetrs=trystmt=dbConn.createStatement();//创建rsstmt.executeQuery"select*fromNorthwind.dbo.Orders得到结果集out.println("out.printlnrs.getString("orderid取出列值out.println("");}out.println("}catch(Exception{e.printStackTrace();}}Servlet压缩输缩大概可以节省40%左右的流量。Stringc=ifc".equals(c))压缩输CompressionResponsecResponse=newCompress