资源描述：
《oracle性能优化(简单版)》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、--数据库巡检或性能优化方法各异，但首要的是要发现数据库性能瓶颈，系统自带的statspack,或awr太耗时，--以下是本人常用的方法，共享之--1、查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系--oracle9iselectt2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2)event_wait_percentfrom(SELECTSUM(time_waited)w1FROMv$system_eventWHEREeventNOTIN('smontimer','pmontimer','rdbm
2、sipcmessage','Nullevent','parallelquerydequeue','pipeget','clientmessage','SQL*Netmessagetoclient','SQL*Netmessagefromclient','SQL*Netmoredatafromclient','dispatchertimer','virtualcircuitstatus','lockmanagerwaitforremotemessage','PXIdleWait','PXDeq:ExecutionMsg','PXDeq:TableQNormal','wakeuptimeman
3、ager','slavewait','i/oslavewait','jobqslavewait','nullevent','gcsremotemessage','gcsforaction','gesremotemessage','queuemessages'))t1,(select*from(selectt.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownumnumfrom(selectevent,total_waits,total_timeouts,time_waited,average_wait
4、fromv$system_eventwhereeventnotin('smontimer','pmontimer','rdbmsipcmessage','Nullevent','parallelquerydequeue','pipeget','clientmessage','SQL*Netmessagetoclient','SQL*Netmessagefromclient','SQL*Netmoredatafromclient','dispatchertimer','virtualcircuitstatus','lockmanagerwaitforremotemessage','PXIdl
5、eWait','PXDeq:ExecutionMsg','PXDeq:TableQNormal','wakeuptimemanager','slavewait','i/oslavewait','jobqslavewait','nullevent','gcsremotemessage','gcsforaction','gesremotemessage','queuemessages')orderbytime_waiteddesc)t)wherenum<11)t2,(SELECTVALUECPUFROMv$sysstatWHERENAMELIKE'CPUusedbythissession')t
6、3--oracle10gselectt2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2)event_wait_percentfrom(SELECTSUM(time_waited)w1FROMv$system_eventWHEREeventNOTIN('smontimer','pmontimer','rdbmsipcmessage','Nullevent','parallelquerydequeue','pipeget','clientmessage','SQL*Netmessagetoclient','SQL*Netmessagefromc
7、lient','SQL*Netmoredatafromclient','dispatchertimer','virtualcircuitstatus','lockmanagerwaitforremotemessage','PXIdleWait','PXDeq:ExecutionMsg','PXDeq:TableQNormal','wakeuptimemanager','slavewait','i/oslavewait',
