如何对数据库进行监控检查
oracle自动工作负载库（AWR）：采集与性能相关的统计数据，并从统计的数据中导出性能量度，以跟踪数据库潜在的问题。
如何生成oracle数据库的自动负载库报告。
手工生成一份oracle数据库的快照：
SQL>execute dbms_workload_repository.create_snapshot();
oracle自动负载库的sql脚本一般位于$ORACLE_HOME/rdbms/admin目录下，文件名为awrrpt.sql,如下图所示：
执行oracle自动工作负载库的sql脚本：
SQL>@?/rdbms/admin/awrrpt.sql
其中“@”表示在oracle的命令窗口中执行SQL脚本，而“？”表示$ORACLE_HOME目录。
根据提示输入自动负载库的类型，默认是html格式，可以输入txt格式。
选择要分析哪天的数据库性能，如果输入1，将会列出当天的数据库快照和对应的时间点，如果输入2，将会列出最近两天的数据库快照和对应的时间点，以此类推。咱们这里输入2，如下图：
选择一个开始和一个结束的快照号，这两个快照号的时间段内数据库不能重启过。
按提示进行操作，生成报告后输入：exit退出数据库。
SQL>exit
使用ftp工具将linux下的报告传到windows下打开。
oracle数据库自动负载报告如下：
WORKLOAD REPOSITORY report for
| DB Name | DB Id | Instance | Inst num | Startup Time | Release | RAC |
| ORCL | 1384228360 | orcl | 1 | 17-Sep-14 11:09 | 11.2.0.1.0 | NO |
| Host Name | Platform | CPUs | Cores | Sockets | Memory (GB) |
| localhost.localdomain | Linux IA (32-bit) | 2 | 2 | 1 | 1.98 |
| Snap Id | Snap Time | Sessions | Cursors/Session | |
| Begin Snap: | 13 | 17-Sep-14 12:00:57 | 27 | 1.6 |
| End Snap: | 14 | 17-Sep-14 13:00:23 | 29 | 1.3 |
| Elapsed: | 59.43 (mins) | |||
| DB Time: | 1.22 (mins) |
Report Summary
Cache Sizes
| Begin | End | |||
| Buffer Cache: | 324M | 324M | Std Block Size: | 8K |
| Shared Pool Size: | 144M | 144M | Log Buffer: | 5,012K |
Load Profile
| Per Second | Per Transaction | Per Exec | Per Call | |
| DB Time(s): | 0.0 | 0.2 | 0.01 | 0.07 |
| DB CPU(s): | 0.0 | 0.0 | 0.00 | 0.01 |
| Redo size: | 737.2 | 7,917.8 | ||
| Logical reads: | 22.2 | 237.9 | ||
| Block changes: | 2.8 | 30.2 | ||
| Physical reads: | 0.2 | 2.5 | ||
| Physical writes: | 0.2 | 2.6 | ||
| User calls: | 0.3 | 3.3 | ||
| Parses: | 2.1 | 22.3 | ||
| Hard parses: | 0.0 | 0.4 | ||
| W/A MB processed: | 0.0 | 0.2 | ||
| Logons: | 0.1 | 0.6 | ||
| Executes: | 3.6 | 38.5 | ||
| Rollbacks: | 0.0 | 0.0 | ||
| Transactions: | 0.1 |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 99.99 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 98.95 | In-memory Sort %: | 100.00 |
| Library Hit %: | 96.22 | Soft Parse %: | 98.22 |
| Execute to Parse %: | 42.02 | Latch Hit %: | 99.99 |
| Parse CPU to Parse Elapsd %: | 100.95 | % Non-Parse CPU: | 92.74 |
Shared Pool Statistics
| Begin | End | |
| Memory Usage %: | 73.00 | 79.85 |
| % SQL with executions>1: | 56.93 | 82.26 |
| % Memory for SQL w/exec>1: | 51.68 | 71.33 |
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
| DB CPU | 15 | 19.90 | |||
| log file sync | 67 | 2 | 23 | 2.09 | Commit |
| db file sequential read | 28 | 0 | 2 | 0.09 | User I/O |
| switch logfile command | 1 | 0 | 38 | 0.05 | Administrative |
| asynch descriptor resize | 7,534 | 0 | 0 | 0.03 | Other |
Host CPU (CPUs: 2 Cores: 2 Sockets: 1)
| Load Average Begin | Load Average End | %User | %System | %WIO | %Idle |
| 0.05 | 0.00 | 0.3 | 0.2 | 0.6 | 95.2 |
Instance CPU
| %Total CPU | %Busy CPU | %DB time waiting for CPU (Resource Manager) |
| 0.3 | 7.3 | 0.0 |
Memory Statistics
| Begin | End | |
| Host Mem (MB): | 2,026.8 | 2,026.8 |
| SGA use (MB): | 484.0 | 484.0 |
| PGA use (MB): | 49.4 | 53.8 |
| % Host Mem used for SGA+PGA: | 26.32 | 26.53 |
oracle数据库的自动诊断工具（ADDM）
oracle数据库自动诊断报告脚本一般位于$ORACLE_HOME/rdbms/admin/目录下，文件名为addmrpt.sql
如何生成一个oracle数据库自动诊断报告：
SQL>@?/rdbms/admin/addmrpt.sql
按要求一步一步执行即可，最后通过ftp工具将报告传到windows下进行查看。
oracle自动诊断文档内容如下：
ADDM Report for Task ‘TASK_53‘
------------------------------
Analysis Period
---------------
AWR snapshot range from 13 to 14.
Time period starts at 17-SEP-14 12.00.58 PM
Time period ends at 17-SEP-14 01.00.24 PM
Analysis Target
---------------
Database ‘ORCL‘ with DB ID 1384228360.
Database version 11.2.0.1.0.
ADDM performed an analysis of instance orcl, numbered 1 and hosted at
localhost.localdomain.
Activity During the Analysis Period
-----------------------------------
Total database time was 73 seconds.
The average number of active sessions was .02.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
注意：报告的具体说明，详见书。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
