Oracle 性能优化简介-中行

Oracle应用系统优化向-mail:xiang-xj@139.com目录性能优化误区性能优化概述线性可扩充性硬件规划与软件规划系统架构规划与设计应用设计原则性能检测与优化常用优化技术的分析性能优化实践内存管理及调整性能优化实践常用的优化检测脚本数据库性能检测指南数据库索引的维护性能优化的扩展如何收集性能问题优化案例简介Oracle应用优化误区不重视应用设计期的前期规划简单增加/升级计算机硬件只强调数据库参数优化技术没有考虑负载平衡数据库以外的优化占优化工作60%以上没有利用数据库已有的资源只注意优化数据库本身性能优化概述性能问题常常由于对系统资源竞争，使得某些资源被耗尽所产生新的性能调优方法论是基于仔细地规划和设计数据库系统，避免系统资源被耗尽而引起系统崩溃。通过消除系统资源竞争，系统可以通过扩展来满足业务发展的需要。性能优化，不是一个一蹴而就的工作，也不是一个一劳永逸的工作，需要定期维护，定期观察，在发现性能瓶颈时及时进行调整。所以在优化前，我们需要确定一个优化目标，我们的目标是满足我们的应用性能要求就可以了。Oracle优化，涉及的范围太广泛，包含的有主机性能，内存使用性能，网络传输性能，SQL语句执行性能等应用实施初期，应用使用的用户数不多，进入系统的数据量也不太大。因此往往会掩盖性能问题性能优化概述Oracle优化内存等参数配置的优化。内存优化，是性能受益最快的地方。减少物理读写的优化。内存逻辑I/O操作的时间，远远小于物理I/O的操作时间。减少物理读写的优化，一般所用的方法有：增加内存databuffer的大小，尽可能让数据库操作的数据都能在内存里找到，不需要进行物理读写操作。通过使用索引，避免不必要的全表扫描。大表物理分区，Oracle具有很好的分区识别功能，减少数据扫描范围。其他的一些减少物理读写的优化方法，比如使用materializedview，Cluster等方法；还有一些分散I/O的方法，比如Oracle日志文件不与数据文件放在一个物理硬盘，数据热点文件物理I/O分开等等方法批量重复操作的SQL语句及大表操作的优化。减少SQL执行次数，减少大表操作次数。性能优化概述-可扩充性系统可扩展性是当增加新的系统资源时能够处理更多的业务负荷的能力,性能的提高为线形。影响线形可扩充性的因素：当用户数增加时需要更多的并发管理等待资源的锁的增加保证数据一致性带来的负荷操作系统负荷增加数据容量增加时交易处理需要访问更多的数据没有很好设计的SQL语句和索引会产生大量的逻辑I/O数据库对象维护需要更多的时间,从而减少了系统可用性性能优化概述-可扩充性一个应用不可扩展时是当系统的一个资源被耗尽以至当负载增加时不能提供更多的处理能力的那一个点。硬件资源耗尽扫描高容量数据表的处理引起磁盘I/O短缺过度的网络请求导致网络瓶颈内存分配引起页对换过多的进程和线索分配引起操作系统失效性能优化概述-线性可扩充性影响线形化可扩充性的因素低劣的应用设计\开发\系统配置硬件配置不合理软件组件的限制硬件组件的限制性能优化概述-线性可扩充性影响线形化可扩充性的因素低劣的应用设计\开发\系统配置应用本身是影响线性可扩充性的最大因素：低劣的方案设计造成了nPoorschemadesigncancauseexpensiveSQLthatdoesnotscale.低劣的事务处理设计产生了锁和串行化问题低劣的连接管理–

