Oracle管理监控之如何对数据库进行监控检查
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.
注意：报告的具体说明，详见书。
