播布客视频-Performance Tuning笔记(三)Database Configuration and IO Issues

最新推荐文章于 2024-10-02 00:27:17 发布

cr147369

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量97

点赞数

文章标签：数据库

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