响应速度低/系统不可靠应用实施也会成为一个薄弱环节：nSystemscanmovetoproductionenvironmentswithbadI/Ostrategies.生产环境使用与测试环境产生的不同的执行计划给内存密集型应用分配大量的内存却没有考虑在运行时释放内存分配不合理以及内存不足会产生页面的对换影响性能和系统可用性硬件配置不合理软件组件的限制硬件组件的限制性能优化概述-线性可扩充性影响线形化可扩充性的因素低劣的应用设计\开发\配置硬件配置不合理逐渐降低的硬件价格可以弥补硬件能力规划的不足过多的硬件配置往往会掩盖系统可扩充性的问题（本可以花较少的钱）软件组件的限制硬件组件的限制性能优化概述-线性可扩充性影响线形化可扩充性的因素低劣的应用设计\开发\配置硬件配置不合理软件组件的限制所有的软件组件都有可扩充性和资源使用的限制（包括：应用服务器，数据库服务器和操作系统）应用设计者不能指望软件能够超过它的处理能力硬件组件的限制性能优化概述-线性可扩充性影响线形化可扩充性的因素低劣的应用设计\开发\系统配置硬件配置不合理软件组件的限制硬件组件的限制硬件不完全具有可伸缩性的例如：在一定数量之内，多CPU系统的性能可以线性提升（<=32)，但超过以后性能的提升就是非线性的。甚至会出现增加CPU会降低性能。这主要是由于系统管理，资源竞争，同步等因素造成的。这种现象与系统负载和操作系统设置关系很大。性能优化概述-互连网环境下的可扩充性问题在Internet下运行的应用其性能问题和可用性问题更加复杂,Internet环境下的应用系统特点：需要24X7X365天并发用户数不可预测和不确定系统容量规划方面困难允许任何类型的查询多层架构无状态的中间件快速扩充没有大量的时间测试性能优化概述-系统架构系统架构由两部分组成：硬件与软件组件配置适合的系统架构以满足应用的要求性能优化概述-硬件规划硬件资源包括：CPU内存I/O子系统网络资源目标：规划多层环境下每一层的处理能力平衡的设计尽可能让所有的组件同时达到它们的设计极限优化的考虑：通过增加硬件的方法来获得的性能提高只能是对当前性能问题的短期补救措施,如果应用的负荷继续增加,我们将在不久的将来还要面对同样的性能问题.一个没有很好地设计的系统,不论增加多少硬件资源都不能提高其性能.在购买新的硬件前,要确认应用中没有存在串行化问题或单线索问题性能优化概述-软件规划大多数应用软件包括下列组件:管理用户界面的组件处理业务逻辑的组件（执行应用的核心功能）管理用户请求和资源分配的组件管理数据和事务处理的组件性能优化概述-软件规划大多数应用软件包括下列组件:管理用户界面的组件绘制显示屏幕收集并传递用户数据到业务逻辑组件校验数据录入有效性在不同的应用级别和状态之间导航用户处理业务逻辑的组件（执行应用的核心功能）管理用户请求和资源分配的组件管理数据和事务处理的组件性能优化概述-软件规划大多数应用软件包括下列组件:管理用户界面的组件处理业务逻辑的组件（执行应用的核心功能）转化数据模型到一个关系数据表结构定义关系表结构的约束条件编写过程逻辑代码执行业务规则管理用户请求和资源分配的组件管理数据和事务处理的组件性能优化概述-软件规划大多数应用软件包括下列组件:管理用户界面的组件处理业务逻辑的组件（执行应用的核心功能）管理用户请求和资源分配的组件负责数据库连接管理高效地执行SQL(通过使用cursors和共享SQL技术)管理客户端状态信息在硬件资源中平衡客户请求的负载设置硬件、软件组件的操作对象不断建立异步执行任务队列管理数据和事务处理的组件性能优化概述-软件规划大多数应用软件包括下列组件:管理用户界面的组件处理业务逻辑的组件（执行应用的核心功能）管理用户请求和资源分配的组件管理数据和事务处理的组件使用锁机制和事务处理规则提供并发访问数据的能力使用索引和内存缓存技术提供优化的数据访问能力保证数据的修改被记录以防硬件失效对数据执行已定义的任何规则如何合理配置系统架构配置初始化系统架构在很大程度上是个反复的过程架构设计师必须保证在受到预算和时间计划的限制的情况下满足系统的需求如果系统是需要用户不断地与系统进行交互来完成交易处理并根据数据库内容进行决策，那么架构是由用户需求驱动的(OLTP)如果系统上只有少数交互用户，那么系统架构是由流程驱动的(OLAP/DSS)注意:产生一个系统架构不是一个确定的过程，它需要仔细考虑业务需求，技术路线的选择，已有的系统和架构以及实际可以使用的物理资源，例如：预算和人力资源等。性能优化概述-常用的应用系统交互式应用系统帐务管理系统定单录入系统邮件服务器Web-based的零售系统交易系统面向流程的应用系统公用事业帐单系统欺诈侦测系统直递邮件系统性能优化概述-配置系统架构-问题一系统支持多少用户？在负载很轻的系统或专用机上只有很少的用户效率/短的响应时间/很少的管理/很少的用户间交互/无资源冲突在企业内有中等规模到大量的用户用户数受限/业务明确/可靠的系统服务/响应速度/减少资源冲突/提供扩展空间在互联网上的无数用户群系统组件不超出设计极限/负载平衡/无状态应用服务器（webservices）/高效数据库连接管理/统计/监控性能优化概述-配置系统架构-问题二用户与系统的交互方法是什么？客户/服务器模式（Client/Server）主机/终端模式（Host）Web/Server性能优化概述-配置系统架构-问题三用户的分布如何?网络布局（LAN/WAN/Internet)网络延时使用高峰期批任务的执行系统维护的工作性能优化概述-配置系统架构-问题四网络速度如何?高交互式界面的实现大量数据的下载，本地处理用户访问的数据量有多少，有多少数据是只读的?数据表设计索引的设计本地数据缓冲实现数据复制实现用户响应时间的要求是什么?数据查询报表生成数据录入性能优化概述-配置系统架构-问题五…是否需要24小时服务?Internet跨国/地区企业是否所有的内容都需要实时改变?批命令/队列数据库规模多大?业务交易的处理能力是多少?系统可用性的要求是什么？已有的经验和能力能否满足组建和管理该应用系统的要求?如果受预算的限制可以放弃哪些内容?…应用设计原则应用设计简单化（越简单越好）数据建模数据表与索引的设计使用视图SQL的执行效率应用开发工具/语言的选择应用开发趋势应用设计原则之一应用设计简单化（越简单越好）如果数据表的设计复杂得没有人看得懂，那么该设计可能有问题如果SQL语句非常长而且复杂难懂以至于任何优化器不能够实时有效地对它优化，那么可能存在一个低质量的语句或处理或数据表设计.如果一个表上存在针对同一个字段的多个索引，那么索引设计有问题.如果在线用户提交的查询效率很低，那么可能界面设计或业务处理设计有问题.数据建模数据表与索引的设计使用视图SQL的执行效率应用开发工具/语言的选择应用开发趋势应用设计原则之二应用设计简单化（越简单越好）数据建模关系型应用系统成功的关键数据模型尽可能表达业务处理活动关注影响最常用的业务处理(核心业务)的实体使用合适的建模工具数据表与索引的设计使用视图SQL的执行效率应用开发工具/语言的选择应用开发趋势应用设计原则之三应用设计简单化（越简单越好）数据建模数据表与索引的设计灵活性与核心业务处理效率之间的平衡关注关键业务相关数据表的设计,保证处理速度索引的设计可能产生的不断调整的过程给索引增加所有必要的列/使用索引组织表((IOT)使用不同的索引类型(B-TreeIndexes,BitmapIndexes,Function-basedIndexes,PartitionedIndexes,ReverseKeyIndexes)使用视图SQL的执行效率应用开发工具/语言的选择应用开发趋势应用设计原则之四应用设计简单化（越简单越好）数据建模数据表与索引的设计使用视图简化应用设计/安全性/数据表隔离查询优化问题SQL的执行效率应用开发工具/语言的选择应用开发趋势应用设计原则之五应用设计简单化（越简单越好）数据建模数据表与索引的设计使用视图SQL的执行效率好的数据库连接管理合理地使用和管理指针技术HardParsingSoftParsing应用开发工具/语言的选择应用开发趋势应用设计原则之六应用设计简单化（越简单越好）数据建模数据表与索引的设计使用视图SQL的执行效率应用开发工具/语言的选择选择适合软件组件的开发环境用户界面(HTML/WindowsPRGs)–

响应时间/减少网络传输量(Java)业务逻辑(解释型语言Java,Plsql,编译型语言;程序所处位置)用户请求和资源分配(考虑工具的数据库连接管理和指针管理模式)数据管理和事务处理开发一个软件模块时，只考虑该模块自身的功能不留功能缺陷过程化模块-C，PL/SQL，Java；数据存取模块-SQL经常访问的，很少修改的数据最好放入高速缓存或存到本地优化组件间的接口，使得所有组件以最可扩充的配置运行使用外键引用（利用Oracle的内部机制保证引用完整性)应用开发趋势应用设计原则之七应用设计简单化（越简单越好）数据建模数据表与索引的设计使用视图SQL的执行效率应用开发工具/语言的选择应用开发趋势Java替代C，C++Java优点–

