Oracle I/O优化小技巧

1. 重做日志文件与归档日志文件放到不同硬盘.
2. 数据文件与重做日志文件放到不同硬盘.
3. 不同的重做日志文件、控制文件、归档日志文件最好放到不同硬盘
4. 控制文件、重做日志文件放到高速硬盘。不行就放到同一硬盘高速区，即柱面号低的硬盘外部半圈。
5. 归档日志可以放在低速硬盘或分区
6. 单纯增加硬盘大小不太管用。最好增加硬盘物理数量和控制器数量。
   
   
7. SYSTEM表空间只存放系统表和数据.
8. 还原表空间只做还原用.
9. 临时表空间只做排序区使用.
10. 数据表和索引放到不同表空间.索引多或大时,可以考虑不备份索引.
11. 大对象(LOB)最好放到单独的表空间
    
    
    
12. 文件I/O监控与诊断
    a.) 需要TIMED_STATISTICS参数为TRUE
    b.) SELECT file#, phyblkrd, phyblkwrt, readtim, writetim FROM v$filestat;
13. 查询表的数据行迁移状况,需要先做统计.
    a.) ANALYZE TABLE xxx.yyy COMPUTE STATISTICS;
    b.) SELECT num_rows, chain_cnt FROM dba_tables WHERE owner = 'xxx' AND table_name = 'yyy';
    
         注意''中内容需要大写.其中chain_cnt字段就是迁移数量.
14. 如果迁移比较严重,首先要避免后面继续发生.所以要加大PCTFREE的参数.
    a.)首先确认当前值
         SELECT table_name, pct_free FROM dba_tables WHERE owner = 'xxx' AND table_name = 'yyy';
    b.) 调整,如需要的话.
          ALTER TABLE xxx.yyy PCTFREE 30;
15. 消除已有迁移
    a.)非分区表
        ALTER TABLE xxx.yyy MOVE;
    b.)其他表
        ba.) 首先利用exp或expdp导出该表
        bb.) 使用DDL语句TRUNCAT该表. 注意不要使用DML语句DELETE,那样会产生大量UNDO数据,且速度慢.
                TRUNCATE TABLE xxx.yyy
        bc.) 利用imp或impdp导入之前导出的数据
16. 索引可能存在左右不平衡,或大量被标记删除却并未真正删除的索引,从而影响性能.需修正.
    a.)收集信息
        EXECUTE dbms_stats.gather_index_stats('用户名','索引名');
        或
        ANALYZE INDEX xxx.iii COMPUTE STATISTICS;
    b.)查询索引沉余信息
        SELECT name, (del_lf_rows_len/lf_rows_len) * 100 AS wastage FROM index_stats;
    
        index_stats表中的列含义:
        lf_rows 当前索引记录行数              lf_rows_len 全部索引长度
        del_lf_rows 删除的索引记录行数    del_lf_rows_len 被删除索引的全部长度
    c.)查询索引深度信息
        SELECT index_name, num_rows, blevel, status FROM dba_indexes WHERE table_owner = 'xxx' AND table_name = 'yyy';
       
         一般blevel在3以下都没问题.
    d.)重建索引消除问题. 有时需要加大数据块(DB_BLOCK_SIZE)大小来降低索引深度,推荐8K.
        ALTER INDEX xxx.iii REBUILD;
17. 删除大量数据时,避免使用DELETE而是使用TRUNCATE.可见减少还原数据量和运行时间.如有少量例外数据可使用临时表暂存,而后再行导入.如:
       CREATE TABLE sales_temp AS (SELECT * FROM sales WHERE rownum <= 18);
       TRUNCATE TABLE sales;
       INSERT INTO sales (SELECT * FROM sales_temp);
       DROP TABLE sales_temp;
    注意TRUNCATE是DDL不可被ROLLBACK
18. 查询重做日志切换情况
    SELECT sequence#, to_char(first_time, 'RR-MM-DD HH:MM:SS') "Date Time" FROM v$log_history;
19. 改变用户默认表空间
    ALTER USER xxx DEFAULT TABLESPACE tbs;
    
    确认:
    SELECT username, created, default_tablespace FROM dba_users WHERE username = 'xxx';
20. 移动表和索引到不同表空间.
    ALTER TABLE xxx MOVE TABLESPACE tbs;
    ALTER INDEX pk_emp REBUILD TABLESPACE tbs;
    
    查询:
    SELECT table_name, tablespace_name FROM dba_tables WHERE owner = 'xxx';
    SELECT index_name, tablespace_name, table_name, status FROM dba_indexes WHERE owner = 'xxx';
    
    注意,即使只是移动了表也要重建该表的所有索引.因为此时索引的状态(STATUS)已是不可用(UNUSABLE).此时对该表的访问都是以全表扫描的方式进行的.