3-3DB configuration and IO issuse
three standard storage
file system:
network attached storage(NAS)
SAN --storage area network
direct attached storage

Raw partitions --被淘汰

Automatic Storage management--ASM

basic performance rules are as follows：
keep disk IO to a minimum
spread your disk load across disk devices
and controllers

use temporary tablespaces where appropriate

separate data files and redo log files

stripe table data

索引和表同时读写，所以表和索引分开磁盘

磁盘布局了解

undo表空间越大越好

每个用户建议一个临时表空间

tools for IO statistics：

v$datafile
v$tempstat
v$filestat---重要，可以看到每个文件的读写负荷

bbk2125

full table scans--FTS
DB_FILE_MULTIBLOCK_READ_COUNT
--FTS多的情况下设置大一点，可以减小IO

操作系统一次最大读写大小要注意

oracle数据块是操作系统的数据块大小的指数倍

这个参数会影响执行计划，影响CBO--cost-based optimizer

select name,value from v$sysstat where name like '%table scan%';

short tables<=4blocks
long tables>4blocks很多的时候就要注意

SQL> select name,value from v$sysstat where name like '%table scan%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table scans (short tables)                                        305050789
table scans (long tables)                                           3423588
table scans (rowid ranges)                                               10
table scans (cache partitions)                                           62
table scans (direct read)                                                 5
table scan rows gotten                                           1.6269E+13
table scan blocks gotten                                         7.5484E+11

7 rows selected.

v$session_longops--超过6s

设置超时过长的会话--为什么提供这工具呢？

dbms_application_info.set_session_longops

Checkpoints
the two most common types of checkpoints are:

Incremental checkpoints:
CKPT updates the control file
During a log switch CKPT updates the control file
and the data file headers

Full checkpoints:--alter system checkpoint
CKPR updates the control file and the data file headers
DBWn writes out all buffers on the checkpoint queue

show parameter log_checkpoints_to_alert
可以设置checkpoint是否写入alert日志

FULL checkpoints的两种情况：
Complete
Tablespace

bbk2126

Regulating Checkpoint Queue

queue长度决定参数
FAST_START_MTTR_TARGET--单位s，决定了queue长度

FAST_START_IO_TARGET
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT

v$instance_recovery

Redo groups and members

日志同一个组不同成员，要不同磁盘上
日志文件太大时，组切换后归档未做完；--多扩几组

报错：thread 1 cannot allocate new log,checkpoint nnot complete

v$logfile
v$log
v$log_history---日志切换频度记录

archiving performance

alter system archive log all to

LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_DEST_n

v$archive_dest
v$archived_log
v$archive_processes
LOG_ARCHIVE_DEST_STATE_n

ITPub博客

3-3DB configuration and IO issuse

最新文章