代码可移植性，对程序员的实用性Java缺点–

执行效率低，占用更多的资源面向对象的建模设计访问方法封装，继承性数据库访问效率低Java端数据处理，频繁访问数据库使用面向对象数据库（Oracle8i以上）的设计方法优化概述-负载评估，建模，开发评估数据增长当系统开始使用后，数据库的增长变得难以预测索引的增长更加难以预测随着数据的不断增加和删除，数据碎片和浪费的空间也增加利用索引重建技术监控和寻找增长过快的数据表是保证系统高效和高可用性的关键之一评估负荷从类似的系统中推断使用基准评估程序（Benchmarking）应用建模优化概述-负载评估，建模，开发对设计进行测试，调试，验证测试过程主要包括功能测试和稳定性测试，有时也要进行性能测试。测试规则包括：使用真实的数据量和数据分布进行测试使用正确的优化模式(推荐采用基于成本的优化模式)测试单用户性能获得并记录所有的SQL语句执行计划（找到负荷大的事务处理）尽可能进行多用户测试(确保没有锁的冲突和串行化问题)使用正确的硬件配置进行测试评估稳定状态下的性能优化概述-发布新应用首次展示策略大爆炸式方式–

所以用户立刻移植到新系统中优点：最小的数据转换和需要与旧系统的数据同步工作缺点：需要在必要规模上完成全面的测试逐步进展方式–

用户慢慢地从旧系统移植到新系统优点：可以实现跟踪系统负载逐渐增加的状况缺点：数据需要在新、旧系统之间来回迁移优化概述-发布新应用性能检查清单下列初始化参数MAXINSTANCES,MAXDATAFILES,MAXLOGFILES,MAXLOGMEMBERS,MAXLOGHISTORY在生产环境应该比测试环境设得大一些设置与开发环境同样的数据块大小，优化模式尽可能使用缺省的初始化参数，只改变影响SGA区缓冲区大小的参数给数据库对象设置存储选项来管理块竞争所有的SQL语句要确认是优化的并且它的资源使用是明确的确认连接数据库的中间件和程序在连接管理上效率高并且不重复执行logon、logoff确认所有的SQL语句有效地使用Cursors确认所有的方案对象都正确地移植到生产环境只要系统一发布，建立数据库和操作系统的统计起点预测第一个瓶颈，并完成对它的优化优化概述-监测和优化重要的统计数据操作系统统计数据CPU使用统计虚拟内存使用统计磁盘操作统计最重要的磁盘统计是当前的响应时间和磁盘队列长度响应时间应该<20ms,否则就意味磁盘负载过重，成为瓶颈队列<=2网络流量及状态统计网络延时可能是响应时间过大的重要原因优化概述-监测和优化重要的统计数据数据库统计数据库统计提供了数据库负载类型，以及由数据库使用的内部及外部资源等信息。当数据库资源耗尽时，很可能就成为应用的瓶颈。TIMED_STATISTICS=TRUE缓冲区使用统计 大多数统计信息可以从虚表V$SYSSTAT中获得共享池使用统计共享池包含有用户会话，所有数据库使用者共享的数据结构，以及数据字典缓冲等信息统计信息中除了有SQL本身，还有SQL执行的次数和所占用的CPU，磁盘I/O等统计信息可以从虚表V$SQL中获得当分析师不了解，或没有应用源代码时，分析共享池最有效，帮助找到有瓶颈的数据库对象优化概述-监测和优化重要的统计数据数据库统计等待事件统计进程要共享某个资源或与其它进程同步进程将控制交给外部程序用户进程等待，该等待作为响应时间的一部分如果多个进程同时争一个资源，数据库变成单线索，可伸缩性受影响从虚表V$SYSTEM_EVENT,V$SESSION_EVENT,andV$SESSION_WAIT可以查询到等待事件的历史以及当前等待的事件优化概述-监测和优化重要的统计数据应用系统统计应提供每天的事务处理量都有哪些事务处理，每个事务处理所花时间（响应时间）等优化概述-监测和优化统计数据收集工具操作系统数据收集工具UNIXCPUsar,vmstat,mpstat,iostatMemorysar,vmstatDisksar,iostatNetworknetstatWindows

