发现问题:
通过statspack(statspack的bug导致buffer gets为负值,部分top sql无法正确列出),经观察,由于两个数据库同在一台主机,c数据库也消耗了主机1/4的CPU资源,也存在着未优化的sql语句,具体如下:该语句从2012年1月13日凌晨开始出现,消耗了C数据库80%的资源,即整个主机15%的CPU。需要提前说明一,由于历史原因,该数据库使用RBO。
CPU Elapsd
Buffer GetsExecutions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------------------- -------------- ------ -------- --------- ----------
3,968,965,486 26914,754,518.5 81.9 ######## 36067.8626012873
Module: JDBC ConnectClient
select distinct a.transactionid, a.tansactioncode, a.brand,a.m
sisdn, a.new_msisdn,a.user_status, a.status_chg_time,a.status
_opr_time,a.create_time, a.rec_sts, '08' as src,b.BILL_FLG,b.SP
_ID,b.BIZ_CODE from aicbs_usc_proc_batcha,aicbs_spu_biz_order
b where a.msisdn = b.msisdn and rec_sts=:v0 andb.biz_type = :v
1 and b.STS != 'D' and a.user_status!='05' order bya.create_ti
me asc
优化方法:
调整表B和表A的顺序(具体如下),使oracleRBO优化器使用小表作为驱动表,经测试单个执行时间从120秒可以下降到1秒的时间,估计能使主机CPU利用率下降10%。
修改后的sql如下:
select distincta.transactionid,
a.tansactioncode,
a.brand,
a.msisdn,
a.new_msisdn,
a.user_status,
a.status_chg_time,
a.status_opr_time,
a.create_time,
a.rec_sts,
'08' as src,
b.BILL_FLG,
b.SP_ID,
b.BIZ_CODE
fromaicbs_spu_biz_order b,aicbs_usc_proc_batch a,
where a.msisdn =b.msisdn
and rec_sts = :v0
and b.biz_type = :v1
and b.STS != 'D'
and a.user_status != '05'
order bya.create_time asc
原理补充:
基于规则的优化器(RBO)使用oracle内部的执行规则来确认sql的执行路径。另外,ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(我们称它为驱动表或基础表,drivingtable)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
参考资料:
http://blog.csdn.net/zhangao0086/article/details/6250777
分享到:
相关推荐
Oracle_SQL优化 Oracle的优化器有两种方式,一种是基于规则的优化方式(RBO),另外一种是基于代价的优化方式(CBO),Oracle推荐在oracle8以后的版本强烈推荐使用CBO的方式。
ORACLE CBO RBO 优化
第一部分 ORACLE系统优化基本知识 23 第1章 ORACLE结构回顾 23 §1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1...
本文介绍了SQL的执行过程,ORACLE优化器的基本原理,优化器的选择和应用,以及如何建立 和管理索引来提高程序的执行效率。并以实际工作经验介绍了优化工具以及提出对SQL优化的体会。
RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。 CBO方式:它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否...
oracle一些基本的SQL优化,适合入门讲座 ORACLE的优化器共有3种: a. RULE (基于规则rbo) b. COST (基于成本cbo) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中 OPTIMIZER_MODE参数的各种声明,...
CBO,RBO在ORACLE中的应用
4.1 rbo基于规则的优化器 66 4.2 cbo基于成本的优化器 69 第5章 执行计划 85 5.1 cardinality (基数) 85 5.2 sql的执行计划 94 第6章 hint 109 6.1 和优化器相关的hint 115 6.1.1 all_rows和first_rows(cbo) 115 ...
1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query Language)简称SQL,是一种关系数据库查询语言,用于存取数据以及查询、更新和管理关系数据库系统。 1.2 语句结构 1.2.1 数据查询语言(DQL) 对数据库进行...
高级SQL优化教程 pdf 文档,分五部分: 第一部分:细说RBO和CBO 第二部分:理解RBO 第三部分:SQL调整背景知识 。。。。。。
在optimizer_mode=choose时,如果表有统计信息(分区表外),优化器将选择CBO,否则选RBO。RBO遵循简单的分级方法学,使用15种级别要点,当接收到查询,优化器将评估使用到的要点数目, 然后选择最佳级别(最少的数量)的...
1. 基于规则的优化器-- Rule Based (Heuristic) Optimization(简称RBO) 2. 基于代价的优化器 -- Cost Based Optimization(简称CBO) 3. 判断当前数据库使用何种优化器—CHOOSE 4. 如何设定选用哪种优化模式 访问...
4.1 rbo基于规则的优化器 66 4.2 cbo基于成本的优化器 69 第5章 执行计划 85 5.1 cardinality (基数) 85 5.2 sql的执行计划 94 第6章 hint 109 6.1 和优化器相关的hint 115 6.1.1 all_rows和first_rows(cbo) 115 ...
优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径,也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则,oracle数据库里的优化器又分为RBO和CBO两种...
Oracle10g起,在基于CBO的优化器模式下Oralce会进行自动优化,但在基于RBO(基于规则)的优化器模式下,依然保持此规则。1.索引对不等号和NOT的限制RBO模式下,执行计划如下:1.索引对不等号和NOT的限制RBO模式下,...
RBO文件加密_V1.0.0
1.设置最大文件长度,2.生成密钥,3.加密(明文->密文),4.解密(密文->明文)
oracle数据库执行计划文档,描述了cbo,rbo等优化方式,以及数据库执行计划的稳定性