SQL及应用程序优化_SQL及应用程序优化培训.doc

SQL及应用程序优化培训 一、 Oracle相关知识 3 1. 两种连接Oracle的模式 3 2. 文件类型 3 3. 存储结构 3 4. 内存结构简介 3 5. 锁 4 6. Undo和Redo 4 二、 SQL知识及优化经验 4 1. SQL语句处理过程 4 1) SQL语句处理的四个基本步骤 4 2) SQL语句分析的四种情况 5 2. 绑定变量 5 1) 使用绑定变量的好处 5 2) 哪些是可以共享的SQL语句 6 3) ZLHIS中使用绑定变量的方法 6 4) ZLHIS中使用绑定变量的注意事项 6 3. 优化器 7 1) 优化器模式 7 2) RBO与CBO的优缺点 7 3) RBO主要规则 8 4) CBO成本计算规则 8 5) CBO相关知识 9 6) Hints 9 4. 执行计划相关知识 10 5. 数据访问路径 11 1) 全表扫描 11 2) 通过RowID访问 11 3) 索引扫描 11 4) 群扫描 12 6. 表间连接方式 12 1) 嵌套循环 12 2) 散列连接 13 3) 排序合并连接 13 4) 笛卡儿连接 13 5) 反连接 13 6) 全外部连接 13 7. 分析函数 14 8. SQL优化经验 14 三、 SQL分析诊断方法和工具 15 1. AutoTrace使用介绍 15 1) 创建执行计划环境 15 2) 创建AutoTrace环境 16 3) 使用AutoTrace 16 2. SQL Trace的几种方法 16 1) SQL_Trace 16 2) 事件跟踪 16 3) SQL Trace的几种工具 16 3. ZL SQLTRACE工具使用介绍 17 4. Trace文件分析 17 5. StatsPack查看与分析简介 17 四、 应用程序优化经验 17 1. ZLHIS优化历程回顾与总结 17 2. 应用程序优化原则 18 本次培训的背景 医大二院性能诊断过程中发现应用程序中较多SQL存在性能问题和优化空间。 目前已知的反映过ZLHIS存在性能问题的用户： 医大二院，遵义医院，广西容县人民医院，重庆市三院 仅介绍SQL调优涉及的Oracle相关知识，不要求深入理解。 连接Oracle的模式 专用服务器共享服务器 MTS具有以下一些缺点： 共享服务器的代码路径比专用服务器长，所以它天生就比专用服务器慢。 存在人为死锁的可能，因为它是串行的，只要一个连接阻塞，则该服务器进程上的所有用户都被阻塞，并且极可能死锁。 存在独占事务的可能，因为如果一个会话的事务运行时间过长，它独占共享资源，其它用户只能等待，而专用服务器，每个客户端是一个会话。 共享服务器模式限制了某些数据库特性，例如：不能单独启动和关闭实例，不能进行介质恢复，不能使用Log Miner，并且SQL_TRACE没有意义(因为是共享而不是当前会话的)。 MTS减少的内存实际上是专用服务器模式下每个用户连接到操作系统进程所需的内存，但它却使用SGA的Large_Pool来分配UGA，拆东墙补西墙，所减少的内存是很少的。 如果用户会话的连接和断开很频繁，数据库进程的创建和删除的开销会非常大，这种情况最好采用共享服务器模式(否则，应该使用连接池技术)。 如果客户端一次连接终身使用(会话生命周期内)，使用共享服务器模式的意义不大。因为大部分时间，一个会话就连接到一个服务器进程，无法共享服务器进程。 文件类型 参数文件，跟踪文件，警告文件，数据文件，临时文件，控制文件，日志文件，密码文件，修改跟踪文件，数据泵文件，DMP文件，闪回日志文件，平面文件 存储结构 逻辑结构：表空间，段，区，块 内存结构 SGA： 日志缓冲区 每3秒一次，事务提交，切换日志文件，缓冲区1/3满或达1MB 数据块缓冲区 多池，多块尺寸，LRU、LRUW和接触计数 散列存储桶，散列链，散列锁存器 共享池 字典缓冲区（Data dictionary cache），包括关于数据库文件、表、索引、列、用户、权限以及其它数据库对象的信息。 在语法分析阶段，Server Process访问数据字典中的信息以解析对象名和对存取操作进行验证。将数据字典信息缓存在内存中有助于缩短响应时间。 库高速缓冲区（Library cache ），包括游标，SQL和PL/SQL语句，执行计划 共享池的管理 LRU算法，FreeList管理Free块，Bucket与Chunk 共享池过大带来的问题，绑定变量与共享池 大池 回收型的内存空间，用途： 共享服务器模式时分配UGA，并行查询的消息缓冲，RMAN缓冲区 Java池 专用服务器：每个JAVA类的共享部分 共享服务器：每个JAVA类的共享部分，UGA中的会话状态部分 PGA： workarea_size_policy 与