1 报告内容概要
AWR (Automatic Workload Repository) 报告是我们进行日常数据库性能评定、问题发现的重要手段，因此，当oracle遇到性能问题时，我们的一个重要思路就是导出数据库的AWR报告，通过报告分析定位问题根源。如何正确的导入导出AWR报告将是进行性能分析的重要前提工作，在本次报告中，将详细介绍生成分析报告的具体步骤。
2 详细过程说明
2.1 常用脚本说明
u Awrextr.sql 脚本：利用Awrextr.sql 脚本可以将数据库中的一系列的 AWR快照数据抽取到Data Pump导出文件中。导出 AWR快照数据后，就可以将此 dump文件传输到其他数据库上。要运行awrextr.sql脚本，必须以SYS用户身份连接到数据库中。
u Awrload.sql 脚本：利用Awrload.sql 脚本可以在数据库中载入抽取的AWR数据。Awrload.sql脚本首先会创建一个中转schema，将Data Pump文件载入数据库中，随后会将该数据从中转的schema传入相应的AWR表中。要运行awrload.sql脚本，必须以SYS用户身份连接到数据库中。
u Awrrpt.sql 脚本：利用Awrrpt.sql 脚本可以生成不同快照间显示数据统计的html文件或者text文本文件。
u Awrrpti.sql脚本：同Awrrpt.sql脚本功能类似，只是可以指定数据库和实例。
u Awrsqrpt.sql脚本：利用Awrsqrpt.sql脚本可以生成一个HTML或文本报告来显示特定SQL语句的一系列数据统计信息。通过这个报告可以检查或调试SQL语句的性能。
u Awrsqrpi.sql脚本：同Awrsqrpt.sql脚本功能类似，但是可以指定数据库和实例。
注：在没有指定路径的情况下，以上脚本均在”$ORACLE_HOME/rdbms/admin”路径下。
2.2 执行步骤：
2.2.1 导出数据库的AWR报告
要求：
u 利用Awrrpt.sql 脚本来生成AWR报告
u 将报告上传至本地
操作如下：
指定报告生成路径，并连接数据库
[oracle@ENMOEDU admin]$ cd -
/home/oracle
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 13:30:27 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
执行Awrrpt.sql 脚本（本次导出的AWR报告为HTML文件，Snap Id 为71-74，生成的
文件名为awrrpt_1_71_74.html）
SYS@ENMOEDU> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
87396644 ENMOEDU 1 ENMOEDU
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
------------ -------- ------------ ------------ ------------
* 87396644 1 ENMOEDU ENMOEDU ENMOEDU
Using 87396644 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:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ENMOEDU ENMOEDU 43 18 Dec 2013 12:16 1
44 18 Dec 2013 13:00 1
45 18 Dec 2013 14:01 1
46 18 Dec 2013 15:00 1
47 18 Dec 2013 16:00 1
48 18 Dec 2013 17:00 1
49 18 Dec 2013 18:00 1
50 18 Dec 2013 19:00 1
51 18 Dec 2013 20:00 1
52 18 Dec 2013 21:01 1
53 18 Dec 2013 22:00 1
54 19 Dec 2013 09:54 1
55 19 Dec 2013 11:00 1
56 19 Dec 2013 12:00 1
57 19 Dec 2013 13:00 1
58 19 Dec 2013 14:00 1
59 19 Dec 2013 15:00 1
60 19 Dec 2013 16:00 1
61 19 Dec 2013 17:00 1
62 19 Dec 2013 18:00 1
63 19 Dec 2013 19:01 1
64 19 Dec 2013 20:01 1
65 20 Dec 2013 10:33 1
66 20 Dec 2013 12:00 1
67 20 Dec 2013 13:00 1
68 20 Dec 2013 14:00 1
69 25 Dec 2013 21:47 1
70 25 Dec 2013 23:00 1
71 26 Dec 2013 09:34 1
72 26 Dec 2013 11:00 1
73 26 Dec 2013 12:00 1
74 26 Dec 2013 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 71
Begin Snapshot Id specified: 71
Enter value for end_snap: 74
End Snapshot Id specified: 74
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_71_74.html. To use this name,
press
Enter value for report_name: awrrpt_1_71_74.html
将生成的awrrpt_1_71_74.html文件导出到本地查看
报告总结如下：
WORKLOAD REPOSITORY report for
|
DB Name |
DB Id |
Instance |
Inst num |
Startup Time |
Release |
RAC |
|
ENMOEDU |
87396644 |
ENMOEDU |
1 |
26-Dec-13 09:12 |
11.2.0.3.0 |
NO |
|
Host Name |
Platform |
CPUs |
Cores |
Sockets |
Memory (GB) |
|
ENMOEDU |
Linux IA (32-bit) |
2 |
1 |
1 |
.99 |
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
|
|
Begin Snap: |
71 |
26-Dec-13 09:34:16 |
33 |
1.6 |
|
End Snap: |
74 |
26-Dec-13 13:00:00 |
29 |
1.5 |
|
Elapsed: |
|
205.73 (mins) |
|
|
|
DB Time: |
|
0.71 (mins) |
|
|
Report Summary
Cache Sizes
|
Begin |
End |
|||
|
Buffer Cache: |
80M |
80M |
Std Block Size: |
8K |
|
Shared Pool Size: |
172M |
172M |
Log Buffer: |
5,944K |
Load Profile
|
Per Second |
Per Transaction |
Per Exec |
Per Call |
|
|
DB Time(s): |
0.0 |
0.0 |
0.00 |
0.02 |
|
DB CPU(s): |
0.0 |
0.0 |
0.00 |
0.01 |
|
Redo size: |
803.2 |
9,905.0 |
|
|
|
Logical reads: |
17.0 |
209.9 |
|
|
|
Block changes: |
3.7 |
45.4 |
|
|
|
Physical reads: |
0.2 |
1.8 |
|
|
|
Physical writes: |
0.3 |
3.6 |
|
|
|
User calls: |
0.2 |
2.2 |
|
|
|
Parses: |
0.7 |
8.8 |
|
|
|
Hard parses: |
0.0 |
0.3 |
|
|
|
W/A MB processed: |
0.0 |
0.3 |
|
|
|
Logons: |
0.1 |
0.6 |
|
|
|
Executes: |
3.0 |
37.3 |
|
|
|
Rollbacks: |
0.0 |
0.0 |
|
|
|
Transactions: |
0.1 |
|
|
|
Instance Efficiency Percentages (Target 100%)
|
Buffer Nowait %: |
100.00 |
Redo NoWait %: |
100.00 |
|
Buffer Hit %: |
99.15 |
In-memory Sort %: |
100.00 |
|
Library Hit %: |
97.49 |
Soft Parse %: |
96.37 |
|
Execute to Parse %: |
76.33 |
Latch Hit %: |
99.98 |
|
Parse CPU to Parse Elapsd %: |
13.20 |
% Non-Parse CPU: |
89.77 |
Shared Pool Statistics
|
Begin |
End |
|
|
Memory Usage %: |
80.01 |
88.71 |
|
% SQL with executions>1: |
58.43 |
85.91 |
|
% Memory for SQL w/exec>1: |
50.76 |
84.45 |
Top 5 Timed Foreground Events
|
Event |
Waits |
Time(s) |
Avg wait (ms) |
% DB time |
Wait Class |
|
DB CPU |
|
17 |
|
38.99 |
|
|
db file sequential read |
566 |
6 |
10 |
13.45 |
User I/O |
|
log file sync |
203 |
3 |
15 |
7.16 |
Commit |
|
latch: shared pool |
34 |
1 |
39 |
3.10 |
Concurrency |
|
control file single write |
42 |
1 |
20 |
2.01 |
System I/O |
Host CPU (CPUs: 2 Cores: 1 Sockets: 1)
|
Load Average Begin |
Load Average End |
%User |
%System |
%WIO |
%Idle |
|
4.47 |
0.05 |
0.2 |
0.3 |
1.1 |
99.2 |
Instance CPU
|
%Total CPU |
%Busy CPU |
%DB time waiting for CPU (Resource Manager) |
|
0.2 |
25.6 |
0.0 |
Memory Statistics
|
Begin |
End |
|
|
Host Mem (MB): |
1,010.8 |
1,010.8 |
|
SGA use (MB): |
292.0 |
292.0 |
|
PGA use (MB): |
175.1 |
141.6 |
|
% Host Mem used for SGA+PGA: |
46.22 |
42.90 |
2.2.2 导出指定的SQL语句的AWR报告
要求：
u 利用Awrsqrpi.sql 脚本来生成指定SQL语句的AWR报告
u 将报告上传至本地并对报告进行分析
操作如下：
指定报告生成路径，并连接数据库
[oracle@ENMOEDU admin]$ cd -
/home/oracle
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 13:30:27 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
执行Awrsqrpi.sql 脚本（本次导出的AWR报告为HTML文件，Snap Id 为71-74，SQL
Id为cm5vu20fhtnq1，生成的文件名为awrsqrpt_1_71_74.html）
SYS@ENMOEDU> @?/rdbms/admin/awrsqrpi
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
------------ -------- ------------ ------------ ------------
* 87396644 1 ENMOEDU ENMOEDU ENMOEDU
Enter value for dbid: 87396644
Using 87396644 for database Id
Enter value for inst_num: 1
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:
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ENMOEDU ENMOEDU 43 18 Dec 2013 12:16 1
44 18 Dec 2013 13:00 1
45 18 Dec 2013 14:01 1
46 18 Dec 2013 15:00 1
47 18 Dec 2013 16:00 1
48 18 Dec 2013 17:00 1
49 18 Dec 2013 18:00 1
50 18 Dec 2013 19:00 1
51 18 Dec 2013 20:00 1
52 18 Dec 2013 21:01 1
53 18 Dec 2013 22:00 1
54 19 Dec 2013 09:54 1
55 19 Dec 2013 11:00 1
56 19 Dec 2013 12:00 1
57 19 Dec 2013 13:00 1
58 19 Dec 2013 14:00 1
59 19 Dec 2013 15:00 1
60 19 Dec 2013 16:00 1
61 19 Dec 2013 17:00 1
62 19 Dec 2013 18:00 1
63 19 Dec 2013 19:01 1
64 19 Dec 2013 20:01 1
65 20 Dec 2013 10:33 1
66 20 Dec 2013 12:00 1
67 20 Dec 2013 13:00 1
68 20 Dec 2013 14:00 1
69 25 Dec 2013 21:47 1
70 25 Dec 2013 23:00 1
71 26 Dec 2013 09:34 1
72 26 Dec 2013 11:00 1
73 26 Dec 2013 12:00 1
74 26 Dec 2013 13:00 1
75 26 Dec 2013 14:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 71
Begin Snapshot Id specified: 71
Enter value for end_snap: 74
End Snapshot Id specified: 74
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: cm5vu20fhtnq1
SQL ID specified: cm5vu20fhtnq1
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_71_74.html. To use this name,
press
Enter value for report_name:
将生成的awrrpt_1_71_74.html文件导出到本地查看，如下图所示：
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
|
DB Name |
DB Id |
Instance |
Inst num |
Startup Time |
Release |
RAC |
|
ENMOEDU |
87396644 |
ENMOEDU |
1 |
26-Dec-13 09:12 |
11.2.0.3.0 |
NO |
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
|
|
Begin Snap: |
71 |
26-Dec-13 09:34:16 |
33 |
1.6 |
|
End Snap: |
74 |
26-Dec-13 13:00:00 |
29 |
1.5 |
|
Elapsed: |
|
205.73 (mins) |
|
|
|
DB Time: |
|
0.71 (mins) |
|
|
SQL Summary
|
SQL Id |
Elapsed Time (ms) |
Module |
Action |
SQL Text |
|
592 |
|
|
select /*+ connect_by_filtering */ privilege#, level from sysauth$ con... |
SQL ID: cm5vu20fhtnq1
- 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
- select /*+ connect_by_filtering */ privilege#,level from sysauth$ conn...
|
# |
Plan Hash Value |
Total Elapsed Time(ms) |
Executions |
1st Capture Snap ID |
Last Capture Snap ID |
|
592 |
8,874 |
72 |
74 |
Plan 1(PHV: 3332340200)
Plan Statistics
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
|
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
|
Elapsed Time (ms) |
592 |
0.07 |
1.39 |
|
CPU Time (ms) |
538 |
0.06 |
3.24 |
|
Executions |
8,874 |
|
|
|
Buffer Gets |
29,580 |
3.33 |
14.08 |
|
Disk Reads |
0 |
0.00 |
0.00 |
|
Parse Calls |
174 |
0.02 |
1.97 |
|
Rows |
12,528 |
1.41 |
|
|
User I/O Wait Time (ms) |
0 |
|
|
|
Cluster Wait Time (ms) |
0 |
|
|
|
Application Wait Time (ms) |
0 |
|
|
|
Concurrency Wait Time (ms) |
0 |
|
|
|
Invalidations |
0 |
|
|
|
Version Count |
3 |
|
|
|
Sharable Mem(KB) |
66 |
|
|
Back to Plan
1(PHV: 3332340200)
Back to Top
Execution Plan
|
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
|
0 |
SELECT STATEMENT |
|
|
|
8 (100) |
|
|
1 |
CONNECT BY WITH FILTERING |
|
|
|
|
|
|
2 |
INDEX RANGE SCAN |
I_SYSAUTH1 |
2 |
16 |
2 (0) |
00:00:01 |
|
3 |
NESTED LOOPS |
|
5 |
105 |
4 (0) |
00:00:01 |
|
4 |
CONNECT BY PUMP |
|
|
|
|
|
|
5 |
INDEX RANGE SCAN |
I_SYSAUTH1 |
3 |
24 |
1 (0) |
00:00:01 |
Back to Plan
1(PHV: 3332340200)
Back to Top
Full SQL Text
|
SQL Id |
SQL Text |
|
cm5vu20fhtnq1 |
select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 |
注：通过以上手动生成数据库的AWR报告和指定SQL语句的AWR报告不仅可以对一个时间段内整个系统资源使用情况作出全面分析，而且还可以对指定的SQL语句进行校验分析，从而找出提高性能的最优方案。
2.2.3 命令行查看SQL执行计划
要求：
u 编写执行脚本
u 通过执行脚本在命令行输出执行计划
操作如下：
注：编写的脚本存放在/home/oracle目录下,SQL id为6x3gu99adnjc7
[oracle@ENMOEDU ~]$ ls
awrrpt_1_69_70.html db.rsp install2013-10-07_17-41-09.log rlwrap-0.37.tar.gz
awrrpt_1_71_74.html examples LOG_cat_owb.TXT sq.sql
awrsqlrpt_1_69_70.html expdp_oltp.dmp p10404530_112030_LINUX_6of7.zip
awrsqlrpt_1_71_74.html expdp_oltp.log rlwrap-0.37
[oracle@ENMOEDU ~]$ cat sq.sql
set linesize 150
set pagesize 999
select * from table(dbms_xplan.display_awr('&SQLID',null,87396644,'ADVANCED'))
[oracle@ENMOEDU ~]$ exit
exit
SYS@ENMOEDU> @/home/oracle/sq.sql
2 ;
Enter value for sqlid: 6x3gu99adnjc7
old 1: select * from table(dbms_xplan.display_awr('&SQLID',null,87396644,'ADVANCED'))
new 1: select * from table(dbms_xplan.display_awr('6x3gu99adnjc7',null,87396644,'ADVANCED'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6x3gu99adnjc7
--------------------
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS = :B3 WHERE STEP_STATUS = :B2
AND STEP_ID = :B1 AND START_TIME < (MGMT_JOB_ENGINE.SYSDATE_UTC() -
(30/60/24))
Plan hash value: 551834676
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | MGMT_JOB_EXECUTION | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| MGMT_JOB_EXECUTION | 1 | 109 | 1 (0)| 00:00:01 |
| 3 | INDEX UNIQUE SCAN | PK_MGMT_JOB_EXECUTION | 1 | | 0 (0)| |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / MGMT_JOB_EXECUTION@UPD$1
3 - UPD$1 / MGMT_JOB_EXECUTION@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"UPD$1")
INDEX_RS_ASC(@"UPD$1" "MGMT_JOB_EXECUTION"@"UPD$1" ("MGMT_JOB_EXECUTION"."STEP_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
2 - :B2 (NUMBER): 2
3 - :B1 (NUMBER): -2
44 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29366942/viewspace-1064684/ ，如需转载，请注明出处，否则将追究法律责任。
