oracle性能优化总结

标签: oracle 性能优化 | 发表时间:2014-03-04 08:05 | 作者:shyboy0358
出处:http://www.iteye.com
oracle是一个很成熟的数据库产品,当然性能方面也有不俗的表现。尤其是9i之后又做了很多好的改进。现在已经到12c了,不过本人只用过11g,最近有时间了,我把自己对性能的一些拙见总结一下。(有一些是来自网上,自己又给整理了一下)

ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面。
自然优化也是围绕这两个方面展开。

1:oracle的内存结构
要说性能,先得说一下oracle的内存结构。
1.1:主要分SGA(system global area)和PGA(program global area)两大部分。
SGA主要包括SQL语句,数据,数据字典,redo日志等的缓存区。
PGA主要包括SQL语句排序区,游标状态区,会话信息区以及堆栈区。
这两部分的内存分配在一定程度上也会影响性能。虽然这两部分的大小,oracle会根据情况自动分配,不过也可以根据自己的需要进行设定,毕竟oracle没有业务人员懂业务,自然分配的不是那么智能。
ORACEL官方的建议是,PGA(20%)SGA(80%)

1.2数据块
每一次I/O读写的大小。一般是,2K,4K,8K,16K
块的设定会通过I/0读写次数影响性能。
比如说OLTP(事务联机)系统,它每次执行的语句比较单一,但是执行次数很多,所以它的块可以设的小一些。DSS(数据仓库)系统它每次执行的语句非常复杂,但是执行次数少,它的块就可以设的大一些。

2:优化措施 (开发人员的角度)
优化之前,先说一下怎么去看执行计划。
命令:
set autotrace on
set autotrace traceonly (不显示语句执行内容,只显示执行计划)
执行计划主要看已下指标:
响应时间,cost(越小越好),consistent gets(内存消耗),physical reads(I/0消耗)
这里要说明一下,每回进行性能测试的时候,要清一下缓存。
命令:
alter system flush buffer_cache;
alter system flush shared_pool;

2.1索引建立的策略
Oracle的索引主要包含两类:BTree和位图索引。没有特别声明(create bitmap ind index on table(column)),oracle都建的是BTtree索引。BTtree索引又分为唯一索引,复合索引(聚簇索引),反向索引和函数索引(不推荐)等。
对于OLTP系统,一般都用BTtree索引(注意不是二叉树)。
对于DSS系统,最好用位图索引。
2.1.1BTtree索引
1)唯一索引:一般是主键字段,约束条件字段(where条件),基数大的字段(可分性高)
order by 字段,也尽量建立索引。这样就不用进行order by 了,减少了排序的消耗。
2)复合索引:条件定义模式固定,例如where条件中经常一起出现的字段。如果组合比较灵活,则分别建单一索引。
复合索引要注意前缀性风险。例如复合索引ind_1(a,b,c),如果没有a字段出现,则索引失效。
复合索引优先于单字段索引。复合索引中也要注意顺序,要按可选性高低或者条件定义的频度进行排序。例如,纳税人识别号,税务机关代码,月份。
3)反向索引:反向索引主要是建立在那些以序列号生成的列上,可以将本来是连在一起的index entry分散到不同的leaf block中去。当索引是从序列中取的时候,如果是一般的b-tree 索引,在大量的插入后会导致块的分裂以及树的倾斜,使用reverse key index可以使索引段条目被更均匀的分布。很多事务访问同一个块,对同一个块并发操作产生的I/0竞争。反向索引可以避免I/O竞争。缺点是当应用需要获取一段范围的数据时,reverse key index将不会被使用,因为键值不是连续的排列的。在这种情况下,CBO将会选择全表扫描。
4)函数索引:某表的一列在平常SQL中该列都是放在函数里面,为了能用到索引来提高检索速度。例如:create index idx_fun on emp (upper(name));

2.1.2位图索引

