1、Oracle 调优简要手册更多oracle资料下载，请收藏http:/2010-12-2一、SGA1、Shared pool tunningShared pool的优化应该放在优先考虑，因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高，由于dictionary数据一般比library cache中的数据在内存中保存的时间长，所以关键是library cache的优化。Gets：（parse）在namespace中查找对象的次数；Pins：（execution）在namespace中读取或执行对象的次数；Reloads：(reparse)在
2、执行阶段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 mi
3、sses”,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/
4、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移出shar
5、ed 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.sql7)查找是否存在过大的匿名pl/sql代码块。两种解决方案：A转换成小的匿名块调用存储过程B将其保留在
6、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，合计应该低于15Select sum(getmisses)/sum(gets) from v$row
7、cache;若超过15，增加shared_pool_size的值。2、Buffer Cache1）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_facto
8、r,estd_physical_reads FROM v$db_cache_advice WHERE NAME=DEFAULT AND advice_status=ON AND 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
9、 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
10、log, v$sysstat phy, v$sysstat dir, v$sysstat LOB where log.name=session logical reads and phy.name=physical reads and dir.name=physical reads direct and lob.name=physical reads direct (lob);影响cache hit ratio的因素：全表扫描；应用设计；大表的随机访问；cache hits的不均衡分布4）表空间使用自动空间管理，消除了自由空间列表的需求，可以减少数据库的竞争3、其他SGA对象1）redo lo
11、g buffer对应的参数是log_buffer，缺省值与 OS相关，一般是500K。检查v$session_wait中是否存在log buffer wait,v$sysstat中是否存在redo buffer allocation retriesA、检查是否存在log buffer wait：Select * from v$session_wait where event=log buffer wait ;如果出现等待，一是可以增加log buffer的大小，也可以通过将log 文件移到访问速度更快的磁盘来解决。B、Select name,value from v$sysstat where
12、 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 lo
13、g 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
14、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对于大的应用
15、，java_pool_size应=50M，对于一般的java存储过程，缺省的20M已经够用了。3）检查是否需要调整DBWnSelect total_waits from v$system_event where event=free buffer waits;二、数据库配置和IO问题降低磁盘的IO分散磁盘的IO表空间使用本地管理1、将文件分散到不同的设备上1）将数据文件与日志文件分开2）减少与服务器无关的磁盘IO3）评估裸设备的使用4）分割表数据2、表空间的使用系统表空间保留给数据字典对象创建本地管理表空间以避免空间管理问题将表和索引分散到独立的表空间中使用独立的回滚表空间将大的数据库对象保存
16、在各自独立的表空间中创建一个或多个独立的临时表空间下列数据库对象应该有单独的表空间：数据字典、回滚段、索引、临时段、表、大对象3、检查IO统计数据Select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name;检查最有可能引起磁盘IO瓶颈的文件。4、分割文件可以通过RAID和手工进行Alter table table_name allocate extent (datafile fiile_name size 10M);但手工操作工作量很大。5、优化全表扫描操作1
17、）检查有多少全表发生：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 ta
18、ble 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、CheckpointCheckpoint进行的操作：DBWn进行IO操作；CKPT更新数据文件头和控制文件。经常进行Checkpoint的结果：减少恢复所
19、需的时间；降低了系统运行时的性能。LGWR以循环的方式将日志写到各个日志组，当一个日志组满时，oracle server必须进行一个Checkpoint，这意味着：DBWn将对应log覆盖的所有或部分脏数据块写进数据文件；CKPT更新数据文件头和控制文件。如果DBWn没有完成操作而LGWR需要同一个文件，LGWR只能等待。在OLTP环境下，如果SGA很大并且checkpoint的次数不多，在Checkpoint的过程中容易出现磁盘竞争的状况，在这种情况下，经常进行Checkpoint可以减少每次Checkpoint涉及到的脏数据块的数目。调节Checkpoint次数的办法：增大日志文件；增加日
20、志组以增加覆盖的时间间隔。7、日志文件建立大小合适的日志文件以最小化竞争；提供足够的日志文件组以消除等待现象；将日志文件存放在独立的、能快速访问的存储设备上（日志文件可以创建在裸设备上）。日志文件以组的方式组织管理，每个组里的日志文件的内容完全相同。8、归档日志文件如果选择归档模式，必须要有两个或两个以后的日志组，当从一个组切换到另一个组时，会引起两种操作：DBWn进行Checkpoint；一个日志文件进行归档。归档有时候会报错：ARC0：Beginning to archive log# 4 seq# 2772 Current log# 3 seq# 2773ARC0: Failed to
21、archive log# 4 seq# 2772ARCH: Completed to archiving log#4 seq# 2772建议init参数修改如下：log_archive_max_processes=2#log_archive_dest = /u05/prodarchlog_archive_dest_1 = location=/u05/prodarch MANDATORYlog_archive_dest_state_1 = enable log_archive_dest_2 = location=/u05/prodarch2 OPTIONAL reopen=10 （或其它目录）l
22、og_archive_dest_state_2 = enable log_archive_min_succeed_dest=1log_archive_dest_state_3 = DEFERlog_archive_dest_state_4 = DEFERlog_archive_dest_state_5 = DEFER三、优化排序操作1、概念服务器首先在sort_area_size指定大小的内存区域里排序，如果所需的空间超过sort_area_size，排序会在临时表空间里进行。在专用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空间在UGA中。如果没有建立large pool，UG
23、A处于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
24、_aggregate_target已定义时才能设置为auto。这两个参数会取代所有的*_area_size参数。措施：尽可能避免排序；尽可能在内存中排序；分配合适的临时空间以减少空间分配调用。2、需要进行排序的操作：A、创建索引；B、涉及到索引维护的并行插入C、order by或者group by（尽可能对索引字段排序）D、DistinctE、union/intersect/minusF、sort-merge joinG、analyze命令（仅可能使用estamate而不是compute）3、诊断和措施Select * from v$sysstat where name like %sort%
25、;Sort(disk):要求Io去临时表空间的排序数目Sort(memory)：完全在memory中完成的排序数目Sort(rows)：被排序的行数合计Sort（disk）/ Sort（memory）5;C、library cache不够大。五、Rollback(undo) Segment 优化1、概念Transaction以轮循的方式使用rollback segment里的extent，当前所在的extent满时就移动到下一个extent。可能有多个transaction同时向同一个extent写数据，但一个rollback segment block中只能保存一个transaction的数
26、据。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、
27、诊断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) Rat
28、io, sum(waits) Waits, sum(gets) Gets from v$rollstat;waits的汇总数与gets的汇总数的比率应低于1，如果超过1，应创建更多的rollback segment。下列字段数值如果大于0，则表明在rollback segment header上存在竞争：A、v$rollstat 中的waitsB、v$waitstat中的undo header行C、v$system_event中的undo segment tx slot事件3、消耗更少的rollback segment1）如果是删除表里所有的数据，尽可能使用trauncate而不是delete
29、。2）在应用中允许用户有规律的提交，尽可能不用长事务。3） Import Set COMMIT = Y Size the set of rows with BUFFER Export: Set CONSISTENT=N SQL*Loader: Set the COMMIT intervals with ROWS4、小回滚段可能出现的问题A、事务由于缺少回滚空间失败B、由于下列原因导致的“Snapshot too old”问题：Block里的事务列表被刷新，block里的SCN比列表Interested Transaction List（ITL）里起始事务的SCN更新；Rollback segm
30、ent header里的Transaction slot被重用；回滚数据已经被重写；5、9i的自动回滚管理Undo_managment指定了回滚空间的管理方式：Auto：自动管理；Manual：手工管理回滚段。Undo_retention指定了回滚数据的保留期限；Undo_tablespace指定了被使用的回滚表空间；Oracle自动管理的表空间可以在常见数据库的时候创建，也可以单独建立。回滚表空间可以相互转换（switch），但在某一时刻只能有一个回滚表空间处于活动状态。回滚表空间处于非活动状态时可以删除，如果有对处于被删除回滚表空间里的已提交事务的查询时，oracle会返回一个错误。估计u
31、ndo tablespace大小的公式：Undo space = (undo_retention * (undo blocks per second * db_block_size) + db_block_size;可以使用下列的sql设定undo_retention和undo tablespace：select (rd*(ups*overhead)+overhead) bytes from (select value rd from v$parameter where name =undo_retention),(select (sum(undoblks)/sum(end_time-begi
32、n_time)*10800) ups from v$undostat),(select value overhead from v$parameter where name=db_block_size);其中：Rd：undo_retention设置的时间；Ups：undo blocks per second；Overhead：rollback segment header；六、Lock Contention1、概念DML事务使用row-level locks,查询不会锁定数据。锁有两种模式：exlusive、share。锁的类型： DML or data locks: Table-level
33、locks（TM） Row-level locks（TX） DDL or dictionary locks一个transaction至少获得两个锁：一个共享的表锁，一个专有的行锁。Oracle server将所有的锁维护在一个队列里，队列跟踪了等待锁的用户、申请锁的类型以及用户的顺序信息。Lock在下列情况会释放：commit；rollback；terminated（此时由pmon清理locks）。Quiesced database：一个数据库如果除了sys和system之外没有其他活动session，这个数据库即处于quiesced状态。活动session是指这个session当前处于一个t
34、ransaction中，或一个查询中，一个fetch中，或正占有某种共享资源。2、可能引起lock contention的原因不必要的高层次的锁；长时间运行的transaction；未提交的修改；其他产品施加的高层次的锁。解决lock contention的方法：锁的拥有者提交或回滚事务；杀死用户会话。3、死锁Oracle自动检测和解决死锁，方法是通过回滚引起死锁的语句（statement），但是这条语句对应的transaction并没有回滚，因此当收到死锁的错误信息后，应该去回滚改transaction的剩余部分。七、应用优化1、概念为了提高性能，可以使用下列数据访问方法：A、Cluster
35、sB、Indexes-B-tree（normal or reverse key）-bitmap-function-basedC、Index-organized tablesD、Materialized views索引的层次越多，效率越低，如果索引中含有许多已删除的行，这个索引也会变得低效，如果索引数据的15已经被删除，应该考虑重建索引。2、应用问题A、使用可声明的约束而不是通过代码限制B、代码共享C、使用绑定变量而不是文字来优化共享sqlD、调整cursor_sharing的值（EXACT/SIMILAR/FORCE）八、提升block的效率1、避免动态分配的缺陷创建本地管理的表空间；合理设置
36、segment的大小；监控将要扩展的segment：SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) .1;2、high water mark记录在segment header block中，在segment创建的时候设定在segment的起始位置，当记录被插入的时候以5个block的增量增加，truncate可以重设high water mark的位置，但delete不能。在full table scan中，oracle会读取hig
37、h water mark以下的所有的数据块，所以high water mark以上的块也许会浪费存储空间，但不会降低性能。可以通过下列方法收回表中high water mark以上的块：Alter table_name deallocate unused；对于high water mark以下的块：使用import/export工具：export数据；drop或truncate表；import数据。或者利用alter table tanle_name move命令去移动表的存储位置（此时需要重建索引）。3、表统计用analyize命令生成表统计，然后到dba_table查询相关信息。ANALY
38、ZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;SELECT num_rows, blocks, empty_blocks as empty,avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner =NDLS AND table_name=T_WH_SHIPPING_BILL;Columns Description NUM_ROWS Number of rows in the table BLOCKS Number of blocks below the table high-water mark EMPTY_BLOCKS Number of blocks above the table high-water mark AVG_SPACE Average
