查看: 14362|回复: 23

数据库巡检优化-快速定位top10等待事件,定位数据库瓶颈

[复制链接]
招聘 : 数据库管理员
认证徽章
论坛徽章:
2
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:532011新春纪念徽章
日期:2011-02-18 11:42:50
发表于 2010-7-1 18:51 | 显示全部楼层 |阅读模式
--数据库巡检或性能优化方法各异,但首要的是要发现数据库性能瓶颈,系统自带的statspack,或awr太耗时,
--以下是本人原创,也是常用的方法,不敢独享,特此贴出,想看更多个人原创,请到http://space.itpub.net/24179204/
--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%优化问题都能解决。

[ 本帖最后由 ultradb 于 2010-7-8 13:57 编辑 ]
论坛徽章:
23
ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:482013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-7-1 19:26 | 显示全部楼层
理解并学习之

使用道具 举报

回复
论坛徽章:
13
2010广州亚运会纪念徽章:轮滑
日期:2010-09-03 12:44:53马上有房
日期:2014-04-04 13:51:34马上加薪
日期:2014-04-04 13:35:40优秀写手
日期:2014-03-14 06:00:13夏利
日期:2013-08-05 18:32:18复活蛋
日期:2013-06-25 17:22:592013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2013-01-08 18:08:502011新春纪念徽章
日期:2011-02-18 11:43:33生肖徽章2007版:兔
日期:2011-01-20 12:58:49
发表于 2010-7-2 02:21 | 显示全部楼层
好贴,必须顶

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-7-2 08:19 | 显示全部楼层
好,多谢分享。

使用道具 举报

回复
论坛徽章:
0
发表于 2010-7-2 10:19 | 显示全部楼层
莫非这就是传说中的赤膊

使用道具 举报

回复
论坛徽章:
73
2010新春纪念徽章
日期:2010-03-01 11:06:132011新春纪念徽章
日期:2011-02-18 11:43:332012新春纪念徽章
日期:2012-01-04 11:55:422013年新春福章
日期:2013-02-25 14:51:242014年新春福章
日期:2014-02-18 16:44:08马上有车
日期:2015-01-19 09:45:33最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-11-27 10:16:10问答徽章
日期:2013-10-23 15:13:22林肯
日期:2013-11-02 08:34:46
发表于 2010-7-2 11:57 | 显示全部楼层
xxie

使用道具 举报

回复
论坛徽章:
39
祖国60周年纪念徽章
日期:2009-10-09 08:28:00马上有房
日期:2015-01-12 10:57:17ITPUB社区OCM联盟徽章
日期:2014-08-27 17:33:52青年奥林匹克运动会-帆船
日期:2014-08-27 13:50:412014年世界杯参赛球队: 哥伦比亚
日期:2014-07-10 14:10:592014年世界杯参赛球队:墨西哥
日期:2014-06-24 10:38:072014年世界杯参赛球队: 加纳
日期:2014-06-23 13:12:032014年世界杯参赛球队: 美国
日期:2014-05-21 08:18:362014年世界杯参赛球队: 瑞士
日期:2014-05-20 13:38:33暖羊羊
日期:2015-02-10 17:19:24
发表于 2010-7-2 14:35 | 显示全部楼层
谢谢分享

使用道具 举报

回复
论坛徽章:
26
授权会员
日期:2007-06-27 09:39:552010新春纪念徽章
日期:2010-03-01 11:08:242010数据库技术大会纪念徽章
日期:2010-05-13 09:34:232010年世界杯参赛球队:斯洛伐克
日期:2010-05-25 13:42:01ITPUB9周年纪念徽章
日期:2010-10-08 09:34:032011新春纪念徽章
日期:2011-02-18 11:43:34双黄蛋
日期:2011-07-29 16:22:53ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152012新春纪念徽章
日期:2012-01-04 11:51:22奥运会纪念徽章:跆拳道
日期:2012-07-04 11:25:40
发表于 2010-7-2 14:50 | 显示全部楼层
这个得顶一下。。。
谁写的。。。

[ 本帖最后由 lixiang114 于 2010-7-2 15:00 编辑 ]

使用道具 举报

回复
论坛徽章:
51
蒙奇·D·路飞
日期:2017-12-13 16:57:22问答徽章
日期:2013-10-22 16:28:59大众
日期:2013-10-12 09:52:07红旗
日期:2013-09-11 12:25:22本田
日期:2013-08-30 15:30:10Jeep
日期:2013-08-28 10:20:34咸鸭蛋
日期:2013-04-12 11:24:162013年新春福章
日期:2013-02-25 14:51:24灰彻蛋
日期:2013-02-22 14:08:04双黄蛋
日期:2013-01-09 14:03:02
发表于 2010-7-2 15:15 | 显示全部楼层
好长的sql呀,是不是功力越深厚,sql就能写得越长。。。

使用道具 举报

回复
论坛徽章:
10
2010新春纪念徽章
日期:2010-01-04 08:33:082011新春纪念徽章
日期:2011-02-18 11:43:352012新春纪念徽章
日期:2012-01-04 11:54:46一汽
日期:2013-09-09 13:57:04ITPUB社区12周年站庆徽章
日期:2013-10-08 17:44:422014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-7-2 15:24 | 显示全部楼层
很好的分享,顶个

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表