[ 更换 ]
热门城市
北京上海广州深圳成都杭州南京武汉天津西安重庆青岛沈阳长沙大连厦门无锡福州济南宁波昆明苏州郑州长春合肥南昌哈尔滨常州烟台南宁温州石家庄太原珠海南通扬州贵阳东莞徐州大庆佛山威海洛阳淮安呼和浩特镇江潍坊桂林中山临沂咸阳包头嘉兴惠州泉州三亚赣州九江金华泰安榆林许昌新乡舟山慈溪南阳聊城海口东营淄博漳州保定沧州丹东宜兴绍兴唐山湖州揭阳江阴营口衡阳郴州鄂尔多斯泰州义乌汕头宜昌大同鞍山湘潭盐城马鞍山襄樊长治日照常熟安庆吉林乌鲁木齐兰州秦皇岛肇庆西宁介休滨州台州廊坊邢台株洲德阳绵阳双流平顶山龙岩银川芜湖晋江连云港张家港锦州岳阳长沙县济宁邯郸江门齐齐哈尔昆山柳州绍兴县运城齐河衢州太仓张家口湛江眉山常德盘锦枣庄资阳宜宾赤峰余姚清远蚌埠宁德德州宝鸡牡丹江阜阳莆田诸暨黄石吉安延安拉萨海宁通辽黄山长乐安阳增城桐乡上虞辽阳遵义韶关泸州南平滁州温岭南充景德镇抚顺乌海荆门阳江曲靖邵阳宿迁荆州焦作丹阳丽水延吉茂名梅州渭南葫芦岛娄底滕州上饶富阳内江三明淮南孝感溧阳乐山临汾攀枝花阳泉长葛汉中四平六盘水安顺新余晋城自贡三门峡本溪防城港铁岭随州广安广元天水遂宁萍乡西双版纳绥化鹤壁湘西松原阜新酒泉张家界黔西南保山昭通河池来宾玉溪梧州鹰潭钦州云浮佳木斯克拉玛依呼伦贝尔贺州通化朝阳百色毕节贵港丽江安康德宏朔州伊犁文山楚雄嘉峪关凉山雅安西藏四川广东河北山西辽宁黑龙江江苏浙江安徽福建江西山东河南湖北湖南海南贵州云南陕西甘肃青海台湾内蒙古广西宁夏香港澳门
培训资讯网 - 为兴趣爱好者提供专业的职业培训资讯知识

善用性能工具进行SQL整体优化

优化 工具 性能

作者介绍

梁敬彬,福富研究院副理事长、公司唯一四星级内训师,国内一线知名数据库专家,在数据库优化和培训领域有着丰富的经验。多次应邀担任国内外数据库大会的演讲嘉宾,在业界有着广泛的影响力。著有多本畅销书籍,代表作有《收获,不止Oracle》。文末将有梁老师的新书《收获,不止SQL优化》大彩蛋哦~

SQL优化是一个复杂的工程,首先要讲究从整体到局部。今天我们首先学习关于数据库整体优化都有哪些性能工具,接着分析这些工具的特点,并结合案例进行探索,最后再进行总结和思考。

总体学习思路如下图所示:

都有哪些性能工具

这里首先要分成两部分:一种是不同调优场景的分析,可分为单纯场景的优化和复杂场景的优化;而另一种是基于这些场景的工具应用,就是针对单纯场景的优化手段和复杂场景的优化手段。

1、不同调优场景分析

我们继续探讨,单纯是有多单纯呢?哦,其实可以理解为无菌真空实验室里的实验。比如一条SQL很慢,原因是未走高效的索引查询而走全表扫描,加个索引就快了,执行速度从10s变成了0.1s;或者一条SQL执行速度被优化到1s左右,逻辑读控制在50个左右,应该就已经OK。这就是单纯的环境,我们差不多无须再考虑优化了。

那啥是复杂呢?那就是,刚才那个语句加了索引后,本应该从10s变成0.1s,结果还是10s,甚至变成30s了,这是咋回事呢?原来,现在系统是整体出问题了,数据库主机资源耗尽,啥语句都跑不快的。