使用性能监控工具数据库数据收集工具StatspackOracleEnterpriseManager(EM)BSTAT/ESTATscripts优化概述-监测和优化历史数据和起始监测时间的重要性告诉系统发生什么引起性能问题性能直觉把统计结果与应用中的业务处理进行关联，找到不正常的处理，以及发生变化的原因优化概述-监测和优化Oracle性能优化方法性能优化方法简介性能问题一般是由于系统处理能力不足，用户、任务响应时间不可接受等问题所造成的。首先要从最终用户（尤其是为系统付账的人）那里得到对性能问题的描述：上线性能太低，以至于我们不能做其它事情；开发票处理太慢响应速度太慢，我丢失客户了现在每天交易量5000，系统已经在强撑。下个月要将所有用户加进来，估计交易量达到四倍。。。从反馈回来的信息中，找到影响性能的最关键的因素确定优化目标帐单系统必须在3小时的窗口时间内处理100，000个帐户网站的高峰期，网页刷新速度不能超过5秒通过优化手段消除性能瓶颈-递归的过程直至用户接受优化后的性能优化概述-监测和优化Oracle性能优化方法优化步骤获得最终用户反馈。确定性能优化范围和子目标，以及今后的优化目标。该过程是今后制定能力计划的关键获得完整的操作系统，数据库，应用本身的统计数据（包括性能好的时期和变坏的时期）对影响性能的所有机器进行操作系统完整性检查,找到负载过大的硬件和操作系统资源。另外，检查所有硬件是否有诊断错误。检查前10个最常出现的Oracle错误，确定是否就是可能的问题所在。建立概念模型表示系统正在发生的现象，用症状提示帮助理解什么引起性能问题。提出一系列补救措施和预计产生的效果，顺序执行这些措施。性能优化的黄金规则是：一次进行一项改变然后检查其效果。如果同时进行多项改变，要保证它们之间没有互相影响。检查这些改变是否达到了预期的效果，看用户是否能够接受性能优化的结果。否则，检查其它瓶颈，并继续改进所建立的概念模型直到更准确地理解应用系统。重复步骤5，6，7直到到达性能优化目标，或由于其它限制不可能继续优化了。优化概述-监测和优化如何检查操作系统检查整个系统和每个CPU在用户区和内核区的使用情况确定没有发生页面对换情况检查机器间的网络延迟是否在可接受的范围内找到响应速度慢或队列长的磁盘子系统确定没有硬件错误优化概述-监测和优化判断性能问题方法在单用户或负载低情况下，响应速度或批处理运行时间是否可以接受?如果不满足要求，那么应用开发本身有问题看应用内部统计资料，SQLTrace，SQL计划与开发者一道看索引，事务处理SQL设计，以及可能存在的批命令、后台处理是否存在优化问题是否利用了所有的CPU资源?如果内核使用率>40%，检查操作系统在网络传输，页面调度，页面对换或进程失效的情况否则看用户区的使用情况，看是否有非数据库任务的其它进程消耗CPU资源。例如：备份，文件传输，打印队列等。确定Oracle使用了大部分CPU资源后，检查占用CPU资源最大的SQL。如果应用是优化的并且没有低效率的SQL，考虑将定期执行的作业移到非高峰期运行，或升级到更大一些的机器。系统性能不满意，但CPU资源没有充分利用在该服务器上存在串行化问题查看WAIT_EVENTS统计，确定最大的串行化点如果没有串行化点，问题可能是数据库外消除WAIT_EVENTS包括调整SQL和初始化参数优化概述-监测和优化在Oracle系统中最常犯的10个错误低劣的连接管理（两个数量级）没有很好地使用Cursors和共享池（一个数量级）发生数据库DatabaseI/O错误数据库只按容量分配，没有按带宽分配重置日志设置问题太小的重置日志引起系统检查点（Checkpoint）不断给日志缓冲区和I/O系统带来很高的压力日志文件太少，归档无法跟上步伐。数据库出现等待现象由于缺少freelists,freelistgroups,transactionslots(INITRANS),或缺少回滚段，在缓冲区产生串行化数据块在Insert操作频繁，将块大小升到8k或16k，或活动用户数非常多并且回滚段很少的应用中常常发生优化概述-监测和优化在Oracle系统中最常犯的10个错误6.

很长的全表扫描在线交互系统或数据量非常大的系统中存在大量的全表扫描操作表明事务处理设计有问题，或缺少索引，低劣的SQL优化。属于I/O密集操作，而且不可伸缩7.

在硬盘排序（同上）8.

大量的递归(SYS级)SQL空间管理活动发生，例如：分配扩展数据空间9.Schema错误或优化问题向生产环境移植时丢失索引等10.

使用非标准初始化参数优化概述-监测和优化硬件配置的性能特性 硬盘特性:大小512MB-36GB（SCSI）寻址速度5-10msec传输速度5-10msec吞吐量20-40I/O秒/每个硬盘磁盘控制器吞吐速度1750I/Os/秒内存读取速度1-10微秒点对点的网络延迟1-25msec.负荷很重的系统:(最坏情况)操作型系统（OLTP）-60%usr,40%sys决策支持系统（DSS）-90%usr,10%sys优化概述-监测和优化应急性能优化技术优化步骤检查性能问题并收集问题出现的征状用户反馈系统如何表现不佳，是处理能力问题还是响应速度问题?提出问题：出现性能问题之前发生了什么?全面检查所有硬件的使用情况确定数据库服务器CPU处理能力问题，经常在等待资源上如果是CPU问题，检查：在操作系统级消耗大量CPU的对话在数据库级执行大量buffergets操作的对话或语句(检查V$SYSSTAT,V$SQL)造成非优化执行SQL的执行计划的改变不正确设置的初始化参数导致代码改变的算法问题，或被升级的软件模块否则检查V$SESSION_WAIT中的等待事件列表，找到串行化点如果存在大量的对librarycache的竞争，系统可能就不允许新的登录或向数据库提交SQL。在这种情况下，使用历史数据来确定为什么突然会对该栓（Latch）产生竞争.如果大多数等待是对I/O资源的等待，检查所有执行I/O操作的SQL语句.优化概述-监测和优化应急性能优化技术优化步骤执行应急措施来稳定系统让某些模块离线限制负载重启系统中断某些任务确认系统已经稳定收集数据库统计数据集按前面介绍的严格步骤完成优化使重新上线运行Oracle的Memory管理Oracle的Instance结构

SGA

DataBaseBufferCacheOther(Largepool,Cursors…)SharedPoolRedoLogBufferBackgroundProcessDBWRLGWRPMONCKPT…SMONOracle的Memory管理SGA(SystemGlobalArea):系統全局区在多人使用的环境下,SGA的数据可由系统中所有使用者共享,所以SGA也称为

SharedGlobalAreaSGA的設定参数

SGA_MAX_SIZE用以設定SGA的总大小如以数据库執行效能考虑,此參数尽可能設大参数值尽可能不要超过实际内存大小,否則会用到硬盘上的虚拟内存,反而会导致性能下降参数值大小必須大于