位图索引非常适合于决策支持系统(Decision Support System,DSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列,但通常它们都只用于少量的列。

例如,您的表可能包含一个称为Sex的列,它有两个可能值:男和女。这个基数只为2,如果用户频繁地根据Sex列的值查询该表,这就是位图索引的基列。当一个表内包含了多个位图索引时,您可以体会到位图索引的真正威力。如果有多个可用的位图索引,Oracle就可以合并从每个位图索引得到的结果集,快速删除不必要的数据。

下面的程序清单给出了一个创建位图索引的例子:
create bitmap index dept_idx2_bm on dept (deptno);
Index created.

对于有较低基数的列需要使用位图索引。性别列就是这样一个例子,它有两个可能值:男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快,这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引,所以非常小,因此您可以经常检索表中超过半数的行,并且仍使用位图索引。

当大多数条目不会向位图添加新的值时,位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引,在大多数事务处理应用程序中都会发生这种情况。位图索引更新时用的不是行级锁,而是位图锁,所以不适合平凡更新的OLTP系统,更合适查询量大的DSS系统。


2.3表连接的策略
在进行多表连接的时候,oracle有三种算法。
merge join,hash join,Nested Loops

2.3.1 merge join
操作通常分三步:
1)对连接的每个表做table access full;
2)对table access full的结果进行排序。
3)进行merge join对排序结果进行合并。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下,merge join会比nested loops性能更佳。当表特别小或特别巨大的时候,实行全表访问可能会比索引范围扫描更有效。mrege join的性能开销几乎都在前两步。

2.3.2 hash join
对两个表进行全表扫描,然后oracle读取涉及连接的其中一个表,并且在内存里创建来自表的连接列的唯一关键字的位图。当读取和处理第二个表的行时,创建值的位图被用做过滤器。如果一个行成功的通过位图过滤,则hash算法用于数据查找和后来的连接。(这里涉及数学问题,我也弄的不是很清楚)。

以下条件下hash join可能有优势:
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。

2.3.3Nested Loops
会循环外表(驱动表),逐个比对和内表的连接是否符合条件。在驱动表比较小,内表比较大,而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候,Oracle也会选择使用NL。基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。

2.3.4总结
对于子查询in/exsits来说,能用多表查询,尽量用多表查询。
如果不可避免使用,要遵循下面的规则。
1)限制性强的条件在子查询中,建议用in
2)限制性强的条件在主查询中,建议用exsits
原理:in是先执行子查询,exsits是先执行主查询。
对于多表查询,尽量将限制性最强的表作为驱动表,在被驱动表上的外键字段建立索引。
(oracle根据基数来自动识别驱动表和被驱动表)

2.4 Oracle优化排序的操作
尽可能避免排序;尽可能在内存中排序;分配合适的临时空间以减少空间分配调用。

需要进行排序的操作:

A、创建索引;

B、涉及到索引维护的并行插入

C、order by或者group by(尽可能对索引字段排序)

D、Distinct

E、union/intersect/minus

F、sort-merge join

G、analyze命令(仅可能使用estamate而不是compute)

2.5 Oracle 并行处理

2.5.1查询
Sql代码 
SELECT /*+ Parallel(t,8) */ * FROM emp t;        
2.5.2创建索引
Sql代码 
create index idx_emp_test on emp(empno,ename,job) nologging parallel 32; 
2.5.3.插入
Sql代码 
INSERT /*+ append parallel(30) */  
INTO t_a 
SELECT /*+ parallel(30) */ 
FROM t_b
2.5.4 总结
自动化并行度,数据仓库应用程序通常会利用并行处理信息的优势,迅速有效地处理数据,特别是在大表上查询数据时,或者是一个非常复杂的连接查询时经常使用并行查询,在执行并行操作时,优化器应该使用并行度(Degree of Parallelism ,DOP),可以在查询本身内指定(通过+PARALLEL优化器提示),也可以作为表或索引本身的一个属性(通过它的PARALLEL属性),但要精确地确定一个合适的DOP是有难度的,通常需要对表是如何连接,哪些索引对并行处理有益,同时执行哪些查询都要有一个详细的了解。
Oracle 11g R2为我们带来了好消息,现在它可以自动为任何并行语句确定DOP,优化器使用两个新的初始化参数:PARALLEL_DEGREE_POLICY和PARALLEL_MIN_TIME_THRESHOLD来计算自动化并行度(ADOP)。例如,如果PARALLEL_DEGREE_POLICY被设为AUTO,如果查询确实能从并行操作受益,Oracle 11g R2优化器会首先确定一个合适的DOP值,如果查询的预计执行时间超出了PARALLEL_MIN_TIME_THRESHOLD可接受的值(单位:秒),并且有足够资源支持并行处理,它会允许查询执行,否则就会延迟执行,直到有足够的资源释放出来,这样可以防止一个并行查询过度地消耗资源,例如,所有并行执行线程,或集群环境中的所有CPU都被其它非并行操作占用了,值得注意的是ADOP特性无法扩展到并行恢复或并行复制,它们只适用于并行查询。


