数据库的最优分布来优化I/O性能

数据库的最优分布来优化I/O性能

1.查看
v$filestat
 READTIM      累计读取时间（ms）         
 WRITETIM     累计写入时间         
            

数据文件
select d.name,f.phyrds,f.PHYBLKRD,f.READTIM,f.phywrts,f.PHYBLKWRT,f.WRITETIM
from v$datafile d ,v$filestat f
where d.FILE#=f.file#
order by d.name

 

表空间
select d.tablespace_name TABLESPACE ,d.file_name,f.phyrds,f.PHYBLKRD,f.READTIM,f.phywrts,f.PHYBLKWRT,f.WRITETIM
from v$filestat f,dba_data_files d
where f.FILE#=d.file_id

 

2.调整
DB_FILE_MULTIBLOCK_READ_COUNT
db_block_size

 

系统级分散RAID0、RAID1、RAID0+1、RAID5
create table， alter table ……allocate分散
oracle分区partition by range 分散

 

全表扫描占用I/O
SELECT name, value FROM v$sysstat
WHERE name LIKE '%table scan%';

SELECT sid, serial#, opname,
TO_CHAR(start_time,'HH24:MI:SS') AS STARTs,
(sofar/totalwork)*100 AS PERCENT_COMPLETE
FROM v$session_longops;

 

select sql_text
from v$session_longops a , v$sqltext b
where a.SQL_ADDRESS=b.address

 

Checkpoint
LOG_CHECKPOINT_TIMEOUT 1800″
LOG_CHECKPOINT_INTERVAL 块数
FAST_START_MTTR_TARGET 预期平均恢复时间 最大3600″
FAST_START_IO_TARGET

 

V$INSTANCE_RECOVERY

 

SELECT RECOVERY_ESTIMATED_IOS  ,        
LOG_FILE_SIZE_REDO_BLKS    ,   
LOG_CHKPT_TIMEOUT_REDO_BLKS   ,
LOG_CHKPT_INTERVAL_REDO_BLKS  ,
TARGET_MTTR       ,            
ESTIMATED_MTTR                
FROM V$INSTANCE_RECOVERY  

         

Redo Log Groups
Size，enough groups，on separate、fast devices，
V$LOGFILE，V$LOG，V$LOG_HISTORY

select to_char(first_time,'hh24:mi:ss') from v$loghist  切换时间

select event,total_waits,time_waited
from v$system_event
where event = 'log file parallel write';

 

Archive Log
Allow the LGWR process to write to a disk different from the one the ARCn process is reading
分开日志组在不同disk

LOG_ARCHIVE_MAX_PROCESSES int
ALTER SYSTEM ARCHIVE LOG ALL TO 临时启动PROCESSES

LOG_ARCHIVE_DEST_n

 

V$ARCHIVE_DEST
V$ARCHIVED_LOG
V$ARCHIVE_PROCESSES
LOG_ARCHIVE_DEST_STATE_n