Orace SQL調(diào)優(yōu)系列之ADDM報(bào)告簡(jiǎn)介

我寫的SQL調(diào)優(yōu)專欄：https://blog.csdn.net/u014427391/article/category/8679315

整體分析調(diào)優(yōu)工具

AWR：關(guān)注數(shù)據(jù)庫(kù)的整體性能的報(bào)告；

ASH：數(shù)據(jù)庫(kù)中的等待事件與哪些SQL具體對(duì)應(yīng)的報(bào)告；

ADDM：oracle給出的一些建議

AWRDD：Oracle針對(duì)不同時(shí)段的性能對(duì)比報(bào)告

AWRSQRPT：oracle獲取統(tǒng)計(jì)信息與執(zhí)行計(jì)劃

不同場(chǎng)景對(duì)應(yīng)工具

局部分析調(diào)優(yōu)工具：

explain plan for

set autotrace on

statistics_level=all

直接通過sql_id獲取

10046 trace

awrrpt.sql

整體性能工具要點(diǎn)

AWR關(guān)注點(diǎn)：load profile、efficiency percentages、top 5 time events、SQL Statistics、segment_statistics

ASH關(guān)注點(diǎn)：等待事件與sql完美結(jié)合

ADDM：各種建議與對(duì)應(yīng)SQL

AWRDD：不同時(shí)期 load profile的比較、不同時(shí)期等待事件的比較、不同時(shí)期TOP SQL的比較

AWRSQRPT：獲取與關(guān)注點(diǎn)(統(tǒng)計(jì)信息與執(zhí)行計(jì)劃)

select output from table (dbms_workload_repository.awr_report_html(v_dbid,v_instance_number,v_min_snap_id,v_max_snap_id));

1

相關(guān)查詢?cè)噲D：

v$session (當(dāng)前正在發(fā)生)

v$session_wait(當(dāng)前正在等待)

v$session_wait_history (會(huì)話最近的10次等待事件)

v$active_session_history (內(nèi)存中的ASH采集信息，理論為1小時(shí))

wrh$_active_session_history (寫入AWR庫(kù)中的ASH信息，理論為1小時(shí)以上)

dba_hist_active_sess_history (根據(jù)wrh$_active_session_history生成的視圖)

對(duì)于局部的，比如某個(gè)頁(yè)面列表sql，我們可以使用Oracle的執(zhí)行計(jì)劃進(jìn)行sql調(diào)優(yōu)，但是對(duì)于整個(gè)系統(tǒng)來說，你可以知道哪些sql比較耗時(shí)？當(dāng)然可以通過查Oracle的共享池得到，不過Oracle系統(tǒng)本身就提供了幾種性能分析報(bào)告，比如AWR、ASH、ADDM、AWRDD等等報(bào)告，本博客介紹一下ADDM性能分析報(bào)告

ADDM全稱是Automatic Database Diagnostic Monitor，是Oracle一個(gè)實(shí)現(xiàn)性能自我診斷的最佳利器。它依賴于AWR，也就是說ADDM要診斷，必要要有診斷的依據(jù)。在Oracle中，這個(gè)診斷依據(jù)就是Oracle AWR，因?yàn)镺racle AWR會(huì)定期的收集整個(gè)數(shù)據(jù)庫(kù)在運(yùn)行期間的性能統(tǒng)計(jì)數(shù)據(jù)。

一、ADDM報(bào)告生成

繼之前AWR、ASH方面的博客之后，https://smilenicky.blog.csdn.net/article/details/89414432，https://smilenicky.blog.csdn.net/article/details/89419185，我再寫一篇ADDM方面的博客：

1.1 工具選擇

對(duì)于Oracle數(shù)據(jù)庫(kù)可以使用sqlplus或者plsql developer客戶端軟件

sqlplus 使用

可以使用sqlplus工具登錄

進(jìn)入數(shù)據(jù)庫(kù)

sqlplus / as sysdba

1

查看用戶

show parameter db_name

1

用登錄之后才可以使用

plsql developer使用

plsql developer也可以使用，登錄之后，選擇文件(File)->新建(New)->命令窗口(Command Window)

1.2 自動(dòng)創(chuàng)建快照

開始?jí)簻y(cè)后執(zhí)行

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

1

可以通過dba_hist_wr_control查看當(dāng)前的配置情況，當(dāng)前awr為每1小時(shí)做一次數(shù)據(jù)快照，保留時(shí)間為8天。

select * from dba_hist_wr_control;

1

修改配置，每隔30分鐘收集一次，保存1天

execute dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>14000);

1

關(guān)閉自動(dòng)收集

SQL>exec dbms_workload_repository.modify_snapshot_settings (interval=>0,retention=>24*60);

1

1.3 手工創(chuàng)建快照

除了自動(dòng)創(chuàng)建快照，也可以手工創(chuàng)建快照

select dbms_workload_repository.create_snapshot() from dual;

1

