查看: 3648|回复: 4

[原创] oracle性能优化的一些体会探讨

[复制链接]
论坛徽章:
7
ITPUB北京2009年会纪念徽章
日期:2009-02-09 11:42:452010新春纪念徽章
日期:2010-01-04 08:33:082010数据库技术大会纪念徽章
日期:2010-05-13 09:34:232010系统架构师大会纪念
日期:2010-09-03 16:39:572010广州亚运会纪念徽章:棒球
日期:2010-11-29 10:28:422010广州亚运会纪念徽章:帆船
日期:2010-11-29 11:24:342010广州亚运会纪念徽章:游泳
日期:2010-11-29 11:28:09
发表于 2009-9-13 16:21 | 显示全部楼层 |阅读模式
Oracle 的优化是一个系统工程,不是一朝两熄就能学习的透,讲解的全的,即便是高手也需要几年的锤炼和实战的锻炼和经验能力的积累。Oracle的优化
主要包括如下方面:数据库的硬件配置:CPU、内存、网络条件、sql执行计划、存储。
  1. CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志,并且ORACLE是一个提供并行能力的数据库系统,在CPU方面的要求就更高了,如果运行队列数目超过了CPU处理的数目,性能就会下降,我们要解决的问题就是要适当增加CPU的数量了,当然我们还可以将需要许多资源的进程KILL掉;
  2. 内存:衡量机器性能的另外一个指标就是内存的多少了,在ORACLE中内存和我们在建数据库中的交换区进行数据的交换,读数据时,磁盘I/O必须等待物理I/O操作完成,在出现ORACLE的内存瓶颈时,我们第一个要考虑的是增加内存,由于I/O的响应时间是影响ORACLE性能的主要参数,我将在这方面进行详细的讲解
  3. 网络条件:NET*SQL负责数据在网络上的来往,大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响,还有交换机、集线器等等网络设备的性能对网络的影响很明显,建议在任何网络中不要试图用3个集线器来将网段互联。
4、sql执行计划是一个涉及面广深的知识点,不仅涉及oracle自身的知识,还涉及应用业务的知识。9i之前是RBO, 从9i开始引入了CBO,10g开始基本上完全抛弃了RBO, 但是对于依赖RBO优化访应用,面对系统的升级,可能会带来一些问题,这点需要大家注意。其次索引的建立以及如何建立也会影响着sql的执行路径。
5、存储,涉及到硬件系统架构的设计、硬件的选择。


下面是优化时常用的视图和数据字典
V$session
V$sql
v$waits
v$events
dba_data_files
V$sql_area
dba_index


一、
消耗在准备新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制,你能够控制Oracle花费在评估连接顺序的时间数量,并且能在大体上提高查询性能。
    准备执行SQL语句

    当SQL语句进入Oracle的库缓存后,在该语句准备执行之前,将执行下列步骤:
    1)     语法检查:检查SQL语句拼写是否正确和词序。
    2)     语义分析:核实所有的与数据字典不一致的表和列的名字。
    3)     轮廓存储检查:检查数据字典,以确定该SQL语句的轮廓是否已经存在。
    4)     生成执行计划:使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。
    5)     建立二进制代码:基于执行计划,Oracle生成二进制执行代码。
    一旦为执行准备好了SQL语句,以后的执行将很快发生,因为Oracle认可同一个SQL语句,并且重用那些语句的执行。然而,对于生成特殊的SQL语句,或嵌入了文字变量的SQL语句的系统,SQL执行计划的生成时间就很重要了,并且前一个执行计划通常不能够被重用。对那些连接了很多表的查询,Oracle需要花费大量的时间来检测连接这些表的适当顺序。
    评估表的连接顺序

    在SQL语句的准备过程中,花费最多的步骤是生成执行计划,特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时,它必须考虑到表之间所有可能的连接。例如:六个表的之间连接有720(6的阶乘,或6 * 5 * 4 * 3 * 2 * 1 = 720)种可能的连接线路。当一个查询中含有超过10个表的连接时,排列的问题将变得更为显著。对于15个表之间的连接,需要评估的可能查询排列将超过1万亿(准确的数字是1,307,674,368,000)种。
    使用optimizer_search_limit参数来设定限制

    通过使用optimizer_search_limit参数,你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数,我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值,优化器将检查所有可能的连接组合。

还有
open_cours
等参数也会影响到路径的选择



