Oracle 优化和性能调整
分析评价Oracle数据库性能主要有数据库吞吐量、数据库用户响应时间两项指标。数据库用户响应时间又可以分为系统服务时间和用户等待时间两项，即： 数据库用户响应时间=系统服务时间＋用户等待时间 因此，获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。
数据库性能优化包括如下几个部分： 调整数据结构的设计 这一部分在开发信息系统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。 调整应用程序结构设计 这一部分也是在开发信息系统之前完成的。程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。 调整数据库SQL语句 应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。 Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁管理器（Row-Level Manager）来调整优化SQL语句。 调整服务器内存分配 内存分配是在信息系统运行过程中优化配置的。数据库管理员根据数据库的运行状况不仅可以调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小，而且还可以 调整程序全局区（PGA区）的大小。 调整硬盘I/O 这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O 负载均衡。 调整操作系统参数 例如：运行在Unix操作系统上的 Oracle数据库，可以调整Unix数据缓冲区的大小、每个进程所能使用的内存大小等参数。 实际上，上述数据库优化措施之间是相互联系的。Oracle 数据库性能恶化的表现基本上都是用户响应时间比较长，需要用户长时间的等待。而性能恶化的原因却是多种多样的，有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在。另外，良好的数据库管理工具对于优化数据库性能也是很重要的。
Oracle数据库常用的数据库性能优化工具有：
Oracle数据库在线数据字典 Oracle在线数据字典能够反映出Oracle的动态运行情况，对于调整数据库性能是很有帮助的。 操作系统工具 例如使用Unix操作系统的Vmstat、 Iostat等命令可以查看到系统级内存和硬盘I/O的使用情况，这些工具能够帮助管理员弄清楚系统瓶颈出现在什么地方。 SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，并使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用TKPROF工具查看这些文件。 Oracle Enterprise Manager（OEM） 这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的Oracle数据库管理的命令。 Explain Plan——SQL语言优化命令 使用这个命令可以帮助程序员写出高效的SQL语言。 系统性能评估
信息系统的类型不同，需要关注的数据库参数也是不同的。数据库管理员需要根据自己的信息系统类型来着重考虑不同的数据库参数。
在线事务处理信息系统（OLTP） 这种类型的信息系统一般需要有大量的Insert、Update操作，典型的系统包括民航机票发售系统、银行储蓄系统等。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的Oracle数据库需主要考虑以下参数： 数据库回滚段是否足够？ 是否需要建立Oracle数据库索引、聚集、散列？ 系统全局区（SGA）大小是否足够？ SQL语句是否高效？ 数据仓库系统（Data Warehousing） 这种信息系统的主要任务是从Oracle的海量数据中进行查询，以得到数据之间的某些规律。数据库管理员需要为这种类型的Oracle数据库着重考虑下述参数： 是否采用B＊索引或者Bitmap索引？ 是否采用并行SQL查询以提高查询效率？ 是否采用PL/SQL函数编写存储过程？ 有必要的话，需要建立并行数据库以提高数据库的查询效率。 参数的调整
CPU参数
CPU是服务器的一项重要资源，服务器良好的工作状态表现为在工作高峰时CPU的使用率高于90％。如果空闲时间CPU使用率就在90％以上，说明服务器缺乏CPU资源；如果工作高峰时CPU使用率仍然很低，则说明服务器CPU 资源还比较充足。 使用操作命令可以看到CPU的使用情况，一般Unix操作系统的服务器，可以使用sar-u命令查看CPU的使用率；NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。 数据库管理员可以通过查看v＄sysstat数据字典中的 “CPU used by this session”统计项得知Oracle数据库使用的CPU时间；查看“OS User level CPU time”统计项得知操作系统用户状态下的CPU时间；查看“OS System call CPU time” 统计项得知操作系统系统状态下的CPU时间，操作系统总的CPU时间就是用户状态和系统状态时间之和。如果Oracle数据库使用的CPU时间占操作系统总CPU时间的90％以上，就说明服务器CPU基本上被Oracle数据库使用着，这是合理的，反之，则说明服务器CPU被其他程序占用过多，Oracle数据库无法得到更多的CPU时间。 内存参数
----内存参数的调整主要是指Oracle数据库的系统全局区（SGA）的调整。SGA主要由3部分构成：共享池、数据缓冲区、日志缓冲区。
----共享池由两部分构成：共享SQL区和数据字典缓冲区。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区则存放数据库运行的动态信息。
性能调整案例
一．通过top命令看CPU的利用率：
#top
以下是TOP的结果： last pid: 11225; load averages: 7.95, 6.63, 6.25 17:19:35 273 processes: 259 sleeping, 3 running, 5 zombie, 3 stopped, 3 on cpu CPU states: 10.0% idle, 75.0% user, 15.0% kernel, 0.0% iowait, 0.0% swap Memory: 8192M real, 4839M free, 2147M swap in use, 12G swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 10929 oracle 1 59 0 1048M 1022M cpu/6 2:52 21.59% oracle 11224 oracle 1 59 0 1047M 1018M run 0:03 4.22% oracle 8800 oracle 1 59 0 1048M 1022M run 1:39 3.99% oracle 4354 oracle 1 59 0 1049M 1023M cpu/4 0:28 3.46% oracle 3537 oracle 1 59 0 1048M 1022M sleep 1:01 1.93% oracle 29499 oracle 1 59 0 1048M 1022M sleep 30.0H 1.84% oracle 11185 oracle 1 59 0 1047M 1020M sleep 0:01 0.74% oracle 11225 wacos 1 44 0 2832K 1928K cpu/0 0:00 0.65% top 9326 oracle 1 59 0 1047M 1020M sleep 0:58 0.50% oracle 410 root 14 59 0 7048K 6896K run 76.3H 0.42% picld 21363 oracle 1 59 0 1047M 1019M sleep 574:35 0.36% oracle 10782 oracle 11 59 0 1052M 1024M sleep 749:05 0.28% oracle 13415 oracle 1 59 0 1047M 1019M sleep 6:07 0.27% oracle 5679 oracle 11 59 0 1052M 1026M sleep 79:23 0.19% oracle 5477 oracle 258 59 0 1056M 1021M sleep 57:32 0.14% oracle
二．通过分析找出了消耗CPU最高进程对应的SQL语句：
SQL>set line 240
SQL>set verify off
SQL>column sid format 999
SQL>column pid format 999
SQL>column S_# format 999
SQL>column username format A9 heading "ORA User"
SQL>column program format a29
SQL>column SQL format a60
SQL>COLUMN OSname format a9 Heading "OS User"
SQL>SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
Enter value for 1:10929
最终找到的SQL语句如下：
SELECT NVL(SUM(RURALCHARGE),0.00) AS Fee FROM LOCALUSAGE WHERE ServiceID=219987 and starttime >= to_date('2003/12/30 13:24:20','YYYY/MM/DD HH24:MI:SS');
三．查询localusage表上的分区索引：
通过以下查询得到localusage表上的分区索引有两个：
SQL> select INDEX_NAME from user_part_indexes where TABLE_NAME='LOCALUSAGE';
INDEX_NAME ------------------------------ I_LOCALUSAGE_SID UI_LOCALUSAGE_ST_SEQ
通过执行计划分析出这条语句使用的是UI_LOCALUSAGE_ST_SEQ索引，没有使用I_LOCALUSAGE_SID索引，我统计了一下时间，用UI_LOCALUSAGE_ST_SEQ这个索引的效率很差的，返回时间用了2分钟36秒，而使用I_LOCALUSAGE_SID这个索引的话，返回时间为1秒多。而ORACLE缺省使用的是UI_LOCALUSAGE_ST_SEQ索引，因此占用了大量的CPU资源，导致CPU利用率下降。
以下是用autotrace的分析过程：
SQL>connect wacos/oss
SQL>set autotrace on
SQL> set timing on SQL> SELECT NVL(SUM(RURALCHARGE),0.00) AS Fee FROM LOCALUSAGE WHERE ServiceID=219987 and starttime >= to_date('2003/12/30 13:24:20','YYYY/MM/DD HH24:MI:SS');
FEE ---------- 107.25
Elapsed: 00:02:36.19 （返回时间2分36秒）
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=35) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (C ost=10 Card=10035 Bytes=351225)
4 3 INDEX (RANGE SCAN) OF 'UI_LOCALUSAGE_ST_SEQ' (UNIQUE ) (Cost=2 Card=10035)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11000821 consistent gets 349601 physical reads 0 redo size 292 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
用HINT强制ORACLE使用I_LOCALUSAGE_SID索引,然后查看执行计划:
SQL>connect wacos/oss
SQL>set autotrace on
SQL>set timing on
SQL> SELECT /*+ INDEX(LOCALUSAGE I_LOCALUSAGE_SID)*/ NVL(SUM(RURALCHARGE),0.00) AS Fee FROM LOCALUSAGE WHERE ServiceID=219987 and starttime >= to_date('2003/12/30 13:24:20','YYYY/MM/DD HH24:MI:SS');
FEE ---------- 107.25
Elapsed: 00:00:01.15 （返回时间1秒）
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=35) 1 0 SORT (AGGREGATE) 2 1 PARTITION RANGE (ALL) 3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'LOCALUSAGE' (Cost=15 Card=10035 Bytes=351225)
4 3 INDEX (RANGE SCAN) OF 'I_LOCALUSAGE_SID' (NON-UNIQUE) (Cost=14 Card=10035)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 307 consistent gets 232 physical reads 0 redo size 292 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
建议研发人员调整该语句，使这条语句缺省使用I_LOCALUSAGE_SID索引，或在语句中使用HINT来强制使用I_LOCALUSAGE_SID索引。
案例二
一. 引言 本文描述了在Linux 环境下对一个大型Web应用系统的Oracle数据库进行 优化调整的过程.分为数据库分析,数据库调整,数据库监控三部分.在每部分 中,对具体的操作都有比较详细的分析说明. 二. 数据库分析 2.1 系统概述 系统是一个针对特定客户的大型web系统.存在几个数据增长频度很高的表, 并发事务不多. 2.2 详细分析 系统的瓶颈目前体现在部分查询的效率比较低,归档日志增幅过快两方面. 随着以后用户数的增多,并发操作的效率也是一个潜在性的问题.因此 需要对数据库服务器进行全面的分析, 包括环境配置,网络配置, 数据库 初始化参数配置, SQL语句的分析等部分. 2.2.1 环境配置 硬件配置: RAM: 4G Disk: 500G 软件配置: OS: Redhat Linux Database: Oracle 817 Enterprise Edition
分析结果: 硬件配置较高,能满足大型OLTP的应用. Oracle 在Unix/Linux 环境下比WINNT环境下性能更好. 2.2.2 数据库初始化参数 对于每个Oracle数据库实例,有一个对应的初始化参数配置文件 initsid.ora. 参数文件中某些参数对数据库性能影响极大.下面列出的 是一些核心参数: DB_BLOCK_BUFFERS SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE LOG_BUFFER DB_FILE_MULTIBLOCK_READ_COUNT SORT_AREA_SIZE SHARED_POOL_RESERVED_SIZE 分析结果: 大部分参数的值都是数据库建立时的缺省值,需要调整. 2.2.3 数据库逻辑结构 根据OFA (Optimal Flexible Architecture) 的规则, 将数据库对象按照对象类型及操作类型进行区分.也就是根据具体的业务特征规划表空间.
分析结果: 表空间组织不太合理,大部分数据建立在SYSTEM表空间 ; 表中的索引没有专属的表空间, 不利于管理; 用户的配额管理和权限管理需要调整.
2.2.4 数据库物理结构 数据库的物理结构主要体现在磁盘分布的规划上,以确保磁盘I/O 对数据库性能的影响最小化.
分析结果: 结合逻辑结构的分析,部分数据文件需要与其他数据文件分开存放,以确保I/O竞争最小化,包括: 业务表空间与索引表空间分离
回滚段空间与业务表空间分离
SYSTEM 表空间与其他表空间分离 同时还确定系统的总体目标是基于恢复的,对于产品数据库, 要启用归档重做日志模式, 对于测试数据库,通过逻辑备份 或冷备份来备份数据. 2.2.5 表结构分析 分析结果: 表结构基本设计合理, 部分表的字段类型需要调整 ; 部分表没有做过STATISTICS 或者 STATISTICS 比较陈旧, 出现了ORACLE 不使用索引,而使用全表扫描的情况 ; 调整FK的使用. 2.2.6 SQL语句分析 分析结果: 系统中没有过于复杂的查询, 但是有一个代价较高的查询 ; 批量转移数据可以有更好更块的方式 ; 可以更多地利用数据库的特性,将处理语句编写成过程或函数, 在服务器上运行,减少网络流量 ; 没有充分使用绑定变量,共享池的使用效率不高 ; 短时间内的大量DML操作会导致 回滚段激增和重做日志切换频 繁, 需要优化 . 2.2.7 网络环境 分析结果: 可将数据库移植到一台单独的服务器上,以发挥更好的性能.
2.3 总结 从上面的分析得出结论: 数据库的性能还没有充分发挥,很多方面都可以通 过调整来提高性能. 三. 数据库调整
原则: 根据数据分析中提到的部分逐个进行调整,调整后进行程序检测. 3.1 参数调整 说明: 服务器内存4G, 考虑到服务器同时还运行其他任务, 从中分配 10%给SGA.可根据实际情况重新分配. 一般来说SGA的范围 是物理内存的 20%-40% , 最大不能超过全部物理内存的50% . 操作: 计算方式: SGA=((db_block_buffers * block size)+ (shared_pool_size+large_pool_size+java_pool_size+ log_buffers) +1MB =0.1 * 4G=0.4G =410M 3.1.1 共享池 约为SGA的一半即200M 调整前: shared_pool_size=31457280(31.45M) 调整后: shared_pool_size=209715200(200M) 说明: 这个值不能太大, 可根据实际业务中SQL语句的复杂度来衡量. 如果业务中没有过多的动态 SQL或者没有复杂的SQL,可以减小 它, 否则会使SHARE POOL中碎片增多, 并且频繁引发OS级别 的内存调度,严重影响系统的正常运行. 监控: 定期运行statspack 或utlstat包,根据获得的监控数据来调整. 监控结果: 通过 statspack包产生的report文件的分析,共享池的值设置 偏大,可以调小. 3.1.2 缓冲区高速缓存块 设置为SGA的一半即200M
缓冲区高速缓存容量=缓冲区高速缓存块数 (DB_BLOCK_BUFFERS) * 每个块的大小 (DB_BLOCK_SIZE) DB_BLOCK_SIZE=8K, 这个参数是安装数据库时的缺省设置.
调整前: db_block_buffers = 2048 调整后: db_block_buffers = 25600(200M /8K)
说明: 缓冲区高速缓存对性能影响极大,因为用户进程所存取的所 有数据都是经过缓冲区高速缓存来存取.调整完毕后,定期 检查它的命中率.如果命中率小于90%, 则需要增大这个参数. 3.1.3 日志缓冲区 (LogBuffer) 不进行调整,保持缺省的参数. 说明: 通过对alert 文件的监控发现,发现有不能分配重做日志文件 空间的信息,增加重做日志文件大小/日志缓冲区大小可以 解决这个问题.
3.1.4 大池 (Large_pool_size) 大存储池的分配堆,它可被多线程服务器用作会话内存,用作并行 执行的消息缓冲区以及用作 RMAN 备份和恢复的磁盘 I/O缓冲区. 调整前: large_pool_size = 614400 调整后: large_pool_size = 1048576 (根据计算公式得出) 说明: 考虑到数据增长的频度, 数据量达到一定程度时需要使用RAMN 进行备份恢复.因此可适当调整. 3.1.5 JAVA 池 不进行调整,保持缺省的参数. 3.1.6 DB_FILE_MULTIBLOCK_READ_COUNT 调整前: 8 (系统缺省) 调整后: 16(适用于中型系统,即事务量不是很大的OLTP系统) 说明: 这个参数对SQL查询策略有影响.如果参数设置的比较大,有 可能造成Oracle 使用全表扫描而不使用索引.
3.1.7 排序缓冲区(SORT_AREA_SIZE) 调整前: 65536 (64K) 调整后: 1048576 (1M) 说明: Oracle 基于SORT_AREA_SIZE的设置值,为大型排序操作 分配内存,如果内存不够用,则使用临时段空间.因此应尽 可能将排序操作放在内存中进行.系统在数据交换的操作中 用到了不少排序, 因此将这个参数适当调大一些. 监控: 定期运行statspack,根据监控数据来调整.
3.1.8 SHARED_POOL_RESERVED_SIZE 默认值: SHARED_POOL_SIZE 值的 5% 调整后: 10485760(200M * 5%=10M) 说明: 共享池使用久了,会出现碎片,为了尽量少的执行刷新共 享池的操作,需要预留一定的内存空间.否则将加重CPU 重新调度的负担,同时也影响其他进程. 3.2 数据库逻辑结构调整 目标: 新建数据表空间USERDATA ; 整理 SYSTEM表空间, 只保留基本的数据字典 ; 转移特定用户, 将目前常用用户的缺省表空间设置为数据表空间 ; 转移索引到专用的索引表空间等. 3.2.1 新建表空间 USERDATA 考虑到UNIX 件系统 2G的限制(可能引发管理问题), 将每个数据 文件设置成2G ; 通过分析全库逻辑备份文件的大小,初始大小设成6G .
3.2.2 特定用户的转移 现有系统中对表的访问大多是使用数据库内置的用户scott, 从安全性和用户管理方面考虑,有以下两种方案: . 保留现有用户, 调整配额策略和分配对应的权限 ; . 设置新用户,重新分配权限 . 下面的改进采用方案1. STEP 1: 回收角色和权限 Revoke resource from scott STEP 2: 取消该用户在SYSTEM表空间上的磁盘配额 Alter user scott quota 0 on system STEP 3: 将该用户的缺省表空间改为 USERATA Alter user scott default tablespace USERDATA STEP 5: 调整磁盘配额, 重新授权 Alter user scott quota unlimited on USERDATA grant create procedure to scott grant create trigger to scott grant create type to scott
说明: 最好不要将系统权限 unlimited tablespace赋予普通用户.同时, 数据库的缺省用户DBSNMP有这个权限,从安全性角度 考虑,需要回收这个权限.
3.2.3 数据转移 有三种方案可以选择: 1. 使用SQL语句转移表 alter table TableName move tablespace TBSNAME
说明: 因为表上索引的物理存储位置已经改变, 需要重建索引.
2. 使用基于对象的导出/导入策略.
目的: 将scott 用户所拥有的全部对象(表, 索引,约束, 触发器等) 到 USERDATA表空间 STEP 1: 导出
exp userid=system/manager parfile=exp_scott.par file=exp_scott.dmp log=exp_scott.log owner="(scott)" 参数文件 exp_scott.par: BUFFER=4096000 COMPRESS=Y GRANTS=Y INDEXES=N ROWS=Y CONSTRAINTS=N DIRECT=Y 说明: 如果不导出索引,约束,并且使用直接路径,加大缓存能 大大提高导出的速度.使用COMPRESS=Y 选项压缩原 表空间; 导出过程前事先估算文件的大小, 如果超过2G,作分割. STEP 2: 导入 imp userid=system/manager parfile=imp_scott.par file=exp_scott.dmp log=imp_scott.log fromuser="(scott)" touser="(scott)" 参数文件: BUFFER=4096000 COMMIT=Y GRANTS=Y INDEXES=N IGNORE=Y ROWS=Y 说明: 在数据量不是很大时,导入的速度还可以接受,但是随着数据 量的增加,导入的速度就会越来越慢.这时可以考虑使用RMAN或者使用冷/热备份方式来备份数据. 3. 使用CTAS语句 CREATE TABLE TableName TABLESPACE USERDATA As select * from …
说明: 在归档重做日志模式下,使用NOLOGGING参数可以大幅 减小重做日志. 数据转移完毕后,删除旧表,新建索引. 3.2.4 索引的转移 目的: 分配索引专属的表空间,可以减小磁盘 I/O竞争, 使得数据库逻辑 结构更清晰. 实现: 通过 CREATE INDEX 语句中的TABLESPACE 参数可以指定索 引使用的表空间 ; 创建表的主键时会同时创建唯一性索引,使用 using index tablespace 子句指定表空间.
例如: Create index IX_T1_SepID ON T1(SepID) tablespace INDX storage (initial 40K next 40K pctincrease 1) Alter table T1 add constraint PK_T1 primary key (ID1,ID2) using index tablespace INDX storage (initial 40K next 40K pctincrease 1)
3.2.5 整理SYSTEM 表空间 目的: SYSTEM 表空间只放置数据字典 实现: 将数据和索引转移后,清理临时表, 最后合并自由盘区. alter tablespace SYSTEM default storage(pctincrease 1) alter tablespace SYSTEM coalesce 3.3 Performance Tuning 3.3.1 Execution Plan 当SQL语句出现性能瓶颈时,首先检查查询计划.如果查询计划 不是最优计划时,从这些方面逐个检查调整. . 表或索引是否做过统计 . 上次统计的时间 . 从上次统计到现在, 活跃表的增长频度 . 是否使用了索引 . 索引访问是不是最佳访问策略 先对两个活跃表 T1, T2作全表统计 ANALYZE TABLE T1 COMPUTE STATISTICS ANALYZE TABLE T2 COMPUTE STATISTICS 对其他表有选择性地作索引统计 ANALYZE TABLE T3 COMPUTE STATISTICS FOR ALL INDEXES 对指定的列按照指定的SIZE进行统计: ANALYZE TABLE T4 COMPUTE STATISTICS FOR COLUMNS COLX SIZE XXX 执行完毕后,检查 USER_INDEXES, USER_TAB_COLUMNS 视图 获得相关信息. 然后作一些简单的测试SQL, 表T1上 col1+col2 为主关键字, col3 列有非唯一索引. Select count(*) from T1 Select col1,col2,col3 from T1 where col1=1 and col2 =… and T1 .SepID= : iLowVal ' || ' and ' || sColumn || ' <= : iHighVal ' || sOtherCon ; -- dbms_output.put_line(' The entire sql is:' || strSQL); Execute immediate strSQL into iCountRet using iLowVal, iHighVal; -- Test --dbms_output.put_line(' Result: count is ' || icountRet); EXCEPTION WHEN OTHERS THEN iCountRet := -1; End; / 2. 索引使用分析: 上面存储过程中有一个参数sOtherCon, 供用户输入其他的查 询条件,假设输入条件是 col5=0.
这条SQL语句的瓶颈主要出在对col5列的访问上,因为该列 只有两个值(1,0), 且没有建索引.尝试建一个复合索引(该列不 能单独建位图索引,位图索引通常用于OLAP系统) create index ix_bind_T1 on T1(Sep_ID,col5) tablespace indx storage (initial 64K NEXT 64K PCTINCREASE 1); 再做一个统计: analyze table T1 compute statistics for columns Sep_ID Size 8; 最后重新执行查询. 查询计划: 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'IX_BIND_T1' (NON-UNIQUE) (Cost=2 Card=83672 Bytes=502032)
统计信息: 0 recursive calls 0 db block gets 392 consistent gets 391 physical reads 0 redo size 293 bytes sent via SQL*Net to client 421 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed 可以看出: 改进后的索引查询效率比较高. 对于复杂的SQL,也按照这样的步骤进行,对大查询中的各个子查询作查询计划,找出其中的性能瓶颈.有一点非常重要:要从整个系统的角度来考虑索引,合理地设置和使用索引. 3.3.2.2 其他DML 系统中除了查询语句,还有其他的DML语句,包括新增,修改和删除.对于OLTP系统来说,优化这些操作对于改善系统的性能至关重要. 新增操作: 大量数据导入时,优化效率可以从这些方面入手:
对于批量导入操作, 考虑这些因素: 1) 优先选择系统不繁忙的时间段进行 ; 2) 准备一个大的回滚段 ; 3) 禁用目标表上的索引, 约束等 ; 4) 对目标表的操作不创建重做日志 ; 5) 使用 Insert /*+ Append */ 直接导入方式; 如果运行在归档日志模式下,上面提到的第3,4点更加重要,因为导入期间会产生大量的归档重做日志,很有可能造成磁盘空间不足. 在操作完成后要马上进行数据库的备份. 使用IMP命令时,考虑这些因素: 1) 加大缓存; 2) 将目标表上的索引,约束暂时去掉, 导入完成后 再重新创建; 3) 批量提交. 删除操作: 对于删除操作,考虑这些因素: 1) 如果有可能,使用一个独立的私有回滚段,大小是 最大数据量的1.5 –2 倍左右 ; 2) 批量提交 ; 3) 区分维护性的删除操作和必需的删除操作, 如果 是定期的维护数据,建一个JOB在数据库不繁忙 的时候(夜间)自动完成; 如果是应用中必要的删除 操作,要保证操作尽可能快速完成. 4) 估算要删除的数据量大小,如果接近于全表, 可以 用SQL命令将表中的剩余数据导出,然后执行 Truncate table 命令将表清空,最后再将数据重新 导入.
四. 数据库监控
4.1 STATSPACK 4.1.1 概述 STATSPACK 是Oracle 提供的性能监控工具之一,可以提供一段时间内数据库运行的综合指标,从中可以检测出目前数据库最大的性能瓶颈. 4.1.2 TOP WAIT EVENTS TOP WAIT EVENTS 是STATSPACK 产生的REPORT中核心的部分, 它列出了系统最多的等待事件.看下面一个例子: Event Waits Wait Time (CS) % Total Wt Time db file scattered read 36,159 694 43.38 db file sequential read 9,900 296 18.50 Log file parallel write 1,620 255 15.94 control file parallel write 1,069 198 12.38 Log buffer space 50 73 4.56 分析: db file scattered read: 过多的使用全表扫描而不是使用索引 ;
db file sequential read: 过多的使用了单块读操作; log file parallel write: 与磁盘I/O操作有关 ; control file parallel write: log buffer space: 需要调整log buffer 大小. 4.1.2.1 db file scattered read 1. 检查SQL语句(将snaplevel 设成5可以获得SQL语句监控的相关信息),看SQL 语句是否使用了太多的物理读.找到REPORT 中相关的部分: Statistic Total Per Second Per Trans physical reads 475,420 150.4 844.4 physical reads direct 132 0.0 0.2 physical writes 4,381 1.4 7.8 physical writes direct 132 0.0 0.2 physical writes non checkpoint 2,748 0.9 4.9
从上面的数据可以得出结论: 由于physical reads 的值比较大, 肯定存在没有很好优化的SQL语句. 从REPORT中找到这些SQL语句: Physical Reads Executions Reads per Exec % Total Hash Value 73,537 4 18,384.3 99.6 1570063194 select count(*) from T1
41,098 6 6,849.7 8.6 1127261530 DELETE /*+NESTED_TABLE_SET_REFS+*/ FROM T2_TMP 40,432 6 6,738.7 8.5 3300685046 Delete from T2_TMP 40,363 6 6,727.2 8.5 2027128933 INSERT INTO T2 SELECT * FROM T2_TMP 40,306 6 6,717.7 8.5 2047057492
delete from T2 where (col1,col2) in (select col1,col2 from T2_TMP) … 分析: 可以看出,这是一个比较典型的数据导入批操作.先根据条件删除重复的数据,然后将将数据从临时表转移到目标表,最后清空临时表. Delete from 子句 会占用很多回滚段空间,产生大量重做日志, 改用Truncate table 语句效率会好很多. INSERT INTO 操作的代价也比较高,因为目标表上有索引,新增数据的时候需要维护索引,也会产生大量的重做日志.如果用直接导入方式,并且将索引置成不产生日志的模式,速度会很多,并且重做日志会大大减小. ALTER INDEX IX_DEST_TABLE NOLOGGING; INSERT /*+ APPEND */ INTO … ; 在ARCHIVELOG 模式下,如果一段时间操作不写重做日志,那么这些操作就是不可恢复的,因此如果使用上面的操作,必须在操作完成后备份数据库,另外应保证这些操作很快完成. 对于其他查询语句, 分析其查询计划,对SQL语句进行优化调整. 2. 检查系统的I/O子系统是否存在瓶颈,查看REPORT中的关于TABLESPACE IO STATS 和 FILE IO STATS的相关信息. 下面是这两部分的具体指标列: Read: Reads Av Av Av Reads/s Rd(ms) Blks/Rd Write: Writes Av Buffer Av Buf Writes/s Waits Wt (ms) 需要关注的信息主要是平均读取时间,如果这个值偏大, 说明 磁盘本身的I/O性能存在瓶颈. 4.1.3 Buffer Hit Ratio & Shared pool 除了 TOP WAIT EVENTS 外,REPORT中关于 Buffer(Library) Hit 和 Shared pool的统计信息也应该关注. 一般情况下,如果库缓冲区命中率长期低于90%,可考虑增加参数 DB_BLOCK_BUFFERS的大小,确保库缓冲区命中率大于95% ; 对于 Shared pool,查看Parse 的有关指标(Total Parse, Hard Parse).尤其 是OLTP系统,如果系统中存在过多的Hard Parse, Parse Time就会大大 增加,同时对LATCH的需求也会增大,这样将严重制约系统的可伸缩性.比较有效的方式是使用绑定变量.
Oracle数据库的优化 摘要本文提出了一种优化Oracle数据库的方法。Oracle中SQL语句的执行过程可分为解析（Parse）、执行（Execute）和提取结果（Fetch）三步，此方法就是通过对SQL语句在Oracle数据库中优化执行的三个过程来提高Oracle数据库的性能。 关键词 数据库 扫描 多表联结 子查询
1 如何优化Parse 1． 1 SQL语句的Parse处理步骤： 1） 计算语句值 2） 共享池中有无与此语句值相同的语句？ 3） 共享池中有与此语句字符完全匹配的语句？ 4） 准备要运行的SQL语句 5） 为新语句在共享池中创建空间 6） 将语句存放在共享池中 7） 修改共享池图，标明语句的值和在共享池中的位置 8） 执行准备好的SQL语句 最理想的是，语句只执行1、2、3和8步来进行处理。不经过2、3步来测试被传给Oracle的语句要使用1～8步进行处理。只经过1、2、3、8的SQL语句要比经过1～8步的语句更为有效。 1．2 在共享池中重用SQL语句 当SQL语句被传递给Oracle处理时，其秘诀是重复使用已经在共享池中的语句，而不是让Oracle在接受语句时去准备新的语句。前面表明，如果Oracle接受了一个与共享池中的语句相一致的语句，就重用共享池中的语句。 Oracle提供在数据库中存储代码的能力。当应用系统开始运行时，从数据库中读取代码（可用PL/SQL语句编制）并像其它语句那样传递到共享池中去处理。从数据库中取出的代码是编译过的并驻留在共享池中。 可以利用数据库中存储的程序代码设计应用系统，检查所有的事务处理以及主要的通用的过程，研究现有的应用系统并把主要的处理程序转换为数据库中存储的程序代码。在Oracle中存储代码可以通过过程、程序包、函数、触发器等来实现。 2 如何优化Execute和Fetch 2．1 避免无计划的全表扫描 全表扫描连续从表读取所有数据，而不管数据是否与查询有关。避免不必要的全表扫描有两个充足理由：1）全表扫描没有选择性 2） 过全表扫描读取的数据很快从SGA的缓冲区移走（如果正在扫描的表不是“高速存储”的表） 在基于规则优化的情况下，如果下列任何条件在SGA语句出现，就要对一个表进行全表扫描。 1） 该表无索引 2） 对返回的行无任何限定条件（如无Where语句） 3） 对数据表与任何索引主列相对应的行无限定条件。例如，在City－State－Zip列上创建了三列复合索引，那么仅对State列有限定条件的查询不能使用这个索引，因为State不是索引的主列。 4） 对索引主列的行有限定条件，但条件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情况下都不会使用索引。 Where city is null Where city is not null Where city!=’liaoning’ 5） 对索引主列的行有限定条件，但条件在表达式里使用。例如，如果在City列上索引，那么限定条件 Where City=’liaoning’ 可以使用索引。然而，如果限定条件是 Where UPPER(City)=’liaoning’ 那么不会使用City列上的索引，因为City列在UPPER函数里。如果将City列与文本字符串联结在一起，也不会使用索引。例如，如果限定条件是 Where City||’x’ like ‘liaoning%’ 那么不会使用City列上的索引。 6） 对索引主列的行有限定条件，但条件使用Like操作以及值以‘％’开始或者值是一个赋值变量。例如，在所有下列情况下都不会使用索引： Where City like ‘%aonin%’ Where City like :City_Bind_Variable 如果表小、索引列无选择性，基于开销的优化器可能决定使用全表扫描。 2．2只使用选择性索引 索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000 或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。 如果使用基于开销的最优化，优化器不应该使用选择性不好的索引。 索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000 或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。 索引的选择性是指索引列里不同值的数目与表中记录数的比。如果表有1000个记录，表索引列有950个不同值，那么这个索引的选择性就是950/1000 或者0.95。最好的可能性选择是1.0。依据非空值列的唯一索引，通常其选择性为1.0。 2．3管理多表联结 Oracle提供了3个联结操作：NESTED LOOPS、HASH JOIN和MERGE JOIN。MERGE JOIN是一组操作，在所有行被处理完之前，它不返任何记录给下一操作。NESTED LOOPS和HASH是行操作，因此会很快将第一批记录返回给下一个操作。 在每个联结选项里，必须执行一些步骤以获取最好的联结性能。如果没有适当地优化联结操作，那么联结所需的时间也许随着表的增长而呈指数级地增长。 2．4管理包含视图的SQL语句 如果查询包含视图，优化器有两种执行查询的方法：首先解决视图然后执行查询，或者把视图文本集成到查询里去。如果首先执行视图，那么首先完成全部的结果集，然后用其余的查询条件做过滤器。 首先解决视图会导致查询性能下降的问题，这取决于所涉及表的相对大小。如果视图被集成到查询里，那么查询的条件也可以应用于视图里，并且可以使用一个小一些的结果集。然而在一些情况下，也许可以通过视图分离组操作提高查询性能。 如果一个视图包含集合的操作（如Group by、SUM、COUNT或者DISTINCT），那么视图不能被集成到查询里去。 不使用组或者没有集合操作的视图的SQL语法可以被集成到大的查询里去。 2．5优化子查询 当使用自查询时，也许会碰到几个独特的问题。涉及子查询的查询潜在问题如下： ? 也许在执行完查询的剩余部分前执行子查询（与执行分组功能的视图相似）。 ? 子查询也许要求特定的提示，但这些提示不直接与调用该子查询的查询有关 ? 可以作为单个查询执行的子查询也许被代替写成几个不同的子查询。 ? 也许在使用not in子句或者not exists子句时，不能在最有效的方式下进行子查询的存在查询。 1）当执行子查询时 如果一个查询包含子查询，那么优化器有两种完成查询的方法：首先完成子查询，然后完成查询（“视图的方法”），或者将子查询集成到查询里去（“联结”的方法）。如果首先解决子查询，那么整个子查询的结果集将首先被计算，并且用查询条件的剩余部分做过滤器。如果没有使用子查询去进行存在检查，那么“联结”方法将通常要比“视图”方法完成得好。 如果一个子查询包括集合操作，如group by，SUM或者DISTINCT，那么不能集成子查询到查询的其余部分里去。非集成的子查询限制了提供给优化器的选项。 2）如何组合子查询 一个查询可以包含多个子查询，使用的子查询越多，集成或者重写它们到大的联结里就越困难。既然有多个子查询使集成困难，就应该尽可能地组合多个子查询。 3）怎样进行存在检查 有时子查询不返回行（记录），但可以进行数据正确性检查。在相关表里的记录或者存在或者不存在的逻辑检查，称为存在检查。可以使用exists和not exists子句提高存在检查的性能。 2．6管理对非常巨大的表的访问 随着表增长到比SGA的数据块高速缓冲区存储器的空间显著大时，需要从另一个角度优化对这个表的查询。 1）问题 当表和它的索引小的时候，在SGA里可以有高度的数据共享。多用户读表或索引范围扫描可以反复使用同一个块。随着表的增长，表的索引也在增长。随着表和它的索引增长到比SGA里提供的空间大时，范围扫描需要的下一行将在SGA里找到的可能性变小，数据库的命中率将减小。最后，每一个逻辑读将要求一个单独的物理读。对使用非常大的表的优化方法着眼于特别的索引技术和有关索引的选择。 2）管理数据接近 在访问非常大的表期间，如果倾向于继续使用索引，那么应该关注数据接近，即逻辑相关记录的物理关系。为了使数据最大限度地接近，应该连续往表里插入记录。记录按通常在表的范围扫描里使用的列排序。 3）避免没有帮助的索引扫描 如果要对大表使用索引扫描，那么不能假定索引扫描将比全表扫描执行得更好。不紧跟表访问的索引唯一扫描或范围扫描执行得比较好，但紧跟通过RowID的表访问的索引范围扫描也许执行得差。随着表增长到比数据块高速缓冲存储器大得多，最终，索引扫描和全表扫描间的平衡点打破。 4）创建充分索引的表 如果表中的数据相当稳定，充分索引一个表是很有用的。创建一个复合索引，它包括所有在查询期间通常选择的列。在查询期间，查询要求的所有数据可以通过索引访问提供，不需要任何表访问。 5）并行选项 可以把一个数据库任务，比如Select语句，分为多个单元的工作，由多个Oracle进程同时执行。这种能够允许数据库的单个查询活动由多个协调的进程透明地进行处理的能力，称为并行查询选项（PQO）。 并行选项调用多个进程来利用空闲的系统资源，以减少完成任务所需要的时间。并行选项并不减少处理过程所要求的资源数量，而是把处理的任务分散给多个 CPU。为了从并行选项中得到最大的好处，应该使CPU和磁盘上的I/O不要满负荷使用。因为并行的目的是使更多的CPU和磁盘同时参与处理数据库的命令，一个缺乏CPU和I/O资源的服务程序是不能从并行选项中得到好处的。 2．7使用UNION ALL而不是UNION 最常用的集操作是UNION操作，UNION操作使多个记录集联结成为单个集。UNION操作的数学定义是返回记录的单个集并且没有重复的行，所以在合并结果集里，Oracle只返回不同的记录。 当UNION操作用作SQL语句的一部分时，唯一性要求强迫Oracle移走重复的记录。Oracle的移走重复记录的功能是SORT UNIQUE操作，它与使用DISTINCT子句时执行的操作类似。 UNION ALL操作允许重复。UNION ALL不要求SORT UNIQUE操作，从而节省了开销。UNION ALL是一个行操作，所以当其变为有效就返回给用户。而UNION包括SORT UNIQUE集操作，在全部记录的集的排序结束前，不返回任何记录给用户。 当UNION ALL操作产生巨大的结果集时，不需要任何排序便返回记录给应用的事实意味着第一行检索的响应时间更快，并且在许多情况下，可以不用临时段完成操作。 在有些情况下，UNION ALL和UNION不返回同样的结果。如果在应用环境中，结果集并不包含任何重复的记录，则可以把UNION转换成UNION ALL。 2．8避免在SQL里使用PL/SQL功能调用 对于增加PL/SQL的使用，许多用户试图利PL/SQL功能的优势产生可重复使用的代码。其中一个强迫重复使用PL/SQL功能的方法是在SQL语句里使用。例如，可以创建一个将国际货币转换为US$的函数。这个函数称为US$。示例如下： select transaction_type, US$ (amount, currency) from international_transaction where US$ (amount, currency) > 1000; 执行前面的SQL语句没有所期望的那样好。在测试时，它的性能大约比下面得出相同结果的SQL语句慢大约几十倍。 select transaction_type, amount*exchange_rate US$ from exchange_rate er, international_transaction it where er.currency = it.currency and amount*exchange_rate > 1000; 响应时间不同的原因是混合PL/SQL和SQL时，Oracle使用的机制不同。在SQL查询里嵌入PL/SQL功能时，在执行时，Oracle将调用分成两部分：用带有赋值变量的SQL语句代替功能调用以及对每一个函数调用的无名PL/SQL块。 select transaction_type, :a1 from international_transaction where :a1 > 1000 和 BEGIN :a1 := US$ (:amount, :currency); END 对在international_transaction表里的每一行，将执行在前面示例里显示的无名块两次。无名块调用导致查询响应时间的剧增。应该避免在SQL语句里使用PL/SQL功能调用。
