下载文档原格式
基于Oracle9i的数据库性能优化策略研究
王振铎;王振辉
【期刊名称】《计算机系统应用》
【年(卷),期】2006(000)006
【摘要】本文从Oracle9i的系统体系结构入手,分析了影响Oracle9i数据库性能的主要因素,主要从软件部分进行优化,操作系统、数据库结构规划、数据库表空间,回滚段、临时表空间、内存、磁盘I/O、SQL语句,应用程序,存储过程等多个方面给出了具体的优化策略.参考资料1.史森、夏丽丽等译,Oracle数据库开发指南,清华大学出版社,1998.2.宋瀚涛、李新社,数据库编程与应用,电子工业出版社,1998.3.范剑波、张晓云,网络数据库技术与应用,西安电子科技大学出版社,2004.
【总页数】4页(P76-79)
【作者】王振铎;王振辉
【作者单位】西京学院经济系,陕西,西安,710123;西北工业大学软件学院,陕西,西安,710065
【正文语种】中文
【中图分类】TP3
【相关文献】
1.基于NVRAM的内存数据库性能优化策略研究与设计 [J], 楚天骄;林中
2.基于Oracle9i关系数据库性能优化策略 [J], 熊曾刚;张学敏;王时绘
3.ORACLE9i数据库系统性能优化研究与实践 [J], 杨志和;刘细武;王岳斌;胡虚怀
4.Oracle9i数据库的性能优化方法 [J], 陈迪;陈琴
5.Oracle9i数据库开发(一)——通过JDBC API连接Oracle9i数据库 [J], 何致亿因版权原因，仅展示原文概要，查看原文内容请购买。
Oracle9i数据库性能调整与优化V1.0在数据库成熟应用的时代，数据库的性能优化已经演变为一项相当严密的系统工程。
作为企业IT基础设施的核心部件之一，数据库并不是孤立的系统，它与网络、操作系统、存储等硬件系统紧密相连，这种与其他IT部件的多重连接特性决定了数据库性能优化是一门综合技术。
数据库性能优化的实现路径和IT系统管理架构越来越密不可分。
Oracle9i数据库在内部特性方面有着非常大增强，其中一个最令Oracle DBA兴奋莫过于可以动态设置全部Oracle SGA控制参数。
与8i不同是，原来都将初始化参数放到一个文本文件中，并且在数据库启动时候读取，Oracle9i却可以通过ALTER DATABASE 和ALTER SYSTEM命令复位全部Oracle参数。
在9i前，如果想对Oracle数据库处理模式作一些改变话，Oracle管理员必须关闭数据库并且重新设置INIT.ORA文件中参数，然后重新启动数据库。
对于白天使用OLTP 模式运作，晚上切换到数据仓库模式Oracle数据库来说，这种重新设置是经常做。
对于需要停止和重新启动Oracle数据库来修改参数来说，Oracle9i在这方面有明显加强，它令实现数据库连续可用目标变得更加简单。
定义：SGA：（System Global Area）是Oracle Instance的基本组成部分，在实例启动时分配。
是一组包含一个Oracle实例的数据和控制信息的共享内存结构。
主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。
它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。
理论上SGA可占OS系统物理内存的1/2——1/3。
SGA几个很重要的特性：1、SGA的构成——数据和控制信息，我们下面会详细介绍；2、SGA是共享的，即当有多个用户同时登录了这个实例，SGA中的信息可以被它们同时访问（当涉及到互斥的问题时，由latch和enquence控制）；3、一个SGA只服务于一个实例，也就是说，当一台机器上有多个实例运行时，每个实例都有一个自己的SGA尽管SGA来自于OS的共享内存区，但实例之间不能相互访问对方的SGA区。
Oracle 9iORACLE9i数据库技术Oracle数据库效劳器的最新版本Oracle 9i是Oracle数据库效劳器家族中的新一代旗舰产品。
ORACLE数据库产品具备许多技术特性。
1-先进性ORACLE公司成立以来，在数据库领域始终处于技术领先地位。
通过自身产品在技术的不断创新和对信息技术开展方向的敏锐洞察，始终领导数据库产品的开展。
不管在哪个时期，都具有技术优势。
为了实现事务处理的高性能，Oracle 9i的多线程的、多效劳器的体系结构能够协调处理上万条并发用户请求。
单个请求均被放入队列，并由最少量的效劳器进程处理。
Oracle 9i的可伸缩的，可靠的体系结构推出了无法匹敌的任务关键系统所需的可伸缩性、可用性以及高性能。
Oracle 9i和Oracle 9i Real Application Server能充分利用所有的硬件系统资源，从单处理器，并行多处理器，集群系统(cluster)到大规模并行处理器(MPP)系统。
Oracle支持行级封锁技术来解决写/写冲突，并支持数据多版本来解决读/写冲突。
行级封锁因为占用的系统资源最少，能最大限度地提高系统的吞吐量。
数据多版本(读一致性快照)防止了读锁，使系统对锁的管理因锁种类的减少而大大简化。
Oracle杜绝锁升级，防止死锁的发生。
并且，Oracle有专门的后台进程监控和解决死锁，一旦出现死锁，自动解除死锁。
Oracle9i 的一个功能，是能够将业务数据和索引进行分区。
经分区的数据和索引具有以下优点：缩短对长时间运行的查询的响应时间；分区减少了磁盘I/O 操作减少对并发查询的响应时间；I/O 操作在每个分区上同时进行索引维护更加轻松，因为可进行分区级创立和重构操作可以重建分区上的索引，而不影响在其他分区上的查询可以更改每个本地索引的存储参数，而与其他分区无关对于民政部民政公用政务平台系统，需要存贮大量的资料、档案和各地的业务数据，同时，还需要同协作机构进行数据交换，而Oracle9i支持最大为512Peta bytes的数据库(1 Petabytes = 1000TB =1000,000GB)，较好地支持大型存储设备的管理操作，整个管理过程对用户是完全透明的。
Oracle9i应用系统优化应用系统方案制定准确，对应用系统运行环境分析合理、正确，在数据库服务器性能、储备空间、网络带宽等方面的配置能够达到系统运行要求.2、优化目标● 响应时刻与吞吐量平稳● 临界资源2.1 响应时刻与吞吐量平稳依照顾用类型的不同，性能优化的目标不同：在线事务处理系统OLTP）把吞吐量定义为性能指标；决策支持系统（DSS）把响应时刻定义为性能指标。
响应时刻响应时刻=服务时刻+等待时刻系统吞吐量系统吞吐量指在给定的时刻内所完成的工作量。
有以下两种技术：● 以相同的资源来完成更多的工作（减少服务时刻）；● 通过减少整个响应时刻来更快完成工作。
等待时刻当竞争增强的时候，某个任务的服务时刻也许保持不变，但它的等待时刻将增长。
我们开发的系统一样为OLTP和DSS的复合系统，侧重于OLTP,在硬件承诺的情形下最好能够将运行数据库、分析数据库分离。
2.2 临界资源诸如CPU、内存、I/O容量、网络带宽等资源，差不多上减少时刻的关键因素。
性能好坏取决于以下因素：● 可用资源的数量● 需要该资源的客户方的数目● 客户方等待资源所消耗的时刻● 客户保持资源的时刻长短随着要求单元的增加，服务时刻也增加。
为了处理这种情形，用户能够选择：● 通过限制要求的速率，从而爱护可同意响应时刻● 还可通过增加资源数目，如CPU和硬盘（增加资源的前提是应用系统设计良好，同时差不多做了充分的优化）3、优化时期从实际做的项目过程来看，除了系统安装优化外，系统优化往往差不多上在系统实施、运行时才考虑，事实上到这时期做系统优化的局限性比较大，因为系统架构设计都成型、固化，大幅度调整设计的代价专门昂贵，一样只能在局部领域做优化，只能通过重新分配内存或优化I/O来或多或少地提高性能，实际上优化应该贯穿系统设计、开发、安装、测试、运行整个过程。
3.1 设计时期为了达到最佳的成效，优化工作应当从设计时期进行，而不是在系统实施后进行。
Oracle 9i SQL优化快速手册——蒋红腥1概述《Oracle 9i数据库SQL优化快速手册》并不全面讲解Oracle9i SQL优化技术，也不具体分析各种优化方法的原理，旨在通过简单明显有效的调整方法，帮助程序员或维护人员在开发、维护过程中，针对执行速度极慢的Oracle数据库开发操作，快速取得更快、更好的执行效果。
此时优化的结果并不是最优的，但我们的目标是能快速满足我们操作数据库的需求即可。
若想取得更多的SQL优化效果，请参考附件《Oracle 9i SQL语句调整》。
若要取得尽可能多的优化效果，则需要参考有关书籍，从Oracle服务器级优化、网络级优化、内存级优化以及优化器-SQL 优化等等入手进行综合优化。
下文中使用的方法，并不是完全规范的，合理的，但为求简单、方便快速的满足一般应用，一些设置采用了推荐设置、默认设置，直接按照说明操作，即可获得大多数的明显的优化结果。
2简易优化过程根据执行计划，通过添加索引，使用CBO提示等优化方式，调整执行计划，达到满足需求。
前提条件：已知那些执行语句慢。
2.1第一步：准备优化调试环境⏹使用windows自带的command，打开2个窗口登陆到数据库服务器。
⏹设置查看“执行计划环境command窗口”。
在其中一个以$sqlplus “/as sysdba”方式登陆的SQL提示符下执行如下命令：SQL>set autotrace traceonly explainSQL>⏹设置“调试SQL语句窗口”。
在另一个command窗口使用执行该语句的用户登陆；执行该语句，记录执行时间。
如：$sqlplus perf/perf@omcdbSQL> set timing on ---设置SQL执行时间记录返回--- 照着执行SQL>alter session set optimizer_index_cost_adj=30; --- 照着执行SQL>alter session set optimizer_index_caching=40; --- 照着执行SQL>执行返回的时间格式说明，如：已用时间: 00: 00: 00.04 小时:分:秒.微秒2.2第二步：查看SQL语句执行计划在“执行计划环境command窗口”中，执行被调试的SQL语句，此时会输出该语句的执行计划，如下所示：（在from table的table表名前，添加该表的所属用户名）如上图所示，兰色部分为我们输入的需要执行计划提示的命令和我们需要调试的SQL语句，红色部分为该语句在数据库中将使用的执行计划。
ORACLE 9i的数据库优化钱咏春任良豪新疆时代石油工程公司计算机中心摘要：本文根据在数据库优化管理工作中的自身体会，阐述了如何利用STATSPACK分析工具优化系统全局区、优化Redo log、优化数据库磁盘I/O等影响数据库性能的因素，实现对Oracle9i性能方面的优化。
关键词：STATSPACK；系统全局区（SGA）；Redo log；磁盘I/O；表空间1.概述数据库技术是信息时代的一个重要技术，它是现代计算机信息管理系统和计算机应用系统的基础和核心。
Oracle公司在这一领域一直占据着领先地位，于2001年发布了Oracle9i。
时代公司运行Oracle 9i已经一年了，最初建立数据库时很多参数是根据以往运行8i 经验以及初步了解9i的新特性后建立的。
随着对9i理解的逐步加深，利用STATSPACK分析工具对现有数据库性能进行分析后，发现数据库配置存在一些不合理的地方，从而造成明显的等待事件和磁盘I/O竞争等方面的问题。
现在借助这篇文章，谈一谈在优化方面的一些体会，供同行参考。
2.硬件环境数据库服务器配置：DELL Power Edge 4600配置：内存8G，CPU xeon(至强)2.0G×2 。
操作系统：Redhat Advance Server 2.1磁盘：1 raid 12 raid 5Oracle：9.2.03.STATSPACK报告Oracle提供的statspack是一个很强大的工具，通过Statspack，可以收集系统信息，诊断数据库故障，也方便第三方技术支持进行远程阅读和建议。
Statspack需要安装配置，安装脚本位于$ORACLE_HOME/rdms/admin目录下，有SYSDBA权限的用户执行spcreate.sql 进行安装，收集信息执行：sql>execute statspack.snap;产生报告执行：sql>$ORACLE_HOME /rdms/admin/spreport.sql ,详细过程请参考《Statspack安装配置使用说明》。
我们收集的主要信息如下：Cache Sizes (end) ――（statspack 1）~~~~~~~~~~~~~~~~~Buffer Cache: 480M Std Block Size: 8KShared Pool Size: 800M Log Buffer: 512KTop 5 Timed Events ――（statspack 2）~~~~~~~~~~~~~~~~~~ % TotalEvent Waits Time (s) Ela Time------------------------------------------ ---- ----------- --------CPU time 59 88.52control file parallel write 547 4 6.60log file sync 325 3 4.08SQL*Net more data to client 8,062 0 .68latch free 17 0 .12Wait Events for DB: ――（statspack 3）AvgTotal Wait wait WaitsEvent Waits Timeouts Time (s) (ms) /txn------------------------- ------------ ---------- ---------- ------ --------log file sync 325 2 3 8 1.4control file parallel write 547 0 4 4 2.1log buffer space 9 0 0 3 0.0control file sequential read 445 0 0 0 1.4log file parallel write 686 686 0 0 2.2direct path read 1,007 0 0 0 3.3db file sequential read 40 0 0 0 0.1db file parallel write 164 82 0 0 0.5direct path write 16 0 0 0 0.1-------------------------------------------------------------PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written ――（statspack 4）--------------- ---------------- -------------------------71.9 464 181Shared Pool Advisory for DB: ORACLE ――（statspack 5）EstdShared Pool SP Estd Estd Estd Lib LC TimeSize for Size Lib Cache Lib Cache Cache Time Saved Estd Lib CacheEstim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem ObjHits----------- ----- ---------- ------------ ------------ ------- ---------------480 .6 479 89,111 228,900 1.0 23,774,467640 .8 637 127,705 228,947 1.0 23,804,570720 .9 716 152,935 228,963 1.0 23,830,377800 1.0 795 176,039 228,978 1.0 23,854,709960 1.2 952 207,762 229,007 1.0 23,899,483因为回滚段采用的是自动撤销管理方式，不需要人为干预，所以不需优化回滚段。
4.优化实施4.1.系统全局区（SGA）（1）shared_pool_sizestatspack 5 显示原来设置的800M（参看statspack 1）过大，可以适当降低。
调整shared_pool_size 600M。
（2）Buffer Cache查询V$DB_CACHE_ADVICE视图，发现Db_cache_size配置到320M即满足系统运行要求，调整shared_pool_size 400M，原值参看statspack 1。
（3）pga_aggregate_targetstatspack 4 显示PGA Cache Hit太低，同时提出waring，根据系统建议(PGA Memory Advisory for DB)修改为150M。
（4）java_pool_size考虑到系统由于没有java应用，java_pool_size 只有oracle系统使用,可以进行缩小。
java_pool_size 20M（5）shared_pool_reserved_size原系统设置40M，查询V$SHARED_POOL_RESERVED视图,发现Free_memory > 70％。
修改shared_pool_reserved_size 10M。
（6）Db_cache_keep_size原系统没有设置Db_cache_keep_size。
在SGA中设置keep pool，将一直保留常用到的数据表，避免被LRU算法挤出内存。
通过analyze命令得到常用小表的空间小于 32M，故设置Db_cache_keep_size 32M.4.2.Redo logSTATSPACK 报告中（statspack 3）等待事件存在log buffer space，说明Log_buffer太小，调整：Log_buffer 2097152。
4.3.数据库磁盘I/Ostatspack 2显示control file parallel write 等待时间较长，查看系统控制文件分布：/u01上有两份，/u02上有一份。
由于我公司磁盘采用了Raid技术，从操作系统级别可以保证数据的正确性，根据I/O优化原则，将控制文件调整为两个即可：/u01和/u02（不同磁盘）上各一份。
同时调整DBWN I/O: DB_WRITER_PROCESSES = 2（需要操作系统支持），也提高了系统响应速度。
4.4.其它优化策略为了提高访问速度，还采取了以下措施：（1）使用不同块大小的表空间时代公司档案管理系统有5、6个表记录超过13万条，最大的表 >16M，数据访问量大，经常做全表扫描。
Oracle9i中不同的表空间可以使用不同的块大小存储数据，可以提高访问速度。
利用此新特性我们创建了16k表空间（操作系统不支持32k），专门存放档案管理系统中经常访问的大表。
首先通过analyze命令得到表所占用的空家大小，设置 db_16k_cache_size 64 M ，然后创建16K表空间就可以使用了。
Sql>create tablespace db16k datafile ‘/u02/9i/db16k’size 200M extent management local segment space management auto blocksize 16K ;sql>alter table emp move table info200016k;移动数据表后，检查索引，如果有误修改错误索引表空间（info2000 info200016k），重建索引。
（2）临时表空间用户info2000的数据表非常多，数据量极大，磁盘I/O相应也高，为提高多个排序操作的并发能力、减少它们的开销，考虑到空间管理更加简单和有效，另外创建了一个本地管理的临时表空间共其使用。
Sql>create temporary tablespace infotmp tempfile '/u02/9i/oradata/ infotmp.dbf' size 200M reuse extent management local ;Sql>alter user info2000 temporary tablespace infotmp;5.优化效果采取以上措施后，重做STATSPACK进行分析，结果如下：Instance Efficiency Percentages (Target 100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Buffer Nowait %: 100.00 Redo NoWait %: 100.00Buffer Hit %: 100.00 In-memory Sort %: 99.90Library Hit %: 99.52 Soft Parse %: 99.17Execute to Parse %: -2.05 Latch Hit %: 100.00Parse CPU to Parse Elapsd %: 93.30 % Non-Parse CPU: 93.69Top 5 Timed Events~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time --------------------------------------- ------------ ----------- -------- CPU time 126 98.24 control file parallel write 513 1 .96 SQL*Net more data to client 12,228 1 .43 log file sync 216 0 .26 SQL*Net break/reset to client 104 0 .04 其它信息略。
