Oracle Database 11g includes three standard storage options:
- File system
- Network attached storage(NAS)
- Storage area network(SAN)
- Direct attached storage
- Raw Partitions(原始分区,此种方式将逐渐被淘汰)
- Automatic Storage Management(AMS)
Oracle Processes and Files
| Process |
Oracle File I/O Data files |
Oracle File I/O Redo Log |
Oracle File I/O Archive |
Oracle File I/O Control |
| CKPT | Read/Write | Read/Write | ||
| DBWn | Write | |||
| LGWR | Write | Read/Write | ||
| ARCn | Read | Write | Read/Write | |
| SERVER | Read/Write | Read | Write | Read/Write |
Performance Guidelines
Basic performance rules are as follows:
- Keep disk I/O to a minimum
- Spread your disk load across disk devices and controllers
- Use temporary tablespaces where appropriate
Distributing Files
- Separate data files and redo log files
根据Oracle存储数据原理（用户commit之后，并不一定立即将数据存储到datafile中，而很大程度上是存储在redo log files），在分配redo log file时，应当将此文件分配到性能最高的磁盘,而且这块磁盘（容量小，但是速度高）不应该再有其他的I/O操作或者其他磁盘操作行为;而Data Files可以放在容量大一些的，性能相对较低的磁盘(容量大，但是速度相对可以慢一点).
- Stripe table data
- Reduce disk I/O unrelated to the database.
SELECT COL1,COL2,... FROM CUST_HEADER,CUST_DETAIL WHERE ... Here is a data management solution: DISK1:CUST_HEADER Table DISK5:CUST_HEADER Index Disk8:CUST_DEATIL Table Disk9:Cust_DEATIL Index
将表和索引放到不同的磁盘的理由：表和索引会同时读取.更新这张表的时候，先更新表数据，然后更新表的索引；如此设计，可以提高读写速度.表和所以在同一个磁盘上，就会容易产生竞争，加重负荷.所以讲表和索引放在不同的磁盘上，就会使disk load分开.
Tablespace Usage
- Reserve the system tablespace for data dictionary objects. (Reserve保留).System Tablespace应当使用local manage管理方式
- Create locally managed tablespaces to avoid space management issues.永远选择使用locally managed tablespaces管理方式.
- Split tables and indexes into separate tablespaces.根本原因就在于表和索引是同时读取的。
- Create rollback segments in their own tablespaces.创建独立的undo tablespace。system tablespace中也有undo tablespace。用户不应该使用system undo tablespace，而应该创建属于自己的Undo tablespace.创建原则就是，在不影响你的数据的情况下，undo tablespace 越大越好.
- Store very large objects in their own tablespace.
- Create one or more temporary tablespace.每个用户都应当有属于自己的临时表空间.
Locally Managed SYS TS
Create databases that have a locally managed system tablespace.
Create DATABASE mydb
...
DATAFILE 'system01.dbf' SIZE 100M EXTENT
MANAGEMENT LOCAL
...
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'temp01.dbf' SIZE 15M
...;
如果你系统表空间是Locally Managed管理方式，还必须要指定一个tempoary tablespace.
如果你的系统表空间是locally Managed管理方式，那么其他的表空间也必须是Locally Managed管理方式.
如果你使用dbca创建数据库的话，默认就创建Locally Managed管理方式.
Tools for I/O Statistics(监控表空间的各个指标)
Server I/O utilization
监控表空的动态性能视图:
v$datafile 当前所有的数据文件；列出非临时表空间的数据文件。
v$tempstat 临时表空间的数据文件对应的状态.
v$filestat 文件状态信息
Using v$filestat View（TIPS SQL）
SQL> select phyrds,phywrts,d.name from v$datafile d,v$filestat f where d.file#=f.file# order by d.name; PHYRDS PHYWRTS NAME ---------- ---------- ---------------------------------------- 6 0 /u01/oradata/DB202/example01.dbf 1 0 /u01/oradata/DB202/mytbs02.dbf 43 0 /u01/oradata/DB202/perfstat_01.dbf 3721 2093 /u01/oradata/DB202/sysaux01.dbf 9237 639 /u01/oradata/DB202/system01.dbf 40 713 /u01/oradata/DB202/undotbs01.dbf 1 0 /u01/oradata/DB202/userdata01.dbf 1 0 /u01/oradata/DB202/users01.dbf 8 rows selected.
查看哪个数据文件，被读的次数，被写的次数，可以判断出哪些是热点?
I/O Statistics
SQL> select d.tablespace_name tablespace,d.file_name,f.phyrds,f.phywrts from v$filestat f,dba_data_files d where file# = d.file_id; TABLESPACE FILE_NAME PHYRDS PHYWRTS ------------------------------ -------------------------------------------------- ---------- ---------- SYSTEM /u01/oradata/DB202/system01.dbf 9238 642 SYSAUX /u01/oradata/DB202/sysaux01.dbf 3721 2097 UNDOTBS1 /u01/oradata/DB202/undotbs01.dbf 40 717 USERS /u01/oradata/DB202/users01.dbf 2 0 EXAMPLE /u01/oradata/DB202/example01.dbf 7 0 USERDATA /u01/oradata/DB202/userdata01.dbf 2 0 PERFSTAT /u01/oradata/DB202/perfstat_01.dbf 44 0 MYTBS02 /u01/oradata/DB202/mytbs02.dbf 2 0 8 rows selected.
哪个表空间，哪个数据文件被读写的次数最多?根据此SQL进行性能调优.
Tuning Full Table Scan Operations
- Investigate the need for full table scans
- Configuration the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter to:DB_FILE_MULTIBLOCK_READ_COUNT参数大小的设置于实际的系统要求有关的，设置时需要查看系统要求.这个参数的设置可以在system级别设置，也可以在session级别设置.
- Determine the number of database blocks the server reads at once
- Infulence the executions plan of the cost-based optimizer
- Monitor long-running full table scans with v$session_longops view.
- v$session_longops此性能视图记录那些操作时间比较长的记录，一般超过6秒的会有记录.
-
SQL> select sid,opname from v$session_longops; SID OPNAME ---------- ---------------------------------------------------------------- 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics 69 Gather Table's Index Statistics SID OPNAME ---------- ---------------------------------------------------------------- 198 Advisor 89 rows selected.
Table Scan Statistics
如何监控oracle server中是否存在全表扫描的活动呢？
SQL> select name,value from v$sysstat where name LIKE '%table scan%'; NAME VALUE ---------------------------------------------------------------- ---------- table scans (short tables) 25608 table scans (long tables) 7 table scans (rowid ranges) 0 table scans (cache partitions) 0 table scans (direct read) 0 table scan rows gotten 2970623 table scan blocks gotten 89508 7 rows selected.
如果一个table占据的block<=4个，就是short tables,>=4个就是long tables.
table scans (long tables) 如果这个值比较大，说明全表扫描比较频繁；可以考察全表扫描是否有必要，如果没有必要，可以考虑是否可以通过添加一些索引，或者调整优化器的一些指标来避免全表扫描.
Monitoring Full Table Scans Operations
Determine the process of long operations using:
SQL> select sid,serial#,opname,to_char(start_time,'HH24:MI:SS') AS "START",(sofar/totalwork)*100 AS PERCENT_COMPLETE FROM v$session_longops; SID SERIAL# OPNAME START PERCENT_COMPLETE ---------- ---------- ------------------------------------ -------- ---------------- 69 116 Gather Table's Index Statistics 22:00:20 100 69 116 Gather Table's Index Statistics 22:00:20 100 69 116 Gather Table's Index Statistics 22:00:21 100 69 116 Gather Table's Index Statistics 22:00:21 100 69 116 Gather Table's Index Statistics 22:00:22 100 69 116 Gather Table's Index Statistics 22:00:22 100 69 116 Gather Table's Index Statistics 22:00:22 100 69 116 Gather Table's Index Statistics 22:00:23 100 69 116 Gather Table's Index Statistics 22:00:23 100 69 116 Gather Table's Index Statistics 22:00:24 100 69 116 Gather Table's Index Statistics 22:00:24 100
...... SID SERIAL# OPNAME START PERCENT_COMPLETE ---------- ---------- ------------------------------------ -------- ---------------- 69 116 Gather Table's Index Statistics 22:00:45 100 69 116 Gather Table's Index Statistics 22:00:46 100 69 116 Gather Table's Index Statistics 22:00:46 100 69 116 Gather Table's Index Statistics 22:00:46 100 69 116 Gather Table's Index Statistics 22:00:46 100 69 116 Gather Table's Index Statistics 22:00:47 100 69 116 Gather Table's Index Statistics 22:00:47 100 69 116 Gather Table's Index Statistics 22:00:47 100 69 116 Gather Table's Index Statistics 22:00:48 100 69 116 Gather Table's Index Statistics 22:00:49 100 69 116 Gather Table's Index Statistics 22:00:50 100 SID SERIAL# OPNAME START PERCENT_COMPLETE ---------- ---------- ------------------------------------ -------- ---------------- 198 1 Advisor 22:00:09 100 89 rows selected.
USE SET_SESSION_LONGOPS to populate v$session_longops.
dbms_application_info.set_session_longops(rindex,slno,"Operation x",obj,0,sofar,totalwork,"table","tables");
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 datafile headers.
- Full checkpoints
CKPT udpates the control file and the data file headers.
DBWn writes out all buffers on the checkpoint queue.
Full Checkpoints
Two categories of full checkpoints
- Complete 将所有的derty buffer datas 全部写入到data file中
- 只要执行命令ALTER SYSTEM CHECKPOINT;就可以实现全检查点.
- log_checkpoints_to_alert 参数设置为true后，执行alter system checkpoint时，oracle server 就会将checkpoint信息写到alert_sid.log文件中.
- Tablespace 只将tablespace中的derty buffer data写入到data file中
Regulating Checkpoint Queue
Derty buffer queue通过调整这个参数，来提高ORACLE性能；通常情况下Derty buffer queue的值越大，性能越高，在断电情况下，数据库恢复的时间也越长；反之，性能能够越低，在断电的情况下，数据库会的时间越短;这是一个矛盾体.
Regulate the checkpoint queue with the following initialization parameters:
- FAST_START_IO_TRAGET
- LOG_CHECKPOINT_INTERVAL(基本过时)
- LOG_CHECKPOINT_TIMEOUT（基本过时）
- FAST_START_MTTR_TARGET（比较常用的一个参数.单位是秒，恢复系统所需要的时间。DBA设定这个参数后，Oracle会根据这个参数值，自动调整其他参数的值，来保证实现这个目标.）
Fast Start Checkpointing
Use v$instance_recovery to obtain the following information
- RECOVERY_ESTIMATED_IOS
- LOG_FILE_SIZE_REDO_BLKS
- LOG_CHKPT_TIMEOUT_REDO_BLKS
- LOG_CHKPT_INTERVAL_REDO_BLKS
- TARGET_MTTR
- ESTIMATED_MTTR
Redo Groups and Members
通常情况下，为了保证oracle能够正常工作，至少会设置两组联机重做日志文件，每组中至少有一个日志文件，保障Oracle能够循环写数据;但是在通常情况下，为了保障可靠性，每一组里面，至少有2组或以上成员，进行multipleplex（多路复用），而且组中的每个成员，必须放在不同的磁盘上;
LGWR进程负责写redo log file，ARCn进程负责写archive log file.oracle server写日志的顺序如下：LGWR进程写完组1，在写组2之前，ARCn进程会将组1进行归档，归档完成之后，LGWR再接着写组2的redo log file文件.
LGWR，写完组1，写组2，写完组2写组3（假设只有3个组），写完组3回过头来写组1的时候，此时组1被重新利用。现在的问题是，组1被重新利用的前期是组1中的数据已经被可靠的写到data file中。假如LGWR写完组3，回过头来重新利用组1时，结果组1中的数据还没有被DBWn进程写到data file中，那么此时Oracle就会一直等待，等待着DBWn进程安全的将组1中的数据安全可靠的写到data file文件中，oracle server才会继续有效的工作.因此在实际工作中，应当尽可能的避免出现上述情况.这种情况的发生，会大大的降低oracle的性能.
Online Redo Congfiguration
- Size redo log files to minimize contention.
- Provide enough groups to prevent waiting.
- Store redo log files on separeate,fast devices.
- Monitor the redo log file configuration with:
|-v$logfile
|-v$log
|-v$log_history
Archiving Performance
实际的生产环境当中，Oracle server都是出于archived log mode.LGWR负责写，ARCn负责读，二者要避免竞争，配合默契.最好是LGWR进程在写A磁盘时，ARCn进程在读B磁盘，如此这样可以提高性能.
- Allow the LGWR process to write to a disk different from the one the ARCn process is reading.
- Share the archiving work during a temporary increase in workload:
ALTER SYSTEM ARCHIVE LOG ALL TO <log_archive_dest> (全部将日志文件归档到归档文件当中)
- Increase the number of archive processes.
- Change archiving speed:
-LOG_ARCHIVE_MAX_PROCESSES
-LOG_ARCHIVE_DEST_n
Diagnostic Tools（诊断归档性能工具）
- v$archive-dest
- v$archive_log
- v$archive_processes
- LOG_ARCHIVE_DEST_STATE_n
Summary
通本本章，可以大体上知道对oracle进行磁盘布局的大体原则，及如何调优;
In this lesson,you should have learned how to:
- List the advantages of distributing different Oracle file types
- Diagnose tablespace usage problems
- List reasons for segmenting data in tablespaces
- Describe how checkpoints work
- Monitor and tune checkpoints
- Monitor and tune archive logging