1、ORACLEORACLE性能性能优化简介优化简介(一一)杨杨 颖颖 2008.03 2008.03 目录目录一.性能调优概述1.ORACLE框架介绍2.数据库调整常规概念3.调整目标和度量标准4.性能优化分类5.优化过程工具二.调整工具简介1.操作系统工具2.Explain Plan.SQL3.SQL TRACE工具三.SQL语句调优四.性能调整案例ORACLE数据库架构数据库架构PasswordfileSGARedo LogBufferShared PoolData DictionaryCacheLibraryCacheDBWRSMONPMONCKPTLGWROthersUserproces
2、sServerprocessPGAControl filesDatafiles DatabaseDatabaseBuffer CacheRedo Log filesJava PoolLarge PoolParameterfileArchived Log files(注：本图引自ORACLEOCP考试培训官方资料)数据库调整概念数据库调整概念 数据库调整：是基于可以达到的调整目标，正在进行的优化数据库配置的过程。摘自Oracle 9i 数据库性能调整与优化调整目标调整目标调整目标必须根据以下实际标准来衡量数据库响应时间数据库统计数据CPU利用率内存利用率由读和写引起的I/O应用类型的平均查询 响
3、应时间Web 3s联机事务应用OLTP 1sOLTP和决策支持系统的组合 30sDSS数据仓库、报告和对比处理 60sOracle性能优化的分类性能优化的分类为了有目的优化系统性能，首先应明确优化目标，然后再根据目标进行优化，通常优化方式有：应用程序设计的优化 内存使用的优化 指定类型SQL语句的优化 数据存储、物理存储和逻辑存储的优化 网络通信量的优化 Oracle Performance Tools1.操作系统工具操作系统工具 使用Unix操作系统的Vmstat、Iostat等命令可以查看到系统级内存和硬盘I/O的使用情况，这些工具能够帮助管理员弄清楚系统瓶颈出现在什么地方2.SQL-Re
4、lated Performance ToolsEXPLAIN PLAN：语言优化命令 使用这个命令可以帮助程序员写出高效的 SQL语句SQL TRACE：SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，并使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统 3.System-Related Performance ToolsOracle Enterprise Manager Diagnostics Pack（OEM）：图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的Oracle数据库管理的命令V$views：O
5、RACLE数据字典STATSPACK：收集系统信息，诊断数据库故障 ORACLE性能调优性能调优 二二.工具介绍工具介绍使用使用UNIX工具监控系统性能工具监控系统性能 使用正确的工具来发现cpu、内存和磁盘io的问题，对于确定性能问题的出现位置是至关重要的。用于监控系统性能的常用命令有：sar可以用来监控cpu是否负载过高top发现系统中最影响性能的用户vmstat监控系统负载 ipcs监控oracle sga的使用情况iostat 报告I/O统计信息 使用使用sar命令监控命令监控cpu的使用的使用sar 命令：收集、报告系统运行的信息。加-u选项可以监控cpu的使用第一个参数，读取间隔的
6、秒数；第二个参数，希望sar运行的次数%usr：用户进程使用CPU百分比%sys：系统进程使用CPU百分比%wio：等待IO完成使用CPU的百分比%idle：空闲CPU的百分比使用使用top命令发现系统中最影响性能的用户命令发现系统中最影响性能的用户Top命令可以连续显示活动进程的情况，实用程序每隔几秒就会自动更新自身显示的内容使用使用vmstat命令监控系统的负载命令监控系统的负载 vmstat reports information about processes,memory,paging,block IO,and cpu activity。使用vmstat命令可以发现被阻塞的进程（用户
7、等待cpu时间），以及分页和交换问题。r正在运行的进程 b可以运行但正在等待资源的进程 w可以运行但已经交换出去的进程使用使用iostat确定磁盘确定磁盘io瓶颈瓶颈 iostat命令可以报告磁盘io活动的情况。Blk_read/s每秒读取的blocks数量 Blk_read从磁盘读取的blocks总量 当某个磁盘和其他相比负载过重，可以考虑将信息从该磁盘转移一些到其他磁盘。EXPLAIN PLANEXPLAIN PLAN：开发阶段，使用该工具查看oracle优化器所采用的执行sql语句的执行计划，包括访问表的方式和join表的方法等，用来判断该SQL是否可以进一步的优化，并不真正执行sql语
8、句，只是列出所要使用的计划。EXPLAIN PLAN帮助找出以下值得改进的情况帮助找出以下值得改进的情况:Full scans Unselective range scans Wrong join order Late filter operations.EXPLAIN PLAN SQL traceSQL trace facility and TKPROF：SQL trace对指定的操作所消耗的资源做统计。有助于找出系统中可能的瓶颈。SQL trace生成的文件难于阅读，由 TKPROF工具转换为便于阅读的格式。由工具分析出系统瓶颈，然后改写耗资源的sql，采用适当优化措施（如控制访问表的方式
9、、调整Join的次序等）。Provides performance information on individual SQL statements.Therefore,it can give you some insight into which statements are the most costly in a program unit.It generates the following statistics for each statement:Parse,execute,and fetch counts CPU and elapsed times Physical reads a
10、nd logical reads Number of rows processed Misses on the library cache Username under which each parse occurred Each commit and rollback SQL TRACE和和 TKPROF使用步骤使用步骤1:启动启动 SQL Trace 对当前session设置 alter session set sql_trace=true;2:使用使用 TKPROF生成格式化输出生成格式化输出 trace文件位于udump目录下，用以下语句找出trace文件名称select s.user
11、name,p.spid from v$process p,v$session s,v$mystat m where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr;Trace文件内容文件内容 ORACLE性能调优性能调优三三.SQL性能优化性能优化Sql语句处理过程语句处理过程 ORACLE的优化器的优化器 ORACLE的优化器共有3种:a.RULE(基于规则)b.COST(基于成本)c.CHOOSE(选择性)设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,AL
12、L_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其进行覆盖.为了使用基于成本的优化器(CBO,Cost-Based Optimizer),你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性.如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(full tab
13、le scan),你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.索引的使用索引的使用一一.合理使用合理使用Index优化优化SQL 语句语句 减少逻辑读（减少逻辑读（logical reads）是优化）是优化SQL的基本原的基本原则之一则之一,通过索引或改变通过索引或改变join方式等方法减少读取方式等方法减少读取,可可以明显提高查询性能以明显提高查询性能。索引基本原理说明索引基本原理说明1）数据表Test有A、B两列，均为num：rowidAB1 1 52 8 43 7 94 3 2 2）对列A建立索引，可以简单的理解为将列A及rowid单独提出来，针对列A进
14、行排序存储：A rowid 1 1 3 4 7 3 8 23）如果在where中针对列A进行查找，由于列A在索引中已经是有序存储，那么可以使用有效的查找算法快速定位符合条件的rowid，进而通过rowid定位获取数据表中的数据行4）如果对表中的某些列建立了索引，那么对表进行INSERT、UPDATE、DELETE操作的时候，不但要更新数据表，还需要对索引文件进行修改，必然增加了计算和IO的消耗建索引的原则建索引的原则1.Consider indexing keys that are used frequently in WHERE clauses.2.keys that are used fr
15、equently to join tables in SQL statements.3.Index keys that have high selectivity.基于成本的优化器(CBO,Cost-Based Optimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率.如果索引有很高的选择性,那就是说对于每个不重复的索引键值,只对应数量很少的记录.选择性越高,通过索引键值检索出的记录就越少.如果索引的选择性很低,检索数据就需要大量的索引范围查询操作和ROWID 访问表的操作。也许会比全表扫描的效率更低.如果检索数据量超过如果检索数据量超过30%的表中记录数的表中记录数.使用索引
16、将没有显著的效率提高使用索引将没有显著的效率提高.4.Do not index columns that are modified frequently5.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引的注意事项使用索引的注意事项 存在下面情况的存在下面情况的SQLSQL，不会用到索引：，不会用到索引：1 1
17、）存在数据类型隐形转换的存在数据类型隐形转换的,如：如：select select*from staff_member where staff_id from staff_member where staff_id=123=123；使用使用substrsubstr字符串函数的，如：字符串函数的，如：select select*from staff_member from staff_member where substr(last_name,1,4)=FRED;where substr(last_name,1,4)=FRED;2 2）避免在索引列上使用函数避免在索引列上使用函数 对索引的列使用
18、函数，会使优化器忽略掉这些索引。一些常见函数：Trunc，substr，to_date,to_char和instr等，都能改变列的值。因此无法使用已被索引的列的值。通过改变多比较的列上的值，而不用改变列本身，就可以启用索引，避免全表扫描。3 3）%通配符在第一个字符的，如通配符在第一个字符的，如:select select*from staff_member where first_name from staff_member where first_name like%DON;like%DON;LIKELIKE操作符操作符 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索
19、引将不被采用。LIKE%5400%使用全表扫描 LIKE 5400%使用范围索引（Range Index）。4 4）字符串连接字符串连接(|)(|)的，如：的，如：WHERE ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA;WHERE ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA;改为改为WHERE ACCOUNT_NAME=AMEX AND ACCOUNT_TYPE=A;WHERE ACCOUNT_NAME=AMEX AND ACCOUNT_TYPE=A;SQL性能调优性能调优二二.优化优化GROUP BY 提高GROUP BY 语句的效率,可以通过将不需要
20、的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.低效:SELECT JOB,AVG(SAL)FROM EMPGROUP BY JOB HAVING JOB=PRESIDENTOR JOB=MANAGER高效:SELECT JOB,AVG(SAL)FROM EMPWHERE JOB=PRESIDENTOR JOB=MANAGERGROUP BY JOB SQL性能调优性能调优三、选择合适的三、选择合适的Join次序次序 选择驱动表（Driving Table），在多个表的Join中，选择限制条件最多的表作为驱动表。选择Join的次序，优先Join限制条件最多
21、的表，以获得较小的结果集 通过Explain Plan查看Join的情况，可以使用ORDERED 或 hint来限制Join的次序SQL性能优化性能优化四四.ORDER BY ORDER BY按索引列排序效率较高，但ORDER BY 子句只在两种严格的条件下使用索引.1.ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.2.ORDER BY中所有的列必须定义为非空(not null).五五.SELECT子句中避免使用子句中避免使用 *ORACLE在解析的过程中,会将*依次转换成所有的列名,通过查询数据字典完成的,耗费更多的时间.每少提取一个字段，数据的提取速度就会有相应
22、的提升。提升的速度还要看舍弃的字段的大小来判断。六六.使用使用DECODE函数来减少处理时间函数来减少处理时间 使用使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表函数可以避免重复扫描相同记录或重复连接相同的表 七七.删除重复记录删除重复记录最高效的删除重复记录方法 DELETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO=E.EMP_NO);SQL性能优化性能优化八。八。用用NOT EXISTS替代替代NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况
23、下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例如:SELECT FROM EMPWHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);为了提高效率.改写为:(方法一:高效)SELECT.FROM EMP A,DEPT BWHERE A.DEPT_NO=B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+)=A(方法二:最高效)SELECT.FROM EMP
24、 EWHERE NOT EXISTS(SELECT X FROM DEPT D WHERE D.DEPT_NO=E.DEPT_NO AND DEPT_CAT=A);SQL性能优化性能优化九九.避免使用耗费资源的操作避免使用耗费资源的操作 带有DISTINCT,UNION,MINUS,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.十.用用TRUNCATE替代替代DELETE 当删除表中的记录时,在通常情况下,回滚段(rollback segments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到
25、执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.性能调整案例性能调整案例四四 性能调整案例性能调整案例案例一案例一 SGA设置设置SGA：System Global Area系统全局区。是一块用于加载 数据对象并保存运行状态和数据库控制信息的一块内存区域，在数据库实例启动时分配，每个实例都拥有自己的SGA区。案例一案例一 SGA调整调整 Oracle SGA区共享池部分由库高速缓存、字典高速缓存及其他一些用户和服务器会话信息组成，共享池是最大的消耗成分。调整SGA区各个结构的大小，可以极
26、大地提高系统的性能。最关键的参数:：DB_CACHE_SIZE（用来存储和处理内存中数据的SGA区域的大小)，把DB_CACHE_SIZE设的太低，无论怎样调试系统，Oracle也没有足够的内存来有效的执行操作，系统运行状况会很差，如果DB_CACHE_SIZE设的过高，系统在运行一段时间后很快就会停下来。案例一案例一 SGA调整调整命中率：指不用从硬盘上物理读操作的数据库的访问比例，是系命中率：指不用从硬盘上物理读操作的数据库的访问比例，是系统效率的关键指标。统效率的关键指标。缓存命中率的计算方法缓存命中率的计算方法-计算公式：1-(physical reads/(db block gets
27、+consistent gets)-命中率应大于0.95最好 select sum(decode(name,physical reads,value,0)phys,sum(decode(name,db block gets,value,0)gets,sum(decode(name,consistent gets,value,0)con_gets,(1-(sum(decode(name,physical reads,value,0)/(sum(decode(name,db block gets,value,0)+sum(decode(name,consistent gets,value,0)*1
28、00 hitratio from v$sysstat;一个非常低的命中率意味着系统配置或应用存在严重问题；但是，一个非常高（大于99%）的缓存命中率也往往同样意味着应用中存在严重低效率的SQL语句案例一案例一 SGA调整调整调整Library Cache 库高速缓存（Library Cache）中包含私用和共享SQL区和PL/SQL区。调整SGA的重要问题是确保库高速缓存足够大，以使 ORACLE能在共享池中保持分析和执行语句，提高语句分析和执行效率，降低资源消耗。通过比较Library Cache的命中率来决定它的大小。查询 V$LIBRARYCACHE 数据字典视图（其中，pins表示高速
29、缓存命中率，reloads表示高速缓存失败）如果sum(reload)/sum(pins)0，说明Library Cache的命中率比较合适，若大于1，则需要增加共享池（SHAREDPOOLSIZE）的大小。案例一案例一 SGA调整调整.调整数据字典高速缓存（Dictionary Cache）数据字典高速缓存包括了有关数据库的结构、用户、实体信息等。数据字典的命中率对系统有很大的影响。命中率的计算中，getmisses 表示失败次数，gets表示成功次数。查询V$ROWCACHE表：如果该值90%，说明命中率合适。否则，应增大共享池的大小。案例一案例一 SGA调整调整 java pool 对于
30、大的应用，java_pool_size应=50M，对于一般的java存储过程，缺省的20M已经够用了。使用可用内存来判断使用可用内存来判断SHARED_POOL_SIZE是否设置正确是否设置正确如果在系统中运行大量查询后还有很多可用的空闲内存（大于2MB），就不必增大参数SHARED_POOL_SIZE的值。案例一案例一 SGA调整调整1.监控服务器的性能确定是否需要调整2.确定调整策略 a.调整原则：尽可能使Oracle服务器使用资源最大化,特别在Blowser/Server中尽量让服务器上所有资源都来运行Oracle服务。b.调整依据：物理内存大小、操作系统占用内存多少、并发连接数量、是O
31、LAP还是OLTP系统 c.调整目标 d.调整方案3.调整实施继续监控服务器的性能4.对比调整前后的结果，确定调整结果做好备份做好备份案例二案例二CPU高度消耗高度消耗二.如何诊断和解决CPU高度消耗（100）的问题问题描述：系统CPU高度消耗，系统运行缓慢1.首先通过首先通过TOP命令查看命令查看$top发现进程列表中，存在很多占有CPU很高的进程，但是内存和I/O的占用率都不高2.查看告警日志查看告警日志ALERT文件文件。没有发现什么错误存在，日志显示数据库运行正常，就可以排除数据库本身存在问题。3.找到存在问题的进程信息，确认连接的用户进程找到存在问题的进程信息，确认连接的用户进程$p
32、s ef|grep pid4.捕获存在问题的捕获存在问题的SQL语句，通过如下语句，通过如下getsql.sql，可以，可以获取相关获取相关SQL语句语句案例二案例二CPU高度消耗高度消耗输入一个pid，这个pid process id就是top中看到的pid通过pid和V$process,spid相关联，获得process的相关信息，进而通过V$process.addr和V$session.paddr相关联，可以获得和session相关的所有信息，在结合V$sqltext，就可获得当前session正在执行的SQL语句，通过V$process视图，我看得以把操作系统和数据库关联起来。Sele
33、ct sql_text,spid,v$session.program,process fromV$sqlarea,v$session,v$processWhere v$sqlarea.address=v$session.sql_addressAnd v$sqlarea.hash_value=v$session.sql_hash_valueAnd v$session.paddr=v$process.addrAnd v$process.spid in(PID);很多时候，高CPU消耗都是由于问题SQL导致的，所以找到这些SQL通常也就找到了问题所在，通过优化调整通常就可以解决。但是有时候可能会发现，这些最消耗CPU的进程是后台进程，这一般是由于异常、BUG导致，那么就需要具体问题具体分析了。推荐资料推荐资料 Oracle 9i 性能调整性能调整 Richard J.Niemiec 著，王海涛 译，清华大学出版社 Oracle 9i 数据库性能优化数据库性能优化 盖国强 冯春培 叶梁 冯大辉编著，人民邮电出版社 ORACLE性能调整性能调整感谢大家！感谢大家！
