查看: 8753|回复: 36

[原创] 《oracle性能优化内幕》--笔记连载

[复制链接]
论坛徽章:
7
2011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:羽毛球
日期:2011-03-31 09:40:352010广州亚运会纪念徽章:卡巴迪
日期:2011-04-26 13:46:28ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292012新春纪念徽章
日期:2012-01-04 11:57:36ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00优秀写手
日期:2013-12-18 09:29:11
发表于 2011-3-28 11:18 | 显示全部楼层 |阅读模式
最近在阅读关于oracle性能优化的知识,希望通过自己整理的内容,和大家分享一下,同时希望大家能够多支持和鼓励。
论坛徽章:
7
2011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:羽毛球
日期:2011-03-31 09:40:352010广州亚运会纪念徽章:卡巴迪
日期:2011-04-26 13:46:28ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292012新春纪念徽章
日期:2012-01-04 11:57:36ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00优秀写手
日期:2013-12-18 09:29:11
 楼主| 发表于 2011-3-28 11:19 | 显示全部楼层

《第一章》 关于性能和优化

1.性能是什么:
性能通常可以通过吞吐量、响应时间、可用性等参考指标来衡量,而性能优化的目标就是在单位资源环境下增加吞吐量,减少响应时间,提高可用性。
吞吐量:比如每秒处理的交易是25笔/秒,如果通过增加一些资源可以是吞吐量提高到29
笔/秒,我们还可以通过减少平均响应时间来增加我们交易的吞吐量;
响应时间:我们典型交易平均响应时间为5秒,如果通过改进,我们将平均响应时间降低到4秒,那我们就产生了优化效果。
以上两种指标往往是联系在一起的,我们在性能优化的过程中既要考虑到我们是否有吞吐量的限制,也要考虑到我们的响应时间是否满足当前客户需求。
可用性:备份和恢复策略、容灾、分布(集群、垂直、水平)、读写分离,都可以是提高系统可用性的策略,可用性的提高不是盲目的,要根据企业的资金投入,企业的目标去界定。

2.优化是什么:
Oracle系统的优化就是通过监控当前系统运行的状态和收集用户关于提高性能的一个指标,
通过专业的技术手段去达到目标,所以对于优化的概念我们要搞清以下几点:
何时优化
优化什么
瓶颈,那块是当前系统运行的瓶颈,我们就去优化它,无论是要增加资源还是要提高SQL
等,目的就是解决这些瓶颈问题。
优化程度
优化时无止境的,达到我们制定的优化目标即可停止,否则可能会带来更多的负面影响。
何时停止

3.性能管理方式:
主动性能管理:
定期性能监控,避免风险变成问题;如果从体系结构中考察主动性能管理,包括硬件选择、操作系统、性能和功能规划、海量存储系统选择、IO子系统配置及优化;从调度方面考虑可以是系统作业合理分布以达平衡。
被动性能管理:
指性能评估,性能优化、故障排除以及在现有的软件和硬件环境进行巧妙的优化等。