SGA相关区域参数值的总和Oracle的Memory管理SGA包含下列几个区域DataBaseBufferCache数据高速缓冲区RedoLogBuffer重做日志缓冲区SharedPool共享池其他,如LargePool及Cursors等SGA的相关参数修改在Oracle8i之前版本,修改后必須重新启动数据库才会生效Oracle9i提供一新技术称动态SGA,可以动态配置内存的大小Oracle的Memory管理databaseBufferCache数据高速缓存区用來存放读取自数据库的数据块副本,或是使用者曾经处理过的数据。其用途在于於有效降低读取数据库时造成磁盘读写动作,以提升数据存取速度数据高速缓存区包含两种缓冲区队列WriteList:存放dirtybuffer之副本,会在适当时机写入磁盘Dirtybuffer是用來存放“己修改但尚未写入磁盘的数据”的緩冲区LRUList:包含freebuffer、dirtybuffer及pinnedbufferFreebuffer:空白(可用)的緩冲区Pinnedbuffer:已被使用中的緩冲区Oracle的Memory管理DatabaseBufferCache的工作原理当使用者向Oracle送出查询请求,Oracle会會先在数据高速缓冲区內寻找该数据。如找到称之为cachehit,直接从内存中读取数据,否則称之为cachemiss,Oracle才会从磁盘上的数据库中读出数据块放入缓冲区后,使用者才从缓冲区读取数据。当磁盘上的数据块读出,要放入数据高速缓冲区內时,系統必須确定缓冲区內有freebuffer供存放,此时oracle便会开始扫描LRUList,如能順利找到freebuffer,Oracle就会將数据块放入此freebuffer中,再將其移到LRUList的MRU端。LRU(LeastRecentlyUsed)端:較不常使用的MRU(MostRecentlyUsed)端:最近使用的Oracle的Memory管理LRUList的扫描原則从

LRU端扫到MRU端如发現dirtybuffer就將它移到WriteList当扫到freebuffer或扫描的缓冲区数目超过临界值,就会停止扫描动作当LRUList真的沒有freebuffer,Oracle便会通知数据库写入器(DatabaseWrite,DBWR)后台处理程序將部份的dirtybuffers先写入磁盘,然后从LRUList的LRU端清除缓冲区,以騰出freebufferOracle的Memory管理LRUList与

LRU的演算规則當内存的可用空間不足时,缓冲区尽可能保留使用者最常用的数据,优先清除“較不常使用的数据”,並释放空間LRUListState0:State1:State2:………A………BA………MRU端LRU端Statem:Staten:MAE………DBCNMA………EDBOracle的Memory管理設定数据高速缓冲区大小在Oracle8i之前的版本DatabaseBufferCache的大小等于

DB_BLOCK_SIZE*DB_BLOCK_BUFFERSDB_BLOCK_SIZE:数据块(datablock)单位大小,以Bytes計;預設为8192(8k)DB_BLOCK_BUFFERS:缓冲区数目;預設2048(个)Oracle8i的DB_BLOCK_SIZE参数值在数据库建立后就不可再修改Oracle9i支持多重数据块大小,除了預設的DB_BLOCK_SIZE之外,DBA可另外設定其他大小的数据块Oracle的Memory管理RedoLogBuffer重置日志缓冲区记录数据库內所有数据变动的详細资料,称为redoentries。系統会在适当的时候调用LGWR后台处理进程,将redoentries內的数据写入磁盘內的RedoLogFiles,以便日后执行复原(Recovery)动作LOG_BUFFER参数可用來設定RedoLogBuffer的大小,單位为Bytes;預設为操作系统数据块的四倍若重置日志缓冲区设大一点,可減少RedoLogFiles的读写动作,可提升系統性能,但亦不宜太大Oracle的Memory管理SharedPool共享区当使用者把SQL指令发至Oracle数据库后,系統会先解析語法是否正確。解析时所需的系統信息以及解析后的結果,就存放在SharedPool共享区內。当有其他使用者用到相同的SQL指令,即可共享已解析好的信息,达到提升SQL指令的执行速度SHARED_POOL_SIZE参数可設定共享区的大小32-bit操作系统下;預设值为8(MB)64-bit操作系统下;預设值为64(MB)Oracle的Memory管理SharedPool共享区的組成;主要分两类LibraryCache(程序库高速缓冲区):包含SharedSQLArea(共享SQL区

)PrivateSQLArea(私有SQL区

)PL/SQLProgramArea(PL/SQL程序单元区

)解析后的結果及信息即是存放在SharedSQLArea內DictionaryCache(数据字典缓冲区):包含Table、View、Column及DataType等使用者的相关系統管理权限及对象存取权限Oracle在解析SQL語句時所需的系統信息即是存放在此Oracle的内存管理-数据仓库数据仓库shared_pool_size及shared_pool_reserved_size的配置数据加载阶段由于有大量的索引要建立，因此shared_pool_reserved_size为shared_pool_size的50%正常访问阶段由于基本上是使用select语句访问，因此shared_pool_reserved_size为shared_pool_size的10%Shared_pool_size约占Oracle可用内存大小的30-40%Oracle的内存管理-数据仓库Large_Pool：主要为在并行操作执行期间所需的消息缓冲区和备份所需的磁盘I/O缓冲区留出的一个内存段建议开始为2000000B常用优化技术的分析共享服务器（MTS）-多线索机制优点：占用较少内存资源缺点：速度较慢/串行化建议采用缺省的专用服务器模式集群技术（RAC）优点–

横向扩展能力、高可用性缺点–

高速缓冲竞争、过多的I/O、文件级锁分区分割大的数据表或索引提高可用性易于管理提高性能（减少扫描数据量,并行查询）适合于数据仓库应用并行处理多处理器、分区数据表设置Parallel参数适合数据仓库应用性能优化实践-索引优化索引调优误区：误区1：索引创建得越多越好？ 实际上：创建的索引可能建立后从来未使用。索引的创建也是需要代价的，对于删除、某些更新、插入操作，对于每个索引都要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。DML代价×3误区2：对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤？ 实际上：假设查询语句如下select*fromt1wherec1=1andc2=2，c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。性能优化实践-索引优化索引优化的基本原则：将索引和数据存放到不同的文件组

没有将表数据和索引数据存储到不同的文件组，而不加区别地将它们存储到同一文件组。这样，不但会造成I/O竞争，也为数据库的维护工作带来不变。组合索引的使用

假设存在组合索引it1c1c2(c1,c2)，查询语句select*fromt1wherec1=1andc2=2能够使用该索引。查询语句select*fromt1wherec1=1也能够使用该索引。但是，查询语句select*fromt1wherec2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

