优化Oracle数据库能.doc

优化Oracle数据库性能优化策略 为了保证Oracle数据库运行在最佳的性能状态下，在信息系统开发之前就应该考虑数据库的优化策略。优化策略一般包括服务器操作系统参数调整、数据库参数调整、网络性能调整、应用程序SQL语句分析及设计等几个方面，其中应用程序的分析与设计是在信息系统开发 分析评价Oracle数据库性能主要有数据库吞吐量、数据库用户响应时间两项指标。数据库用户响应时间又可以分为系统服务时间和用户等待时间两项，即： 数据库用户响应时间=系统服务时间用户等待时间 因此，获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。 数据库性能优化包括如下几个部分： 1. 调整数据结构的设计 这一部分在开发信息系统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。 2. 调整应用程序结构设计 这一部分也是在开发信息系统之前完成的。程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源 3. 调整数据库SQL语句 应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。 Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁管理器（Row-Level Manager）来调整优化SQL语句。 4. 调整服务器内存分配 内存分配是在信息系统运行过程中优化配置的。数据库管理员根据数据库的运行状况不仅可以调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小，而且还可以调整程序全局区（PGA区）的大小。 5. 调整硬盘I/O 这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O 负载均衡。 6. 调整操作系统参数 例如：运行在Unix操作系统上的 Oracle数据库，可以调整 Unix数据缓冲区的大小、每个进程所能使用的内存大小等参数。 实际上，上述数据库优化措施之间是相互联系的。Oracle 数据库性能恶化的表现基本上都是用户响应时间比较长，需要用户长时间的等待。而性能恶化的原因却是多种多样的，有时是多个因素共同造成了性能恶化的结果，这就需要数据库管理员有比较全面的计算机知识，能够敏感地察觉到影响数据库性能的主要原因所在。另外，良好的数据库管理工具对于优化 性能优化工具 Oracle数据库常用的数据库性能优化工具有： 1. Oracle数据库在线数据字典 Oracle在线数据字典能够反映出Oracle的动态运行情况，对于调整数据库性能是很有帮助的。 2. 操作系统工具 例如使用Unix操作系统的Vmstat、 Iostat等命令可以查看到系统级内存和硬盘I/O的使用情况，这些工具能够帮助管理员弄清楚系统瓶颈出现在什么地方。 3. SQL语言跟踪工具（SQL Trace Facility） SQL语言跟踪工具可以记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，并使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统 4. Oracle Enterprise Manager（OEM） 这是一个图形的用户管理界面，用户可以使用它方便地进行数据库管理而不必记住复杂的Oracle数据库管理的命令。 5. Explain Plan?SQL语言优化命令 使用这个命令可以帮助程序员写出高效的 系统性能评估 信息系统的类型不同，需要关注的数据库参数也是不同的。数据库管理员需要根据自 1. 在线事务处理信息系统（OLTP） 这种类型的信息系统一般需要有大量的Insert、 Update操作，典型的系统包括民航机票发售系统、银行储蓄系统等。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的Oracle数据库需主要考虑以下参数： 数据库回滚段是否足够？ 是否需要建立Oracle数据库索引、聚集、散列？ 系统全局区（SGA）大小是否足够？ SQL语句是否高效？ 2. 数据仓库系统（Data Warehousing） 这种信息系统的主要任务是从Oracle的海量数据中进行查询，以得到数据之间的某些规律。数据库管理员需要为这种类型的Oracle数据 是否采用B?索引或者Bitmap索引？ 是否采用并行SQL查询以提高查询效率？ 是否采用PL/SQL函数编写存储过程？ 有必要的话，需要建立并行数据库以提高数据库的查询效率。参数的调整 1. CPU参数 CPU是服务器的一项重要资源，服务器良好的工作状态表现为在工作高峰时CPU的使用率高于90。如果空闲时间CPU使用率就在90以上，说明服务器缺乏CPU资源；如果工作高峰时CPU使用率仍然很低，则说明服务器CPU 资源还比较充足。 使用操作命令可以看到CPU的使用情况，一般Unix操作系统的服务器，可以使用 sar-u命令查看CPU的使用率；NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使 数据库管理员可以通过查看vsysstat数据字典中的 “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数据库无法 操作系统优化 1）概念 操作系统优化时应该考虑的因素有：内存的使用；Cpu的使用；IO级别；网络流量。各个因素互相影响，正确的优化次序是内存、IO、CPU。 操作系统使用了虚拟内存的概念，虚拟内存使每个应用感觉自己是使用内存的唯一的应用，每个应用都看到地址从0开始的单独的一块内存，虚拟内存被分成4K或8K的page，操作系统通过MMU（memory management unit）将这些page与物理内存映射起来，这个映射关系通过page table控制。 Raw device是没有文件结构或目录结构的磁盘或磁盘分区，由于它忽略了操作系统缓存，在某些情况下可以显著提升性能，但是在windows NT下，由于操作系统IO操作本身不使用文件系统缓存，所以raw device不能显示性能上的优点。 2）Guideline CPU的最高使用率：90； OS/USER进程数之比：40/60； 各个CPU的负载应该大致均衡。3）服务器安全性检查 A、检查UNIX系统用户口令： 检查：/etc/passwd、/etc/shadow，UNIX密码采用了shadow机制，安全性能高 建议：参考UNIX命令passwd，修改/etc/default/passwd文件的某些设置如MAXWEEKS、MINWEEKS、PASSLENGTH使口令修改更加合理化。 建议：定期更改UNIX系统的如下用户口令：root、oraprod、applprod、appprodB、检查 Remote Login 启动了rlogin，服务器数据库a、数据库b、数据库c，终端console1、console2、console3及T3形成相互非常信任的关系，用户只要拥有一个服务器的超级权限就可以rlogin到.rhosts指明的任一主机而无需要口令。 建议：非常不安全，参考UNIX命令rlogin和/目录下的文件.rhosts。在正式环境服务器和测试环境服务器之间不要建立这种远程信任的机制。 C、检查FTP服务 检查可以FTP到服务器的用户（/etc/ftpusers），注释了root用户，就是说用户可以用root权限FTP到服务器上。权限太大。 建议：把这种权力取消，将/etc/ftpusers中root的注释符号（#）去掉，在列表中添加oraprod、applprod、appprod等用户使之不能FTP服务器。必要时（如上传PATCH时）再打开applprod的FTP权限。 D、建议：UNIX系统管理员定期检查/var/adm下的messages、sulog；/etc/syslog.conf 等信息。检查是否有非法用户登陆UNIX。 建议：与UNIX工程师探讨更好的监控方式4）数据库与应用产品安全性检查 A、建议：修改oracle用户根目录下的.profile文件，修改该文件的权限为500。即使得用户登陆时并不执行和数据库或应用相关的环境变量，增加安全性。 B、检查数据库DBA权限的用户密码和应用系统用户密码：SYSTEM、APPS密码都已经改变，SYS密码还是初始安装密码Change_on_install。建议：立即修改SYS用户密码，定期更改APPS、SYSTEM、SYS密码。 C、定期检查并清除$ORACLE_HOME/admin/bdump目录下的alert_PROD.log文件和后台进程trace文件。定期清除$ORACLE_HOME/admin/udump目录下的trc文件。 D、建议：给应用产品登陆的用户设置口令过期限制，如口令访问次数限制或时间（天数）限制。 建议：不要给使用应用产品的用户共享用户名和口令，每个用户分配一个应用产品用户名。 建议：对有应用系统管理员权限的用户登记，不适合有系统管理员权限的用户要把权限回收，统一管理。 E、定期检查并清除与Apache Server有关的log文件，目录为: /u01/prodora/iAS/Apache/Apache/logs/acccess_log、error_log /u01/prodora/iAS/Apache/Jserv/logs/jserv.log、mod_jserv.log F、定期检查清除listener、tnsname的log文件，文件存放在: /u01/prodora/8.0.6/network/admin/apps_prod.log、 /u01/proddb/8.1.7/network/admin/prod.log /u01/proddb/8.1.7/network/log/listener.log、sqlnet.log G、数据库控制文件做多个镜像，放在多个磁盘位置，提高安全性。5）网络安全性检查 检查$ORACLE_HOME/dbs/initPROD.ora文件：#remote_login_passwordfile=EXCLUSIVE 设置为REMOTE_LOGIN_PASSWORDFILE=NONE，不允许远程客户用INTERNAL方式登陆。 2、资源管理器（Resource Manager） 通过资源管理器可以管理混合工作负载，控制系统性能。数据库资源管理器包括： Resource plans：包括 resource plan directives, 它指定了被分配到各个 resource consumer group的资源。 Resource consumer groups：定义了具有类似资源使用需求的一组用户。 Resource plan directives：包括下列内容:为consumer groups 或 subplans 指定resource plans；在各个 consumer groups 或资源计划的subplans 分配资源。内存参数 内存参数的调整主要是指Oracle数据库的系统全局区（SGA）的调整。SGA主要由3部分构成：共享池、数据缓冲区、日志缓冲区。 一、SGA1、Shared pool tunning Shared pool的优化应该放在优先考虑，因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高，由于dictionary数据一般比library cache中的数据在内存中保存的时间长，所以关键是library cache的优化。 Gets：（parse）在namespace中查找对象的次数； Pins：（execution）在namespace中读取或执行对象的次数； Reloads：(reparse)在执行阶段library cache misses的次数，导致sql需要重新解析。1）检查v$librarycache中sql area的gethitratio是否超过90，如果未超过90，应该检查应用代码，提高应用代码的效率。 Select gethitratio from v$librarycache where namespace=sql area;2) v$librarycache中reloads/pins的比率应该小于1，如果大于1，应该增加参数shared_pool_size的值。 Select sum(pins) “executions”,sum(reloads) “cache misses”,sum(reloads)/sum(pins) from v$librarycache; reloads/pins1%有两种可能，一种是library cache空间不足，一种是sql中引用的对象不合法。 3）shared pool reserved size一般是shared pool size的10，不能超过50。V$shared_pool_reserved中的request misses0或没有持续增长，或者free_memory大于shared pool reserved size的50%，表明shared pool reserved size过大，可以压缩。4）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程。 5）从9i开始，可以将execution plan与sql语句一起保存在library cache中，方便进行性能诊断。从v$sql_plan中可以看到execution plans。6）保留大的对象在shared pool中。大的对象是造成内存碎片的主要原因，为了腾出空间许多小对象需要移出内存，从而影响了用户的性能。因此需要将一些常用的大的对象保留在shared pool中，下列对象需要保留在shared pool中： a. 经常使用的存储过程； b. 经常操作的表上的已编译的触发器 c. Sequence，因为Sequence移出shared pool后可能产生号码丢失。 查找没有保存在library cache中的大对象： Select * from v$db_object_cache where sharable_mem10000 and type in (PACKAGE,PROCEDURE,FUNCTION,PACKAGE BODY) and kept=NO; 将这些对象保存在library cache中： Execute dbms_shared_pool.keep(package_name); 对应脚本：dbmspool.sql 7)查找是否存在过大的匿名pl/sql代码块。两种解决方案： A转换成小的匿名块调用存储过程 B将其保留在shared pool中 查找是否存在过大的匿名pl/sql块：Select sql_text from v$sqlarea where command_type=47 and length(sql_text)500; 8）Dictionary cache的 优化 避免出现Dictionary cache的misses，或者misses的数量保持稳定,只能通过调整shared_pool_size来间接调整dictionary cache的大小。 Percent misses应该很低：大部分应该低于2，合计应该低于15。 若超过15，增加shared_pool_size的值。 Select sum(getmisses)/sum(gets) from v$rowcache; 2、Buffer Cache 1）granule大小的设置，db_cache_size以字节为单位定义了default buffer pool的大小。 如果SGA128M，granule=4M,否则granule16M，即需要调整sga的时候以granule为单位增加大小，并且sga的大小应该是granule的整数倍。2) 根据v$db_cache_advice调整buffer cache的大小 SELECT size_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_reads FROM v$db_cache_advice WHERE NAME=DEFAULT AND advice_status=ONAND block_size=(SELECT Value FROM v$parameter WHERE NAME=db_block_size); estd_physical_read_factor90%，如果低于90，可以用下列方案解决： 增加buffer cache的值； 使用多个buffer pool； Cache table； 为 sorting and parallel reads 建独立的buffer cache；SELECT NAME,value FROM v$sysstat WHERE NAME IN (session logical reads,physical reads,physical reads direct,physical reads direct(lob); Cache hit ratio=1-(physical reads-physical reads direct-physical reads direct (lob)/session logical reads;Select 1-(phy.value-dir.value-lob.value)/log.value from v$sysstat log, v$sysstat phy, v$sysstat dir, v$sysstat LOB where =session logical reads and =physical reads and =physical reads direct and =physical reads direct (lob); 影响cache hit ratio的因素：全表扫描；应用设计；大表的随机访问；cache hits的不均衡分布 4）表空间使用自动空间管理，消除了自由空间列表的需求，可以减少数据库的竞争 3、其他SGA对象 1）redo log buffer：对应的参数是log_buffer，缺省值与 OS相关，一般是500K。检查v$session_wait中是否存在log buffer wait,v$sysstat中是否存在redo buffer allocation retries A、检查是否存在log buffer wait： Select * from v$session_wait where event=log buffer wait ; 如果出现等待，一是可以增加log buffer的大小，也可以通过将log 文件移到访问速度更快的磁盘来解决。 B、Select name,value from v$sysstat where name in (redo buffer allocation retries,redo entries) Redo buffer allocation retries接近0，小于redo entries 的1，如果一直在增长，表明进程已经不得不等待redo buffer的空间。如果Redo buffer allocation retries过大，增加log_buffer的值。 C、检查日志文件上是否存在磁盘IO竞争现象 Select event,total_waits,time_waited,average_wait from v$system_event where event like log file switch completion%; 如果存在竞争，可以考虑将log文件转移到独立的、更快的存储设备上或增大log文件。 D、检查点的设置是否合理：检查alert.log文件中，是否存在checkpoint not complete； Select event,total_waits,time_waited,average_wait from v$system_event where event like log file switch (check%; 如果存在等待，调整log_checkpoint_interval、log_checkpoint_timeout的设置。 E、检查log archiver的工作 Select event,total_waits,time_waited,average_wait from v$system_event where event like log file switch (arch%; 如果存在等待，检查保存归档日志的存储设备是否已满，增加日志文件组，调整log_archiver_max_processes。 F、DB_block_checksum=true，因此增加了性能负担。（为了保证数据的一致性，oracle的写数据的时候加一个checksum在block上，在读数据的时候对checksum进行验证） 2）java pool： 对于大的应用，java_pool_size应=50M，对于一般的java存储过程，缺省的20M已经够用了。 3）检查是否需要调整DBWn：Select total_waits from v$system_event where event=free buffer waits;1二、数据库配置和IO问题 降低磁盘的IO 分散磁盘的IO 表空间使用本地管理 1、将文件分散到不同的设备上 1）将数据文件与日志文件分开 2）减少与服务器无关的磁盘IO 3）评估裸设备的使用 4）分割表数据 2、表空间的使用1) 系统表空间保留给数据字典对象2) 创建本地管理表空间以避免空间管理问题3) 将表和索引分散到独立的表空间中4) 使用独立的回滚表空间5) 将大的数据库对象保存在各自独立的表空间中6) 创建一个或多个独立的临时表空间 下列数据库对象应该有单独的表空间： 数据字典、回滚段、索引、临时段、表、大对象 3、检查IO统计数据： Select phyrds,phywrts, from v$datafile d,v$filestat f where f.file#=d.file# order by ; 检查最有可能引起磁盘IO瓶颈的文件。 4、分割文件。 但手工操作工作量很大。 可以通过RAID和手工进行： Alter table table_name allocate extent (datafile fiile_name size 10M); 5、优化全表扫描操作 1）检查有多少全表发生： Select name,value from v$sysstat where name like %table scan%; table scans (short tables)/ table scans (long tables)与全表扫描相关，如果table scans (long tables)的值很高，说明大部分的table access 没有经过索引查找，应该检查应用或建立索引，要确保有效的索引在正确的位置上。 合理的DB_FILE_MULTIBLOCK_READ_COUNT能减少table scan需要调用的IO次数，提高性能（与OS相关）。 2）查看full table scan操作： Select sid,serial#,opname,target，to_char(start_time,HH24:MI:SS) “start”,(sofar/totalwork)*100 “percent_complete” from v$session_longops; 通过v$session_longops里的sql_hash_value与v$sqltext关联，可以查询导致full table scan的sql。 6、Checkpoint： Checkpoint进行的操作：DBWn进行IO操作；CKPT更新数据文件头和控制文件。 经常进行Checkpoint的结果：减少恢复所需的时间；降低了系统运行时的性能。 LGWR以循环的方式将日志写到各个日志组，当一个日志组满时，oracle server必须进行一个Checkpoint，这意味着：DBWn将对应log覆盖的所有或部分脏数据块写进数据文件；CKPT更新数据文件头和控制文件。如果DBWn没有完成操作而LGWR需要同一个文件，LGWR只能等待。 在OLTP环境下，如果SGA很大并且checkpoint的次数不多，在Checkpoint的过程中容易出现磁盘竞争的状况，在这种情况下，经常进行Checkpoint可以减少每次Checkpoint涉及到的脏数据块的数目。 调节Checkpoint次数的办法： 增大日志文件；增加日志组以增加覆盖的时间间隔。 7、日志文件 建立大小合适的日志文件以最小化竞争； 提供足够的日志文件组以消除等待现象； 将日志文件存放在独立的、能快速访问的存储设备上（日志文件可以创建在裸设备上）。日志文件以组的方式组织管理，每个组里的日志文件的内容完全相同。 8、归档日志文件 如果选择归档模式，必须要有两个或两个以后的日志组，当从一个组切换到另一个组时，会引起两种操作：DBWn进行Checkpoint；一个日志文件进行归档。 归档有时候会报错： ARC0：Beginning to archive log# 4 seq# 2772 Current log# 3 seq# 2773 ARC0: Failed to archive log# 4 seq# 2772 ARCH: Completed to archiving log#4 seq# 2772 建议init参数修改如下： log_archive_max_processes=2 #log_archive_dest = /u05/prodarch log_archive_dest_1 = location=/u05/prodarch MANDATORY log_archive_dest_state_1 = enable log_archive_dest_2 = location=/u05/prodarch2 OPTIONAL reopen=10 （或其它目录） log_archive_dest_state_2 = enable log_archive_min_succeed_dest=1 log_archive_dest_state_3 = DEFER log_archive_dest_state_4 = DEFER log_archive_dest_state_5 = DEFER三、优化排序操作 1、概念 服务器首先在sort_area_size指定大小的内存区域里排序，如果所需的空间超过sort_area_size，排序会在临时表空间里进行。在专用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空间在UGA中。如果没有建立large pool，UGA处于shared pool中，如果建立了large pool，UGA就处于large pool中，而PGA不在sga中，它是与每个进程对应单独存在的。 PGA：program global area,为单个进程（服务器进程或后台进程）保存数据和控制信息的内存区域。PGA与进程一一对应，且只能被起对应的进程读写，PGA在用户登录数据库创建会话的时候建立。 有关排序空间自动管理的两个参数： Pga_aggregate_target: 10M-4000G，等于分配给oracle instance的所有内存减去SGA后的大小。 Workarea_size_policy: auto/manual，只有Pga_aggregate_target已定义时才能设置为auto。 这两个参数会取代所有的*_area_size参数。 措施： 尽可能避免排序；尽可能在内存中排序；分配合适的临时空间以减少空间分配调用。 2、需要进行排序的操作： A、创建索引； B、涉及到索引维护的并行插入 C、order by或者group by（尽可能对索引字段排序） D、Distinct E、union/intersect/minus F、sort-merge join G、analyze命令（仅可能使用estamate而不是compute） 3、诊断和措施 Select * from v$sysstat where name like %sort%; Sort(disk):要求Io去临时表空间的排序数目 Sort(memory)：完全在memory中完成的排序数目 Sort(rows)：被排序的行数合计 Sort（disk）/ Sort（memory）5; C、library cache不够大。1五、Rollback(undo) Segment 优化 1、概念 Transaction以轮循的方式使用rollback segment里的extent，当前所在的extent满时就移动到下一个extent。可能有多个transaction同时向同一个extent写数据，但一个rollback segment block中只能保存一个transaction的数据。 Oracle 在每个Rollback segment header中保存了一个transaction table，包括了每个rollback segment中包含的事务信息，rollback segment header的活动控制了向rollbak segment写入被修改的数据。rollback segment header是经常被修改的数据库块，因此它应该被长时间留在buffer cache中，为了避免在transaction table产生竞争导致性能下降，应有多个rollback segment或应尽量使用oracle server 自动管理的rollback segment。 2、诊断rollback segment header的竞争 如果rollback segment 由手工管理，下列措施诊断rollback segment header的竞争 SELECT class,count FROM v$waitstat WHERE class LIKE %undo% ; SELECT Sum(Value) sum FROM v$sysstat WHERE NAME IN (db block gets,consistent gets); 任何类型的等待次数（count）与总请求数(sum)的比率，不能超过1。 或 select sum(waits)*100/sum(gets) Ratio, sum(waits) Waits, sum(gets) Gets from v$rollstat; waits的汇总数与gets的汇总数的比率应低于1，如果超过1，应创建更多的rollback segment。 下列字段数值如果大于0，则表明在rollback segment header上存在竞争： A、v$rollstat 中的waits B、v$waitstat中的undo header行 C、v$system_event中的undo segment tx slot事件 3、消耗更少的rollback segment 1）如果是删除表里所有的数据，尽可能使用trauncate而不是delete。 2）在应用中允许用户有规律的提交，尽可能不用长事务。 3） Import Set COMMIT = Y Size the set of rows with BUFFER Export: Set CONSISTENT=N SQL*Loader: Set the COMMIT intervals with ROWS 4、小回滚段可能出现的问题 A、事务由于缺少回滚空间失败 B、由于下列原因导致的“Snapshot too old”问题： Block里的事务列表被刷新，block里的SCN比列表Interested Transaction List（ITL）里起始事务的SCN更新； Rollback segment header里的Transaction slot被重用； 回滚数据已经被重写； 5、9i的自动回滚管理 Undo_managment指定了回滚空间的管理方式：Auto：自动管理；Manual：手工管理回滚段。 Undo_retention指定了回滚数据的保留期限； Undo_tablespace指定了被使用的回滚表空间； Oracle自动管理的表空间