【AWR】資料庫診斷工具AWR使用全程記錄
操作起來非常的簡單，相比過去常用的statspack簡化了不知道有多少，贊一下下
其實用到的就是下面一條命令，其後的操作就按照提示做就OK了。
sys@secooler> @?/rdbms/admin/awrrpt
下面全程記錄一下兩次（一次是生成HTML report格式的報告，一次是生成plain text report格式的報告）生成的過程，以便給大家一個直觀的感覺。
secooler@testdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 26 10:37:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sys@secooler> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
23221517 SECOOLER 1 secooler
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 23221517 1 SECOOLER secooler testdb
Using 23221517 for database Id
Using 1 for instance number
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.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
secooler SECOOLER 9 25 Jun 2009 00:00 1
10 25 Jun 2009 01:00 1
11 25 Jun 2009 02:00 1
12 25 Jun 2009 03:00 1
13 25 Jun 2009 04:00 1
14 25 Jun 2009 05:00 1
15 25 Jun 2009 06:00 1
16 25 Jun 2009 07:00 1
17 25 Jun 2009 08:00 1
18 25 Jun 2009 09:00 1
19 25 Jun 2009 10:00 1
20 25 Jun 2009 11:00 1
21 25 Jun 2009 12:00 1
22 25 Jun 2009 13:00 1
23 25 Jun 2009 14:01 1
24 25 Jun 2009 15:00 1
25 25 Jun 2009 16:00 1
26 25 Jun 2009 17:00 1
27 25 Jun 2009 18:00 1
28 25 Jun 2009 19:00 1
29 25 Jun 2009 20:00 1
30 25 Jun 2009 21:00 1
31 25 Jun 2009 22:00 1
32 25 Jun 2009 23:00 1
33 26 Jun 2009 00:00 1
34 26 Jun 2009 01:00 1
35 26 Jun 2009 02:00 1
36 26 Jun 2009 03:00 1
37 26 Jun 2009 04:00 1
38 26 Jun 2009 05:00 1
39 26 Jun 2009 06:00 1
40 26 Jun 2009 07:00 1
41 26 Jun 2009 08:00 1
42 26 Jun 2009 09:00 1
43 26 Jun 2009 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 26
Begin Snapshot Id specified: 26
Enter value for end_snap: 27
End Snapshot Id specified: 27
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_26_27.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_26_27.html
AWR Report body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;}
pre.awr {font:8pt Courier;color:black; background:White;}h1.awr {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
……此處省略大量HTML輸出資訊……
user_dump_dest /oracle/app/oracle/admin/secooler/udump
Back to Top
End of Report
Report written to awrrpt_1_26_27.html
sys@secooler>
sys@secooler>
sys@secooler>
sys@secooler>
sys@secooler> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
23221517 SECOOLER 1 secooler
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 23221517 1 SECOOLER secooler testdb
Using 23221517 for database Id
Using 1 for instance number
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.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
secooler SECOOLER 9 25 Jun 2009 00:00 1
10 25 Jun 2009 01:00 1
11 25 Jun 2009 02:00 1
12 25 Jun 2009 03:00 1
13 25 Jun 2009 04:00 1
14 25 Jun 2009 05:00 1
15 25 Jun 2009 06:00 1
16 25 Jun 2009 07:00 1
17 25 Jun 2009 08:00 1
18 25 Jun 2009 09:00 1
19 25 Jun 2009 10:00 1
20 25 Jun 2009 11:00 1
21 25 Jun 2009 12:00 1
22 25 Jun 2009 13:00 1
23 25 Jun 2009 14:01 1
24 25 Jun 2009 15:00 1
25 25 Jun 2009 16:00 1
26 25 Jun 2009 17:00 1
27 25 Jun 2009 18:00 1
28 25 Jun 2009 19:00 1
29 25 Jun 2009 20:00 1
30 25 Jun 2009 21:00 1
31 25 Jun 2009 22:00 1
32 25 Jun 2009 23:00 1
33 26 Jun 2009 00:00 1
34 26 Jun 2009 01:00 1
35 26 Jun 2009 02:00 1
36 26 Jun 2009 03:00 1
37 26 Jun 2009 04:00 1
38 26 Jun 2009 05:00 1
39 26 Jun 2009 06:00 1
40 26 Jun 2009 07:00 1
41 26 Jun 2009 08:00 1
42 26 Jun 2009 09:00 1
43 26 Jun 2009 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 26
Begin Snapshot Id specified: 26
Enter value for end_snap: 27
End Snapshot Id specified: 27
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_26_27.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_26_27.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
SECOOLER 23221517 secooler 1 10.2.0.1.0 NO testdb
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 26 25-Jun-09 17:00:45 17 2.2
End Snap: 27 25-Jun-09 18:00:33 17 2.5
Elapsed: 59.80 (mins)
DB Time: 15.35 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 436M 436M Std Block Size: 8K
Shared Pool Size: 84M 84M Log Buffer: 6,216K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 605.00 6,518.37
Logical reads: 3,436.96 37,030.54
Block changes: 2.93 31.61
Physical reads: 3,407.63 36,714.61
Physical writes: 0.65 6.97
User calls: 0.74 7.95
Parses: 2.04 21.99
Hard parses: 0.56 6.05
Sorts: 0.83 8.95
Logons: 0.03 0.35
Executes: 3.56 38.37
Transactions: 0.09
% Blocks changed per Read: 0.09 Recursive Call %: 97.43
Rollback per transaction %: 0.60 Rows per Sort: 7.11
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 0.85 In-memory Sort %: 100.00
Library Hit %: 89.48 Soft Parse %: 72.52
Execute to Parse %: 42.68 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 23.03 % Non-Parse CPU: 98.74
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.51 94.51
% SQL with executions>1: 82.20 79.34
% Memory for SQL w/exec>1: 89.92 91.12
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
……此處省略大量TXT輸出資訊……
sga_target 562036736
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /oracle/app/oracle/admin/secooler
-------------------------------------------------------------
End of Report
Report written to awrrpt_1_26_27.txt
sys@secooler>
Good luck.
secooler
090626
-- The End --
其實用到的就是下面一條命令，其後的操作就按照提示做就OK了。
sys@secooler> @?/rdbms/admin/awrrpt
下面全程記錄一下兩次（一次是生成HTML report格式的報告，一次是生成plain text report格式的報告）生成的過程，以便給大家一個直觀的感覺。
secooler@testdb /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 26 10:37:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sys@secooler> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
23221517 SECOOLER 1 secooler
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 23221517 1 SECOOLER secooler testdb
Using 23221517 for database Id
Using 1 for instance number
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
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
secooler SECOOLER 9 25 Jun 2009 00:00 1
10 25 Jun 2009 01:00 1
11 25 Jun 2009 02:00 1
12 25 Jun 2009 03:00 1
13 25 Jun 2009 04:00 1
14 25 Jun 2009 05:00 1
15 25 Jun 2009 06:00 1
16 25 Jun 2009 07:00 1
17 25 Jun 2009 08:00 1
18 25 Jun 2009 09:00 1
19 25 Jun 2009 10:00 1
20 25 Jun 2009 11:00 1
21 25 Jun 2009 12:00 1
22 25 Jun 2009 13:00 1
23 25 Jun 2009 14:01 1
24 25 Jun 2009 15:00 1
25 25 Jun 2009 16:00 1
26 25 Jun 2009 17:00 1
27 25 Jun 2009 18:00 1
28 25 Jun 2009 19:00 1
29 25 Jun 2009 20:00 1
30 25 Jun 2009 21:00 1
31 25 Jun 2009 22:00 1
32 25 Jun 2009 23:00 1
33 26 Jun 2009 00:00 1
34 26 Jun 2009 01:00 1
35 26 Jun 2009 02:00 1
36 26 Jun 2009 03:00 1
37 26 Jun 2009 04:00 1
38 26 Jun 2009 05:00 1
39 26 Jun 2009 06:00 1
40 26 Jun 2009 07:00 1
41 26 Jun 2009 08:00 1
42 26 Jun 2009 09:00 1
43 26 Jun 2009 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 26
Begin Snapshot Id specified: 26
Enter value for end_snap: 27
End Snapshot Id specified: 27
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_26_27.html. To use this name,
press
Enter value for report_name:
Using the report name awrrpt_1_26_27.html
pre.awr {font:8pt Courier;color:black; background:White;}h1.awr {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}
……此處省略大量HTML輸出資訊……
Back to Top
End of Report
Report written to awrrpt_1_26_27.html
sys@secooler>
sys@secooler>
sys@secooler>
sys@secooler>
sys@secooler> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
23221517 SECOOLER 1 secooler
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 23221517 1 SECOOLER secooler testdb
Using 23221517 for database Id
Using 1 for instance number
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
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
secooler SECOOLER 9 25 Jun 2009 00:00 1
10 25 Jun 2009 01:00 1
11 25 Jun 2009 02:00 1
12 25 Jun 2009 03:00 1
13 25 Jun 2009 04:00 1
14 25 Jun 2009 05:00 1
15 25 Jun 2009 06:00 1
16 25 Jun 2009 07:00 1
17 25 Jun 2009 08:00 1
18 25 Jun 2009 09:00 1
19 25 Jun 2009 10:00 1
20 25 Jun 2009 11:00 1
21 25 Jun 2009 12:00 1
22 25 Jun 2009 13:00 1
23 25 Jun 2009 14:01 1
24 25 Jun 2009 15:00 1
25 25 Jun 2009 16:00 1
26 25 Jun 2009 17:00 1
27 25 Jun 2009 18:00 1
28 25 Jun 2009 19:00 1
29 25 Jun 2009 20:00 1
30 25 Jun 2009 21:00 1
31 25 Jun 2009 22:00 1
32 25 Jun 2009 23:00 1
33 26 Jun 2009 00:00 1
34 26 Jun 2009 01:00 1
35 26 Jun 2009 02:00 1
36 26 Jun 2009 03:00 1
37 26 Jun 2009 04:00 1
38 26 Jun 2009 05:00 1
39 26 Jun 2009 06:00 1
40 26 Jun 2009 07:00 1
41 26 Jun 2009 08:00 1
42 26 Jun 2009 09:00 1
43 26 Jun 2009 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 26
Begin Snapshot Id specified: 26
Enter value for end_snap: 27
End Snapshot Id specified: 27
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_26_27.txt. To use this name,
press
Enter value for report_name:
Using the report name awrrpt_1_26_27.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
SECOOLER 23221517 secooler 1 10.2.0.1.0 NO testdb
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 26 25-Jun-09 17:00:45 17 2.2
End Snap: 27 25-Jun-09 18:00:33 17 2.5
Elapsed: 59.80 (mins)
DB Time: 15.35 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 436M 436M Std Block Size: 8K
Shared Pool Size: 84M 84M Log Buffer: 6,216K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 605.00 6,518.37
Logical reads: 3,436.96 37,030.54
Block changes: 2.93 31.61
Physical reads: 3,407.63 36,714.61
Physical writes: 0.65 6.97
User calls: 0.74 7.95
Parses: 2.04 21.99
Hard parses: 0.56 6.05
Sorts: 0.83 8.95
Logons: 0.03 0.35
Executes: 3.56 38.37
Transactions: 0.09
% Blocks changed per Read: 0.09 Recursive Call %: 97.43
Rollback per transaction %: 0.60 Rows per Sort: 7.11
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 0.85 In-memory Sort %: 100.00
Library Hit %: 89.48 Soft Parse %: 72.52
Execute to Parse %: 42.68 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 23.03 % Non-Parse CPU: 98.74
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.51 94.51
% SQL with executions>1: 82.20 79.34
% Memory for SQL w/exec>1: 89.92 91.12
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
……此處省略大量TXT輸出資訊……
sga_target 562036736
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /oracle/app/oracle/admin/secooler
-------------------------------------------------------------
End of Report
Report written to awrrpt_1_26_27.txt
sys@secooler>
Good luck.
secooler
090626
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-607619/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 9. Oracle常用分析診斷工具——9.1. AWROracle
- oracle工具 awr formatOracleORM
- oracle awr快照點不記錄問題Oracle
- 本機生成遠端資料庫AWR報告資料庫
- ODX 診斷資料庫轉換工具 — DDC資料庫
- 達夢資料庫AWR報告日常管理方法資料庫
- 【AWR】DBA_HIST檢視檢視儲存在AWR中的歷史資料
- AWR 報告深度解讀：Redo Nowait指標的演算法和診斷AI指標演算法
- ORACLE AWROracle
- [轉]Oracle資料庫ASH和AWR的簡單介紹Oracle資料庫
- 達夢資料庫如何來配置並生成AWR報告資料庫
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- 匯入和匯出AWR的資料
- 講講AWR
- AWR跨庫匯出與匯入
- [20181102]資料檔案改名與awr.txt
- 使用SQL_TRACE進行資料庫診斷(轉)SQL資料庫
- 【AWR】Oracle awr相關檢視及體系介紹Oracle
- 強大的AWR-Format工具下載ORM
- 【最佳化】AWR
- 效能優化之達夢AWR使用優化
- dbca建立資料庫時報錯ORA-13516: AWR Operation failed: CATPROC not valid資料庫AI
- MySQL使用event等待事件進行資料庫效能診斷MySql事件資料庫
- 詳解Oracle AWR執行日誌分析工具Oracle
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 網路診斷工具的使用
- 將AWR資料傳輸到另一個系統
- AWR TOP SQL實現SQL
- 資料庫異常智慧分析與診斷資料庫
- awr-----一份經典的負載很高的awr報告負載
- [20181024]修改awr收集資訊設定.txt
- [20230220][20230110]生成相關備庫的awr報表
- 吃透 JVM 診斷方法與工具使用JVM
- Part II 診斷和優化資料庫效能優化資料庫
- 大語言模型與資料庫故障診斷模型資料庫
- statspack、awr、addm,ash影片分享
- oracle之 AWR固定基線Oracle
- AWR報告基礎操作