还有那个逻辑读在50左右的SQL,如果一天执行几百几千万次,这要是能将逻辑读降低一点,得省多少的逻辑读啊。原来复杂环境真的很复杂,要考虑SQL本身没问题而是被环境影响,还要考虑SQL的执行频率,判断其调优价值与调优空间,这些在单纯的环境里,是不用考虑的。

2、不同场景对应工具

接下来,我们说说这两种场景对应的工具的使用。关于局部分析调优工具,这个其实就是在说SQL的执行计划了,这是SQL优化最重要的手段之一,通过分析执行计划,我们可以知道SQL语句的访问路径,知道它慢在哪里,从而进行SQL优化。由于在随后的章节中我们会详细介绍执行计划相关知识,这里就不再细述了。

关于整体的调优工具,这里我们先撇开主机、网络、存储等层面的因素,暂时从数据库的整体层面入手。主要工具有AWR、ASH、ADDM、AWRDD这四个工具。其中AWR是关注数据库的整体性能的报告;ASH是数据库中的等待事件与哪些SQL具体对应的报告;ADDM是Oracle给出的一些建议;而AWRDD是Oracle针对不同时段的性能的一个比对报告,比如今天早上9点系统很慢,而昨天这个时候很正常,很多人就想知道今天早上9点和昨天早上9点有什么不同,于是就有了这个报告。

整体分析调优是必需的,那么我们对此的学习也有规律可循。首先是获取系统整体信息的手段,一般通过报告和日志获取。好比破案一样,这就是收集证据的阶段。接下来要找到蛛丝马迹,那就是如何发现问题。在本书中就是需要关注提取到的这些报告的哪些要点、哪些关键字,具体流程图如下:

整体性能工具的要点

现代人对健康都比较重视,每年都会进行健康体检。其实数据库性能工具的应用(报告获取和关注要点)和体检是非常类似的。

1. 报告的获取

Oracle性能报告分成AWR、ASH、ADDM、AWRDD和AWRSQRPT这5个类型。

什么?这么多,好复杂啊,记也记不住,我不想听不想听!

别急,你只要去医院体检过,你就能听懂。

Really?

我们去医院体检,最终会得到一份体检报告,往往能看到很多总体性指标,这些指标会判断你是否健康。没毛病最好,万一有毛病,报告里要进一步判断是什么毛病,是高血压,还是骨质增生,还是胃有毛病……这就是现实中的体检报告。

而Oracle提供的一种性能收集和分析工具,它能提供一个时间段内整个系统资源使用情况的报告,这个报告里有很多总体性指标来判断系统是否健康。没毛病最好,万一有毛病,问题出在什么模块,是日志切换过于频繁,还是硬解析过大,还是某些SQL相关等待事件在耗资源……这就是AWR报告。这样看来,体检报告和AWR报告非常类似。

假设体检报告说你有胃病,很可能只告诉你胃有问题,却无法告诉你具体啥毛病,因为你手上的体检报告不会详细到拥有你胃部所有相关指标。你要得到这些指标需要做进一步信息收集,那就是胃镜。同样假设你的数据库是SQL相关等待事件问题,AWR报告很可能只告诉你有这个问题而无法告诉你是哪些SQL引发的。你要得到这些指标,想了解具体某些SQL和相关等待事件的对应需要做进一步的信息收集,那就是ASH报告。看来对比胃镜和ASH报告,二者也非常类似。

刚才说的胃病,或许是医生告诉你的,因为上面有很多指标你无法读懂,这时如果你能拿到一张医生的病历卡记录,这里没有指标,只有白底黑字用文字描述的病情,告诉你要如何治疗,那你一定会看得很明白。同样假设,如果将含各种晦涩的指标的数据库体检报告用一些白底黑字的文字代替,用文字直接说明数据库遇到了什么问题,告诉你该如何去优化,那新手一定会看得很明白,这就是ADDM报告。看来病历卡记录和ADDM报告,二者也非常类似。

假如你在一年前也做过体检,并将报告带到了医院,负责任的医生就一定会让你将旧的体检报告也提供给他。他会认真地比对两张报告,查看他关注的健康指标是否有异常波动,这些波动对医生很有参考意义,往往预示着病情的发展趋势。好了,别紧张,这只是比喻。

