分类目录归档:Performence Tuning

固定执行计划-使用SPM(Sql Plan Management)固定执行计划

测试目的: 使用SPM(Sql Plan Management)固定执行计划和hint中指定no index,谁的优先级高? . 固定执行计划-使用SQL Tuning Advisor 固定执行计划-手工指定PLAN OUTLINE 固定执行计划-手工指定索引名称的方式 固定执行计划-使用coe_xfr_sql_profile固定执行计划 固定执行计划-使用SPM(Sql Plan Management)固定执行计划 . 在Oracle 11g前,我们可以借助存储大纲(Stored Outline)和SQL Profile来帮助我们固定某个SQL语句的执行计划。 11g中,Oracle 提供了SPM(Sql Plan Management)。 通过这个特性,可以考虑让Oracle自动去判断某个SQL的新的执行计划是否更加合理(成本更低),只有在新的执行计划比原来的执行计划更好才会被使用,从而保护了执行计划的稳定性和SQL语句的执行效率。 可以考虑手工捕获和自动捕获两种方式,这里我们采用手工捕获(11.2的缺省设置是非自动捕获)。 首先查看当前为禁止自动捕获的状态: 当前SPM中没有内容: 手工加载一个SQL到SPM中: 由于上一个测试,我们已经有了正确的执行计划,即: 下面,我们装载指定的执行计划: 确认该执行计划的OUTLINE: 这里看到是我们需要的走索引的outline,详细的OUTLINE信息如下: 现在我们删除profile以前用coe绑定的sql profile: 再次验证SPM的执行计划: 执行SQL,发现SPM可以固定执行计划,使用了我们期待的: 这里看到已经使用了SPM中的SQL Profile:SQL_PLAN_0k9pqn7v9yvuw02b73393 … 继续阅读

发表在 Performence Tuning | 标签为 , | 留下评论

固定执行计划-使用coe_xfr_sql_profile(BASELINE)固定执行计划