4)Oracle 绑定变量 详解
变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快,内存效率极高;不使用绑定变量可能会使OLTP 数据库不堪重负,资源被SQL解析严重耗尽,系统运行缓慢。



         当一个用户与数据库建立连接后,会向数据库发出操作请求,即向数据库送过去SQL语句。 Oracle 在接收到这些SQL后,会先对这个SQL做一个hash 函数运算,得到一个Hash值,然后到共享池中寻找是否有和这个hash 值匹配的SQL存在。 如果找到了,Oracle将直接使用已经存在的SQL 的执行计划去执行当前的SQL,然后将结果返回给用户。 如果在共享池中没有找到相同Hash 值的SQL,oracle 会认为这是一条新的SQL。 会进行解析。

在OLTP系统中,我们可以使用绑定变量是因为在OLTP中,SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引,那么这种极小结果集的操作使用索引最合适,并且几乎所有的SQL的执行计划的索引都会被选择,因为这种情况下,索引可能只需要扫描几个数据块就可以定位到数据,而全表扫描将会相当耗资源。 因此,这种情况下,即使每个用户的谓词条件不一样,执行计划也是一样的,就是都用索引来访问数据,基本不会出现全表扫描的情况。 在这种执行计划几乎唯一的情况下,使用绑定变量来代替谓词常量,是合适的。



在OLAP系统中,SQL的操作就复杂很多,OLAP数据库上大多数时候运行的一些报表SQL,这些SQL经常会用到聚合查询(如:group by),而且结果集也是非常庞大,在这种情况下,索引并不是必然的选择,甚至有时候全表扫描的性能会更优于索引,即使相同的SQL,如果谓词不同,执行计划都可能不同。





7)global temporary table
http://hwhuang.iteye.com/blog/551864

8)物化视图
1、物化视图的类型:ON DEMAND、ON COMMIT
    二者的区别在于刷新方法的不同,ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。

2、ON DEMAND物化视图
    物化视图的创建本身是很复杂和需要优化参数设置的,特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的,类似于普通视图的方式来做,所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
    物化视图的特点:
    (1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
    (2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
    (3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证;
    创建语句:create materialized view mv_name as select * from table_name
    默认情况下,如果没指定刷新方法和刷新模式,则Oracle默认为FORCE和DEMAND。

    物化视图的数据怎么随着基表而更新?
    Oracle提供了两种方式,手工刷新和自动刷新,默认为手工刷新。也就是说,通过我们手工的执行某个Oracle提供的系统级存储过程或包,来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新,其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包,加以实现。
   
    ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别,即前者不刷新(手工或自动)就不更新物化视图,而后者不刷新也会更新物化视图,——只要基表发生了COMMIT。
    创建定时刷新的物化视图:create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 (指定物化视图每天刷新一次)
    上述创建的物化视图每天刷新,但是没有指定刷新时间,如果要指定刷新时间(比如每天晚上10:00定时刷新一次):create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')

3、ON COMMIT物化视图
    ON COMMIT物化视图的创建,和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的,所以ON COMMIT物化视图,需要再增加个参数即可。

    需要注意的是,无法在定义时仅指定ON COMMIT,还得附带个参数才行。
    创建ON COMMIT物化视图:create materialized view mv_name refresh force on commit as select * from table_name
    备注:实际创建过程中,基表需要有主键约束,否则会报错(ORA-12014)
4、物化视图的刷新
    刷新(Refresh):指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种:ON DEMAND和ON COMMIT。(如上所述)
    刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新,只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
     对于已经创建好的物化视图,可以修改其刷新方式,比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次:alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')
  
5、物化视图具有表一样的特征,所以可以像对表一样,我们可以为它创建索引,创建方法和对表一样。

6、物化视图的删除:
     虽然物化视图是和表一起管理的,但是在经常使用的PLSQL工具中,并不能用删除表的方式来删除(在表上右键选择‘drop’并不能删除物化视图),可以使用语句来实现:drop materialized view mv_name

普通视图和物化视图根本就不是一个东西,说区别都是硬拼到一起的,首先明白基本概念,普通视图是不存储任何数据的,他只有定义,在查询中是转换为对应的定义SQL去查询,而物化视图是将数据转换为一个表,实际存储着数据,这样查询数据,就不用关联一大堆表,如果表很大的话,会在临时表空间内做大量的操作。
普通视图的三个特征:
1、是简化设计,清晰编码的东西,他并不是提高性能的,他的存在只会降低性能(如一个视图7个表关联,另一个视图8个表,程序员不知道,觉得很方便,把两个视图关联再做一个视图,那就惨了),他的存在未了在设计上的方便性
2、其次,是安全,在授权给其他用户或者查看角度,多个表关联只允许查看,不允许修改,单表也可以同WITH READ ONLY来控制,当然有些项目基于视图做面向对象的开发,即在视图上去做INSTAND OF触发器,就我个人而言是不站同的,虽然开发上方便,但是未必是好事。
3、从不同的角度看不同的维度,视图可以划分维度和权限,并使多个维度的综合,也就是你要什么就可以从不同的角度看,而表是一个实体的而已,一般维度较少(如:人员表和身份表关联,从人员表可以查看人员的维度统计,从身份看,可以看不同种类的身份有那些人或者多少人),其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图,不同的用户下看到的肯定是不一样的,看的是自己的东西。

物化视图呢,用于OLAP系统中,当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点,因为表关联的开销很大,所以在开发中很多人就像把这个代价交给定期转存来完成,ORACLE当然也提供了这个功能,就是将视图(或者一个大SQL)的信息转换为物理数据存储,然后提供不同的策略:定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择,总之你差的是表,不是视图。

数据库的设计
要遵循三范式
1)属性的唯一性
2)每一条记录要有主键,并且其他字段依赖于主键。
3)每一个属性仅依赖于主键。
有些情况下,为了性能要求,可以违背三范式。这样有利有弊。虽然一定程度上提高了性能,但是破环了数据的一致性,需要程序去保证数据的一致性。

