播布客視訊-Performance Tuning筆記(三)Database Configuration and IO Issues

Database Configuration and IO Issues

一、目標
    1. 不同的oracle檔案型別的優缺點
    2. 診斷表空間的使用情況
    3. 為什麼要對錶空間中的資料進行分割槽
    4. 檢查點的工作原理
    5. 如何監控和調優檢查點
    6. 如何監控和調優redo log
   
二、Oracle Database 11g includes 3 standard storage options:
    1. File system
       Network attched storage(NAS)
       Storage area network(SAN)
       Direct attached storage
    2. Raw paratitions
    3. Automatic Storage Management(ASM)
   
三、Oracle process and files
    1. DBWn: Write data files(不讀，只寫), Read/Write Control file
    2. LGWR: Write log
    3. ARCn: Read log, Write Archive, Read/Write Control fil
    4. CKPT: Read/Write Data files header, Read/Write Control file
    5. SERVER: Rdad/Write data files, Read log, Write Archive(手動歸檔)
   
四、Performance Guidelines Basic rules
    1. Keep disk I/O to a minimum.
    2. Spread your disk load across disk devices and controllers.
    3. Use temporary tablespaces where appropriate.
   
五、Distributing Files
    1. Separate data files and redo log files on different disk.
    2. Stripe table data.
    3. Reduce disk I/O unrelated to the database.
   
六、Tablespace Usage
    1. Reserve the system/sysaux tablespace for data dictionary objects.
    2. Create locally managed tablesapces to avoid space management issues.
    3. tables and indexes into separate tablespaces.
    4. Create rollback segments in their own tablespaces.
    5. Stroe large objects in their own tablespace.
    6. Create on or more temp tablespaces.
   
七、Tools for I/O Statistics
    1. Server I/O utilization
       v$filestat SQL
       SELECT phyrds, phywrts, d.name
       FROM v$datafile d, v$filestat f
       WHERE d.file#=f.file#
       ORDER BY d.name ;
       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 f.file#=d.file_id
      
       v$tempstat
       v$datafile
    2. System I/O utilization
       Performance tools  
      
八、File Striping
    1. Operationg system Striping
       os striping software
       RAID
    2. Manaual Striping
       create table/ alter table xxx command with allocate extent new file_id
 
九、Tuning FTS Operations
    1. investigate the need for full table scans.
    2. DB_FILE_MULTIBLOCK_READ_COUNT
       determine the number of db blocks the server reads at once
       influence the execution plan of the CBO
    3. v$session_longops(>6s): Monitor long-running full table scans 
       select name , value
       from v$sysstat
       where name like '%table scan%'
        <4 block = short tables
        >4 block = long tables
       select sid, serial#, opname,
              start_time,
              (sofar/totalwork)*100 as percent_complete
       from v$session_longops
    4. 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
     1. Incremental checkpoints(lead to increase recovery time)
        CKPT updates the control file.
        During a log switch CKPT updates the controlfile and the data file headers.
     2. Full checkpoints
        CKPT updates the control file and data file header
        DBWn writes out all buffers on the checkpoint queue.
        a. Complete: alter system checkpoint;
        b. Tablespace: alter tablespace xxx begin backup/ offline;
     3. Checkpoint Queue (DIRTY BUFFER QUEUE)相關的init parameter
        FAST_START_IO_TARGET
        FAST_START_MTTR_TARGET: the number of seconds the db crash recovery of instance
         
十一、 Redo Groups and Members
       1. LGWR: write Group1, then Group2, then Group3
       2. ARCH: read redo Group1 then write disk.
          DBWn還未寫入disk時， redo不可覆寫；
          e.g. Thread 1 cannot allocate new log , sequence 1466
               Checkpoint not complete
               Current log#2 xxx...
          LGWR與 ARCH爭用
       3. 建議：
          a. 減少競爭  LGWR與DBWn競爭及LGWR與ARCH競爭
          b. redo log file on separate, fast devices.
          c. v$logfile
             v$log
             v$log_history
            
十二、 Archiving Performance
       1. Allow the LGWR process to write to a disk different from the one the ARCn process is reading
       2. Alter system archive log all to
       3. Increase the number of archive processes.
       4. Change archiving speed:
          LOG_ARCHIVE_MAX_PROCESSES
          LOG_ARCHIVE_DEST_n(different disk from redo log disk)
       5. Diagnostic Tools
          v$archive_dest
          v$archived_log
          v$archive_processes
          LOG_ARCHIVE_DEST_STATE_n

參考連結：http://www.boo booke.com/bbs/thread-7630-1-1.html

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