一、SGA

1、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/pins>1%有两种可能，一种是library cache空间不足，一种是sql中引用的对象不合法。

3）shared pool reserved size一般是shared pool size的10％，不能超过50％。V$shared_pool_reserved中的request misses＝0或没有持续增长，或者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_mem>10000 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％
Select sum(getmisses)/sum(gets) from v$rowcache;
若超过15％，增加shared_pool_size的值。

2、Buffer Cache

1）granule大小的设置，db_cache_size以字节为单位定义了default buffer pool的大小。
如果SGA<128M，granule=4M,否则granule＝16M，即需要调整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='ON' AND block_size=(SELECT Value FROM v$parameter WHERE NAME='db_block_size');

estd_physical_read_factor<=1

3) 统计buffer cache的cache hit ratio>90%，如果低于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 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 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’;

二、数据库配置和IO问题

降低磁盘的IO
分散磁盘的IO
表空间使用本地管理
1、将文件分散到不同的设备上
1）将数据文件与日志文件分开
2）减少与服务器无关的磁盘IO
3）评估裸设备的使用
4）分割表数据

2、表空间的使用

系统表空间保留给数据字典对象
创建本地管理表空间以避免空间管理问题
将表和索引分散到独立的表空间中
使用独立的回滚表空间
将大的数据库对象保存在各自独立的表空间中
创建一个或多个独立的临时表空间

下列数据库对象应该有单独的表空间：
数据字典、回滚段、索引、临时段、表、大对象

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）检查有多少全表发生：
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%,如果超过5％，增加sort_area_size的值。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';

4、监控临时表空间的使用情况及其配置
Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;

Column Description
CURRENT_USERS Number of active users
TOTAL_EXTENTS Total number of extents
USED_EXTENTS Extents currently allocated to sorts
EXTENT_HITS Number of times an unused extent was found in the pool
MAX_USED_BLOCKS Maximum number of used blocks
MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort

临时表空间的配置：
A、initial/next设置为sort_area_size的整数倍，允许额外的一个block作为segment的header
B、pctincrease=0
C、基于不同的排序需要建立多个临时表空间
D、将临时表空间文件分散到多个磁盘上

四、诊断latch竞争

1、概念
Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。

1）Latch的作用：
A、序列化访问：保护SGA中的共享数据结构；保护共享内存的分配。
B、序列化执行：避免同时执行某些关键代码；避免互相干扰。

2）Latch请求的两种类型：
A、willing-to-wait：请求的进程经过短时间的等待后再次发出请求，直到获得latch
B、immediate：如果没有获得latch，请求的进程不等待，而是继续处理其他指令。
2、检查Latch竞争
检查latch free是不是主要的wait event：
Select * from v$system_event order by time_waited;

检查latch的使用情况：
Select * from v$latch:
与willing-to-wait请求有关的列：gets、misses、sleeps、wait_time、cwait_time、spin_gets
与immediate请求有关的列：immediate_gets、immediate_misses

Gets: number of successful willing-to-wait requests for a latch;
Misses: number of times an initial wiling-to-wait request was unsuccessful;
Sleeps: number of times a process waited after an initial willing-to-wait request;
Wait_time: number of milliseconds waited after willing-to-wait request;
Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleeping,the overhead of context switches due to OS time slicing and page faults and interrupts;
Spin_gets: gets that misses first try but succeed after spinning.

Immediate_gets: number of successful immediate requests for each latch;
Immediate_misss: number of unsuccessful immediate requests for each latch;

一般无需调整latch，但是下列的措施是有用的：
A、对处于竞争中的latch做进一步的调查
B、如果竞争主要存在于shared pool和library cache中，可以考虑调整应用
C、如果进一步的调查显示需要调整shared pool和buffer cache，就进行调整

Select * from v$latch where name like ‘%shared pool%’ or name like ‘%library cache%’；

如果竞争是在shared pool或library cache上，表示下列集中情况：
A、不能共享的sql,应检查他们是否相似，考虑以变量代替sql中的常量：
Select sql_text from v$sqlarea where executions=1 order by upper(sql_text);
B、共享sql被重新编译，考虑library cache的大小是否需要调整：
SELECT sql_text,parse_calls,executions FROM v$sqlarea where parse_calls>5;

五、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自动管理的表空间可以在常见数据库的时候创建，也可以单独建立。回滚表空间可以相互转换（switch），但在某一时刻只能有一个回滚表空间处于活动状态。回滚表空间处于非活动状态时可以删除，如果有对处于被删除回滚表空间里的已提交事务的查询时，oracle会返回一个错误。

估计undo 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-begin_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 Contention

1、概念
DML事务使用row-level locks,查询不会锁定数据。锁有两种模式：exlusive、share。
锁的类型：
• DML or data locks:
– Table-level 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当前处于一个transaction中，或一个查询中，一个fetch中，或正占有某种共享资源。

2、可能引起lock contention的原因
不必要的高层次的锁；
长时间运行的transaction；
未提交的修改；
其他产品施加的高层次的锁。

解决lock contention的方法：锁的拥有者提交或回滚事务；杀死用户会话。

3、死锁
Oracle自动检测和解决死锁，方法是通过回滚引起死锁的语句（statement），但是这条语句对应的transaction并没有回滚，因此当收到死锁的错误信息后，应该去回滚改transaction的剩余部分。

七、应用优化

1、概念
为了提高性能，可以使用下列数据访问方法：
A、Clusters
B、Indexes
-B-tree（normal or reverse key）
-bitmap
-function-based
C、Index-organized tables
D、Materialized views

索引的层次越多，效率越低，如果索引中含有许多已删除的行，这个索引也会变得低效，如果索引数据的15％已经被删除，应该考虑重建索引。

2、应用问题
A、使用可声明的约束而不是通过代码限制
B、代码共享
C、使用绑定变量而不是文字来优化共享sql
D、调整cursor_sharing的值（EXACT/SIMILAR/FORCE）

八、提升block的效率

1、避免动态分配的缺陷
创建本地管理的表空间；
合理设置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会读取high 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查询相关信息。
ANALYZE 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 free space in bytes in the blocks below high-water mark
AVG_ROW_LEN Average row length, including row overhead
CHAIN_CNT Number of chained or migrated rows in the table

4、block size
通过下列方法可以最小化block的访问次数：
使用更大的block size；紧密压缩行；阻止行镜像。后两者存在冲突，越多的行被压缩在一个block里，越容易产生镜像。Block size 在数据库创建的时候设定，不能被轻易改变，是读取数据文件时最小的IO单元，大小范围是2K－64K，应该设置成OS块的整数倍，小于或等于OS IO时能读取的存储区域。

较小的block size的优点：极少block竞争；有利于较小的行和随机访问。缺点是存在相当高的成本，每个block的行数更少，可能需要读取更多的index块。Block size的选择影响系统的性能，在一个OLTP环境中，较小的block size更合适，而在DSS环境中，适宜选择较大的block size。

九、应用优化

1、概念
为了提高性能，可以使用下列数据访问方法：
A、Clusters
B、Indexes
-B-tree（normal or reverse key）
-bitmap
-function-based
C、Index-organized tables
D、Materialized views

索引的层次越多，效率越低，如果索引中含有许多已删除的行，这个索引也会变得低效，如果索引数据的15％已经被删除，应该考虑重建索引。

2、应用问题
A、使用可声明的约束而不是通过代码限制
B、代码共享
C、使用绑定变量而不是文字来优化共享sql
D、调整cursor_sharing的值（EXACT/SIMILAR/FORCE）

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9907339/viewspace-1045309/ ，如需转载，请注明出处，否则将追究法律责任。