### Oracle Database 11g: SQL优化 - 课堂练习
#### 一、了解Oracle DB体系结构
**课程目标**
- 理解Oracle DB服务器体系结构及其组件。
- 掌握Oracle DB内存结构的关键概念。
- 了解Oracle DB后台进程的作用。
- 学习自动内存管理机制。
**Oracle DB服务器体系结构：概览**
Oracle DB服务器采用多层架构设计，包括内存结构、进程管理和存储结构等。其中，内存结构主要分为SGA(共享全局区)和PGA(程序全局区)两大部分。
**连接到数据库实例**
连接到Oracle DB实例通常通过监听器实现，客户端发送请求后，由监听器将请求转发至相应的服务器进程。此外，还可以通过直接连接的方式进行访问。
**Oracle DB内存结构：概览**
- **数据库缓冲区高速缓存**: 用于存储最近访问过的数据块，提高数据读取速度。
- **重做日志缓冲区**: 存储未提交事务产生的重做记录。
- **共享池**: 包含共享SQL区域、库缓存等，用于存储SQL语句及解析结果，支持SQL重用。
- **大型池**: 可选内存区，用于存储大对象和其他数据结构。
- **Java池**: 用于存储Java虚拟机运行时环境及相关数据。
- **流池**: 支持流处理功能。
- **程序全局区(PGA)**: 每个用户会话都拥有独立的PGA，用于存储临时数据和工作区。
**后台进程的作用**
Oracle DB服务器通过一系列后台进程来支持其正常运行，例如PMON(进程监控)、SMON(系统监控)、DBWn(数据库写入器)等。
**自动共享内存管理**
从Oracle 11g开始，引入了ASM(自动共享内存管理)，允许数据库动态调整SGA和PGA大小，以适应不同的工作负载需求。
**自动化的SQL执行内存管理**
通过自动调整SQL执行过程中所需的内存资源，确保高效执行。
**自动内存管理**
提供了一种机制，可以在SGA和PGA之间自动分配内存，以及在SGA内部的不同内存组件之间动态调整内存。
**数据库存储体系结构**
- **逻辑和物理数据库结构**: 包括表空间、数据文件、段、区和数据块等。
- **段、区和块**: 数据在磁盘上的组织方式，每个段包含多个区，每个区又由多个数据块组成。
- **SYSTEM和SYSAUX表空间**: 分别用于存储系统元数据和辅助数据。
#### 二、SQL优化简介
**课程目标**
- 识别导致SQL性能不佳的原因。
- 学习使用Oracle提供的性能监视和优化工具。
- 理解SQL优化的基本原则和技术。
**SQL性能不高的原因**
- 不合理的SQL编写方式。
- 缺乏索引或索引使用不当。
- 不适当的数据库配置。
- 数据分布不均。
- 高并发导致的竞争。
**低效的SQL：示例**
例如，全表扫描、不必要的排序操作等。
**性能监视解决方案**
Oracle提供了多种工具来帮助监测和诊断SQL性能问题，如AWR(自动工作负载存储库)、ASH(自动采样历史记录)等。
**监视和优化工具：概览**
- **EM性能页**: 提供图形界面来查看性能指标。
- **SQL优化工具**: 如SQL Tuning Advisor(STA)、SQL Access Advisor(SAA)等。
- **其他工具**: 如SQL Trace、TKProf等。
**SQL优化任务：概览**
- 分析SQL执行计划。
- 使用统计信息改进优化器决策。
- 创建或调整索引。
- 调整SQL语句语法。
- 使用分区技术。
**CPU时间和等待时间优化范围**
- CPU时间: 减少不必要的计算操作。
- 等待时间: 减少磁盘I/O和网络延迟。
**可伸缩性与应用程序的设计、实施和配置的关系**
良好的应用程序设计可以显著提高系统的可伸缩性和性能，例如通过减少锁定竞争、使用合适的索引策略等方式。
**客户系统中的常见错误**
- 不当的索引使用。
- 过度规范化或非规范化。
- 缺乏分区。
- 数据类型选择不当。
**主动优化方法**
- 定期审查SQL执行计划。
- 监控性能指标。
- 调整数据库参数。
**应用程序设计的简洁性**
- 简化SQL语句。
- 使用存储过程代替复杂的SQL语句。
**数据建模**
- 合理设计关系模式。
- 适当使用规范化。
**表设计**
- 选择合适的数据类型。
- 使用合适的约束。
**索引设计**
- 根据查询需求选择合适的索引类型。
- 适时更新统计信息。
**使用视图**
- 创建适当的视图简化复杂查询。
- 适时刷新视图内容。
**高效的SQL执行**
- 编写可共享游标的SQL语句。
- 避免不必要的排序和临时表使用。
**编写可共享游标的SQL**
- 使用绑定变量。
- 避免使用动态SQL。
**性能核对清单**
- 审查SQL语句。
- 检查索引使用情况。
- 监控性能指标。
#### 三、优化程序简介
**课程目标**
- 了解SQL语句处理流程。
- 掌握SQL优化器的工作原理。
- 学习SQL优化器中的各种转换规则。
**SQL语句处理：概览**
SQL语句的处理过程主要包括创建游标、分析语句、描述和定义、绑定和并行化等步骤。
**SQL语句处理：步骤**
1. **创建游标**: 为SQL语句分配内存空间。
2. **分析语句**: 解析SQL语法，生成执行计划。
3. **描述和定义**: 为SQL语句中的变量分配内存空间。
4. **绑定和并行化**: 绑定变量值，确定是否使用并行执行。
5. **执行计划生成**: 生成最优的执行路径。
**SQL语句分析：概览**
SQL语句分析过程涉及语法检查、语义验证以及生成执行计划。
**为什么需要优化程序**
- 自动选择最优执行路径。
- 改善SQL性能。
- 减少开发人员的工作量。
**在硬分析操作过程中进行优化**
在SQL语句首次执行时，优化器会对其执行路径进行优化。
**转换器**
- **OR扩展示例**: 将“OR”条件拆分成多个简单的条件。
- **子查询解除嵌套示例**: 将嵌套子查询转化为外部查询。
- **视图合并示例**: 将视图的定义合并到主查询中。
- **谓词推入示例**: 将WHERE子句中的条件推入到子查询中。
- **传递性示例**: 通过传递性简化表达式。
**基于成本的优化程序**
- **评估器**: 评估每个操作的成本。
- **选择性**: 估计返回的行数。
- **基数**: 估计结果集的大小。
- **成本**: 计算总体执行成本。
**计划生成器**
根据评估器的结果，生成最优的执行计划。
以上内容涵盖了Oracle Database 11g SQL优化课程的核心知识点，从数据库体系结构的理解到SQL优化的技术细节，旨在帮助学习者全面掌握Oracle数据库的高级特性。