假设你有系统新旧两个时段的两份AWR报告,负责任的DBA一定会让你将旧的AWR报告也提供给他。他会认真地比对两份报告,查看他关注的数据库指标是否有异常波动,这些波动对DBA很有参考意义,往往预示着数据库性能瓶颈的发展趋势。Oracle提供了一个工具能够将两个时段的AWR报告合并,并能方便地显示出比对信息,这个工具就是AWRDD。看来医生分析前后两次体检报告的动作和AWRDD报告比起来,两者也非常类似。

大家知道做胃镜是一件很麻烦的事(类似ASH报告),如果没毛病就没必要让我们遭这罪。可万一体检报告无情地告诉你胃有毛病,甚至是医生分析你前后两次体检报告(类似ADDM)后告诉你胃病在加速中,你被迫无奈只好去做胃镜了。做完后医生发现你胃部有大量息肉,却无法判断这些息肉是否为良性。于是还要做进一步的检查,这就是活检。不要紧张,平时注意健康生活就好。同样ASH报告判断出某些SQL有问题,却无法得到执行计划等更详细的信息,只能依靠AWRSQRPT去获取这些信息。看来活检和AWRSQRPT报告比起来,两者也非常类似。

最后恭喜你,活检报告显示未产生癌变,只要好好治疗,注意身体,胃就能恢复健康!看本书的读者们,你们都是IT人士,生活无规律加班熬夜者居多,一定要注意身体哦!

对了,还有一件最重要的事没交代。大家似乎搞懂了Oracle五大性能报告,可是这些好东西在哪里才能得到呢?别着急,后续章节马上就会告诉你如何获取这五大性能报告。

2. 报告的关注点

如果患者拿着有各种晦涩指标的体检报告来到门诊请教医生,他一定会关注各种指标来判断患者具体是什么毛病。同样你也会对Oracle的性能报告中的各种指标进行关注来判断数据库出了什么毛病。两者非常类似,关注不同的指标,都是为了施救,前者救人,后者救数据库。

听起来是不是很激动,恨不得马上就要开始当救库英雄了!别急,接下来还要告诉你关注什么,然后在案例中让你感受一下什么叫救库英雄。

特别提醒:

这里有一个特别值得注意的地方,那就是性能报告的采样时间。Oracle默认是每小时产生一个采样点,你可以收集每个小时的性能报告。我们对此要敏感,比如你的性能故障是发生在今天早上7点~8点。然后系统自动恢复了,你获取一张8点~9点的性能报告来查问题,就毫无意义了。

3、五大性能报告的获取1AWR的获取与说明

获取AWR报告的方式有两种:一种是直接获取方式,调后台脚本awrrpt.sql来获取,执行方式一般是在sqlplus下执行@?/rdbms/admin/awrrpt.sql;

另一种则是通过调用命令包,获取dbms_workload_repository这个包的awr_report_html程序,用SQL命令的形式输出内容。

Select output from table(dbms_workload_repository.awr_report_html

(v_dbid, v_instance_number,v_min_snap_id,v_max_snap_id))

(1)直接获取

试验1(未使用批量提交):

接下来通过提示就可以生成AWR报告了,具体步骤略去,详情请扫本章最后的二维码。

试验2(单机下,正确使用批量提交):

接下来通过提示就可以生成awr报告了,具体步骤略去。

(2)通过调用命令包获取

直接调用工具包的方式,特别适合用在程序自动获取报告的场景。

注:其中977587123是数据库的主机标识,可以在数据库的数据字典中查到,1是标识实例,如果是RAC,就有1和2两个,单机就只有1。1920和1921是两个断点时间,比如9点和10点之间。

2ASH的获取与说明

获取ASH报告的方式也有两种:一种是直接获取方式,调后台脚本ashrpt.sql来获取,执行方式一般是在sqlplus下执行@?/rdbms/admin/ashrpt.sql;另一种则是通过调用命令包,获取dbms_workload_repository这个包的ash_report_html程序。用SQL命令的形式输出内容。

