oracle性能调优学习笔记(第三章Database configuration and i/o)

 Objectives:
 1 .list the advantages of distributing different oracle file types

 2. diagnose tablespace usage problems

 3. list erasons for partitioning data in tablesapces

 4. describe how checkpoints work

 5. monitor and tune checkpoints
 
 oracle support starndard storage options:
 1. file system (NAS,SAN)

 2. raw partitions(oracle 以后不支持)

 3. automatic storage managerment(ASM)

 

performance guidelines
basic performance rules are as folows:

1. keep disk i/o to a minimunm

2. spread your disk lad across disk devices and controllers

3. use temporary tablespaces where appropriate.
   
distributing files:
 1. seprarate data files and redo log files(redo log files应该放在最快的磁盘。oracle 提交把数据写入redo log file中)

 2. stripe(条带) table data

 3. reduce disk i/o unrelated to the database.
    表和索引分别放在不同的磁盘和表控件中 (读一次数据的话，会同时读取表和索引的数据)

tablespace useage:

1. reserve the system tablespace for data dictionary objects

2. create locally managed tablespaces to avoid space managerment issuces.(locally managed tablespaces)

3. split tables and indexes into separate tablespaces.

4. create rollback segments in their own tablespaces

5. store very large objects in their own tablespace(clob,blob)

6. create one or more temporary tablespaces.

 

locally managed sys TS

create databases that have a locally managed system tablespace.

 

create database mydb

datafile 'system01.dbf' size 100m extend management local

default temporary tablespace temp tempfile 'temp01.dbf' size 15m

用DBCA创建的也是locally manager.如果系统表空间是locally ，那么所有的表空间也是locally

 

Tools for i/O statistics

v$filestat,v$tempstat,v$datafile

 

查看文件的读写次数:

select phyrds,phywrts,d.name from v$datafile d,v$filestat f where d.file#=f.file# order by d.name

 

I/O statistics:

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;

 

 

 

                                         oracle 性能调优第8讲

tuning FTS(full table scane) operations

 

1. investigate the need for full table scans

2. configure the DB_FILE_MULTIBLCOCK_READ_COUNT initialization parameter to:
   a. determine the number of database blocks the server reads at once
   b. influence the execution plan of the cost-based optimizer

3. Monitor long-running full table scans with v$session_longops view(大于6秒钟的操作)

 

4. 查询全表扫描的语句:select name,value from v$sysstat where name like '%table scan%'(short tables 少于4个block,大于4个block是 long table)

   select sid,serial#,opname,to_char(start_time,'hh24:mi:ss') as "start",(sofar/totalwork)*100 as percent_complete from v$session_longops;
  
  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:

1. incremental checkpoints
   a. CKPT updates the control file.

   b. during a log switch CKPT updates the control file and the data file headers.

 

2. full checkpoints

   a. CKPT updates the control file and the data file headers

   b. DBWn writes out all buffers on the checkpoint queue.

 

FULL checkpoints:

two catagories of full checkpoints:

1. complete :sql>alert system checkpoint;(把全部数据写入数据文件中)

2. tablespace

 

                                           oracle 性能调优第9讲

Regulating(调节) checkpoint queue

regulate the checkpoint queue with the following initialization arameters:

1. FAST_START_TO_TARGET

2. LOG_CHECKPOINT_INTERVAL(过时)

 

3. LOG_CHECKPOINT_TIMEOUT(过时)

 

4. FAST_START_MTTR_TARGET(最重要:单位秒):掉电到恢复的时间

 

Fast start checkpointing

Use v$instance_recovery to obtain the following information:
1. RECOVERY_ESTIMATED_IOS

 

2. LOG_FILE_SIZE_REDO_BLKS

 

3. LOG_CHKPT_TIMEOUT_REDO_BLKS

 

4. LOG_CHKPT_INTERVAL_REDO_BLKS

 

5. TARGET_MTTR

 

6. ESTIMATED_MTTR

Redo Groups and members:

当lgwr开始把重做日志文件写入数据文件的过程：

 如果lgwr写完一个组1(重做日志组)，写组2. 那么archive进程就会把组1的文件进行归档

 当lgwr写完组2，在开始重新写组1时，如果archive进程没有写完组1.那么oracle就会挂起等待

 

 如何解决这个文件。就需要进行online redo configuration

 

Online Redo configuration:

1. size redo log files to minimize contention

2. provide enough groups to prevent waiting

3. store redo log fles on seprarte,fast devices

4.Monitor the redo log file configuration with:
  a.v$logfile(包括几个组，几个redo log file)

  b.v$log

  c.v$log_history(重做日志的切换)
 
Tread 1 cannot allocate new log,sequence 1466
checkpoint not complete
current log#3 seq#1465 mem# 0: /home/ora10g/oradata/ora10g/redo03.log

Archiving performance
1. allow the LGWR process to write to a disk different from the one the ARCn process is reading.

 

2. share the archiving work during a temporary increase in workload:
   alter system archive log all to <log_archive_dest>

 

3.increase the number of archive process.

 

4.change archiving speed:
 a. LOG_ARCHIVE_MAX_PROCESSES
 B. LOG_ARCHIVE_DEST_n
 
Diagnostic Tools

archive logs

v$archive_dest
v$archived_log
v$archive_processes
LOG_ARCHIVE_DEST_STATE_N