二、优化器
 ORACLE的优化器共有3种:

  a. RULE (基于规则)

  b. COST (基于成本)

  c. CHOOSE (选择性)

  设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖。

  为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性。

  如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

  在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器。

  2. 访问Table的方式ORACLE 采用两种访问表中记录的方式

  a. 全表扫描

  全表扫描就是顺序地访问表中每条记录。 ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。

  b. 通过ROWID访问表

  你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息……ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高。

  3. 共享SQL语句

  为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径。 ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。

  可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询。数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句。



三、索引
索引在各种关系型数据库系统中都是举足轻重的组成部分,其对于提高检索数据的速度起至关重要的作用。在Oracle中,索引基本分为以下几种:B*Tree索引,反向索引,降序索引,位图索引,函数索引,interMedia全文索引等。本文主要就前6种索引进行分析,由于interMedia全文索引涉及的内容可以单独写一篇文章,所以不在此对其做分析。
    首先给出各种索引的简要解释:
    b*tree index:几乎所有的关系型数据库中都有b*tree类型索引,也是被最多使用的。其树结构与二叉树比较类似,根据rid快速定位所访问的行。
    反向索引:反转了b*tree索引码中的字节,是索引条目分配更均匀,多用于并行服务器环境下,用于减少索引叶的竞争。
    降序索引:8i中新出现的索引类型,针对逆向排序的查询。
    位图索引:使用位图来管理与数据行的对应关系,多用于OLAP系统。
    函数索引:这种索引中保存了数据列基于function返回的值,在select * from table where function(column)=value这种类型的语句中起作用。
    2 常用索引的结构分析
在平时的优化过程中最常用的是bTree索引,函数索引,组合索引。
    B*Tree索引B*Tree索引是最常见的索引结构,默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列(高基数数据列是指该列有很多不同的值)时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的,由分支块(branch block)和叶块(leaf block)组成。在树结构中,位于最底层底块被称为叶块,包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块,用来导航结构,包含了索引列(关键字)范围和另一索引块的地址。
    假设我们要找索引中值为80的行,从索引树的最上层入口开始,定位到大于等于50,然后往左找,找到第2个分支块,定位为75-100,最后再定位到叶块上,找到80所对应的rowid,然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的,比如where column >20 and column <80,那么会先定位到第一个包含20的叶块,然后横向查找其他的叶块,直到找到包含80的块为止,不用每次都从入口进去再重新定位。
   
四、存储

未完待写

作者:国成
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2009-9-13 16:37 | 显示全部楼层
最关键的:表设计

使用道具 举报

回复
论坛徽章:
4
ITPUB官方微博粉丝徽章
日期:2011-08-22 09:11:372012新春纪念徽章
日期:2012-01-04 11:55:05蛋疼蛋
日期:2012-04-25 13:22:10奥运会纪念徽章:田径
日期:2012-06-22 22:12:46
发表于 2009-9-13 16:41 | 显示全部楼层

回复 #2 〇〇 的帖子

赞同二楼,优化从设计开始!

使用道具 举报

回复
论坛徽章:
8
2009日食纪念
日期:2009-07-22 09:30:002010年世界杯参赛球队:巴拉圭
日期:2010-08-31 10:49:49ITPUB9周年纪念徽章
日期:2010-10-08 09:31:222010广州亚运会纪念徽章:射箭
日期:2010-11-11 10:15:282010广州亚运会纪念徽章:羽毛球
日期:2010-11-11 10:15:332010广州亚运会纪念徽章:马术
日期:2010-11-11 10:15:40蜘蛛蛋
日期:2011-12-27 15:41:052012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2009-9-13 16:48 | 显示全部楼层
优化这个东西涉及到太多的东西,从ORACLE的安装分布,到application的设计和代码,不过楼主写的汇总确实不错,期待继续连载。

使用道具 举报

回复
论坛徽章:
4
祖国60周年纪念徽章
日期:2009-10-09 08:28:00参与WIN7挑战赛纪念
日期:2009-11-06 16:05:25生肖徽章2007版:鼠
日期:2009-11-17 16:35:582013年新春福章
日期:2013-02-25 14:51:24
发表于 2009-9-13 19:27 | 显示全部楼层
关于这个话题,我觉得每个人对优化的认识和理解都不一样。我的理解的就是,业务是时刻变化的,要求总是跟着业务在变化,问题总是随着业务的不断变化而涌现,如何安全的、稳定的使业务系统高效处理和满足业务的需求是我们一直追求的目标。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表