select output from table(dbms_workload_

repository.ash_report_html( dbid,inst_num,l_btime,l_etime)

(1)直接获取

说明:

  1. 如果你是一路回车,就是获取最近5分钟的ASH报告。

  2. 如果你根据Oldest ASH sample available 时间,然后回车,选择的是目前可收集的最长ASH运行情况。

  3. 你可以选择Oldest ASH sample available和Latest ASH sample available之间时间,然后输入时长,比如30表示30分钟,取你要取的任何时段的ASH报告。

  4. ASH报告的获取不同于AWR的地方在于,快照之间有无重启动作不影响报告的获取。

  5. ASH报告可以直接手工获取,比如select output from table(dbms_workload_ repository.ash_report_html( dbid,inst_num,l_btime,l_etime)。

(2)通过调用命令包获取

直接调用工具包的方式,特别适合用在程序自动获取报告的场景。

注:其中977587123是数据库的主机标识,可以在数据库的数据字典中查到,1是标识实例,如果是RAC,就有1和2两个,单机就只有1。SYSDATE-30/1440,SYSDATE-1/1440 分别是开始时间和结束时间。

3ADDM的获取与说明

获取ADDM报告的方式也有两种,一种是直接获取方式:调后台脚本addmrpt.sql来获取,执行方式一般是在sqlplus下执行@?/rdbms/admin/addmrpt.sql。另一种则是通过调用命令包的方式获取:调用dbms_workload_repository这个包的addm_report_html程序。用SQL命令的形式输出内容。

- Create an ADDM task.

DBMS_ADVISOR.create_task (

advisor_name => "ADDM",

task_name => "MYADDM",

task_desc => "MYADDM");

(1)直接获取

@?/rdbms/admin/addmrpt.sql

具体执行过程略去。

(2)通过调用命令包获取

注:直接调用工具包的方式,适合用在自动获取报告的场景。

4AWRDD的获取与说明

获取AWRDD报告一般是用直接获取的方式,这个脚本的交互部分需要输入要进行对比的两个awr报告的begin snap_id与end snap_id,然后输入对比结果报告的名称,这里就不详细介绍了,请读者自行试验完成。

直接获取:

@?/rdbms/admin/awrddrpt.sql

具体略去。

5AWRSQ获取与说明

获取AWRSQRPT报告的关键之处在于,交互部分要输入所要分析的SQL的SQL_ID,这是关键之处。而这个SQL_ID可以从AWR报告中获取。

以上5个报告的获取本身并不难,操作一遍就会了,笔者也会再提供在线操作视频,让大家实际体会一遍。现在关键在于,要明白这5个报告的作用和相互之间的区别,搞懂这些,调优之路就算完成过半了。当然,接下来如何分析读懂这五大报告的关键指标就非常重要了,有一些指标你必须关注,否则你就当不了“医生”了。

4、五大报告关注的要点1AWR的关注点

AWR报告是五大报告中最全面最重要的一个报告,它的相关指标也显得格外重要。这里我们列出DB Time、load_profile、efficiency percentages、top 5 events、SQL Statistics、Segment_statistics这6个指标入手分析。

(1)AWR关注点1之DB Time

DB Time这个指标主要用来判断当前系统有没有遇到相关瓶颈,是否较为繁忙导致等待时长很长。一般来说,Elapsed时间乘以CPU个数的时间如果结果大于DB Time,我们认为系统压力不大,反之则压力较大。如下例子中,60.11×64=3847.04<5990.6,说明系统现在还是比较繁忙的。

(2)AWR关注点2之load_profile

load_profile这个指标主要用来展现当前系统的一些指示性能的总体参数,比如经典的Redo size就是用来显示平均每秒的日志尺寸和平均每个事务的日志尺寸,结合Transactions这个每秒事务数的指标,就可以分析出当前事务的繁忙程度。

下图中显示每秒有6777.1个事务数,这在现实中几乎不可能,现实中的运营商系统一般在200上下比较正常,超过1000就属于非常繁忙了。

把上图和下面的图进行比较,就非常明显了,下图显示每秒有0.6个事务,平均每个事务产生的日志尺寸是7位数。这说明系统是一个提交不频繁的处理大任务事件的系统。而上图的尺寸是3位数。这里非常容易看出,这是一个提交非常频繁且每个事务都非常小的密集提交系统。

(3)AWR关注点3之efficiency percentages

efficiency percentages是一些命中率指标,其中Buffer Hit、Library Hit等都表示SGA(System global area)的命中率。在下图中Soft Parse指标表示共享池的软解析率,在OLTP系统中如果该指标低于90%应当引起你的注意,这表示存在未使用绑定变量的情况。我们通过比对两个报告,可以看出明显差异,如下面系列图所示。

报告1(未有效地使用绑定变量,产生大量硬解析的场景)。

上一篇:数据库教程-SQL Server查询结果列转行实现与分析

下一篇:开课吧数据产品经理课程包括什么?开课吧培训的怎么样

相关内容

怎么学?如何干?西藏7市(地)委书记在西藏日报发表文章

启航新征程 开创新局面为全面建设社会主义现代化新拉萨努力奋斗自治区党委常委、拉萨市委书记 普布顿珠党的二十大大笔擘画坚持以中国式现代化全面推进中华民族伟大复兴的宏伟蓝图,区党委十届三次全会全面铺开建设美丽幸福西藏、共圆伟大复兴梦想的壮阔实践···

2023年全区智慧旅游专业人才培训班圆满结业

6月2日,自治区旅游发展厅主办的全区智慧旅游专业人才培训班在拉萨圆满结业。培训共历时三天,累计完成全区旅游行政管理部门工作人员及涉旅企业专业技术人员培训100人。本次培训是区旅发厅深入推进学习贯彻习近平新时代中国特色社会主义思想主题教育重要···

徐汇萨迦共同举办大美西藏首届口腔学习班

随着现代医学的发展,口腔健康越来越受到人们的关注,为提高西藏地区口腔技术水平和服务质量,近日,徐汇区牙病防治所和萨迦县中心医院远程连线共同举办首届口腔学习班开班仪式。徐汇区卫生健康委副主任胡强,上海援藏干部、萨迦县委常务副书记、常务副县长沈···

自觉抵制“一对一”“一对多”等学科类培训!哈尔滨中小学生暑期预警来了

17日,记者从哈尔滨市各区教育局获悉,2023年暑假将至,南岗区、道里区、香坊区教育局向家长发出预警,自觉抵制违规培训,各区义务教育阶段学科类培训机构已经全部注销,暑假期间以任何形式开展的学科类培训均属于违规培训。家长要自觉抵制任何机构或个···

哈尔滨市道里区企投局举办“招商大讲堂”专题培训

黑龙江网讯(记者 王惠婷)10月24日,哈尔滨市道里区企投局组织开展“招商大讲堂”专题培训班,全区各招商专班负责同志及业务骨干参加培训。本次培训从实际需求出发,紧紧围绕当前招商工作中的热点、难点,对“什么是招商引资”“道里区的产业结构和主导···

团黑龙江省委举办全省青年文明号青年岗位能手学习宣传贯彻党的二十大精神培训交流会

中国青年报客户端讯(李海涛)为深入学习贯彻党的二十大精神,充分发挥青年文明号、青年岗位能手示范引领作用,在全省职业青年中掀起学习党的二十大精神热潮,11月22日,团黑龙江省委举办全省青年文明号青年岗位能手学习宣传贯彻党的二十大精神培训交流会···

辽宁葫芦岛举办外贸政策培训会推动外贸保稳增量

辽宁省葫芦岛市外贸政策培训会4月3日举办。 辽宁省贸促会供图中新网葫芦岛4月3日电 (李晛)辽宁省葫芦岛市外贸政策培训会4月3日举办。本次活动由辽宁省贸促会支持、葫芦岛市商务局主办,葫芦岛市贸促会、葫芦岛海关、中国出口信用保险辽宁分公司和辽···

山西运城:严查无证校外培训机构 查封9家警告2家

新华社太原8月5日电(记者王飞航)记者从山西省运城市政府了解到,运城市教育局近日联合市公安局等多家单位,对中心城区无证校外培训机构进行了一次突击检查,共检查了13家校外培训机构,查封9家,警告2家,发放整改通知书4份。今年7月,运城市教育局···

山西开展培训筑牢森林“防火墙”

山西新闻网3月30日讯(记者 卢奕如)今日,记者从山西省应急管理厅获悉,全省举办森林草原防灭火业务培训,邀请专家以视频会议形式,围绕森林扑火指挥实操、森林草原火灾防控经验做法、火灾现场各级各类指挥员具体操作中遇到的问题等内容进行授课。培训内···

校外培训机构治理工作取得进展 山西停办近1300所

资料图:小学生排队等待进入校园。中新社记者 刘文华 摄中新网5月11日电 据教育部网站消息,按照校外培训机构专项治理工作整体安排,教育部、民政部、国家市场监管总局启动了校外培训机构治理专项督查工作。5月9日至10日,督查组率先在北京市开展华···

山西综改区举办省技术创新中心申报培训

  8月18日,山西综改区科技金融部举办2023年度省技术创新中心申报培训会,来自区内企业、科研院所及有关单位代表160余人参加了培训。  山西省技术创新中心是以产业前沿引领技术和关键共性技术研发为核心的产业技术创新平台,承担着为区域和产业···

山西汾阳医院开展健康教育与控烟知识培训

来源:【吕梁日报-吕梁新闻网】本报讯 (记者 刘少伟) 5月18日,在“世界无烟日”到来之际,山西汾阳医院组织开展健康教育与控烟知识培训。近年来,山西汾阳医院全面落实健康中国战略,根据国家卫健委《关于2011年起全国医疗卫生系统全面禁烟的决···

山西省文物局年度田野考古技术培训班开班

10月10日,山西省文物局在运城闻喜上郭城址、邱家庄墓群举办2023年度田野考古技术培训班开班仪式。该次培训为期三个月,通过理论和实践两部分教学,旨在推进山西考古工作高质量发展,提升考古业务人员专业技术水平。本次培训由山西省考古研究院和山西···

最低每课时9元!全省学科类校外培训课时长和收费标准出台

近日,山西省发改委、省教育厅下发《关于中小学学科类校外培训收费标准及有关事项的通知》,明确全省中小学学科类校外培训收费标准,从12月17日起执行。《通知》对全省线上线下学科类校外培训基准收费标准和浮动幅度制定了科学标准。其中,义务教育阶段线···

山西天镇 阳光职业培训学校培养乡村“新农人”乡村振兴添动能

(记者 贺文生) 山西天镇县阳光职业培训学校紧紧围绕乡村振兴战略,按照“政府引导、农民自愿、立足产业、突出重点”的原则,创新高素质农民技能培训方式方法,采取以“授人以渔”的方式,让人才振兴成为助推农业农村现代化的内生动力,以高素质农民引领现···

山西:建立全过程 全链条 无缝隙安全培训制度

黄河新闻网讯(记者杨江涛)日前,山西省应急管理厅下发了《山西省安全培训管理暂行办法》(以下简称《办法》)。我省将进一步抓好安全生产这个基本盘、基本面,推动全省安全培训工作制度化、规范化、科学化,促进安全培训工作高质量发展。山西省应急管理厅厅···

山西:艺考培训机构纳入全国监管平台管理

央广网太原10月6日消息(记者郎麒) 日前,山西省教育厅、省发改委、省公安厅等部门联合制定《加强面向高中阶段学生艺考培训规范管理工作方案》,针对艺考培训的突出特点和实际情况,全面规范艺考培训行为,将艺考培训机构统一纳入全国校外教育培训监管与···

太平财险阳泉中支开展消防安全教育和有限空间作业培训

为强化员工安全意识,进一步提升员工消防和有限空间突发事件应急处理能力,9月14日,太平财险阳泉中支邀请北京市卫民安消防教育咨询中心山西分中心讲师向全体员工开展了一次消防安全教育和有限空间作业课程培训。按照防消结合、预防为主的原则,本次讲座通···

山西省数字化转型贯标试点工作宣贯培训会在太原举行

  10月20日消息,山西省数字化转型贯标试点工作宣贯培训会在太原举行。省工信厅介绍,作为国家数字化转型贯标试点省份,试点启动后将引导企业加快数字化转型,助力制造业高端化、智能化、绿色化发展。  今年,工信部启动数字化转型贯标试点工作,我省···

山西马兰花创业培训讲师大赛收官 太原市获多个奖项

山西新闻网8月31日讯(记者 冯耿姝)8月29日,山西省第四届马兰花创业培训讲师大赛圆满收官,太原市代表队在比赛中分获多个二、三等奖和优秀奖。本届大赛以“启迪创新思维·激发创业梦想”为主题,全省共有56名教师晋级复赛,其中,太原市有7名选手···