一SGA
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需要重新解析
） 检查v$librarycache中sql area的gethitratio是否超过％如果未超过％应该检查应用代码提高应用代码的效率
Select gethitratio from v$librarycache where namespace=sql area;
) v$librarycache中reloads/pins的比率应该小于％如果大于％应该增加参数shared_pool_size的值
Select sum(pins) executionssum(reloads) cache missessum(reloads)/sum(pins) from v$librarycache;
reloads/pins>%有两种可能一种是library cache空间不足一种是sql中引用的对象不合法
）shared pool reserved size一般是shared pool size的％不能超过％V$shared_pool_reserved中的request misses＝或没有持续增长或者free_memory大于shared pool reserved size的%表明shared pool reserved size过大可以压缩
）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程
）从i开始可以将execution plan与sql语句一起保存在library cache中方便进行性能诊断从v$sql_plan中可以看到execution plans
）保留大的对象在shared pool中大的对象是造成内存碎片的主要原因为了腾出空间许多小对象需要移出内存从而影响了用户的性能因此需要将一些常用的大的对象保留在shared pool中下列对象需要保留在shared pool中
a 经常使用的存储过程
b 经常操作的表上的已编译的触发器
c Sequence因为Sequence移出shared pool后可能产生号码丢失
查找没有保存在library cache中的大对象
Select * from v$db_object_cache where sharable_mem> and type in (PACKAGEPROCEDUREFUNCTIONPACKAGE BODY) and kept=NO;
将这些对象保存在library cache中
Execute dbms_shared_poolkeep(package_name);
对应脚本dbmspoolsql
)查找是否存在过大的匿名pl/sql代码块两种解决方案
A．转换成小的匿名块调用存储过程
B．将其保留在shared pool中
查找是否存在过大的匿名pl/sql块
Select sql_text from v$sqlarea where command_type= and length(sql_text)>;
）Dictionary cache的 优化
避免出现Dictionary cache的misses或者misses的数量保持稳定只能通过调整shared_pool_size来间接调整dictionary cache的大小
Percent misses应该很低大部分应该低于％合计应该低于％
Select sum(getmisses)/sum(gets) from v$rowcache;
若超过％增加shared_pool_size的值
Buffer Cache
）granule大小的设置db_cache_size以字节为单位定义了default buffer pool的大小
如果SGA<Mgranule=M否则granule＝M即需要调整sga的时候以granule为单位增加大小并且sga的大小应该是granule的整数倍
) 根据v$db_cache_advice调整buffer cache的大小
SELECT size_for_estimatebuffers_for_estimateestd_physical_read_factorestd_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<=
) 统计buffer cache的cache hit ratio>%如果低于％可以用下列方案解决
增加buffer cache的值
使用多个buffer pool
Cache table
为 sorting and parallel reads 建独立的buffer cache
SELECT NAMEvalue FROM v$sysstat WHERE NAME IN (session logical readsphysical readsphysical reads directphysical reads direct(lob));
Cache hit ratio=(physical readsphysical reads directphysical reads direct (lob))/session logical reads;
Select (phyvaluedirvaluelobvalue)/logvalue from v$sysstat log v$sysstat phy v$sysstat dir v$sysstat LOB where logname=session logical reads and phyname=physical reads and dirname=physical reads direct and lobname=physical reads direct (lob);
影响cache hit ratio的因素
全表扫描应用设计大表的随机访问cache hits的不均衡分布
）表空间使用自动空间管理消除了自由空间列表的需求可以减少数据库的竞争
其他SGA对象
）redo log buffer
对应的参数是log_buffer缺省值与 OS相关一般是K检查v$session_wait中是否存在log buffer waitv$sysstat中是否存在redo buffer allocation retries
A检查是否存在log buffer wait
Select * from v$session_wait where event=log buffer wait ;
如果出现等待一是可以增加log buffer的大小也可以通过将log 文件移到访问速度更快的磁盘来解决
BSelect namevalue from v$sysstat where name in (redo buffer allocation retriesredo entries)
Redo buffer allocation retries接近小于redo entries 的％如果一直在增长表明进程已经不得不等待redo buffer的空间如果Redo buffer allocation retries过大增加log_buffer的值
C检查日志文件上是否存在磁盘IO竞争现象
Select eventtotal_waitstime_waitedaverage_wait from v$system_event where event like log file switch completion%;
如果存在竞争可以考虑将log文件转移到独立的更快的存储设备上或增大log文件
D检查点的设置是否合理
检查alertlog文件中是否存在checkpoint not complete
Select eventtotal_waitstime_waitedaverage_wait from v$system_event where event like log file switch (check%;
如果存在等待调整log_checkpoint_intervallog_checkpoint_timeout的设置
E检查log archiver的工作
Select eventtotal_waitstime_waitedaverage_wait from v$system_event where event like log file switch (arch%;
如果存在等待检查保存归档日志的存储设备是否已满增加日志文件组调整log_archiver_max_processes
FDB_block_checksum=true因此增加了性能负担（为了保证数据的一致性oracle的写数据的时候加一个checksum在block上在读数据的时候对checksum进行验证）
）java pool
对于大的应用java_pool_size应>=M对于一般的java存储过程缺省的M已经够用了
）检查是否需要调整DBWn
Select total_waits from v$system_event where event=free buffer waits;
二数据库配置和IO问题
降低磁盘的IO
分散磁盘的IO
表空间使用本地管理
将文件分散到不同的设备上
）将数据文件与日志文件分开
）减少与服务器无关的磁盘IO
）评估裸设备的使用
）分割表数据
表空间的使用
系统表空间保留给数据字典对象
创建本地管理表空间以避免空间管理问题
将表和索引分散到独立的表空间中
使用独立的回滚表空间
将大的数据库对象保存在各自独立的表空间中
创建一个或多个独立的临时表空间
下列数据库对象应该有单独的表空间
数据字典回滚段索引临时段表大对象
检查IO统计数据
Select phyrdsphywrtsdname from v$datafile dv$filestat f where ffile#=dfile# order by dname;
检查最有可能引起磁盘IO瓶颈的文件
分割文件
可以通过RAID和手工进行
Alter table table_name allocate extent (datafile fiile_name size M);
但手工操作工作量很大
优化全表扫描操作
）检查有多少全表发生
Select namevalue 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相关）
）查看full table scan操作
Select sidserial#opnametargetto_char(start_timeHH:MI:SS) start(sofar/totalwork)* percent_complete from v$session_longops;
通过v$session_longops里的sql_hash_value与v$sqltext关联可以查询导致full table scan的sql
Checkpoint
Checkpoint进行的操作DBWn进行IO操作CKPT更新数据文件头和控制文件
经常进行Checkpoint的结果减少恢复所需的时间降低了系统运行时的性能
LGWR以循环的方式将日志写到各个日志组当一个日志组满时oracle server必须进行一个Checkpoint这意味着DBWn将对应log覆盖的所有或部分髒数据块写进数据文件CKPT更新数据文件头和控制文件如果DBWn没有完成操作而LGWR需要同一个文件LGWR只能等待
在OLTP环境下如果SGA很大并且checkpoint的次数不多在Checkpoint的过程中容易出现磁盘竞争的状况在这种情况下经常进行Checkpoint可以减少每次Checkpoint涉及到的髒数据块的数目
调节Checkpoint次数的办法
增大日志文件增加日志组以增加覆盖的时间间隔
日志文件
建立大小合适的日志文件以最小化竞争
提供足够的日志文件组以消除等待现象
将日志文件存放在独立的能快速访问的存储设备上（日志文件可以创建在裸设备上）日志文件以组的方式组织管理每个组里的日志文件的内容完全相同
归档日志文件
如果选择归档模式必须要有两个或两个以后的日志组当从一个组切换到另一个组时会引起两种操作DBWn进行Checkpoint一个日志文件进行归档
归档有时候会报错
ARCBeginning to archive log# seq#
Current log# seq# ……
ARC: Failed to archive log# seq#
ARCH: Completed to archiving log# seq#
建议init参数修改如下
log_archive_max_processes=
#log_archive_dest = /u/prodarch
log_archive_dest_ = location=/u/prodarch MANDATORY
log_archive_dest_state_ = enable
log_archive_dest_ = location=/u/prodarch OPTIONAL reopen= （或其它目录）
log_archive_dest_state_ = enable
log_archive_min_succeed_dest=
log_archive_dest_state_ = DEFER
log_archive_dest_state_ = DEFER
log_archive_dest_state_ = DEFER
三优化排序操作
概念
服务器首先在sort_area_size指定大小的内存区域里排序如果所需的空间超过sort_area_size排序会在临时表空间里进行在专用服务器模式下排序空间在PGA中在共享服务器模式下排序空间在UGA中如果没有建立large poolUGA处于shared pool中如果建立了large poolUGA就处于large pool中而PGA不在sga中它是与每个进程对应单独存在的
PGAprogram global area为单个进程（服务器进程或后台进程）保存数据和控制信息的内存区域PGA与进程一一对应且只能被起对应的进程读写PGA在用户登录数据库创建会话的时候建立
有关排序空间自动管理的两个参数
Pga_aggregate_target: MG等于分配给oracle instance的所有内存减去SGA后的大小
Workarea_size_policy: auto/manual只有Pga_aggregate_target已定义时才能设置为auto
这两个参数会取代所有的*_area_size参数
措施
尽可能避免排序尽可能在内存中排序分配合适的临时空间以减少空间分配调用
需要进行排序的操作
A创建索引
B涉及到索引维护的并行插入
Corder by或者group by（尽可能对索引字段排序）
DDistinct
Eunion/intersect/minus
Fsortmerge join
Ganalyze命令（仅可能使用estamate而不是compute）
诊断和措施
Select * from v$sysstat where name like %sort%;
Sort(disk):要求Io去临时表空间的排序数目
Sort(memory)完全在memory中完成的排序数目
Sort(rows)被排序的行数合计
Sort（disk）/ Sort（memory）<%如果超过％增加sort_area_size的值
SELECT diskValue diskmemValue mem(diskValue/memValue)* ratio FROM v$sysstat diskv$sysstat mem WHERE memNAME=sorts (memory) AND diskNAME=sorts (disk);
监控临时表空间的使用情况及其配置
Select tablespace_namecurrent_userstotal_extentsused_extentsextent_hitsmax_used_blocksmax_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
临时表空间的配置
Ainitial/next设置为sort_area_size的整数倍允许额外的一个block作为segment的header
Bpctincrease=
C基于不同的排序需要建立多个临时表空间
D将临时表空间文件分散到多个磁盘上
四诊断latch竞争
概念
Latch是简单的低层次的序列化技术用以保护SGA中的共享数据结构比如并发用户列表和buffer cache里的blocks信息一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch在完成以后释放latch不必对latch本身进行优化如果latch存在竞争表明SGA的一部分正在经历不正常的资源使用
）Latch的作用
A序列化访问保护SGA中的共享数据结构保护共享内存的分配
B序列化执行避免同时执行某些关键代码避免互相干扰
）Latch请求的两种类型
Awillingtowait请求的进程经过短时间的等待后再次发出请求直到获得latch
Bimmediate如果没有获得latch请求的进程不等待而是继续处理其他指令
检查Latch竞争
检查latch free是不是主要的wait event
Select * from v$system_event order by time_waited;
检查latch的使用情况
Select * from v$latch:
与willingtowait请求有关的列getsmissessleepswait_timecwait_timespin_gets
与immediate请求有关的列immediate_getsimmediate_misses
Gets: number of successful willingtowait requests for a latch;
Misses: number of times an initial wilingtowait request was unsuccessful;
Sleeps: number of times a process waited after an initial willingtowait request;
Wait_time: number of milliseconds waited after willingtowait request;
Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleepingthe 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= order by upper(sql_text);
B共享sql被重新编译考虑library cache的大小是否需要调整
SELECT sql_textparse_callsexecutions FROM v$sqlarea where parse_calls>;
Clibrary cache不够大
五Rollback(undo) Segment 优化
概念
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
诊断rollback segment header的竞争
如果rollback segment 由手工管理下列措施诊断rollback segment header的竞争
SELECT classcount FROM v$waitstat WHERE class LIKE %undo% ;
SELECT Sum(Value) sum FROM v$sysstat WHERE NAME IN (db block getsconsistent gets);
任何类型的等待次数（count）与总请求数(sum)的比率不能超过％
或
select sum(waits)*/sum(gets) Ratio sum(waits) Waits sum(gets) Gets from v$rollstat;
waits的汇总数与gets的汇总数的比率应低于％如果超过％应创建更多的rollback segment
下列字段数值如果大于则表明在rollback segment header上存在竞争
Av$rollstat 中的waits
Bv$waitstat中的undo header行
Cv$system_event中的undo segment tx slot事件
消耗更少的rollback segment
）如果是删除表里所有的数据尽可能使用trauncate而不是delete
）在应用中允许用户有规律的提交尽可能不用长事务
）&#; Import
– Set COMMIT = Y
– Size the set of rows with BUFFER
&#; Export: Set CONSISTENT=N
&#; SQL*Loader: Set the COMMIT intervals with ROWS
小回滚段可能出现的问题
A事务由于缺少回滚空间失败
B由于下列原因导致的Snapshot too old问题
Block里的事务列表被刷新block里的SCN比列表Interested Transaction List（ITL）里起始事务的SCN更新
Rollback segment header里的Transaction slot被重用
回滚数据已经被重写
i的自动回滚管理
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_timebegin_time)*))) ups from v$undostat)(select value overhead from v$parameter where name=db_block_size);
其中
Rdundo_retention设置的时间
Upsundo blocks per second
Overheadrollback segment header
六Lock Contention
概念
DML事务使用rowlevel locks查询不会锁定数据锁有两种模式exlusiveshare
锁的类型
&#; DML or data locks:
– Tablelevel locks（TM）
– Rowlevel locks（TX）
&#; DDL or dictionary locks
一个transaction至少获得两个锁一个共享的表锁一个专有的行锁Oracle server将所有的锁维护在一个队列里队列跟蹤了等待锁的用户申请锁的类型以及用户的顺序信息
Lock在下列情况会释放commitrollbackterminated（此时由pmon清理locks）Quiesced database一个数据库如果除了sys和system之外没有其他活动session这个数据库即处于quiesced状态活动session是指这个session当前处于一个transaction中或一个查询中一个fetch中或正占有某种共享资源
可能引起lock contention的原因
不必要的高层次的锁
长时间运行的transaction
未提交的修改
其他产品施加的高层次的锁
解决lock contention的方法锁的拥有者提交或回滚事务杀死用户会话
死锁
Oracle自动检测和解决死锁方法是通过回滚引起死锁的语句（statement）但是这条语句对应的transaction并没有回滚因此当收到死锁的错误信息后应该去回滚改transaction的剩余部分
七应用优化
概念
为了提高性能可以使用下列数据访问方法
AClusters
BIndexes
Btree（normal or reverse key）
bitmap
functionbased
CIndexorganized tables
DMaterialized views
索引的层次越多效率越低如果索引中含有许多已删除的行这个索引也会变得低效如果索引数据的％已经被删除应该考虑重建索引
应用问题
A使用可声明的约束而不是通过代码限制
B代码共享
C使用绑定变量而不是文字来优化共享sql
D调整cursor_sharing的值（EXACT/SIMILAR/FORCE）
八提升block的效率
避免动态分配的缺陷
创建本地管理的表空间
合理设置segment的大小
监控将要扩展的segment
SELECT owner table_name blocks empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < ;
high water mark
记录在segment header block中在segment创建的时候设定在segment的起始位置当记录被插入的时候以个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命令去移动表的存储位置（此时需要重建索引）
表统计
用analyize命令生成表统计然后到dba_table查询相关信息
ANALYZE TABLE ndlst_wh_shipping_bill COMPUTE STATISTICS;
SELECT num_rows blocks empty_blocks as emptyavg_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 highwater mark
EMPTY_BLOCKS Number of blocks above the table highwater mark
AVG_SPACE Average free space in bytes in the blocks below highwater mark
AVG_ROW_LEN Average row length including row overhead
CHAIN_CNT Number of chained or migrated rows in the table
block size
通过下列方法可以最小化block的访问次数
使用更大的block size紧密压缩行阻止行镜像后两者存在沖突越多的行被压缩在一个block里越容易产生镜像Block size 在数据库创建的时候设定不能被轻易改变是读取数据文件时最小的IO单元大小范围是K－K应该设置成OS块的整数倍小于或等于OS IO时能读取的存储区域
较小的block size的优点极少block竞争有利于较小的行和随机访问缺点是存在相当高的成本每个block的行数更少可能需要读取更多的index块Block size的选择影响系统的性能在一个OLTP环境中较小的block size更合适而在DSS环境中适宜选择较大的block size
九应用优化
概念
为了提高性能可以使用下列数据访问方法
AClusters
BIndexes
Btree（normal or reverse key）
bitmap
functionbased
CIndexorganized tables
DMaterialized views
索引的层次越多效率越低如果索引中含有许多已删除的行这个索引也会变得低效如果索引数据的％已经被删除应该考虑重建索引
应用问题
A使用可声明的约束而不是通过代码限制
B代码共享
C使用绑定变量而不是文字来优化共享sql
D调整cursor_sharing的值（EXACT/SIMILAR/FORCE）
八提升block的效率
避免动态分配的缺陷
创建本地管理的表空间
合理设置segment的大小
监控将要扩展的segment
SELECT owner table_name blocks empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < ;
high water mark
记录在segment header block中在segment创建的时候设定在segment的起始位置当记录被插入的时候以个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命令去移动表的存储位置（此时需要重建索引）
表统计
用analyize命令生成表统计然后到dba_table查询相关信息
ANALYZE TABLE ndlst_wh_shipping_bill COMPUTE STATISTICS;
SELECT num_rows blocks empty_blocks as emptyavg_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 highwater mark
EMPTY_BLOCKS Number of blocks above the table highwater mark
AVG_SPACE Average free space in bytes in the blocks below highwater mark
AVG_ROW_LEN Average row length including row overhead
CHAIN_CNT Number of chained or migrated rows in the table
block size
通过下列方法可以最小化block的访问次数
使用更大的block size紧密压缩行阻止行镜像后两者存在沖突越多的行被压缩在一个block里越容易产生镜像Block size 在数据库创建的时候设定不能被轻易改变是读取数据文件时最小的IO单元大小范围是K－K应该设置成OS块的整数倍小于或等于OS IO时能读取的存储区域
较小的block size的优点极少block竞争有利于较小的行和随机访问缺点是存在相当高的成本每个block的行数更少可能需要读取更多的index块Block size的选择影响系统的性能在一个OLTP环境中较小的block size更合适而在DSS环境中适宜选择较大的block size
PCTFREEPCTUSED
）PCTFREEPCTUSED使你能控制一个segment里所有数据块里free space的使用
PCTFREE一个数据块保留的用于块里已有记录的可能更新的自由空间占block size的最小比例
PCTUSED在新记录被插入block里之前这个block可以用于存储行数据和其他信息的空间所占的最小比率
）这两个参数的使用
如果创建表的时候指定pctfree＝％oracle会在这个表的data segment的每个block都保留％的空间用于已有记录的更新Block的已使用空间上升到整个block size的％时这个block将移出free list在提交了deleteupdate之后oracle server处理这条语句并检查对应block的已使用空间是否低于PCTUSED如果是则这个block放进free list
）PCTFREEPCTUSED的设定
&#; PCTFREE
– Default
– Zero if no UPDATE activity
– PCTFREE = × upd / (average row length)
&#; PCTUSED
– Default
– Set if rows deleted
– PCTUSED = – PCTFREE – × rows × (average row length) / blocksize
其中upd the average amount added by updates in bytesThis is determined by subtracting the average row length of intercurrent average row length;
average row length在运行了analyize命令之后这个值可以从dba_tables中的avg_row_len列中获得
rows the number of rows to be deleted before free list maintenance occurs
）Deleteupdate可以增加block的自由空间但是释放出来的空间有可能是不连续的oracle在下列情况下会对碎片进行整理一个block有足够的自由空间容纳row piece但是由于每个碎片都较小以至这个row piece不能存放在一个连续的section中
Migration和Chaining
）如果一行的数据太大以至一个单独的block容纳不下会产生两种现象
AChaining行数据太大以至一个空block容纳不下oracle会将这一行的数据存放在一个或多个block 组成的block chain中insertupdate都可能导致这个问题在某些情况下row chaining是不能避免的
BMigration一次update操作可能导致行数据增大以至它所在的block容纳不下oracle server会去寻找一个有足够自由空间容纳整行数据的block如果这样的block存在oracle server把整行移到新的block在原位置保存一个指向新存放位置的镜像行镜像行的rowid和原来的rowid一致
ChainingMigration的弊端insertupdate的性能降低索引查询增加了IO次数
）检测migration和chaining
Analyize table table_name compute statistics
Select num_rowschain_cnt from dba_tables where table_name=;
查询镜像行
Analyize table table_name list chained rows
Select owner_nametable_namehead_rowid from chained_rows where table_name=;
产生Migration的原因可能是由于PCTFREE设置的太低以至没有保留足够的空间用于更新
可以通过增加PCTFREE的值避免行镜像产生
）消除镜像行的步骤
运行analyize table list chained rows;
复制镜像行到另一个表tmp
从源表中删除这些行
从tmp中将这些行插回到源表中
脚本
/* Get the name of the table with migrated rows */
accept table_name prompt Enter the name of the table with migrated rows:
/* Clean up from last execution */
set echo off
drop table migrated_rows;
drop table chained_rows;
/* Create the CHAINED_ROWS table */
@?/rdbms/admin/utlchain
set echo on
spool fix_mig
/* List the chained & migrated rows */
analyze table &table_name list chained rows;
/* Copy the chained/migrated rows to another table */
create table migrated_rows as
select orig* from &table_name orig chained_rows cr
where origrowid = crhead_rowid
and crtable_name = upper(&table_name);
/* Delete the chained/migrated rows from the original table */
delete from &table_name
where rowid in ( select head_rowid from chained_rows );
/* Copy the chained/migrated rows back into the original table */
insert into &table_name select * from migrated_rows;
spool off
使用这个脚本时必须将涉及到的外键约束去掉
索引重组
在一个不稳定的表上建索引会影响性能一个索引block只有完全空时才能进入free list即使一个索引block里只含有一个条目它也必须被维护因此索引需要进行阶段性的重建
）检查索引是否需要重组
A收集一个index的使用统计
ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
B查看收集的统计数据
SELECT NAME(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * AS index_usage FROM index_stats;
Column Description
LF_ROWS Number of values currently in the index
LF_ROWS_LEN Sum in bytes of the length of all values
DEL_LF_ROWS Number of values deleted from the index
DEL_LF_ROWS_LEN Length of all deleted values
C如果浪费超过％则索引需要重建
ALTER INDEX acct_no_idx REBUILD;
D或者对索引进行整理
Alter index acct_no_idx coalesce;
）标记未使用的索引
A 开始监测索引的使用
Alter index hremp_name_ix monitoring usage;
B 停止监测索引的使用
Alter index hremp_name_ix nomonitoring usage;
C 查询索引的使用情况
Select index_nameused from v$object_usage;
删除未使用过的索引可以降低DML操作的成本从而提升系统性能
为了尽可能经济的利用block应对存在较多空block镜像行的表进行重建对建立不稳定表上的索引应有规律的进行重建并尽可能创建本地管理的表空间
九SQL优化
优化器模式
Oraclei有两种优化器模式可以选择
&#; Rulebased:
– Uses a ranking system
– Syntax and data dictionary–driven
&#; Costbased:
– Chooses leastcost path
– Statisticsdriven
Rulebased模式满足向后兼容而Costbased模式中的成本大部分来自于逻辑读的次数推荐使用Costbased模式
固定optimizer plan
）概念
对于每一个查询optimizer都会准备一个定义了操作执行顺序和方法的操作树（执行计划）oracle server根据这个执行计划执行语句通过固定执行计划可以强制应用通过一种理想的方式访问数据并且一个稳定的执行计划可以经历数据库的变化而保持不变固定执行计划通过创建stored outline实现outline使用costbased的optimizer因为其由一系列的hints组成
执行计划的固定依赖于当判定一个查询是否存在stored outline时查询语句是否完全一致与判定shared pool里一个执行计划是否可以重用时的匹配方式是一致的
Outline被保存在outln schema中
） 创建stored outline
alter session set CREATE_STORED_OUTLINES = train;
create or replace OUTLINE co_cl_join
FOR CATEGORY train ON
select cocrs_id
from courses coclasses cl
where cocrs_id = clcrs_id;
stored outline通过category组织相同的sql语句可以在多个category同时拥有stored outline如果categoey没有指定缺省是default category
当CREATE_STORED_OUTLINES等于true或category名时oracle会为所有被执行的sql语句创建stored outline也可以通过create outline手工创建
） 使用stored outline
将USE_STORED_OUTLINES设置为true或category名
alter session set USE_STORED_OUTLINES = train;
当为一个查询寻找stored outline时查询语句与stored outline里的语句必须完全一致在outline里的hints也必须在查询语句中出现
private outline
Private outline是当前保存的stored outline的副本可以被编辑而不影响正在运行的系统一个private outline只能被当前session看到它的数据被保存在当前被解析的schema里知道显示的将其公布
当USE_PRIVATE_OUTLINES=TRUE时一个已有outline的sql被提交时optimizer会检查是否存在private outline如果不存在optimizer就不使用optimizer编译语句而不会去检查公布的stored outline
在sql中使用hints
Create index gen_idx on customers(cust_gender);
Select /*+ index(customers gen_idx)*/
Cust_last_namecust_street_addresscust_postal_code
From shcustomers where upper(gender)=M;
EXPLAIN PLAN
可以不通过tracing需要建立plan_table表
Sql>@oracle_home/rdbms/admin/utlxplan;
建立explain plan
Explain plan for select last_name from hremp;
查询plan_table中的explain plan可以直接查询也可以通过脚本utlxplxsql(隐藏并行查询信息)utlxplpsql（显示并行查询信息）查询
管理统计信息
利用analyize命令收集或删除信息
参数
Compute统计精确的数据
Estimate估计的统计数据
各类统计数据的位置
表dba_tables;
索引dba_indexes;
列user_tab_col_statistics;
柱状图（histogram）详细的描述了一个特定列中数据的分布情况可以通过analyize table for columns 命令创建保存在dba_histogram/dba_tab_histograms中
十操作系统优化和使用资源管理器
操作系统优化
）概念
操作系统优化时应该考虑的因素有内存的使用Cpu的使用IO级别网络流量各个因素互相影响正确的优化次序是内存IOCPU
操作系统使用了虚拟内存的概念虚拟内存使每个应用感觉自己是使用内存的唯一的应用每个应用都看到地址从开始的单独的一块内存虚拟内存被分成K或K的page操作系统通过MMU（memory management unit）将这些page与物理内存映射起来这个映射关系通过page table控制
Raw device是没有文件结构或目录结构的磁盘或磁盘分区由于它忽略了操作系统缓存在某些情况下可以显着提升性能但是在windows NT下由于操作系统IO操作本身不使用文件系统缓存所以raw device不能显示性能上的优点
）Guideline
CPU的最高使用率％
OS/USER进程数之比/
各个CPU的负载应该大致均衡
）服务器安全性检查
A检查UNIX系统用户口令
检查/etc/passwd/etc/shadowUNIX密码采用了shadow机制安全性能高
建议参考UNIX命令passwd修改/etc/default/passwd文件的某些设置如MAXWEEKSMINWEEKSPASSLENGTH使口令修改更加合理化
建议定期更改UNIX系统的如下用户口令
rootoraprodapplprodappprod
B检查 Remote Login
启动了rlogin服务器数据库a数据库b数据库c终端consoleconsoleconsole及T形成相互非常信任的关系用户只要拥有一个服务器的超级权限就可以rlogin到rhosts指明的任一主机而无需要口令
建议非常不安全参考UNIX命令rlogin和/目录下的文件rhosts在正式环境服务器和测试环境服务器之间不要建立这种远程信任的机制
C检查FTP服务
检查可以FTP到服务器的用户（/etc/ftpusers）注释了root用户就是说用户可以用root权限FTP到服务器上权限太大
建议把这种权力取消将/etc/ftpusers中root的注释符号（#）去掉在列表中添加oraprodapplprodappprod等用户使之不能FTP服务器必要时（如上传PATCH时）再打开applprod的FTP权限
D建议UNIX系统管理员定期检查/var/adm下的messagessulog/etc/nf 等信息检查是否有非法用户登陆UNIX
建议与UNIX工程师探讨更好的监控方式
）数据库与应用产品安全性检查
A建议修改oracle用户根目录下的profile文件修改该文件的权限为即使得用户登陆时并不执行和数据库或应用相关的环境变量增加安全性
B检查数据库DBA权限的用户密码和应用系统用户密码SYSTEMAPPS密码都已经改变SYS密码还是初始安装密码Change_on_install
建议立即修改SYS用户密码定期更改APPSSYSTEMSYS密码
C定期检查并清除$ORACLE_HOME/admin/bdump目录下的alert_PRODlog文件和后台进程trace文件定期清除$ORACLE_HOME/admin/udump目录下的trc文件
D建议给应用产品登陆的用户设置口令过期限制如口令访问次数限制或时间（天数）限制
建议不要给使用应用产品的用户共享用户名和口令每个用户分配一个应用产品用户名
建议对有应用系统管理员权限的用户登记不适合有系统管理员权限的用户要把权限回收统一管理
E定期检查并清除与Apache Server有关的log文件目录为:
/u/prodora/iAS/Apache/Apache/logs/acccess_logerror_log
/u/prodora/iAS/Apache/Jserv/logs/jservlogmod_jservlog
F定期检查清除listenertnsname的log文件文件存放在:
/u/prodora//network/admin/apps_prodlog
/u/proddb//network/admin/prodlog
/u/proddb//network/log/listenerlogsqlnetlog…
G数据库控制文件做多个镜像放在多个磁盘位置提高安全性
）网络安全性检查
检查$ORACLE_HOME/dbs/initPRODora文件
#remote_login_passwordfile=EXCLUSIVE
设置为REMOTE_LOGIN_PASSWORDFILE=NONE不允许远程客户用INTERNAL方式登陆
资源管理器（Resource Manager）
通过资源管理器可以管理混合工作负载控制系统性能数据库资源管理器包括
&#; Resource plans包括 resource plan directives 它指定了被分配到各个 resource consumer group的资源
&#; Resource consumer groups定义了具有类似资源使用需求的一组用户
&#; Resource plan directives包括下列内容:为consumer groups 或 subplans 指定resource plans在各个 consumer groups 或资源计划的subplans 分配资源