已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [oracle 性能优化] 推荐:

oracle性能优化

- - 行业应用 - ITeye博客
                              oracle数据库的性能优化. 主要从内部存储结构,逻辑分区,表结构范式,sql语句优化. hibernate处的层次更高,主要从缓存,和sql语句的使用.     对于ORACLE数据库的数据存取,主要有四个不同的调整级别,第一级调整是操作系统级包括硬件平台,第二级调整是ORACLE RDBMS级的调整,第三级是数据库设计级的调整,最后一个调整级是SQL级.

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

oracle性能优化总结

- - Oracle - 数据库 - ITeye博客
oracle是一个很成熟的数据库产品,当然性能方面也有不俗的表现. 尤其是9i之后又做了很多好的改进. 现在已经到12c了,不过本人只用过11g,最近有时间了,我把自己对性能的一些拙见总结一下. (有一些是来自网上,自己又给整理了一下). ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

Hebernate 性能优化

- - 企业架构 - ITeye博客
文章分为十三个小块儿对Hibernate性能优化技巧进行总结性分析,分析如下:. 一、在处理大数据量时,会有大量的数据缓冲保存在Session的一级缓存中,这缓存大太时会严重显示性能,所以在使用Hibernate处理大数 据量的,可以使用session. clear()或者session. evict(Object) 在处理过程中,清除全部的缓存或者清除某个对象.

Hbase 性能优化

- - CSDN博客云计算推荐文章
因 官方Book Performance Tuning部分章节没有按配置项进行索引,不能达到快速查阅的效果. 所以我以配置项驱动,重新整理了原文,并补充一些自己的理解,如有错误,欢迎指正. 默认值:3分钟(180000ms). 说明:RegionServer与Zookeeper间的连接超时时间.

JavaScript性能优化

- - ITeye博客
互联网泡沫让投资者长了记性:态度更加谨慎.         如今主流浏览器都在比拼JavaScript引擎的执行速度,但最终都会达到一个理论极限,即无限接近编译后程序执行速度. 这种情况下决定程序速度的另一个重要因素就是代码本身. 在这里我们会分门别类的介绍JavaScript性能优化的技巧,并提供相应的测试用例,供大家在自己使用的浏览器上验证, 同时会对特定的JavaScript背景知识做一定的介绍.

Mysql性能优化

- - 数据库 - ITeye博客
MySQL性能优化.   性能优化是通过某些有效的方法来提高MySQL的运行速度,减少占用的磁盘空间. 性能优化包含很多方面,例如优化查询速度,优化更新速度和优化MySQL服务器等.   数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能. 语法:SHOW STATUE LIKE ‘value’;其中value参数是常用的几个统计参数.

Linux 性能优化

- - Gsion&apos;s Blog
1) Linux Proc文件系统,通过对Proc文件系统进行调整,达到性能优化的目的. 2) Linux性能诊断工具,介绍如何使用Linux自带的诊断工具进行性能诊断. 加粗斜体表示可以直接运行的命令. 二、/proc/sys/kernel/优化. 该文件有一个二进制值,该值控制系统在接收到ctrl+alt+delete按键组合时如何反应.