性能优化实践-索引优化索引优化的基本原则：创建索引的规则：创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量和表中记录的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。性能优化实践-SGA设置参考物理内存多大操作系统需要多大内存--(200MB)数据库使用文件系统还是裸设备

对于使用文件系统，异步I/O使用操作系统缓存占：0.2-0.3倍物理内存 裸设备没有这样的限制有多少并发用户

关系到PGA的大小（MTS下还有large_pool_size),同时还与应用类型有关。OLTP倾向使用MTS；

OLAP使用独立模式，同时还会有大量的排序操作的查询，都影响内存的使用（sort_area_size)--100个用户-200MB性能优化实践-SGA设置参考是什么样的应用：OLTP/OLAP计算公式：

OS使用内存+SGA+并发执行进程数*（sort_area_size+hash_area_size+2M)<0.7*总物理内存受32位操作系统寻址限制，SGA大小32位操作系统+32位数据库--不能超过1.7GB64为操作系统+32位数据库--不能超过3.7GB性能优化实践-SGA参数调整SGA内参数设置Log_buffer--1-3MBlarge_pool_size--（非MTS）20-30MB主要用于保存并行查询时候的一些信息java_pool_size--10-20MB(没有使用JAVA）；但如果使用则可能要更大〉300MBshared_pool_size--在一个充分使用绑定变量的比较大的系统中300MB，但如果系统使用了大量的存储过程，函数，包的系统，可能要到500MB；性能优化实践-SGA参数调整data_buffer--在SGA中除了前述内存，其它内存都给它，为了减少从磁盘中的读取次数，应尽可能增大该内存。

8i--db_block_buffers*db_block_size两个参数来决定（再加上buffer_pool_keep和buffer_pool_recycle)；

9i以后--（参数设置得越来越智能化） db_cache_size/db_block_buffers, db_keep_cache_size/buffer_pool_keep, db_recycle_cache_size/buffer_pool_recycle;

参数设置为实际大小，而不是块数量； 增加db_nk_cache_size-为了支持同一个数据库中使用不同的块大小（不同的表空间可以使用不同的块大小）n=2,4,6,8,16...SGA_MAX_SIZE--若设置了该参数，则在总和小于等于该大小之内，可以动态调整数据缓冲区和共享池大小；性能优化实践-Lock_sga=true-AIXLock_sga=true

为了减少页面对换,从而提高性能,最好能够将SGA锁定在物理内存中.

如何实现AIX(AIX4.3.3以上): logonaixasroot cd/usr/samples/kernel ./vmtune看v_pingshm参数是否为1 ./vmtune-S1(否则改参数值)

改initSID.ora参数文件,增加lock_sga=true

重新启动数据库,即可.性能优化实践-Lock_sga=true–HP/UXLock_sga=true

为了减少页面对换,从而提高性能,最好能够将SGA锁定在物理内存中.

如何实现HPUNIX: logonHPasroot vi/etc/privgroup addline"dbaMLOCK" /etc/setprivgrp-f/etc/privgroup

修改initSID.ora参数文件,增加lock_sga=true

重新启动数据库,即可.性能优化实践-Lock_sga=true-SolarisLock_sga=true

为了减少页面对换,从而提高性能,最好能够将SGA锁定在物理内存中.

如何实现Solaris(Solaris2.6以上): 8i版本以上数据库默认使用隐藏参数use_ism=true,自动锁定SGA.如果设置lock_sga=true,当用非root用户启动数据库时将报错.性能优化实践-Lock_sga=true-WindowsLock_sga=true

为了减少页面对换,从而提高性能,最好能够将SGA锁定在物理内存中.

如何实现Windows:

不能设定lock_sga=true锁定SGA.可以通过设置pre_page_sga=true使得数据库启动时就把所有内存页面装载.性能优化实践-内存参数的调整如果出现性能问题,首先判断是否为内存分配方面的问题,可以由以下三个方面来判断:

数据缓冲区命中率:

SQL>selectvaluefromv$sysstatwherename='physicalreads'; SQL>selectvaluefromv$sysstatwherename='physicalreadsdirect'; SQL>selectvaluefromv#sysstatwherename='physicalreadsdirect(lob)'; SQL>selectvaluefromv$sysstatwherename='consistentgets'; SQL>selectvaluefromv$sysstatwhereneme='dbblockgets';

命中率(R)计算: R=100-(physicalreads-X)/(consistentgets+dbblockgets-X)*100

其中: X=physicalreadsdirect+physicalreadsdirect(lob)

如果命中率<90%,可以考虑加大数据缓冲区大小性能优化实践-内存参数的调整

共享池命中率:

SQL>selectsum(pinhits)/sum(pins)*100"hitradio"fromv$librarycache;

如果命中率低于95%,就可能要考虑调整应用(例如:使用bindvariable)或增加内存

性能优化实践-内存参数的调整排序问题的优化:

SQL>selectname,valuefromv$sysstatwherenamelike'%sort%'; NAME VALUE

sorts(memory) 67935 sorts(disk) 1 sorts(rows) 7070

如果发现sorts(disk)/(sorts(memory)+sorts(disk))的比例过高,则意味参数sort_area_size过小.需要进行排序的操作：创建索引；涉及到索引维护的并行插入orderby或者groupby（尽可能对索引字段排序）Distinctunion/intersect/minus

sort-mergejoinanalyze命令

性能优化实践-内存参数的调整Log_buffer: SQL>selectname,valuefromv$sysstatwherenamein('redoentries','redobufferallocationretries'); NAME VALUE

redoentries 2325591 redobufferallocationretries 30

如果redobufferallocationretries/redoentries>1%,增大log_buffer性能优化实践-分区技术分区表：

根据实际经验，在一个大数据库中，数据库空间的绝大多数是被少量的表所占有。为了简化大型数据库的管理，改善应用的查询性能，一般可以使用分区这种手段。所谓分区就是动态地将表中的记录分离到若干不同的表空间上，使数据在物理上被分割开来，便于维护、备份、恢复、事务及查询性能。当使用的时候可建立一个连接所有分区的视图，使其在逻辑上仍以一个整体出现。分区索引：

