oracle效能調整2

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

 

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-608940/，如需轉載，請註明出處，否則將追究法律責任。