4.优化方法:
Oracle优化的方法是基于事件而非高的命中率,所以跟踪我们当前系统的哪些主要的等待事件,从而去分析引起这些等待的原因。最基本方法:二叉优化。
即从OS和DB两方面考虑,从一端向前推进,直到达到另一端,当二者相遇,就可能将问题的暴漏出来,从而使用有效的方法去解决。这种方法是以事件驱动的方式进行探索:
(1).建立合理的性能优化目标
如:XXX花费XXX(时/分/秒),但要求它在XXX(时/分/秒)内完成执行;
XXX使用了XXX资源量,但它不能使用超过XXX;
我们需要根据业务要求与客户讨论分析确定性能优化的目标。
(2)测量并记录当前性能
需要在业务峰值活动时间获取性能快照、基线等数据,关键就是在出现问题时间段类的信息采集,采集数据还要注意一下几点:
不要在数据库实例启动后几分钟就开始采集,需要让其运行一段时间;不要让采集时间段过长,这些往往会忽略一些问题,可以在峰值时间段内每隔15分钟;还需要注意参数
TIMED_STATISTICS应设置为true,这样才确保你的动态性能视图有相关数据。
(3).确定当前oracle的性能瓶颈(oracle在等待什么,哪些SQL是等待事件的部分)
重要性能视图分析:
v$system_event:
oracle系统中所有等待事件,包括了实例最后一次启动的所有等待事件,当实例重新启动时将会设置为零,我们可以通过在两个时间点之间作一次差值去分析等待事件的增量。主要常见的一些等待事件:
select * from v$system_event
where event in(\'buffer busy wait\',
               \'db file sequetial read\',
               \'db file scattered read\',
               \'enqueue\',
               \'free buffer waits\',
               \'latch free\',
               \'log file parallel write\',
               \'log file sync\');

v$session_event:
提供的信息与v$system_event,只是它是在session级别。它还包含了SID,可以通过与v$session结合去分析某个特定的会话。查询当前会话中一些等待事件:
select s.USERNAME,s.PROGRAM,s.STATUS,
se.EVENT,se.TOTAL_WAITS,se.TOTAL_TIMEOUTS,se.TIME_WAITED,se.AVERAGE_WAIT
  from v$session s,v$session_event se
where s.SID=se.SID
   and s.STATUS=\'ACTIVE\'
   and s.USERNAME is not null
   and se.EVENT not like \'SQL*Net%\';

v$session_wait:
提供每个事件最基础信息,信息是实时显示的,其中P1~P3提供了等待的详细信息
P1:含有文件号,利用P1查询DBA_DATA_FILES,V$FILESTAT
P2:含有进程正等待的块号,利用P2查询DBA_SEGMENTS,SYS.UET$,可能确定会话正等待的对象;除等待时间外,如果是shuang锁等待,P2指定shuang锁号,从而通过v$latch
P3:含有在P2指定的块号中块数
根据显示的P1 P2信息去查询发生争用的块
select t.owner,t.segment_name,t.segment_type,t.tablespace_name
  from dba_extents t
where t.file_id=&p1
   and &p2 between t.block_id and t.block_id+t.blocks-1;

通过对应的hashvalue值获取对应的SQL
create or replace function return_sqltxt(hashvalue in v$sqltext.HASH_VALUE%type)
return varchar2
is
  v_sqltxt varchar2(32767);
  cursor cur_sqltxt is
  select piece,sql_text
    from v$sqltext
   where hash_value=hashvalue
   order by piece;
begin
  for rec_sqltxt in cur_sqltxt loop
    v_sqltxt:=v_sqltxt||rec_sqltxt.sql_text;
  end loop;
  return v_sqltxt;
end;

select sid,return_sqltxt(sql_hash_value)
  from v$session
where sid=&sid;--sid可以通过上述分析得出有可能存在资源争用的session。

关于v$session_wait中几个字段解释:
state:状态,提供了wait_time和seconds_in_wait两列的详细信息,如果不理解该状态,可能这两列毫无意义
该字段有以下几个值:
waiting
waited unknown time(如果timed_statistics设置为false则为该状态)
waited short time,如果不是很频繁出现,这个等待事件可以忽略
waited known time,如果一个进程请求它正等待的资源,state值将变为waited known time)
wait_time:当state字段不是waited known time时,则该字段毫无用处
seconds_in_wait:当state字段不是wait time时,该字段毫无意义
查看当前会话中等待详细信息
select sw.SID,s.USERNAME,sw.EVENT,sw.WAIT_TIME,
       sw.STATE,sw.SECONDS_IN_WAIT
  from v$session s,v$session_wait sw
where s.SID=sw.SID
   and sw.EVENT not like \'SQL*Net%\'
   and s.USERNAME is not null
order by sw.WAIT_TIME desc;

上述所有根据数据字典视图去分析,特别适合某个特定的应用程序性能分析,如执行xxxx查询很慢。

4把等待时间记入跟踪文件
使用10046事件跟踪当前会话或其他用户会话的的等待事件。

5确定当前OS瓶颈
监控CPU:
sar -u 5 10(后面的5表示每隔5秒打印一次,10表示共打印10次):
%user             %system        %iowait              %idle
当前用户          操作系统内部   进程当前正使用CPU    空闲的CPU
占用cpu的百分比   工作占用cpu    但却等待IO服务请求

设备使用情况:
sar -d 5 10

