oracle功能优化总结ITeye - 千亿集团

oracle功能优化总结ITeye

2019年03月30日10时12分56秒 | 作者: 静枫 | 标签: 索引,视图,优化 | 浏览: 1830

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算法用于数据查找和后来的衔接。(适用于DSS体系。)

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

2.3.3Nested Loops
会循环表面(驱动表),逐一比对和内表的衔接是否契合条件。在驱动表比较小,内表比较大,而且内表面的衔接列有索引的时分比较好。当SORT_AREA空间缺乏的时分,Oracle也会挑选运用NL。依据Cost的Oracle优化器(CBO)会主动挑选较小的表做表面。适用于OLTP体系。

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)每一个特色仅依赖于主键。
有些状况下,为了功用要求,能够违反三范式。这样有利有弊。尽管必定程度上进步了功用,可是破环了数据的共同性,需求程序去确保数据的共同性。

from:http://www.w3c.com.cn/oracle%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E6%80%BB%E7%BB%93
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    oracle功能优化总结ITeye

    索引,视图,优化
  • 2

    oracle存储进程ITeye

    存储,进程,数据
  • 3

    Oracle中rownum、rowid、rowITeye

    相同,记载,排名
  • 4
  • 5
  • 6

    解析oracle的rownum(转载)ITeye

    查询,记载,运用
  • 7
  • 8
  • 9

    MYSQL5.5 主从装备:AITeye

    装备,同步,重置
  • 10