Oracle 运维最佳实践-下
数据库运维
Kinghow@hotmail.com
第 1 页(共 4313 页)
Oracle 运维最佳实践
下
（V3.0.1）
Kinghow
Oracle 运维最佳实践-下
数据库运维
Kinghow@hotmail.com
第 2 页(共 4313 页)
目录
1 概述.........................................................................................................................11
2 性能........................................................................................................................ 12
2.1 Oracle ....................................................................................................... 12
2.1.1 AW R .................................................................................................. 12
2.1.2 SPA...................................................................................................160
2.1.1 性能问题诊断案例.............................................................................197
2.1.2 Oracle 性能调整的十大要点 ...............................................................304
2.1.3 Session Snappe...................................................................................323
2.1.4 query optimizer（查询优化器） ..........................................................337
2.1.5 可以 nologging 执行的操作 ................................................................341
2.1.6 可以并行化执行的 SQL 操作 .............................................................342
2.1.7 检测 CPU 计算能力 ...........................................................................343
2.1.8 IO 基准测试 ......................................................................................345
2.1.9 一些能提高性能的参数......................................................................346
2.1.10 查看 SQL PROFILE 使用的 HINT.......................................................355
2.1.11 查看哪些 SQL 使用了动态采样..........................................................355
2.1.12 oracle aio 异步 IO..............................................................................356
2.1.13 optimize connection performance..........................................................358
2.1.14 DBMS_AD VAN CED_ REWRITE ........................................................366
2.1.15 Histogram Investigation.......................................................................370
2.1.16 io 优化--db_writer_processes & dbwr_io_slaves 对比.............................374
2.1.17 Oracle 技术支持是如何分析数据库性能问题的 ...................................376
2.1.18 针对性能问题的主动型数据收集........................................................378
2.1.19 主动避免数据库和查询相关的性能问题 .............................................384
2.1.20 如何分析发生在过去的数据库性能问题 .............................................391
2.1.21 索引重建的必要性与影响 ..................................................................392
2.1.22 研究 b-tree 索引结构的脚本 .............................................................393
2.1.23 为何在查询中索引未被使用...............................................................402
2.1.24 Shared Pool 优化和 Library Cache Latch 冲突优化 ...............................412
2.1.25 Library Cache 诊断：Lock, Pin 以及 Load Lock .................................424
2.1.26 Real Application Testing ......................................................................429
2.1.27 Master Note: Database Performance Overview .......................................489
2.1.28 How to Log a Good Performance Service Request ..................................497
2.1.29 Troubleshooting Performance Issues .....................................................501
2.1.30 How to use the Automatic Database Diagnostic Monitor ..........................506
2.1.31 How to Collect Errorstacks for use in Diagnosing Performance Issues. ......514
2.1.32 How to Investigate Slow or Hanging Database Performance Issues ...........516
2.1.33 Best Practices: Proactive Data Collection for Performance Issues..............520
Oracle 运维最佳实践-下
数据库运维
Kinghow@hotmail.com
第 3 页(共 4313 页)
2.1.34 Best Practices: Proactively Avoiding Database and Query Performance Issues
527
2.1.35 How to Gather Optimizer Statistics on 11g .............................................533
2.1.37 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch
Contention .......................................................................................................548
2.1.38 How to Collect Standard Information for a Database Performance Problem567
2.1.39 How to Gather Statistics on SYS Objects and 'Fixed' Objects? ..................571
2.1.40 Data Dictionary View Queries are Slow - Potential Solutions....................572
2.1.41 Query Against Dictionary Views Extracting Tablespace Information is Slow or
Appears to Hang ...............................................................................................575
2.1.42 Query Joining V$SESSION And V$LOCK Is Hanging............................578
2.1.43 Select COUNT(*) from DBA_FREE_SPACE Hangs...............................579
2.1.44 Query Hangs after Re-parsing...............................................................581
2.1.45 Query Hangs When Accessing a Remote Database via a Database Link .....582
2.1.46 Query Involving Bitmap Conversion Appears to Hang .............................583
2.1.47 How to Collect Errorstacks for use in Diagnosing Performance Issues. ......584
2.1.48 Optimizer Dynamic Statistics (OPTIMIZER_DYNAMIC_SAMPLING) ...586
2.1.49 Different Level for Dynamic Statistics (Dynamic Sampling) used than the Level
Specified 588
2.1.50 Automatic Dynamic Statistics...............................................................590
2.1.51 System Statistics: Scaling the System to Improve CBO optimizer .............592
2.1.52 How to Collect and Display System Statistics (CPU and IO) for CBO use..597
2.1.53 Troubleshooting: 'Log file sync' Waits ...................................................607
2.1.54 LGWR consumes 100% CPU ...............................................................619
2.1.55 Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)..........622
2.1.56 WAITEVENT: "log file sync" Reference Note ........................................631
2.1.57 High Waits for 'Log File Sync': Known Issue Checklist for 11.2 ................636
2.1.58 Adaptive Log File Sync Optimization....................................................639
2.1.59 LOG FILE SYNC WAITS SPIKES DURING RMAN ARCHIVELOG
BACKUPS ......................................................................................................642
2.1.60 Adaptive Switching Between Log Write Methods can Cause 'log file sync' Waits
643
2.1.61 Troubleshooting I/O Related Waits ........................................................646
2.1.62 How to Tell if the I/O of the Database is Slow ........................................664
2.1.63 Checkpoint Tuning and Troubleshooting Guide ......................................671
2.1.64 * FAQ: Database Performance Frequently Asked Questions .....................677
2.1.65 SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and
'OPEN_CURSORS' Parameters Based on Usage...................................................689
2.1.66 How To Track The Changes That May Affect Database Performance .........694
2.2 系统.........................................................................................................698
2.2.1 NMON ..............................................................................................698
2.2.2 交换空间设置调优.............................................................................716
3 监控.......................................................................................................................723
Oracle 运维最佳实践-下
数据库运维
Kinghow@hotmail.com
第 4 页(共 4313 页)
3.1 Oracle ......................................................................................................723
3.1.1 Get Proactive - Oracle Health Checks - Installation, troubleshooting, catalog
and more.723
3.1.2 alert.log .............................................................................................736
3.1.3 检查 Oracle 数据库环境 .....................................................................745
3.1.4 简单显示数据库性能脚本 ..................................................................750
3.1.5 PLATO .............................................................................................786
3.1.6 MOATS .............................................................................................787
3.1.7 SQL 调优健康检查脚本 .....................................................................819
3.1.8 11g 新特性 Health Monitor..................................................................887
3.1.9 11g New Feature: Health monitor .........................................................889
3.1.10 11gR2 新特性：Oracle Cluster Health Monitor(CHM) ..........................894
3.1.11 如何安装独立版的 CHM（Oracle Cluster Health Monitor） ..................898
3.1.12 Introducing Cluster Health Monitor (IPD/OS) ........................................903
3.1.13 Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware
Processes907
3.1.14 How To Troubleshoot Database Contention With Procwatcher ..................915
3.1.15 oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single
Instance 929
3.1.16 分析监听器日志 ................................................................................932
3.1.17 数据库最近的性能度量......................................................................934
3.1.18 监控 Oracle 每小时的 redo 重做日志产生量 ........................................962
3.1.19 收集 11g Oracle 实例 IO 性能信息 ......................................................965
3.1.20 监控一个大事务的回滚......................................................................968
3.1.21 监控当前重做日志文件使用情况........................................................972
3.1.22 Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over
Time 973
3.1.23 列出数据库内的排序活跃性能信息并监控临时空间的使用 .................980
3.1.24 列出 Oracle 日志文件切换的频率图....................................................992
3.1.25 汇总表空间使用情况报告 ..................................................................994
3.1.26 列出 Oracle 中数据文件的状况...........................................................998
3.1.27 收集数据库安全风险评估信息 ......................................................... 1000
3.1.28 监控并行进程状态........................................................................... 1009
3.1.29 监控数据库中的活跃用户及其运行的 SQL ....................................... 1012
3.1.30 监控临时表空间使用率.................................................................... 1014
3.1.31 显示活跃的分布式事务.................................................................... 1014
3.1.32 Report Information on Indexes ........................................................... 1018
3.1.33 Detect Tablespace Fragmentation ........................................................ 1024
3.1.34 监控表使用率.................................................................................. 1025
3.1.35 监控索引使用.................................................................................. 1028
3.1.36 查询重复的索引列........................................................................... 1032
3.1.37 sqlplus 下的 RAC dashboard ............................................................. 1034
3.1.38 Web ASH......................................................................................... 1036
Oracle 运维最佳实践-下
数据库运维
Kinghow@hotmail.com
第 5 页(共 4313 页)
3.1.39 oramon ............................................................................................ 1037
3.1.40 数据库 io 数据采集.......................................................................... 1038
3.1.41 空间增长监控.................................................................................. 1043
3.1.42 如何执行对数据库的健康状况检查 .................................................. 1048
3.1.43 数据库连接监控 .............................................................................. 1060
3.1.44 RMAN 监控 .................................................................................... 1061
3.1.45 "hcheck.sql" script to check for known problems in Oracle8i, Oracle9i,
Oracle10g and Oracle 11g ................................................................................ 1062
3.1.46 Introduction to the "H*" Helper Scripts................................................ 1065
3.1.47 Script to Install the "hOut" Helper Package ("hout.sql") ......................... 1070
3.2 系统....................................................................................................... 1076
3.2.1 cfg2html .......................................................................................... 1076
3.2.2 Linux 系统检查 ............................................................................... 1076
3.2.3 AIX 系统检查.................................................................................. 1078
3.2.4 Linux 性能监控............................................................................... 1091
4 审计..................................................................................................................... 1109
4.1 Oracle .................................................................................................... 1109
4.1.1 Oracle 审计 ..................................................................................... 1109
4.1.2 11g 默认审计选项............................................................................ 1135
4.1.3 远程登录和口令认证 ....................................................................... 1137
4.1.4 无审计环境追踪 DELETE 操作 ........................................................ 1138
4.1.5 无审计环境中追踪 Truncate/Drop 等危险的 DDL 操作 ...................... 1140
4.1.6 LogMiner 分析日志.......................................................................... 1142
4.1.7 oracle10g 监听加密 .......................................................................... 1147
4.1.8 限定默认账号使用 SQL Plus 访问数据库 .......................................... 1149
4.1.9 ORACLE 数据传输加密................................................................... 1149
4.1.10 Oracle 数据库安全性解决方案 ......................................................... 1153
4.1.11 安全性需求及解决策略.................................................................... 1156
4.1.12 安全性评估 ..................................................................................... 1160
4.1.13 ............................................................................................................ 1171
4.1.14 透明数据加密常见问题解答............................................................. 1171
4.1.15 Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks
1181
4.1.16 Security Vulnerability FAQ for Oracle Database and Fusion Middleware
Products 1186
4.1.17 All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit,
Encryption, OLS, Database Vault, Audit Vault .................................................... 1197
5 诊断..................................................................................................................... 1209
5.1 Oracle .................................................................................................... 1209
5.1.1 Oracle 诊断工具 .............................................................................. 1215
5.1.2 弹性特性......................................................................................... 1644
5.1.3 Oracle 11g 诊断新特性——ADR 简介 ............................................. 1665
