如何对数据库进行监控检查

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.

注意:报告的具体说明,详见书。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。