数据库配置和I/O
1)oracle支持的存储类型:file system,asm(automatic storage management)
2)I/O statistics视图:v$filestat,v$tempstat,v$datafile
3)查看数据文件I/O读写
SQL>select phyrds,phywrts,d.name from v$datafile d,v$filestat f
where d.file#=f.file# order by d.name;
4)查看表空间、数据文件I/O读写
SELECT d.tablespace_name TABLESPACE, d.file_name, f.phyrds, f.phywrts FROM v$filestat f, dba_data_files d WHERE f.file# = d.file_id; |
1) 配置初始化参数DB_FILE_MULTIBLOCK_READ_COUNT,确定server process一次读取数
据块数目。
监控长时间运行的全表扫描:v$session_longops
统计全表扫描:
Select name,value from v$sysstat where name like ‘%table scan%’; |
确定全表扫描进展:
SELECT sid, serial#, opname, TO_CHAR(start_time,'HH24:MI:SS') AS "START", (sofar/totalwork)*100 AS PERCENT_COMPLETE FROM v$session_longops; |
调节checkpoint队列用初始化参数:FAST_START_MTTR_TRAGET
用v$instance_recovery获得checkpoint信息
2) 监控redo log file
v$logfile
v$log
v$log_history
3) 把归档日志都存放到目录:
alter system archive log all to <log_archive_dest>
改变归档速度:
log_archive_max_processes
log_archive_dest_n