Oracle物理设计及性能优化详解.docVIP

Oracle物理设计及性能优化 设计资料整理 汤延涛 yantao929@163.com 2007-12-20 目录 1 前言 1 2 硬件体系构架 1 2.1 SMP 1 2.2 MPP 2 2.3 PVFS(并行虚拟文件系统) 4 2.3.1 概述 4 2.3.2 PVFS的存取机制 5 2.3.3 PVFS的应用实例与性能 7 2.4 OPS(Oracle Parallel Server) 8 2.4.1 体系结构 8 2.4.2 并行处理 9 2.4.3 分区技术 12 3 Oracle体系结构 12 3.1 内存结构和进程结构 12 3.2 Oracle实例 13 3.3 Oracle 10g动态内存管理 14 3.3.1 系统全局区SGA（System Global Area） 14 3.3.2 Oracle实例的进程结构（Process Structure） 22 4 存储管理 28 4.1 ASM（自动存储管理） 28 4.2 DMT,LMT,ASSM 28 4.3 自动管理问题 32 5 时间空间转换平衡 32 5.1 数据压缩 32 5.2 索引机制分析 32 5.2.1 Index full scan VS Index fast full scan 32 5.2.2 Index range scan VS Index skip scan 33 6 设计注意问题 34 6.1 字符集问题 34 6.2 代理主键 34 6.3 程序监控 34 6.4 临时表空间和回滚表空间释放 34 6.5 并行操作 34 6.6 关于APPEND提示 35 6.7 数据库统计信息收集 35 7 SQL语句优化 35 7.1 选用适合的ORACLE优化器 35 7.2 访问Table的方式 35 7.3 共享SQL语句 36 7.4 选择最有效率的表名顺序 37 7.5 WHERE子句中的连接顺序 38 7.6 SELECT子句中避免使用 ‘ * ‘ 38 7.7 减少访问数据库的次数 38 7.8 使用DECODE函数来减少处理时间 39 7.9 整合简单,无关联的数据库访问 40 7.10 删除重复记录 41 7.11 用TRUNCATE替代DELETE 41 7.12 尽量多使用COMMIT 41 7.13 计算记录条数 41 7.14 用Where子句替换HAVING子句 41 7.15 减少对表的查询 42 7.16 通过内部函数提高SQL效率 43 7.17 使用表的别名(Alias) 44 7.18 用EXISTS替代IN 44 7.19 用NOT EXISTS替代NOT IN 44 7.20 用表连接替换EXISTS 45 7.21 用EXISTS替换DISTINCT 45 7.22 识别“低效执行”的SQL语句 46 7.23 使用TKPROF 工具来查询SQL性能状态 46 7.24 用EXPLAIN PLAN 分析SQL语句 47 7.25 用索引提高效率 48 7.26 索引的操作 48 7.27 基础表的选择 50 7.28 多个平等的索引 50 7.29 等式比较和范围比较 51 7.30 不明确的索引等级 51 7.31 强制索引失效 52 7.32 避免在索引列上使用计算 53 7.33 自动选择索引 54 7.34 避免在索引列上使用NOT 54 7.35 用=替代 55 7.36 用UNION替换OR (适用于索引列) 56 7.37 用IN来替换OR 58 7.38 避免在索引列上使用IS NULL和IS NOT NULL 59 7.39 总是使用索引的第一个列 59 7.40 ORACLE内部操作 60 7.41 用UNION-ALL 替换UNION ( 如果有可能的话) 60 7.42 使用提示(Hints) 61 7.43 用WHERE替代ORDER BY 62 7.44 避免改变索引列的类型 63 7.45 需要当心的WHERE子句 64 7.46 连接多个扫描 65 7.47 CBO下使用更具选择性的索引 66 7.48 避免使用耗费资源的操作 66 7.49 优化GROUP BY 66 7.50 使用日期 67 7.51 使用显式的游标(CURSORS) 67 7.52 优化EXPORT和IMPORT 67 7.53 分离表和索引 68 前言 要对数据库进行优化，首先要从数据库的设计方面入手，包含数据的概念模型，逻辑模型，物理模型（设计对以后的优化维护起决定性的作用，好的设计是优化维护的决定性条件）；所以：在设计阶段一定要确定好系统的模型构架，逻辑构架，硬件构架等，尽量做到软件和硬件的无缝结合；维护只是对程序的处理流程和相关的SQL进行调整，而不会