测试目的: 使用coe_xfr_sql_profile的方式固定执行计划和hint中指定no index,谁的优先级高? 在SQLT工具中包含了几个轻巧方便的coe脚本,用来固定执行计划,其中coe_xfr_sql_profile是我常用的。 . 固定执行计划-使用SQL Tuning Advisor 固定执行计划-手工指定PLAN OUTLINE 固定执行计划-手工指定索引名称的方式 固定执行计划-使用coe_xfr_sql_profile固定执行计划 固定执行计划-使用SPM(Sql Plan Management)固定执行计划 . 查看当前的SQL Profile: 使用coe_xfr_sql_profile脚本自动识别和指定执行计划: 因为删除了SQL Profile,对应的执行计划也会被删除,因此当前只有一个全表扫描的执行计划(上次测试留下的),以前的执行计划都不在了。 上面的执行过程中已经告诉我们,这个执行计划会使用全表扫描(q'[FULL(@”SEL$1″ “LUNARTEST1″@”SEL$1”)]’): 我们查看一下这个SQL Profile的主要内容: 现在我们产生一个正确的执行计划,让该sql执行时使用到索引: 找出sqlid 找出正确的outline: 这里我们看到已经使用了索引。 使用coe_xfr_sql_profile固定执行计划: 现在,测试一下,使用SYS用户绑定是否会影响其他用户使用执行计划: 再次执行查询: 这里我们看到该SQL还是忽略了hint而使用了索引。 结论: 1,使用SYS用户绑定执行计划不会影响其他用户使用该SQL Profile及执行效果 2,这里已经使用了我们的SQL PROFILE(coe_bjgduva68mbqm_3241900148),sql使用了索引,说明coe_xfr_sql_profile绑定执行计划的方式比hint的优先级高

发表在 Performence Tuning | 标签为 | 留下评论

固定执行计划-手工指定索引名称的方式

测试目的:手工指定索引的方式绑定的执行计划和hint中指定no index,谁的优先级高? . 固定执行计划-使用SQL Tuning Advisor 固定执行计划-手工指定PLAN OUTLINE 固定执行计划-手工指定索引名称的方式 固定执行计划-使用coe_xfr_sql_profile固定执行计划 固定执行计划-使用SPM(Sql Plan Management)固定执行计划 . 由于上一个测试中,已经绑定了SQL Profile。 这里我们需要先删除该SQL Profile,再手工指定索引的方式绑定执行计划试试看。 确认下SQL PROFILE的内容: 删除这个sql profile 通过sqlprof_attr来实现手工指定索引的方式绑定执行计划,执行计划起名为“Lunar_bjgduva68mbqm_profile”: 查看这个Lunar_bjgduva68mbqm_profile的一些参数,确认是我们指定的索引: 再次执行SQL,看看这个Lunar_bjgduva68mbqm_profile是否生效: 结论: 这里看到已经使用了Lunar_bjgduva68mbqm_profile,但是SQL并没有按照Lunar_bjgduva68mbqm_profile中指定的索引名称使用索引。 这里已经使用了我们的SQL PROFILE,但是仍然走全表扫描 说明hint覆盖了手工指定索引的方式绑定的执行计划,说明手工指定索引名称的方式比hint的优先级低。

发表在 Performence Tuning | 标签为 | 留下评论

固定执行计划-手工指定PLAN OUTLINE

测试目的: hint和手工指定OUTLINE参数的方式来帮顶执行计划,谁的优先级高? . 固定执行计划-使用SQL Tuning Advisor 固定执行计划-手工指定PLAN OUTLINE 固定执行计划-手工指定索引名称的方式 固定执行计划-使用coe_xfr_sql_profile固定执行计划 固定执行计划-使用SPM(Sql Plan Management)固定执行计划 . 由于上一个测试中,已经绑定了sql使用SQL Tuning Advisor中的执行计划,从而使SQL走索引了(覆盖了hint)。这里我们需要先删除该SQL Profile,再手工绑定试试。 确认下SQL PROFILE的内容: 这里我们看到该SQP Profile中提供了详细的表和列的统计信息 并且有“IGNORE_OPTIM_EMBEDDED_HINTS”,也就是忽略嵌入到SQL中的hint 确认一下,当前SQL语句使用了该SQL Profile: 这里我们看到,当前执行确实使用了SQL profile SYS_SQLPROF_015236655fb80000,因此hint no_index失效了 (优先级低于SYS_SQLPROF_015236655fb80000,具体参见固定执行计划-使用SQL Tuning Advisor) 查看SYS_SQLPROF_015236655fb80000的OUTLINE信息: 这里我们看到该SQP Profile中提供了详细的表和列的统计信息 并且有“IGNORE_OPTIM_EMBEDDED_HINTS”,也就是忽略嵌入到SQL中的hint 现在,我们删除这个SQL Profile,稍后使用上面的OUTLINE手工绑定执行计划: 此时,应该正常按照hint走了全表扫描: 手工指定sqlprofile参数的方式绑定执行计划: … 继续阅读

发表在 Performence Tuning | 标签为 | 留下评论

固定执行计划-使用SQL Tuning Advisor

测试目的,当有hint时,并且hint跟需要绑定的执行计划有冲突,谁的优先级高。 . 固定执行计划-使用SQL Tuning Advisor 固定执行计划-手工指定PLAN OUTLINE 固定执行计划-手工指定索引名称的方式 固定执行计划-使用coe_xfr_sql_profile固定执行计划 固定执行计划-使用SPM(Sql Plan Management)固定执行计划 . 这里是第一个测试,使用SQL Tuning Advisor来测试: 创建测试用例: 执行查询,我们看到sql按照hint的方式没有使用索引,而是全表扫描,这是我们预期的结果: 下面我们运行SQL Tuning Advisor来生成建议报告: 查看生成的报告内容: 这里我们看到SQL Tuning Advisor提示了两个建议: 1,收集统计信息: 并且给出了这个执行计划和原始执行计划的对比,可以看到 执行效率提高了89%以上,逻辑读从23降低为2,减少了91.3%。 . 下面我们按照建议执行。 首先收集统计信息: 然后我们接受建议中的执行计划: 现在,再次查询看看效果: 这里我们看到,这个执行计划中已经使用了索引,并且逻辑读从49降低为14, 但是这里还有物理读,因此,我们再次执行看看: 逻辑读从14降低为3,这个执行计划已经是我们需要的。 现在我们查看一下这个SQL Profile的OUTLINE: 这里我们看到该SQP Profile中提供了详细的表和列的统计信息 … 继续阅读

发表在 Performence Tuning | 标签为 | 留下评论

SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report

SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report 1,查看当前STS中的SQL数量: 删除一些没用的: 2,在新库创建Lunar_11201STS_LUNAR SQLSET集 —(2)使用LUNAR用户,创建STS:Lunar_11204STS_LUNAR —(2)使用LUNARSPA用户,将源库的LUNAR.Lunar_11201STS_LUNAR的SQL优化器映射到LUNARSPA.Lunar_11204STS_LUNAR 使用LUNARSPA用户执行remap: 至此,SPA在新库的数据已经准备完毕,可以开始生成SPA报告了。 常见报告的就提步骤如下: 1)创建SPA任务 2)生成11.2.0.1的SPA Trail,采用STS转化方式 5 执行比较任务(一般取Elapsed Time、CPU Time、Buffer Get等指标) 6 生成SPA报告 生成的报告一般如下:

发表在 Performence Tuning | 留下评论

SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set

SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report . SPA(SQL Performance Analyzer , SQL 性能分析器),是11g引入的新功能,主要用于预测潜在的更改对 SQL 查询工作量的性能影响。 一般有几种情况下,我们会建议做SPA: 1,OS版本发生变化 2,硬件发生变化 3,数据库版本的升级 4,实施某些优化建议 5, 收集统计信息 6,更改数据库参数 等等 . SPA的主要实施步骤如下: 1, 在生产系统上捕捉SQL负载,并生成SQL Tuning Set; 2, 创建一个中转表,将SQL Tuning Set导入到中转表,导出中转表并传输到测试库; 3, 导入中转表,并解压中转表的数据到SQL Tuning Set; 4, 创建SPA任务,先生成10g的trail,然后在11g中再生成11g的trail; … 继续阅读

发表在 Performence Tuning | 标签为 | Comments Off on SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set

奇怪的AWR–部分sql执行次数跟实际情况相差10倍之多

应用通知有个应用很慢,于是查原因 下面是AWR的采样时间: 从AWR可以看到该语句30分钟内,这两条应用反应慢的语句的执行次数分别在930万次和950万次,这个跟应用的反映差了一个数量级: 但是应用反映,实际上在应用中实际设置的加载量是10分钟25万次。 这就很奇怪了,首先用logminer随机抽查了10分钟的归档,并挖掘了数据: 根据Logminer的挖掘的结果,发现实际执行次数跟应用反馈的执行次数差不多,跟AWR的数据相差大概10倍: 再查一下其他语句: 检查awr发现,该语句的执行次数基本跟吻合。 这里也就是说,部分语句执行次数相差10倍,但是部分语句的执行次数又是正常的,奇葩! 检查share pool,发现大量sql异常LUNARDB 这里的sql信息感觉严重失真,貌似重复了很多,也就是应该purge的sql在V$SQL中没有被更新,而是越积越多…… 重启服务器后,恢复正常,目前还没有找到具体的bug信息。

发表在 Performence Tuning | 标签为 | 留下评论

insert 的enq: TM – contention的情况-1 —–对有主外键关系的表的操作

insert 的enq: TM – contention的情况-1 —–对有主外键关系的表的操作 insert 的enq: TM – contention的情况-2 —–只有主键约束无子表的情况 #################################################################################### 总结1 :当外键无索引时 1,对子表的insert操作所在的事务没有完成前,对于父表的DML操作(INSERT/UPDATE/DELETE)都会因为不能获得对子表的TM锁而出现enq: TM – contention。 2,在总结1的基础上,如果又有了对子表的insert(我没有测试,但是我怀疑这里如果改成对子表的update和delete,也是相同道理……). 那么这个对子表的insert同样被阻塞,等待事件也是 enq: TM – contention。 3,对父表的insert会阻塞对父表的delete。同样的道理,此时对父表的insert也阻塞对父表的update pk操作。 #################################################################################### ######################################################### 当没有索引的时候: ######################################################### 测试1, 在子表发生:Insert,然后在父表上有update操作 Session 1: 对子表进行insert,不commit时: 这是,我们看到,该回话在主表(1062788 DEPT)和子表(1062790 EMP)上都分别持有了 exclusive … 继续阅读

发表在 Performence Tuning | 标签为 | 留下评论

11.2定时任务引起的系统负载异常—案例1

今天同事反映,周末新切换的一个数据库CPU load定期出现高峰,图形怪异: 检查了一下系统的定时任务: 主要是resource manager的定时维护任务,因此手工关闭定是维护任务。 这里,EXFSYS是Oracle Expression Filter 组件的owner,根据mos的建议,可以卸载该组件: 然后停止相关的自动维护的job: 从zabbix上观察,11点02分操作完成后,到现在为止,系统已经平稳了,O(∩_∩)O哈哈~

发表在 Performence Tuning | 标签为 | 留下评论