【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來診斷資料庫效能問題資料庫
- 如何使用AWR報告來診斷資料庫效能問題資料庫
- awr診斷分析之二
- 9. Oracle常用分析診斷工具——9.1. AWROracle
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 常見問題:如何使用AWR報告來診斷資料庫效能問題 (Doc ID 1523048.1)資料庫
- Oracle資料庫AWR的使用例項詳解Oracle資料庫
- AWR歷史資料包表工具AHR
- 診斷network網路SQL*Net more data to client_awrSQLclient
- ODX 診斷資料庫轉換工具 — DDC資料庫
- oracle工具 awr formatOracleORM
- oracle awr快照點不記錄問題Oracle
- 獲取Oracle資料庫awr報告方法Oracle資料庫
- 【手工建庫】手工方式建立 ORACLE資料庫全程記錄Oracle資料庫
- AWR資料採集方法
- Oracle AWR資料採集Oracle
- mysql資料庫效能診斷MySql資料庫
- 【Oracle】資料庫hang 診斷Oracle資料庫
- Oracle配置資料庫診斷Oracle資料庫
- 本機生成遠端資料庫AWR報告資料庫
- 遠端資料庫AWR 匯入到本地分析資料庫
- 自動工作量資料檔案庫(AWR)
- 使用SQL_TRACE進行資料庫診斷SQL資料庫
- AWR取樣資料存放位置
- Oracle 11g AWR 系列六：使用 AWR 檢視Oracle
- _awr_sql_child_limit是否能控制awr記錄sql執行次數的問題SQLMIT
- 手工生成AWR執行期對比報告記錄
- 達夢資料庫AWR報告日常管理方法資料庫
- 資料庫診斷一例資料庫
- Oracle透過AWR的SQL ordered by Gets和SQL ordered by Reads診斷問題OracleSQL
- 診斷與分析itpub壇友提出關於為何awr cpu usage非常高
- 使用SQL_TRACE進行資料庫診斷(轉)SQL資料庫
- 使用SQL_TRACE進行資料庫診斷(1)SQL資料庫
- 使用SQL_TRACE進行資料庫診斷(2)SQL資料庫
- 使用SQL_TRACE進行資料庫診斷（zt）SQL資料庫
- oracle診斷工具-RDA使用Oracle
- Oracle診斷工具RDA使用Oracle
- Oracle中AWR的使用Oracle