我们都知道Oracle的AWR报告是一个很强大的功能通过分析AWR报告可以打出Oracle数据运行过程中出现的问题和可能存在的隐患。但是AWR报告中没有关于单个SQL执行计划、统计信息的详细描述，但不代表AWR不能提供这种功能。
本文介绍的是如何使用AWR报告生成一条sql的详细执行统计报告。
实验环境11.2.0.4
1、调整AWR关于SQL收集的设置，调整的目的是因为默认情况下AWR并非捕获所有的sql语句，此调整是为了让AWR可以收集实验过程中的SQL语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
zx@ORCL>
select
*
from
dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------
1444351641 +00000 01:00:00.0 +00008 00:00:00.0
DEFAULT
zx@ORCL>
exec
dbms_workload_repository.modify_snapshot_settingS(topnsql=>
'MAXIMUM'
);
PL/SQL
procedure
successfully completed.
zx@ORCL>
select
*
from
dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ------------------------------
1444351641 +00000 01:00:00.0 +00008 00:00:00.0 MAXIMUM
|
2、手工创建一个AWR快照
|
1
2
3
|
zx@ORCL>
exec
dbms_workload_repository.create_snapshot;
PL/SQL
procedure
successfully completed.
|
3、创建测试表并在不同情况下执行测试sql，并找到测试sql的sql_id
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
zx@ORCL>
create
table
t
as
select
*
from
dba_objects;
Table
created.
zx@ORCL>
create
unique
index
idx_unique_t
on
t(object_id);
Index
created.
zx@ORCL>
exec
dbms_stats.gather_table_stats(
USER
,
'T'
,
CASCADE
=>
TRUE
);
PL/SQL
procedure
successfully completed.
zx@ORCL>
select
object_name
from
t
where
object_id=123;
OBJECT_NAME
------------------------------
ECOL$
zx@ORCL>
select
sql_id,sql_text
from
v$sql
where
sql_text=
'select object_name from t where object_id=123'
;
SQL_ID SQL_TEXT
------------------- -----------------------------------------------
2dymmcx3kf7h1
select
object_name
from
t
where
object_id=123
|
4、再次手工生成AWR快照
|
1
2
3
|
zx@ORCL>
exec
dbms_workload_repository.create_snapshot;
PL/SQL
procedure
successfully completed.
|
5、使用awrsqrpt.sql脚本生成对于sql_id=2dymmcx3kf7h1的sql的详细统计信息。过程与生成awrrpt类似，不同的是需要指定要生成报告的sql_id
|
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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
zx@ORCL>@?/rdbms/admin/awrsqrpt
Current
Instance
~~~~~~~~~~~~~~~~
DB Id DB
Name
Inst Num Instance
----------- ------------ -------- ------------
1444351641 ORCL 1 orcl
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
------------ -------- ------------ ------------ ------------
* 1444351641 1 ORCL orcl rhel6
Using 1444351641
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 <
return
> without
specifying a number lists
all
completed snapshots.
Enter value
for
num_days: 1
Listing the
last
day
's Completed Snapshots
Snap
Instance DB
Name
Snap Id Snap Started
Level
------------ ------------ --------- ------------------ -----
orcl ORCL 826 15 Feb 2017 09:25 1
827 15 Feb 2017 10:00 1
828 15 Feb 2017 10:00 1
829 15 Feb 2017 10:04 1
830 15 Feb 2017 10:09 1
831 15 Feb 2017 11:00 1
832 15 Feb 2017 11:02 1
833 15 Feb 2017 11:07 1
Specify the
Begin
and
End
Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value
for
begin_snap: 832
Begin
Snapshot Id specified: 832
Enter value
for
end_snap: 833
End
Snapshot Id specified: 833
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value
for
sql_id: 2dymmcx3kf7h1
SQL ID specified: 2dymmcx3kf7h1
Specify the Report
Name
~~~~~~~~~~~~~~~~~~~~~~~
The
default
report file
name
is
awrsqlrpt_1_832_833.txt.
To
use this
name
,
press <
return
>
to
continue
, otherwise enter an alternative.
Enter value
for
report_name:
Using the report
name
awrsqlrpt_1_832_833.txt
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB
Name
DB Id Instance Inst Num Startup
Time
Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORCL 1444351641 orcl 1 15-Feb-17 09:14 11.2.0.4.0
NO
Snap Id Snap
Time
Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin
Snap: 832 15-Feb-17 11:02:01 27 1.3
End
Snap: 833 15-Feb-17 11:07:24 29 1.5
Elapsed: 5.38 (mins)
DB
Time
: 0.06 (mins)
SQL Summary DB/Inst: ORCL/orcl Snaps: 832-833
Elapsed
SQL Id
Time
(ms)
------------- ----------
2dymmcx3kf7h1 1
Module: SQL*Plus
select
object_name
from
t
where
object_id=123
-------------------------------------------------------------
SQL ID: 2dymmcx3kf7h1 DB/Inst: ORCL/orcl Snaps: 832-833
-> 1st Capture
and
Last
Capture Snap IDs
refer
to
Snapshot IDs witin the snapshot range
->
select
object_name
from
t
where
object_id=123
Plan Hash Total Elapsed 1st Capture
Last
Capture
# Value
Time
(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 3476657867 1 1 833 833
-------------------------------------------------------------
Plan 1(PHV: 3476657867)
-----------------------
Plan
Statistics
DB/Inst: ORCL/orcl Snaps: 832-833
-> % Total DB
Time
is
the Elapsed
Time
of
the SQL statement divided
into
the Total
Database
Time
multiplied
by
100
Stat
Name
Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed
Time
(ms) 1 0.8 0.0
CPU
Time
(ms) 0 0.0 0.0
Executions 1 N/A N/A
Buffer Gets 3 3.0 0.0
Disk Reads 0 0.0 0.0
Parse Calls 1 1.0 0.1
Rows
1 1.0 N/A
User
I/O Wait
Time
(ms) 0 N/A N/A
Cluster Wait
Time
(ms) 0 N/A N/A
Application Wait
Time
(ms) 0 N/A N/A
Concurrency Wait
Time
(ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version
Count
1 N/A N/A
Sharable Mem(KB) 15 N/A N/A
-------------------------------------------------------------
Execution Plan
--------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | | | 2 (100)| |
| 1 |
TABLE
ACCESS
BY
INDEX
ROWID| T | 1 | 30 | 2 (0)| 00:00:01 |
| 2 |
INDEX
UNIQUE
SCAN | IDX_UNIQUE_T | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Full
SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
2dymmcx3kf7h
select
object_name
from
t
where
object_id=123
Report written
to
awrsqlrpt_1_832_833.txt
|
报告中列出了AWR记录中sql执行的统计信息和执行计划。
参考：http://blog.csdn.net/leshami/article/details/8732708
http://www.linuxidc.com/Linux/2013-01/77196.htm
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69140
《基于Oracle的SQL优化》
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1897981，如需转载请自行联系原作者