当分区中出现许多事务并且要保证所有分区中的数据记录的惟一性时采用全局索引，在建立全局索引时，Global子句允许指定索引的范围值，这个范围值可以不同于表分区的范围值。只有建立局部索引才会使索引分区与表分区间建立起一一对应关系。因此，在大多数情况下，应该使用局部索引分区。若使用了此索引，分区就能够很容易地将索引分区与表分区建立关联，局部索引比全局索引更易于管理。分区管理：

根据实际需要，还可以使用Altertable命令来增加、删除、交换、移动、修改、重命名、划分、截短一个已存在分区的结构。

性能优化实践-段的碎片整理当生成一个数据库对象时（一个表或一个索引），通过用户缺省值或指定值来为它指定表空间。一个在表空间中生成的段，用于存储对象的相关数据。在段被关闭、收缩、截断之前，段所分配的空间将不被释放。

一个段是由范围组成，而范围是由相邻的Oracle块组成。一旦存在的范围不能再存储新的数据，这个段就会去获得新的范围，但并不要求这些范围是彼此相邻的。这样的扩展会一直继续下去，直到表空间中的数据文件不能提供更多的自由空间，或者范围数量已达到极限。因此，一个碎片太多的数据段，不仅会影响运行，也会引发表空间中的空间管理问题。所以，每个数据段只含有一个范围是十分有益的。借助监控系统，可以通过检查DBA_SEGMENTS数据字典视图来了解哪些数据库对象含有10个或更多范围的段，确定其数据段碎片。若一个段的碎片过多，可用两种方法解决:(1)用正确的存储参数建立一个新表，将旧表中的数据插入到新表中，再删除旧表；(2)利用Export/Import工具。性能优化实践-自由范围的碎片整理表空间中的一个自由范围是表空间中相连的自由（空间）块的集合。当一个段关闭时，它的范围将被释放，并被标记为自由范围。然而，这些自由范围再也不能与相邻的自由范围合并，它们之间的界线始终存在。但是:

当表空间的缺省值pctincrease设置不是0时，SMON后台进程会定期将这些相邻的自由范围合并。若pctincrease设置为0，那么相邻自由范围不会被数据库自动合并。但可以使用Altertable命令“coalesce”选项，来强迫进行相邻自由范围的合并。

不进行自由范围合并，在日后的空间请求中，会影响到表空间中的空间分配。当需要一个足够大的范围时，数据库并不会合并相邻的自由范围，除非没有其他选择。这样，当表空间中前面较小的自由范围已被使用时，将使用表空间中后面部分最大的一个自由范围。结果，会因为没有足够多的使用空间，从而导致表空间需求的矛盾。由于这样的情况出现，使数据库的空间分配距理想越来越远。自由空间碎片常会出现在那些经常关闭又重新生成的数据库表和索引中。

在理想的Oracle表空间中，每一个数据库对象存储在一个单独的范围中，并且所有有效自由空间集中在一个巨大而连续的范围中。这样，在一个对象需要附加存储空间时，可以在增加获取足够大自由空间的可能性的同时，最小化空间中的循环调用，提高自由空间使用率。性能优化实践-数据库连接方案使用直接的OLEDB数据库连接方式通过ADO可以使用两种方式连接数据库，一种是传统的ODBC方式，一种是OLEDB方式。ADO是建立在OLEDB技术上的，为了支持ODBC，必须建立相应的OLEDB到ODBC的调用转换，而使用直接的OLEDB方式则不需转换，从而提高处理速度。使用ConnectionPool机制在数据库处理中，资源花销最大的是建立数据库连接，而且用户还会有一个较长的连接等待时间。解决的办法就是复用现有的Connection，也就是使用ConnectionPool对象机制。

ConnectionPool的原理是：IIS+ASP体系中维持了一个连接缓冲池，这样，当下一个用户访问时，直接在连接缓冲池中取得一个数据库连接，而不需重新连接数据库，因此可以大大地提高系统的响应速度。性能优化实践-数据库性能检查指导方案建议每个月检查一次产品数据库检查方式均为以sysdba身份登录数据库执行指定的脚本该指导方案适用于Oracle9i数据库性能优化实践-内存性能评估内存性能指数（MPI,MemoryPerformanceIndex），MPI指数分类所需等级最高分缓冲区命中率（BufferCache） >98%数据字典命中率（DictionaryCache） >98%库缓存命中率（LibraryCache） >98%内存中的排序（SortinMemory） >98%空闲的数据缓冲区比例 10-25%使用最多的前10个SQL占用的内存 <5%是否已经调整使用最多的前25个SQL 是是否尝试固定高速缓存中经常使用的对象 是10MPI指数总分 250

性能优化实践-内存性能评估

缓冲区命中率

显示了对于数据总读取量而言，非磁盘读取（缓冲区命中）的百分比。当然，十分高的命中率并不代表数据库性能一定优良，也有可能是糟糕的SQL引起了大量的缓冲区读操作，只有在已经调整过首要的查询之后，这个命中率才能更好地反映数据库性能。

检查方法：select(1-(sum(decode(name,‘physicalreads’,value,0))/

(sum(decode(name,‘dbblockgets’,value,0))+

sum(decode(name,‘consistentgets’,value,0)))))*100

“HitRatio”

fromv$sysstat;评估准则：等级分数：<90%=0 90-94%=10 95-98%=20 >98%=30

性能优化实践-内存性能评估数据字典命中率显示了对数据字典和其它对象的内存读操作的百分比。检查方法：select(1-(sum(getmisses)/sum(gets)))*100“HitRatio”

fromv$rowcache;评估准则：等级分数<85%=086-92%=1092-98%=20>98%=30

性能优化实践-内存性能评估库缓存命中率显示了对SQL和PL/SQL对象的内存读操作的百分比。同样注意，很高的命中率并不总是反映数据库性能优秀。检查方法：select

sum(pins)/(sum(pins)+sum(reloads))*100“HitRatio”

fromv$librarycache;

评估准则：等级分数

<90%=090-94%=1094-98%=20>98%=30

性能优化实践-内存性能评估内存中的排序根据初始化参数PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE的值，用户的排序操作可能在内存中执行，也可能在临时表空间中执行。这个检查用以显示在内存中排序占总排序的百分比。检查方法：selecta.value

“DiskSorts”,

b.value

“MemorySorts”,

