Oracle数据库等待事件分析-优快云博客

作者：佚名文章来源：转自网卫大学堂点击数：10 更新时间：2010-7-12 10:47:13 -->--以下是本人常用的方法，共享之 --1、查询数据库等待事件top10,关注前前几个等待事件，关注前三个等待事件是否有因果或关联关系 --oracle 9i select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event where event not in ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get', 'client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client', 'dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait', 'PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait','i/o slave wait', 'jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages') order by time_waited desc ) t) where num<11) t2, (SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' ) t3 --oracle10g select t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent from ( SELECT SUM(time_waited) w1 FROM v$system_event WHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get','client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait', 'i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages','wait for unread message on broadcast channel','PX Deq Credit: send blkd','PX Deq: Execute Reply','PX Deq: Signal ACK','PX Deque wait','PX Deq Credit: need buffer','STREAMS apply coord waiting for slave message', 'STREAMS apply slave waiting for coord message', 'Queue Monitor Wait', 'Queue Monitor Slave Wait', 'wakeup event for builder', 'wakeup event for preparer', 'wakeup event for reader', 'wait for activate message', 'PX Deq: Par Recov Execute','PX Deq: Table Q Sample','STREAMS apply slave idle wait','STREAcapture process filter callback wait for ruleset','STREAMS fetch slave waiting for txns', 'STREAMS waiting for subscribers to catch up','Queue Monitor Shutdown Wait','AQ Proxy Cleanup Wait','knlqdeq','class slave wait','master wait','DIAG idle wait', 'ASM background timer','KSV master wait','EMON idle wait','Streams AQ: RAC qmn coordinator idle wait','Streams AQ: qmn coordinator idle wait','Streams AQ: qmn slave idle wait', 'Streams AQ: waiting for time management or cleanup tasks','Streams AQ: waiting for messages in the queue','Streams fetch slave: waiting for txns','Streams AQ: deallocate messages from Streams Pool', 'Streams AQ: delete acknowledged messages','LNS ASYNC archive log','LNS ASYNC dest activation','LNS ASYNC end of log','LogMiner: client waiting for transaction', 'LogMiner: slave waiting for activate message','LogMiner: wakeup event for builder','LogMiner: wakeup event for preparer','LogMiner: wakeup event for reader') ) t1, (select * from ( select t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum num from (select event,total_waits,total_timeouts,time_waited,average_wait from v$system_event where event not in ('smon timer','pmon timer','rdbms ipc message','Null event','parallel query dequeue','pipe get','client message','SQL*Net message to client','SQL*Net message from client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time manager','slave wait', 'i/o slave wait','jobq slave wait','null event','gcs remote message','gcs for action','ges remote message','queue messages','wait for unread message on broadcast channel','PX Deq Credit: send blkd','PX Deq: Execute Reply','PX Deq: Signal ACK','PX Deque wait','PX Deq Credit: need buffer','STREAMS apply coord waiting for slave message', 'STREAMS apply slave waiting for coord message', 'Queue Monitor Wait', 'Queue Monitor Slave Wait', 'wakeup event for builder', 'wakeup event for preparer', 'wakeup event for reader', 'wait for activate message', 'PX Deq: Par Recov Execute','PX Deq: Table Q Sample','STREAMS apply slave idle wait','STREAcapture process filter callback wait for ruleset','STREAMS fetch slave waiting for txns', 'STREAMS waiting for subscribers to catch up','Queue Monitor Shutdown Wait','AQ Proxy Cleanup Wait','knlqdeq','class slave wait','master wait','DIAG idle wait', 'ASM background timer','KSV master wait','EMON idle wait','Streams AQ: RAC qmn coordinator idle wait','Streams AQ: qmn coordinator idle wait','Streams AQ: qmn slave idle wait', 'Streams AQ: waiting for time management or cleanup tasks','Streams AQ: waiting for messages in the queue','Streams fetch slave: waiting for txns','Streams AQ: deallocate messages from Streams Pool', 'Streams AQ: delete acknowledged messages','LNS ASYNC archive log','LNS ASYNC dest activation','LNS ASYNC end of log','LogMiner: client waiting for transaction', 'LogMiner: slave waiting for activate message','LogMiner: wakeup event for builder','LogMiner: wakeup event for preparer','LogMiner: wakeup event for reader') order by time_waited desc ) t) where num<11) t2, (SELECT VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' ) t3 --2、查询各种指标，根据各种指标反映的问题是否和等待事件有关联关系，进一步确定问题所在 --9i select a.cache_hit_percent,e.rowcache_hitratio,d.pin_ration_percent,d.get_ratio_percent,f.mem_sort_percent ,b.latch_ratio_percent from (SELECT ROUND((1-((s1.VALUE-s4.VALUE-s5.VALUE)/(s2.VALUE+s3.VALUE-s4.VALUE-s5.VALUE)))*100,2) cache_hit_percent FROM v$sysstat s1, v$sysstat s2, v$sysstat s3, v$sysstat s4, v$sysstat s5 WHERE s1.NAME='physical reads' AND s2.NAME='consistent gets' AND s3.NAME='db block gets' AND s4.NAME='physical reads direct (lob)' AND s5.NAME='physical reads direct') a, (select round(100*(1-sum(misses)/sum(gets)),2) latch_ratio_percent from v$latch) b, (select round(100*c.pin_RATIO/b.total,2) pin_ration_percent, round(100*(a.get_ratio/b.total),2) get_ratio_percent from ( select sum(pinhitratio) pin_ratio from v$LIBRARYCACHE) c,(select sum(gethitratio) get_ratio from v$LIBRARYCACHE) a, (select count(*) total from v$LIBRARYCACHE) b) d,(select round(100*(1-sum(getmisses)/sum(gets)),2) rowcache_hitratio from v$rowcache ) e, (SELECT round(100*s1.VALUE/(s2.VALUE+s1.VALUE),2) mem_sort_percent FROM v$sysstat s1 ,v$sysstat s2 WHERE s1.NAME='sorts (memory)' AND s2.NAME='sorts (disk)')f -- 10g select a.cache_hit_percent,e.rowcache_hitratio,d.pin_ration_percent,d.get_ratio_percent,f.mem_sort_percent ,b.latch_ratio_percent from (SELECT ROUND((1-((s1.VALUE-s4.VALUE-s5.VALUE)/(s2.VALUE+s3.VALUE-s4.VALUE-s5.VALUE)))*100,2) cache_hit_percent FROM v$sysstat s1, v$sysstat s2, v$sysstat s3, v$sysstat s4, v$sysstat s5 WHERE s1.NAME='physical reads' AND s2.NAME='consistent gets' AND s3.NAME='db block gets' AND s4.NAME='physical reads direct (lob)' AND s5.NAME='physical reads direct') a, (select round(100*(1-sum(misses)/sum(gets)),2) latch_ratio_percent from v$latch) b, (select round(100*c.pin_RATIO/b.total,2) pin_ration_percent, round(100*(a.get_ratio/b.total),2) get_ratio_percent from ( select sum(pinhitratio) pin_ratio from v$LIBRARYCACHE) c,(select sum(gethitratio) get_ratio from v$LIBRARYCACHE) a, (select count(*) total from v$LIBRARYCACHE) b) d,(select round(100*(1-sum(getmisses)/sum(gets)),2) rowcache_hitratio from v$rowcache ) e, (SELECT round(100*s1.VALUE/(s2.VALUE+s1.VALUE),2) mem_sort_percent FROM v$sysstat s1 ,v$sysstat s2 WHERE s1.NAME='sorts (memory)' AND s2.NAME='sorts (disk)')f --3、产看内存配置，查看内存配置是否合理（结合查询到的各种指标，初步判断内存是否合理） --9i SELECT NAME,ROUND(VALUE/1024/1024) size_M FROM v$parameter WHERE NAME IN ('java_pool_size','large_pool_size','shared_pool_size','log_buffer','db_cache_size','pga_aggregate_target','sga_max_size','db_recycle_cache_size','db_keep_cache_size') ORDER BY size_m DESC; --10g select component,current_size/1024/1024 cur_size_M ,max_size/1024/1024 max_size_M from v$sga_dynamic_components -- 4、查询三个top10sql语句，发现执行次数最多、内存、硬盘读取量大的sql,根据address查询在系统中的执行计划，是否需要进一步优化 select '硬盘读取量最大的top '||rownum t1_id,sql_disk_reads,disk_reads,address from (select sql_text sql_disk_reads ,disk_reads from v$sqlarea order by disk_reads desc) where rownum<11 union all select '高速缓冲区使用最大的top '||rownum t2_id ,sql_buffer_gets ,buffer_gets,address from (select sql_text sql_buffer_gets,buffer_gets from v$sqlarea order by buffer_gets desc) where rownum<11 union all select '执行次数最多的top '||rownum t3_id ,sql_executions ,executions,address from (select sql_text sql_executions,executions from v$sqlarea order by executions desc) where rownum<11 --根据address查询在系统中的执行计划（真实执行计划），这个不同set autotrace on（这个是预执行计划，真实情况不一定， --这也是很多时候我们执行很好，但实际效率较低不得其解的原因） select lpad(' ', 2*(level-1))||operation||' '||decode(id, 0, 'Cost = '||position) "OPERATION",options, object_name from v$sql_plan start with (address ='' and id=0 ) connect by prior id = parent_id and prior address = address and prior hash_value = hash_value and prior child_number = child_number order by id, position ; --5、查询系统运行较长的sql语句，该语句能将多数的耗费资源的sql抓取到 select q.sql_text,s.elapsed_seconds,s.start_time,s.opname from v$session_longops s, v$sqlarea q where s.sql_hash_value=q.hash_value order by s.ELAPSED_SECONDS desc; --根据address查询在系统中的执行计划，是否需要优化 select lpad(' ', 2*(level-1))||operation||' '||decode(id, 0, 'Cost = '||position) "OPERATION",options, object_name from v$sql_plan start with (address ='' and id=0 ) connect by prior id = parent_id and prior address = address and prior hash_value = hash_value and prior child_number = child_number order by id, position ; --6、找到需要优化的sql语句，尝试优化，查看执行计划（set autotrace traceonly）,直到满意为止 --7、查看各个表空间的使用状况，注意收益率超过80%的表空间 SELECT tablespace_name,ROUND(used_percent,2) used_percent,ROUND(total_M,2) total_M,ROUND(used_M,2) used_M FROM ( SELECT d.tablespace_name , NVL(SUM(used_blocks),0)*8/1024 used_M, SUM(blocks)*8/1024 total_m ,NVL(SUM(used_blocks),0)*100/SUM(blocks) used_percent FROM v$sort_segment v ,dba_temp_files d WHERE d.tablespace_name=v.tablespace_name(+) GROUP BY d.tablespace_name UNION ALL SELECT ts.NAME tablespace_name, ts.size_M-fs.size_M used_M , ts.size_M total_M ,(ts.size_M-fs.size_M)/ts.size_M*100 used_percent FROM (SELECT ts.NAME ,SUM(bytes)/1024/1024 size_M FROM v$datafile df,v$tablespace ts WHERE df.ts#=ts.ts# GROUP BY ts.NAME ) ts , (SELECT tablespace_name ,SUM(bytes/1024/1024) size_M FROM DBA_FREE_SPACE GROUP BY tablespace_name ) fs WHERE ts.NAME=fs.tablespace_name ) ORDER BY used_percent DESC; --8、查看最大的前10大字段，是不是耗费资源的sql都是和这些大段有关 Select * from (Select segment_name,bytes/1024/1024 size_M,segment_type,tablespace_name from dba_segments order by bytes desc ) where rownum<11 以上是一个较为快捷的方式，短时间内定位问题，通常80%优化问题都能解决。『关闭该页』　『打印该页』 # 上一篇文章： C语言的实例编程绘制余弦曲线责任编辑:admin # 下一篇文章：没有了最近更新普通文章微软调查通过USB传播的木马利用普通文章暑期培训成新热点黑客紧盯频繁普通文章最容易破解的密码排行榜有没有普通文章千博企业网站管理系统v2010 Bu 普通文章如何利用非热门关键词让购物网普通文章无忧购物时尚ASP版ODAY 普通文章友情链接对PR的提升普通文章黑客入侵成全民公敌网络安全要普通文章洋文凭买卖新动向揭秘黑客入侵普通文章赛门铁克和McAfee等5家安全厂商热门文章固顶文章安防快线----免费试听课程固顶文章 09（新开）0基础学入侵班普通文章 C/C++语言学习资源普通文章流光的初级教程流光教程普通文章如何成为一名黑客普通文章原创：WIN7硬盘安装安装过程普通文章手动清除explorer.exe病毒普通文章网络验证破解教程普通文章 Windows Server2003 IIS服务器普通文章 3389终端服务器超出了最大允许相关文章没有相关文章 | 设为首页 | 加入收藏 | 联系站长 | 友情链接 | 版权申明 | 加入会员咨询QQ:82147377 投稿方法: 广告与合QQ:82147377 湘ICP备09015684号

查询sql