Oracle运维最佳实践-下.pdf 带书签

### Oracle运维最佳实践知识点梳理 #### 一、概述 **Oracle运维最佳实践**是一套针对Oracle数据库维护和管理的专业指南，旨在帮助运维人员提升数据库的性能、稳定性和安全性。本书全面覆盖了Oracle数据库运维中的各个方面，包括但不限于性能监控、问题诊断、参数调优等。 #### 二、性能 ##### 2.1 Oracle - **2.1.1 AWR（自动工作负载资料库）** - AWR是Oracle提供的一个用于收集和存储系统性能信息的工具，可以帮助运维人员了解系统的运行状态和资源使用情况。 - AWR报告提供了关于数据库实例性能的重要信息，包括等待事件统计、SQL执行统计等，是进行性能问题诊断的基础。 - 实例： - 使用`DBMS_WORKLOAD_REPOSITORY.SNAPSHOT`来手动创建AWR快照。 - 使用`DBMS_WORKLOAD_REPOSITORY`包来查询AWR数据，如`DBMS_WORKLOAD_REPOSITORY.REPORT_SQL_MONITOR`函数可以生成特定SQL语句的性能报告。 - **2.1.2 SPA（SQL Performance Analyzer）** - SPA是一个强大的工具，用于评估SQL语句在不同环境下的性能变化，特别适用于版本升级前后的性能比较。 - SPA通过模拟SQL在旧版本上的执行情况来预测新版本中的性能差异，有助于提前发现潜在问题。 - 实例： - 使用`DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE`加载计划到SPM仓库。 - 使用`DBMS_SPM.SET_GLOBAL_PARAMS`设置全局参数，如`ENABLED=TRUE`启用SPA。 - **2.1.1 性能问题诊断案例** - 本书通过一系列实际案例展示了如何使用Oracle提供的工具和技术来解决常见的性能问题。 - 案例涵盖了SQL优化、索引调整、内存配置等多个方面，每个案例都详细记录了问题的症状、诊断过程和解决方案。 - 实例： - 分析`v$session_wait`视图以确定导致性能瓶颈的等待事件。 - 使用`DBMS_SQL_MONITOR`跟踪SQL执行情况，帮助定位问题。 - **2.1.2 Oracle性能调整的十大要点** - 本书总结了Oracle性能调整的关键点，涵盖了硬件配置、软件设置、SQL优化等方面。 - 十大要点包括但不限于：正确配置SGA/RGA大小、合理设置并发级别、使用高效的索引策略等。 - 实例： - 调整`SHARED_POOL_SIZE`以减少Library Cache latch竞争。 - 使用`DBMS_STATS`定期收集统计信息，确保CBO（Cost-Based Optimizer）做出准确的决策。 - **2.1.3 Session Snap** - Session Snap是一种性能监控工具，它记录了会话级别的活动信息，有助于深入了解会话行为。 - Session Snap可用于分析特定会话的性能问题，如响应时间异常、频繁的重做日志写入等。 - 实例： - 使用`DBMS_MONITOR.SESSION_MONITOR`来启用会话监控。 - 查询`v$mystat`和`v$statname`视图获取会话的实时统计数据。 - **2.1.4 Query Optimizer（查询优化器）** - 查询优化器负责选择最优的执行计划，对SQL性能至关重要。 - 本书介绍了如何使用统计信息、提示、成本模型等手段来影响优化器的行为。 - 实例： - 使用`USE_HASH`、`INDEX`等提示来指导优化器选择特定类型的执行计划。 - 通过`DBMS_STATS.SET_TABLE_PREFS`设置特定表的统计偏好，影响优化器决策。 - **2.1.5 可以nologging执行的操作** - 在某些情况下，为了提高性能或满足特定需求，可以允许某些操作以nologging模式执行。 - 例如，对于大数据量的导入导出操作，可以考虑采用nologging方式减少日志开销。 - 实例： - 使用`ALTER TABLE...NOLOGGING`创建无日志记录的表空间。 - 执行`INSERT...SELECT...NOLOGGING`来插入数据时禁用日志记录。 - **2.1.6 可以并行化执行的SQL操作** - 并行查询可以显著提高处理大量数据时的性能。 - 本书列出了可以并行化的操作类型，如全表扫描、索引扫描等，并提供了实现并行查询的方法。 - 实例： - 设置`PARALLEL`参数来指定并行度，如`SELECT /*+ PARALLEL(8) */ ...`。 - 使用`DBMS_PARALLEL_EXECUTE`包来执行并行任务。 - **2.1.7 检测CPU计算能力** - 了解系统的CPU计算能力对于合理配置Oracle数据库非常重要。 - 本书提供了评估CPU性能的方法，如使用`v$system_event`视图来监控CPU利用率。 - 实例： - 分析`v$system_event`中的`CPU usage by sessions`事件以识别CPU瓶颈。 - 使用`v$resource_limit`视图来检查资源限制。 - **2.1.8 IO基准测试** - IO性能直接影响数据库的响应速度，因此进行IO基准测试对于优化存储架构至关重要。 - 本书介绍了如何进行IO测试，包括使用Oracle自带的工具和其他第三方工具。 - 实例： - 使用`DBMS_IO`包来模拟I/O负载，评估磁盘性能。 - 使用第三方工具如`IOzone`来测试磁盘读写速度。 - **2.1.9 一些能提高性能的参数** - 正确配置Oracle参数对于提高性能至关重要。 - 本书列出了多个可以调整的参数，包括内存池大小、进程数量等，并给出了推荐值。 - 实例： - 调整`DB_CACHE_SIZE`以优化缓冲区缓存大小。 - 设置`DB_WRITER_PROCESSES`和`DBWR_IO_SLAVES`来优化IO操作。 - **2.1.10 查看SQL PROFILE使用的HINT** - SQL PROFILE包含了执行计划优化的信息，使用HINT可以帮助优化SQL执行效率。 - 本书介绍了如何查看和使用这些HINT来改进SQL性能。 - 实例： - 使用`DBMS_XPLAN.DISPLAY_CURSOR`显示带有HINT的执行计划。 - 使用`DBMS_SQLTUNE.RECOMMEND_PROFILE`推荐适合的HINT。 - **2.1.11 查看哪些SQL使用了动态采样** - 动态采样是Oracle的一种技术，用于收集额外的统计信息来改善执行计划的选择。 - 本书解释了如何识别使用了动态采样的SQL语句，并提供了解决方案。 - 实例： - 使用`DBMS_XPLAN.DISPLAY_CURSOR`显示执行计划，查找`dynamic sampling`关键字。 - 使用`DBMS_STATS.SET_DYNAMIC_SAMPLING`设置动态采样的级别。 - **2.1.12 Oracle AIO（异步IO）** - 异步IO是一种高级技术，可以提高数据库的I/O性能。 - 本书详细介绍了如何配置和使用Oracle AIO功能。 - 实例： - 使用`ALTER DATABASE...SET FILE...IO_TYPE=AIO`将文件配置为使用AIO。 - 使用`DBMS_AIO`包来管理和监控AIO操作。 - **2.1.13 Optimize Connection Performance** - 优化连接性能对于提高多用户环境下的响应速度非常重要。 - 本书提供了多种方法来提高连接性能，包括使用连接池、调整会话参数等。 - 实例： - 使用`DBMS_RESOURCE_MANAGER`来管理会话资源。 - 设置`OPEN_CURSORS`和`SESSIONS`参数来控制会话数量。 - **2.1.14 DBMS_ADVANCED_REWRITE** - `DBMS_ADVANCED_REWRITE`是Oracle提供的一个用于优化查询重写的API。 - 本书详细介绍了如何使用此包来重写SQL查询，从而提高性能。 - 实例： - 使用`DBMS_ADVANCED_REWRITE.REWRITE_QUERY`函数重写SQL语句。 - 使用`DBMS_ADVANCED_REWRITE.CACHE_PLAN`将重写后的计划缓存起来。 - **2.1.15 Histogram Investigation** - 统计直方图是用于提高CBO性能的重要工具之一。 - 本书解释了如何分析直方图数据，并根据需要调整直方图设置。 - 实例： - 使用`DBMS_STATS.GET_TABLES_STATS`来获取表的统计信息。 - 使用`DBMS_STATS.MODIFY_COLUMN_STATISTICS`修改特定列的直方图设置。 - **2.1.16 IO优化--db_writer_processes & dbwr_io_slaves对比** - 本书对比了`db_writer_processes`和`dbwr_io_slaves`两个参数的区别及其对IO性能的影响。 - 通过实例展示了如何根据实际情况调整这两个参数以达到最优性能。 - 实例： - 设置`db_writer_processes`为较小值，以减少竞争。 - 设置`dbwr_io_slaves`为较高值，以加速DBWR进程的工作。 - **2.1.17 Oracle技术支持是如何分析数据库性能问题的** - 本书详细介绍了Oracle官方技术支持团队在分析性能问题时所采用的方法和技术。 - 包括使用ADDM（自动数据库诊断监视器）、AWR报告等工具来进行深入分析。 - 实例： - 使用`DBMS_ADVISOR`包来生成性能建议报告。 - 使用`DBMS_ADVISOR.TUNING_ADVISOR`调优顾问来自动调优。 - **2.1.18 针对性能问题的主动型数据收集** - 本书强调了主动收集性能数据的重要性，尤其是在出现性能问题之前。 - 提供了一系列策略和工具，如ADDM、AWR快照等，来帮助运维人员提前发现问题。 - 实例： - 定期创建AWR快照，使用`DBMS_WORKLOAD_REPOSITORY.SNAPSHOT`。 - 使用`DBMS_MONITOR.SESSION_MONITOR`来监控关键会话。 - **2.1.19 主动避免数据库和查询相关的性能问题** - 本书提供了预防性措施来避免常见的性能问题。 - 包括合理设计数据库架构、定期维护索引等策略。 - 实例： - 使用`DBMS_STATS`定期收集统计信息，保持统计信息最新。 - 使用`DBMS_SPACE`管理空间，避免表空间填满。 - **2.1.20 如何分析发生在过去的数据库性能问题** - 本书介绍了如何利用历史数据来诊断已经发生的性能问题。 - 包括使用AWR报告、ASH（Active Session History）数据等。 - 实例： - 使用`DBMS_WORKLOAD_REPOSITORY.REPORT_SQL_MONITOR`生成SQL监控报告。 - 使用`DBMS_HISTOGRAM`分析直方图数据。 - **2.1.21 索引重建的必要性与影响** - 索引是提高查询性能的关键因素之一。 - 本书详细解释了何时以及如何重建索引来提高性能。 - 实例： - 使用`ALTER INDEX...REBUILD`命令重建索引。 - 使用`DBMS_STATS.GATHER_INDEX_STATS`重新收集索引统计信息。 - **2.1.22 研究B-Tree索引结构的脚本** - B-Tree索引是Oracle中最常用的索引类型之一。 - 本书提供了一个脚本来展示B-Tree索引的内部结构。 - 实例： - 使用自定义脚本`show_btree_index_structure.sql`来可视化B-Tree索引。 - 使用`DBMS_IND`包来获取索引的详细信息。 - **2.1.23 为何在查询中索引未被使用** - 当索引未被查询优化器使用时，可能会导致性能下降。 - 本书解释了常见的原因，并提供了应对策略。 - 实例： - 使用`DBMS_XPLAN.DISPLAY_CURSOR`显示执行计划，检查是否使用了索引。 - 使用`DBMS_STATS`重新收集索引统计信息，确保准确性。 - **2.1.24 Shared Pool优化和Library Cache Latch冲突优化** - Shared Pool是SGA的一部分，对性能有很大影响。 - 本书介绍了如何优化Shared Pool，以及如何减少Library Cache Latch的竞争。 - 实例： - 使用`DBMS_SHARED_POOL.PURGE`清除Shared Pool中的对象。 - 使用`DBMS_SHARED_POOL.KEEP`将关键SQL语句保留在Shared Pool中。 - **2.1.25 Library Cache诊断：Lock, Pin以及Load Lock** - Library Cache是Shared Pool的一个重要组成部分。 - 本书详细介绍了如何诊断Library Cache中的锁问题。 - 实例： - 使用`v$librarycache`视图来监控Library Cache的状态。 - 使用`v$session_wait`视图来识别长时间的等待事件。 - **2.1.26 Real Application Testing** - Real Application Testing (RAT) 是一种模拟真实应用负载的技术。 - 本书介绍了如何使用RAT来评估系统在生产环境下的性能。 - 实例： - 使用`DBMS_RAT`包来配置和执行测试场景。 - 使用`DBMS_RAT.REPORT`生成性能测试报告。 - **2.1.27 Master Note: Database Performance Overview** - 本书提供了一个关于数据库性能的整体概览。 - 概览涵盖了性能问题的根本原因分析、诊断流程等。 - 实例： - 使用`DBMS_ADVISOR.TUNING_ADVISOR`来生成性能调优建议。 - 使用`DBMS_ADVISOR.DATABASE_ADVISOR`来评估整体性能状况。 - **2.1.28 How to Log a Good Performance Service Request** - 当需要向Oracle技术支持提交性能问题时，如何有效地记录问题是关键。 - 本书提供了详细的指南来帮助用户编写有效的服务请求。 - 实例： - 记录问题发生的时间、频率等信息。 - 提供AWR报告、SQL执行计划等支持材料。 - **2.1.29 Troubleshooting Performance Issues** - 本书提供了一套完整的性能问题排查流程。 - 包括识别问题、收集数据、分析问题等步骤。 - 实例： - 使用`DBMS_MONITOR`来监控会话活动。 - 使用`DBMS_WORKLOAD_REPOSITORY`来收集AWR数据。 - **2.1.30 How to Use the Automatic Database Diagnostic Monitor** - ADDM是Oracle提供的一个自动化诊断工具。 - 本书介绍了如何使用ADDM来分析数据库性能问题。 - 实例： - 使用`DBMS_ADVISOR.REPORT_ADM`生成ADDM报告。 - 使用`DBMS_ADVISOR.ANALYZE_ADM`执行ADDM分析。 - **2.1.31 How to Collect Error Stacks for Use in Diagnosing Performance Issues** - 收集错误堆栈对于诊断复杂的问题非常有帮助。 - 本书详细介绍了如何收集和使用这些错误堆栈。 - 实例： - 使用`DBMS_SUPPORT.COLLECT_ERROR_STACK`收集错误堆栈。 - 使用`DBMS_SUPPORT.ERROR_STACK`查看错误堆栈信息。 - **2.1.32 How to Investigate Slow or Hanging Database Performance Issues** - 本书提供了如何调查缓慢或挂起的性能问题的方法。 - 包括使用`v$session_wait`、`v$process`等视图来定位问题。 - 实例： - 使用`DBMS_SUPPORT.INVESTIGATE_PERFORMANCE_ISSUE`来调查性能问题。 - 使用`DBMS_SUPPORT.PERFORMANCE_ISSUE_REPORT`生成性能问题报告。 - **2.1.33 Best Practices: Proactive Data Collection for Performance Issues** - 本书总结了一系列最佳实践来指导运维人员如何进行主动的数据收集。 - 包括创建定期的AWR快照、收集ASh数据等。 - 实例： - 使用`DBMS_WORKLOAD_REPOSITORY.SCHEDULE_SNAPSHOT`安排AWR快照。 - 使用`DBMS_MONITOR.SESSION_MONITOR`监控关键会话。 - **2.1.34 Best Practices: Proactively Avoiding Database and Query Performance Issues** - 本书进一步阐述了如何主动避免数据库和查询相关的性能问题。 - 包括合理的数据库设计、定期维护索引等。 - 实例： - 使用`DBMS_STATS`定期更新统计信息。 - 使用`DBMS_SPACE`管理空间，防止表空间填满。 - **2.1.35 How to Gather Optimizer Statistics on 11g** - 对于Oracle 11g版本，本书提供了如何收集优化器统计信息的方法。 - 这些统计信息对于优化查询计划至关重要。 - 实例： - 使用`DBMS_STATS.GATHER_TABLE_STATS`收集表统计信息。 - 使用`DBMS_STATS.GATHER_INDEX_STATS`收集索引统计信息。 - **2.1.37 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention** - 本书详细介绍了如何调整Shared Pool和Library Cache Latch，以减少竞争。 - 包括调整`SHARED_POOL_SIZE`、使用`DBMS_SHARED_POOL.KEEP`等。 - 实例： - 使用`DBMS_SHARED_POOL.PURGE`清理Shared Pool。 - 使用`DBMS_SHARED_POOL.KEEP`保持关键SQL在Shared Pool中。 - **2.1.38 How to Collect Standard Information for a Database Performance Problem** - 当遇到性能问题时，本书提供了如何收集标准信息的方法。 - 包括AWR报告、SQL执行计划等。 - 实例： - 使用`DBMS_WORKLOAD_REPOSITORY.REPORT_AWR`生成AWR报告。 - 使用`DBMS_XPLAN.DISPLAY_CURSOR`显示SQL执行计划。 - **2.1.39 How to Gather Statistics on SYS Objects and 'Fixed' Objects?** - 对于SYS对象和固定对象，本书提供了如何收集统计信息的方法。 - 这些统计信息对于优化器做出正确的决定至关重要。 - 实例： - 使用`DBMS_STATS.GATHER_SCHEMA_STATS`收集模式统计信息。 - 使用`DBMS_STATS.GATHER_TABLE_STATS`收集特定表的统计信息。 - **2.1.40 Data Dictionary View Queries are Slow - Potential Solutions** - 本书探讨了为什么查询数据字典视图可能会变慢的原因，并提供了解决方案。 - 实例： - 使用`DBMS_STATS.GATHER_TABLE_STATS`更新视图统计信息。 - 使用`DBMS_STATS.GATHER_INDEX_STATS`更新索引统计信息。 - **2.1.41 Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang** - 查询数据字典视图以获取表空间信息可能会变慢或挂起。 - 本书提供了如何解决这类问题的方法。 - 实例： - 使用`DBMS_STATS.GATHER_TABLE_STATS`更新统计信息。 - 使用`DBMS_STATS.GATHER_INDEX_STATS`更新索引统计信息。 - **2.1.42 Query Joining V$SESSION And V$LOCK Is Hanging** - 当查询`v$session`和`v$lock`视图时，可能会出现挂起的情况。 - 本书提供了如何解决这个问题的方法。 - 实例： - 使用`DBMS_STATS.GATHER_TABLE_STATS`更新统计信息。 - 使用`DBMS_STATS.GATHER_INDEX_STATS`更新索引统计信息。 - **2.1.43 Select...** - 由于文档内容被截断，无法提供更多关于这一节的具体信息。这部分可能涉及到更多具体的查询优化技巧或者案例分析。