round((100*b.value)/

decode((a.value+b.value),0,1,(a.value+b.value)),

2)“PctMemorySorts”

fromv$sysstata,v$sysstatb

wherea.name=‘sorts(disk)’

andb.name=‘sorts(memory)’;评估准则：等级分数

<90%=090-94%=1094-98%=20>98%=30

性能优化实践-内存性能评估空闲的数据缓冲区比例空闲的记录数除以X$BH表中的记录总数（即所分配的数据块缓冲区的总数）得到的空闲缓冲区百分比。同样注意，拥有众多空闲缓冲区的数据库不一定是最佳环境，因为可能是缓冲区设置过大，浪费内存。

检查方法：selectdecode(state,0,‘FREE’,1,

decode(lrba_seq,0,‘AVAILABLE’,‘BEINGUSED’),

3,‘BEINGUSED’,state)“BlockStatus”,count(*)

fromx$bh

group

bydecode(state,0,‘FREE’,1,

decode(lrba_seq,0,‘AVAILABLE’,‘BEINGUSED’),

3,‘BEINGUSED’,state);评估准则：等级分数

<5%=05-19%=3020-25%=20>25%=0

性能优化实践-内存性能评估最浪费内存的前10个语句占全部内存读取量的比例通常一个没有优化系统中，10个最常用的SQL语句的访问量会占到整个系统中内存读操作的50%以上。这些SQL是最需要进行优化的部分，也是优化工作中优先级很高的部分。

检查方法：select

sum(pct_bufgets)

from(selectrank()over(order

bybuffer_getsdesc)asrank_bufgets,

to_char(100*ratio_to_report(buffer_gets)over(),‘999.99’)pct_bufgets

fromv$sqlarea)

whererank_bufgets<11;评估准则：等级分数

<5%=605-19%=5020-25%=30>25%=0

性能优化实践-内存性能评估调整前25个最浪费内存的语句在没有调整的情况下，绝大多数系统中，访问量占前25位的语句的内存读操作将占用整个系统所有内存读操作的75%，对这部分语句进行调整是至关重要的。这部分脚本用于获得访问量占前25位的SQL语句。检查方法：set

serveroutput

on

size1000000

declare

top25number;

text1varchar2(4000);

xnumber;

len1number;

cursorc1is

selectbuffer_gets,substr(sql_text,1,4000)

fromv$sqlarea

order

bybuffer_getsdesc;

begin

dbms_output.put_line('Gets'||''||'Text');

dbms_output.put_line(''||''||'');

openc1;

foriin1..25loop

fetchc1

intotop25,text1;

dbms_output.put_line(rpad(to_char(top25),9)||''||

substr(text1,1,66));

len1:=length(text1);

x:=66;

whilelen1>x-1loop

dbms_output.put_line('"'||substr(text1,x,66));

x:=x+66;

end

loop;

end

loop;

end;

/评估准则：本部分没有评估准则，需要开发人员或者DBA去确认在这25个SQL中属于应用系统的语句是否都已经作过调优。性能优化实践-内存性能评估固定缓存对象尝试在内存中固定（pin）经常使用的对象，包括表，存储过程等。检索需要在共享池中要求大于100K连续空间的对象：select*

fromv$db_object_cache

wheresharable_mem>100000

and

type

in(‘PACKAGE’,‘PACKAGEBODY’,‘PROCEDURE’,‘FUNCTION’);考察返回的结果，确认是否需要pin到共享池中。返回结果中的KEPT字段：

如果是YES，那么表示该对象已经固定在了共享池中，

为NO，则表示还没有固定。性能优化实践-内存性能评估固定缓存对象-续如果需要固定，使用下面的语句：execdbms_shared_pool.keep(‘SYS.STANDARD’);如果我们要固定表，那么可以在创建表的时候或者修改表属性时使用CACHE关键字，将表放置到BufferCache的LRU列表的MRU端。通常我们需要对于较小的但是频繁使用的表进行这种操作：

altertabletable_namecache;我们也可以将需要频繁使用的表放置到另外一个独立的BufferCache中，比如KEEP池。这种操作可以使这些表的数据不至于很快被清除出DefaultBufferCache：

altertabletable_namestorage(bufferpoolkeep);评估准则：本部分没有评估准则，需要开发人员或者DBA在系统分析以后谨慎执行。性能优化实践-存储性能评估概述：在存储性能评估的时候，我们使用磁盘性能指数（DPI,DiskPerformanceIndex），下表列出了DPI中的各项指数，这个评分系统并不意味着对磁盘的使用和分配的全方位评估，而只是代表一个晴雨表，反映当前磁盘的使用和分配上是否存在需要改进或注意的地方。

条目 需要等级 最高分调整表和索引

是

30表的行连接问题 无 30分隔离关键的oracle文档 是 30回滚段的平衡 30临时段的平衡 30使用最多的前10个sql的磁盘使用率 <5% 60是否已调整使用磁盘最多的前25个sql 是 40 DPI指数总分

250数据库性能检查-存储性能评估调整表和索引

由于表和索引的数据块通常是被同时读取的，所以应该尽量将表和其相关联的索引放置在不同的磁盘上，以便减少文档的I/O冲突。检查方法：

selecti.index_name,t.table_name,t.tablespace_name

fromuser_tablest,user_indexesi

wheret.table_name=i.table_name

andt.tablespace_name=i.tablespace_name;

返回结果是创建在相同表空间中的表和相关联的索引。 建议创建新的表空间用于专门存放索引，并将当前的索引rebuild到新创建的表空间中：

alterindexidx_namerebuildtablespacets_name;

评估准则：

表和索引放在同一磁盘上=0

存储使用了磁盘阵列，没有进一步调整=20

存储使用了磁盘阵列，对于raid类型已作过调整=30

表和索引已规划在不同磁盘上30数据库性能检查-存储性能评估表的行链接问题

当更新一张表，而数据块中又没有足够的剩余空间来容纳所作的修改时，就会发生“行链接”现象，该记录被链接到另外一个有足够空间的数据块中，也就是一条记录跨越了多个数据块，这样在读取该记录的时候就会消耗更多的i/o，当数据库中有大量的“行链接”现象存在时，数据库的整体性能就会下降。检查方法：

sqlplus/nolog

co