虚拟内存使用情况:
vmstat -S

监视进程情况:
top

监控系统网络:
netstat
(6).优化所需成分(应用程序,OS,IO,争用,数据库)
没有东西可以代替最佳的SQL语句(一个1w行的本地表连接一个1000w行的远程表)
(7).跟踪并实施更改控制过程
(8).测量并记录当前性能
(9).重复3到7步骤,直到满足性能优化目标为止

使用道具 举报

回复
论坛徽章:
49
2010广州亚运会纪念徽章:台球
日期:2010-09-14 17:25:29ITPUB官方微博粉丝徽章
日期:2011-07-11 13:10:57ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-03-15 14:24:252012新春纪念徽章
日期:2012-01-04 11:53:54紫蛋头
日期:2012-03-07 10:09:01生肖徽章2007版:龙
日期:2012-03-07 10:13:00蜘蛛蛋
日期:2012-04-01 11:20:46奥运会纪念徽章:艺术体操
日期:2012-08-06 09:08:41奥运会纪念徽章:艺术体操
日期:2012-08-27 17:37:53
发表于 2011-3-28 13:08 | 显示全部楼层
up

使用道具 举报

回复
认证徽章
论坛徽章:
20
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44itpub13周年纪念徽章
日期:2014-09-28 10:55:54马上有钱
日期:2014-12-14 22:33:02美羊羊
日期:2015-02-28 17:32:28沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:58:18慢羊羊
日期:2015-05-01 17:38:23美羊羊
日期:2015-06-29 21:06:54秀才
日期:2015-08-28 09:17:412014年世界杯参赛球队: 瑞士
日期:2014-06-13 11:23:53
发表于 2011-3-28 13:25 | 显示全部楼层
顶楼主

使用道具 举报

回复
求职 : 数据库管理员
论坛徽章:
10
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:22茶鸡蛋
日期:2013-06-05 12:50:512013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-05-27 10:23:002013年新春福章
日期:2013-02-25 14:51:24茶鸡蛋
日期:2013-02-22 15:45:00ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:482012新春纪念徽章
日期:2012-01-04 11:55:05ITPUB社区千里马徽章
日期:2013-06-09 10:15:34
发表于 2011-3-28 14:36 | 显示全部楼层
占坑~~

使用道具 举报

回复
论坛徽章:
102
现任管理团队成员
日期:2011-05-07 01:45:08ITPUB十周年纪念徽章
日期:2012-11-26 16:33:25慢羊羊
日期:2015-03-04 14:19:442015年新春福章
日期:2015-03-06 11:57:312016猴年福章
日期:2016-02-23 09:58:34
发表于 2011-3-28 15:36 | 显示全部楼层
再继续

使用道具 举报

回复
论坛徽章:
15
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44马上有房
日期:2014-02-18 16:42:022014年新春福章
日期:2014-02-18 16:42:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:07:312012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15灰彻蛋
日期:2011-06-09 13:34:032011新春纪念徽章
日期:2011-02-18 11:43:32设计板块每日发贴之星
日期:2010-07-04 01:01:02
发表于 2011-3-30 17:29 | 显示全部楼层
顶一下

使用道具 举报

回复
论坛徽章:
0
发表于 2011-3-30 23:32 | 显示全部楼层
顶LZ

使用道具 举报

回复
论坛徽章:
2
2011新春纪念徽章
日期:2011-02-18 11:43:36ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28
发表于 2011-3-31 00:37 | 显示全部楼层
学习学习,谢谢!!

使用道具 举报

回复
论坛徽章:
54
慢羊羊
日期:2015-06-11 16:16:52复活蛋
日期:2013-06-29 22:38:19迷宫蛋
日期:2013-04-30 16:45:27鲜花蛋
日期:2013-04-03 14:04:102013年新春福章
日期:2013-02-25 14:51:24咸鸭蛋
日期:2013-01-14 15:33:02茶鸡蛋
日期:2012-12-21 13:49:22奥运会纪念徽章:马术
日期:2012-10-26 13:48:21奥运会纪念徽章:体操
日期:2012-10-11 13:53:20ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19
发表于 2011-3-31 09:35 | 显示全部楼层
是兔子不?

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表