1.4 ADDM報(bào)告生成

對(duì)于sqlplus客戶端的可以使用

@?/rdbms/admin/addmrpt.sql

1

對(duì)于plsql客戶端，我用絕對(duì)路徑去執(zhí)行，@?的命令找不到文件

這個(gè)要根據(jù)自己的Oracle安裝路徑去修改，例如：

@D:/oracle/product/11.1.0/db_1/RDBMS/ADMIN/addmrpt.sql

1

（1）快照開始id

Enter value for begin_snap:418

要根據(jù)日志打印的快照id范圍來填，所以我可以填寫：418

（2）快照結(jié)束id

Enter value for end_snap:420

要根據(jù)日志打印的快照id范圍來填，所以我可以填寫：420

（3）ADDM報(bào)告名稱

Enter value for report_name

填寫ADDM報(bào)告的名稱，我可以填寫addm_20190421.html，然后在打印的日志里有文件保存的路徑：，比如：D:\oracle\product.2.0\dbhome_1\RDBMS\ADMIN\addm.html

SQL> @D:/oracle/product/11.2.0/dbhome_1/RDBMS/ADMIN/addmrpt.sql Current Instance ~~~~~~~~~~~~~~~~ DBID DB_NAME INST_ INST_NAME ---------- ------------ ----- ------------ 1525762377 ORCL 1 orcl Cannot SET TRIMSPOOL Cannot SET UNDERLINE Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DBBID INSTT DBB_NAME INSTT_NAME HOST ------------ ----- ------------ ------------ ------------ * 1525762377 1 ORCL orcl PC-201508171 906 Using 1525762377 for database Id Using 1 for instance number PL/SQL procedure successfully completed dbid --------- 1525762377 inst_num --------- 1 PL/SQL procedure successfully completed inst_num --------- 1 dbid --------- 1525762377 max_snap_time --------- 18/04/2019 Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Listing the last 3 days of Completed Snapshots INST_NAME DB_NAME SNAP_ID SNAPDAT LV ------------ ------------ -------- ------------------ -- orcl ORCL 417 16 Apr 2019 00:00 1 orcl ORCL 418 16 Apr 2019 19:48 1 orcl ORCL 419 16 Apr 2019 21:00 1 orcl ORCL 420 16 Apr 2019 22:00 1 orcl ORCL 421 16 Apr 2019 23:00 1 orcl ORCL 422 17 Apr 2019 21:20 1 orcl ORCL 423 17 Apr 2019 22:00 1 orcl ORCL 424 17 Apr 2019 23:00 1 orcl ORCL 425 18 Apr 2019 00:00 1 orcl ORCL 426 18 Apr 2019 21:26 1 orcl ORCL 427 18 Apr 2019 22:00 1 11 rows selected dbid --------- 1525762377 inst_num --------- 1 max_snap_time --------- 18/04/2019 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Begin Snapshot Id specified: 418 End Snapshot Id specified: 420 PL/SQL procedure successfully completed bid --------- 418 eid --------- 420 PL/SQL procedure successfully completed inst_num --------- 1 dbid --------- 1525762377 bid --------- 418 eid --------- 420 Cannot SET TRIMSPOOL Cannot SET UNDERLINE Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is addmrpt_1_418_420.txt. To use this name, press to continue, otherwise enter an alternative. Using the report name addm.html Running the ADDM analysis on the specified pair of snapshots ... bid --------- 418 eid --------- 420 inst_num --------- 1 dbid --------- 1525762377 task_name --------- TASK_953 Generating the ADDM report for this analysis ... Started spooling to D:\oracle\product.2.0\dbhome_1\RDBMS\ADMIN\addm.html ADDM Report for Task 'TASK_953' ------------------------------- Analysis Period --------------- AWR snapshot range from 418 to 420. Time period starts at 16-APR-19 07.48.09 PM Time period ends at 16-APR-19 10.00.20 PM Analysis Target --------------- Database 'ORCL' with DB ID 1525762377. Database version 11.2.0.1.0. ADDM performed an analysis of instance orcl, numbered 1 and hosted at PC-201508171906. Activity During the Analysis Period ----------------------------------- Total database time was 57 seconds. The average number of active sessions was .01. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ There are no findings to report. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Additional Information ---------------------- Miscellaneous Information ------------------------- There was no significant database activity to run the ADDM. The database's maintenance windows were active during 99% of the analysis period. task_name --------- TASK_953 Stopped spooling to D:\oracle\product.2.0\dbhome_1\RDBMS\ADMIN\addm.html End of Report Report written to addm.html.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

二、ADDM報(bào)告性能分析

ADDM性能報(bào)告是從數(shù)據(jù)庫(kù)的整體配置和局部SQL方面給出建議，閱讀時(shí)候按FINDING1，F(xiàn)INDING2，…，的順序閱讀就好

下面的圖來自《收獲，不止SQL優(yōu)化》一書：

Oracle SQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。