Oracle数据库优化

一、数据库优化规范
1.不同用途的数据放在不同的tablespace避免资源争用
2.不同的tablespace放在不同的物理驱动器调高I/O并发能力
3.日志文件放在速度快的磁盘上，数据文件可以放在速度慢的磁盘上
4.日志文件的同组中不同成员放在不同的物理磁盘

二、通过物理文件的读写次数来调优
Select d.tablespace_name,d.file_name,f.phyrds,f.phywrts from v$filestat f,dba_data_files d 
where f.file#=d.file_id;

三、可以通过设置Oracle读写块的大小提高I/O效率，参数：DB_FILE_MULTIBLOCK_READ_COUNT

四、配置多个DBWn
Alter system db_writer_precesses //操作系统必须支持异步I/O，多cpu使用
1.开启异步I/O 
2.在不支持异步I/O的情况下，修改dbwr_io_slaves数量也可以达到增加DBWn的效果

五、LGWR是使性能下将
select sid,event,seconds_in_wait,state from v$session_wait where event='log buffer space'
语句执行后应该没有为空，即没有log buffer space wait

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries')
语句执行后,如果redo buffer allocation retries此值过大说明redo buffer太小


六、归档引起
1.增加redo log 组和成员
2.archive文件太多，没地方写归档文件，可以删除文件
3.增加归档进程 log_archive_max_proceses

七、执行计划
必须在同一个窗口执行这2条语句
1.explain plan for select * from gy_gdxm;
2.select * from table(dbms_xplan.display());

八、索引压缩
压缩索引可以提高索引的查询效率，节约空间
1.当创建索引时使用：
create index emp_last_idx on hr.employees(last name,first_name) compress;
2.重建索引时使用：
alter index emp_last_name_idx rebuild compress;

九、索引判断回滚段竞争的SQL语句
select rn.name, rs.GETS, rs.WAITS, (rs.WAITS / rs.GETS) * 100 ratio
from v$rollstat rs, v$rollname rn where rs.USN = rn.usn;
当Ration>2时存在回滚段竞争，则需增加更多的回滚段


十、判断恢复日志竞争的SQL语句    
select name,(t.IMMEDIATE_MISSES /decode((t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES),0,-1,
(t.IMMEDIATE_GETS + t.IMMEDIATE_MISSES))) * 100 immediate_contention,
(t.MISSES / decode((t.GETS + t.MISSES), 0, -1, (t.GETS + t.MISSES))) * 100 wait_contention 
from v$latch t where name in ('redo copy', 'redo allocation');

mmediate_contention或wait_contention的值大于1时存在竞争


十一、判断表空间碎片
select t.tablespace_name,sum(t.bytes),max(t.bytes),count(*),max(t.bytes)/sum(t.bytes) radio 
from dba_free_space t 
group by t.tablespace_name order by t.tablespace_name;

如果最大空闲空间占总空间很大比例则可能不存在碎片，如果比例较小，且有许多空闲空间，则可能碎片很多

十二、确定高速缓冲区命中率
select 1-sum(decode(name, 'physical reads', value, 0))/(sum(decode(name, 'db block gets', value, 0)) + 
sum(decode(name, 'consistent gets', value, 0))) hit_ratio 
from v$sysstat t where name in ('physical reads', 'db block gets', 'consistent gets');

如果命中率低于70％，则应该加大init.ora参数中的DB_BLOCK_BUFFER的值


十三、确定共享池命中率
select sum(pins) pins,sum(reloads) reloads ,(sum(reloads)/sum(pins) )* 100 ratio1 from v$librarycache ;
select sum(gets) gets,sum(getmisses) getmisses,(sum(getmisses) / sum(gets)) * 100 ratio2 from v$rowcache;

如果ratio1大于1时，需要加大共享池，如果ratio2大于10％时，需要加大共享池SHARED_POOL_SIZE

<script type="text/javascript"> $(function () { $('pre.prettyprint code').each(function () { var lines = $(this).text().split('\n').length; var $numbering = $('<ul/>').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('<li/>').text(i)); }; $numbering.fadeIn(1700); }); }); </script>