awrsqlrpt.sql的使用
1. 首先获取AWR报告：$ cd /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/
$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 17 11:09:38 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @awrrpt
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt.Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type: active-html
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('active-html','html')) report_type from dual
Type Specified:active-html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'active-html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
868254545 RCAT 1 rcat rcat
<font color="Red">......</font>
ui0pdUguneHq/gKAALzj
<report db_version="19.0.0.0.0" cpu_cores="8" hyperthread="N" timezone_offset="28800" packs="2" encode="base64" compress="zlib">
<report_id><!]></report_id>
eAGNVEtP4zAQvvMrRj6vmjiUp5JKUVu0aKFoaffAXio3cVJDYofYBfrvGTuPFra7
i5JDHI9n5nuMw5IJOToCCCtWs1LbT1yoWuRCsgLUq+R1ROYPcwKG6aelZCWPSDyZ
3F6en54HJ0N8lnRJqX9GvPZ0qVKRCZ5+OK3X6nWpn4uIXMU38+kX0oHmiRFKRmQR
z38QSNlWR4QS0JUyNa8i4jffhdDGLlz90NshCVNumCjAbKu26a+UdThFGpGhPzxp
D4gUSzdrJpcvlhN6MfCbhwD+a2pcz+aLeDaeko4KbADSlc3W04VIVn9k0JJVFJNS
/xRB4SJwizOsL0qOO/6JR8+8wA8CoP6lj+9xs4eB+3vU7tEhFtnUrKHv+JQiazV/
jkjfX6eVBduqjrobrNYD/5fUCAEpD/xj2tKOh729XKGQ2jCZ8F1uATY1NvKJ36ZM
nTBDYK2skslLkqaaXpwP3uQgexwkEikxzGxQg3gW3zz8nk56s3mfKoWZkKmQ+a5w
tg8qa5tAkzj/NNWlqktWfAO9SbBlnW0KSFRZFdwSiB07/1zPru7ub+PF9d3MSugs
B5ko8GPW6u2Gp9T5aLHmNYdXpkEq0CKXOBAJkwZNbNiKaQ4Mvf0izBaMgnojwaw5
2LEahJ5N4ObQpRMyU61aluSsdZb3EWdY82QH2TZcsmovS+nYx4HBbn/JJ4mDDfcc
QZZcYk8IExZ4qKO1AdIrhrxXVYEIXGCMN8NWC30gOHAFJh3IsZKZyFsjHgi3Fo7I
d1Qd/heKjsbQ+c8bWGwk6nsgmx1WDOE5YjJ/D8MBs2HJGimC8ZrJnB+CYkcvItM3
VgrJoXOZ02hXG02/T3Xo7WQIrcJK6m7SmmlkBe9+qNUjXnD9vnUSr/slXpVLw9/a
/dBrrrLREdrDXdnvSkyWPQ==
</report>
<report db_version="19.0.0.0.0" cpu_cores="8" hyperthread="N" timezone_offset="28800" packs="2" encode="base64" compress="zlib">
<report_id><!]></report_id>
eAGNVE1P4zAQvfMrRj6vmqS0QFGCFNGiRQtFS7sH9lK5iZMaknGIXaD/nnE+C9vd
RbnY8XjevPdm7Odc4sURgF/wkufaLmmjSplK5BmoVxRlwBYPCwaG66cV8lwELJxO
b8/PTs6G4xF9K2/lee4Jc5rbuYplIkX84bbeqNeVfs4CdhXeLGZfSAdaREYqDNgy
XPxgEPOdDpjHQBfKlKIImFuvM6mN3VT4vtMz8WNhuMzA7Iqm6K/AVjxlHLCROxo1
F2RM0PWe4+rFauJNBm79MaB/Ncb1fLEM55cz1kpBBUC8ttk6uYjJ+o8MGnnhUVLP
HRMp2gyrzQnhy1zQiTt2vFNn6A6H4E7OXfd8PKzPKHDvzHPtmXtMINuS1/Idj8ek
WimeA9bV13plyTauk++G0Dri/7KaKJDkHmWejDrfnb1kvkRtOEaiTy7B5qZKPglc
45QRNww2yloZvURxrL3J2eANB8njIELSxHCzJRPCeXjz8Hs27VE/IfmJxFhi2gMn
+6ySpgjqkqqBanRUZc6zb6C3EZWsk20GkcqLTFgFqeKqga7nV3f3t+Hy+m5uPax6
DhKZ0WLeGF5NT67Ti+VGlAJeuQZUoGWKNBERR0NdbPiaawGcmvtFmh0YBeUWwWwE
2Lka+I5NUA1ilU5iohq7yCInaVrL+cjTL0XUU7YF57zYy5JX6tPEULW/8AlpsuFe
EMlcINVENGFJl1pZayKdY6R7UWTEoAoM6WnYaakPBNumDNi0JXmpMJFp04kHwqlP
Kfw7uQ7/C7WjSC/RzxtYbpH8PZCNRseGiJQ4mb+H2aGisGhDEsHlhmMqDlE5rcJm
bzyXKKDtssqjHtt3PkjtO70NvnVYoW5HrR5Hnon2h1o/0gvXndtOEmW3pbdyZcRb
c+479Vt2cUTtUb3Z79lfloM=
</report>
</report>
</report>
<!--FXTMODEL-->
</script>
</body>
</div>
End of Report
</body></html>
Report written to /home/oracle/awrrpt_1_1106_1107.html
2. 查看AWR报告中的那条SQL语句：
SQL ordered by Elapsed Time
[*] Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
[*] % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
[*] %Total - Elapsed Timeas a percentage of Total DB time
[*] %CPU - CPU Time as a percentage of Elapsed Time
[*] %IO - User I/O Time as a percentage of Elapsed Time
[*]Captured SQL account for 3.0E+04% of Total DB Time (s): 0
[*]Captured PL/SQL account for 1.6E+04% of Total DB Time (s): 0
ElapsedTime (s)Executions Elapsed Time per Exec (s) %Total%CPU%IO SQL IdSQL ModuleSQL Text
2.0712.0710195.8541.4847.94drktaf71uygnb
BEGIN sys.prvt_hdm.auto_execut...
0.7280.093563.2998.920.0022356bkgsdcnh
SELECT COUNT(*) FROM X$KSPPI A...
0.48300.022342.2195.640.006uxga5vnsgugt
select s.file#, s.block#, s.t...
0.3740.091797.8998.890.001fvsn5j51ugz3
begin dbms_rcvman.resetAll; e...
0.3640.091796.5399.030.0028bgqbzpa87xf
declare policy varchar2(512);...
0.321000.001580.4814.2187.823un99a0zwp4vd
select owner#, name, namespace...
0.29650.001420.4311.9393.4103guhbfpak0w7
select /*+ index(idl_ub1$ i_id...
0.2420.121184.6238.3564.53adzjh275fvvx4DBMS_SCHEDULERcall WWV_FLOW_WORKSHEET_API.DO...
0.24900.001161.1495.260.001u8v867f5ys43
select ts#, file#, block#, hwm...
0.20600.001006.1483.150.00c9umxngkc3byq
select sql_id, sql_exec_id, db...
0.19650.00941.709.2194.25fh5ufah919kun
select /*+ index(idl_sb4$ i_id...
0.172,2650.00860.7252.7452.250sbbcuruzd66f
select /*+ rule */ bucket_cnt,...
0.1740.04849.8799.670.005pj6mtazkhmdd
BEGIN /* KSXM:FLUSH DML_MON */...
0.17650.00828.228.4794.58a3jpjzh7mtwwp
select /*+ index(idl_ub2$ i_id...
0.161000.00806.8611.9091.778swypbbr0m372
select order#, columns, types ...
0.1610.16803.3699.460.049qrhhm7pf2ghv
insert into wrh$_mvparameter (...
0.1640.04779.8099.150.003kqrku32p6sfn
MERGE /*+ OPT_PARAM('_parallel...
0.157220.00747.1551.5558.902sxqgx5hx76qr
select /*+ rule */ bucket, end...
0.1510.15729.9153.7847.6913zr771hsyduv
select sum(BLOCKS) from DBA_SE...
0.151200.00719.0792.760.00aykvshm7zsabd
select size_for_estimate, size...
0.142220.00678.5874.0724.54121ffmrc95v7g
select i.obj#, i.ts#, i.file#,...
0.122,5810.00615.01106.870.022p9fv35c7zxtg
select /* KSXM:LOAD_DML_INF */...
3. 使用awrsqlrpt单独做那条SQL语句的报告：
drktaf71uygnb
$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 17 11:05:45 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @awrsqrpt.sql
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
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
868254545 RCAT 1 rcat
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 868254545 1 RCAT rcat cvcdds198.xn
Using868254545 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:
Listing all Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
rcat RCAT 91309 May 2022 09:00 1
91409 May 2022 10:00 1
91509 May 2022 11:00 1
91609 May 2022 12:00 1
91709 May 2022 13:00 1
91809 May 2022 14:00 1
91909 May 2022 15:00 1
92009 May 2022 16:00 1
92109 May 2022 17:00 1
92209 May 2022 18:00 1
92309 May 2022 19:00 1
92409 May 2022 20:00 1
92509 May 2022 21:00 1
92609 May 2022 22:00 1
92709 May 2022 23:00 1
92810 May 2022 00:00 1
92910 May 2022 01:00 1
93010 May 2022 02:00 1
93110 May 2022 03:00 1
93210 May 2022 04:00 1
93310 May 2022 05:00 1
93410 May 2022 06:00 1
93510 May 2022 07:00 1
93610 May 2022 08:00 1
93710 May 2022 09:00 1
93810 May 2022 10:18 1
93910 May 2022 11:00 1
94010 May 2022 12:00 1
94110 May 2022 13:00 1
94210 May 2022 14:00 1
94310 May 2022 15:00 1
94410 May 2022 16:00 1
94510 May 2022 17:00 1
94610 May 2022 18:00 1
94710 May 2022 19:00 1
94810 May 2022 20:00 1
94910 May 2022 21:00 1
95010 May 2022 22:00 1
95110 May 2022 23:00 1
95211 May 2022 00:00 1
95311 May 2022 01:00 1
95411 May 2022 02:00 1
95511 May 2022 03:00 1
95611 May 2022 04:00 1
95711 May 2022 05:00 1
95811 May 2022 06:00 1
95911 May 2022 07:00 1
96011 May 2022 08:00 1
96111 May 2022 09:00 1
96211 May 2022 10:00 1
96311 May 2022 11:00 1
96411 May 2022 12:00 1
96511 May 2022 13:00 1
96611 May 2022 14:00 1
96711 May 2022 15:00 1
96811 May 2022 16:00 1
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
rcat RCAT 96911 May 2022 17:00 1
97011 May 2022 18:00 1
97111 May 2022 19:00 1
97211 May 2022 20:00 1
97311 May 2022 21:00 1
97411 May 2022 22:00 1
97511 May 2022 23:00 1
97612 May 2022 00:00 1
97712 May 2022 01:00 1
97812 May 2022 02:00 1
97912 May 2022 03:00 1
98012 May 2022 04:00 1
98112 May 2022 05:00 1
98212 May 2022 06:00 1
98312 May 2022 07:00 1
98412 May 2022 08:00 1
98512 May 2022 09:00 1
98612 May 2022 10:00 1
98712 May 2022 11:00 1
98812 May 2022 12:00 1
98912 May 2022 13:00 1
99012 May 2022 14:00 1
99112 May 2022 15:00 1
99212 May 2022 16:00 1
99312 May 2022 17:00 1
99412 May 2022 18:00 1
99512 May 2022 19:00 1
99612 May 2022 20:00 1
99712 May 2022 21:00 1
99812 May 2022 22:00 1
99912 May 2022 23:00 1
100013 May 2022 00:00 1
100113 May 2022 01:00 1
100213 May 2022 02:00 1
100313 May 2022 03:00 1
100413 May 2022 04:00 1
100513 May 2022 05:00 1
100613 May 2022 06:00 1
100713 May 2022 07:00 1
100813 May 2022 08:00 1
100913 May 2022 09:00 1
101013 May 2022 10:00 1
101113 May 2022 11:00 1
101213 May 2022 12:00 1
101313 May 2022 13:00 1
101413 May 2022 14:00 1
101513 May 2022 15:00 1
101613 May 2022 16:00 1
101713 May 2022 17:00 1
101813 May 2022 18:00 1
101913 May 2022 19:00 1
102013 May 2022 20:00 1
102113 May 2022 21:00 1
102213 May 2022 22:00 1
102313 May 2022 23:00 1
102414 May 2022 00:00 1
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
rcat RCAT 102514 May 2022 01:00 1
102614 May 2022 02:00 1
102714 May 2022 03:00 1
102814 May 2022 04:00 1
102914 May 2022 05:00 1
103014 May 2022 06:00 1
103114 May 2022 07:00 1
103214 May 2022 08:00 1
103314 May 2022 09:00 1
103414 May 2022 10:00 1
103514 May 2022 11:00 1
103614 May 2022 12:00 1
103714 May 2022 13:00 1
103814 May 2022 14:00 1
103914 May 2022 15:00 1
104014 May 2022 16:00 1
104114 May 2022 17:00 1
104214 May 2022 18:00 1
104314 May 2022 19:00 1
104414 May 2022 20:00 1
104514 May 2022 21:00 1
104614 May 2022 22:00 1
104714 May 2022 23:00 1
104815 May 2022 00:00 1
104915 May 2022 01:00 1
105015 May 2022 02:00 1
105115 May 2022 03:00 1
105215 May 2022 04:00 1
105315 May 2022 05:00 1
105415 May 2022 06:00 1
105515 May 2022 07:00 1
105615 May 2022 08:00 1
105715 May 2022 09:00 1
105815 May 2022 10:00 1
105915 May 2022 11:00 1
106015 May 2022 12:00 1
106115 May 2022 13:00 1
106215 May 2022 14:00 1
106315 May 2022 15:00 1
106415 May 2022 16:00 1
106515 May 2022 17:00 1
106615 May 2022 18:00 1
106715 May 2022 19:00 1
106815 May 2022 20:00 1
106915 May 2022 21:00 1
107015 May 2022 22:00 1
107115 May 2022 23:00 1
107216 May 2022 00:00 1
107316 May 2022 01:00 1
107416 May 2022 02:00 1
107516 May 2022 03:00 1
107616 May 2022 04:00 1
107716 May 2022 05:00 1
107816 May 2022 06:00 1
107916 May 2022 07:00 1
108016 May 2022 08:00 1
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
rcat RCAT 108116 May 2022 09:00 1
108216 May 2022 10:00 1
108316 May 2022 11:00 1
108416 May 2022 12:00 1
108516 May 2022 13:00 1
108616 May 2022 14:00 1
108716 May 2022 15:00 1
108816 May 2022 16:00 1
108916 May 2022 17:00 1
109016 May 2022 18:00 1
109116 May 2022 19:00 1
109216 May 2022 20:00 1
109316 May 2022 21:00 1
109416 May 2022 22:00 1
109516 May 2022 23:00 1
109617 May 2022 00:00 1
109717 May 2022 01:00 1
109817 May 2022 02:00 1
109917 May 2022 03:00 1
110017 May 2022 04:00 1
110117 May 2022 05:00 1
110217 May 2022 06:00 1
110317 May 2022 07:00 1
110417 May 2022 08:00 1
110517 May 2022 09:00 1
110617 May 2022 10:00 1
110717 May 2022 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1106
Begin Snapshot Id specified: 1106
Enter value for end_snap: 1107
End Snapshot Id specified: 1107
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: drktaf71uygnb
SQL ID specified:drktaf71uygnb
Listing all available Container DB Ids for SQL Id drktaf71uygnb
Container DB Id Container Name
----------------- --------------
* 868254545 rcat
Using Container DB Id 868254545
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_1106_1107.html.To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrsqlrpt_1_1106_1107.html
Using the report name /home/oracle/awrsqlrpt_1_1106_1107.html
<html lang="en"><head><title>AWR SQL Report for DB: RCAT, Inst: rcat, Snaps: 1106-1107, SQL Id: drktaf71uygnb</title>
<style type="text/css">
body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;}
pre.awr{font:8pt Courier;color:black; background:White;}
pre_sqltext.awr{white-space: pre-wrap;}
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;}
h2.awr {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
h3.awr {font:bold 16pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}
li.awr {font: 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;}
th.awrnobg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;padding-left:4px; padding-right:4px;padding-bottom:2px}
th.awrbg {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}
td.awrnc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}
td.awrc {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}
td.awrnclb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;border-left: thin solid black;}
td.awrncbb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;border-left: thin solid black;border-right: thin solid black;}
td.awrncrb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;border-right: thin solid black;}
td.awrcrb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-right: thin solid black;}
td.awrclb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-left: thin solid black;}
td.awrcbb {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-left: thin solid black;border-right: thin solid black;}
a.awr {font:bold 8pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}
td.awrnct {font:8pt Arial,Helvetica,Geneva,sans-serif;border-top: thin solid black;color:black;background:White;vertical-align:top;}
td.awrct {font:8pt Arial,Helvetica,Geneva,sans-serif;border-top: thin solid black;color:black;background:#FFFFCC; vertical-align:top;}
td.awrnclbt{font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;border-top: thin solid black;border-left: thin solid black;}
td.awrncbbt{font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;border-left: thin solid black;border-right: thin solid black;border-top: thin solid black;}
td.awrncrbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;border-top: thin solid black;border-right: thin solid black;}
td.awrcrbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-top: thin solid black;border-right: thin solid black;}
td.awrclbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-top: thin solid black;border-left: thin solid black;}
td.awrcbbt {font:8pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;border-top: thin solid black;border-left: thin solid black;border-right: thin solid black;}
table.tdiff {border_collapse: collapse; }
table.tscl {width: 600;}
table.tscl tbody, table.tscl thead { display: block; }
table.tscl thead tr th {height: 12px;line-height: 12px;}
table.tscl tbody { height: 100px;overflow-y: auto; overflow-x: hidden;}
table.tscl tbody td, thead th {width: 200;}
.hidden {position:absolute;left:-10000px;top:auto;width:1px;height:1px;overflow:hidden;}
.pad {margin-left:17px;}
.doublepad {margin-left:34px;}
</style></head><body class="awr">
<h1 class="awr">
WORKLOAD REPOSITORY SQL Report
</h1>
<h2 class="awr">
Snapshot Period Summary
</h2>
<p />
<table border="1" width="500" summary="This table displays database instance information">
<tr><th class="awrbg" scope="col">DB Name</th><th class="awrbg" scope="col">DB Id</th><th class="awrbg" scope="col">Instance</th><th class="awrbg" scope="col">Inst num</th><th class="awrbg" scope="col">Startup Time</th><th class="awrbg" scope="col">Release</th><th class="awrbg" scope="col">RAC</th></tr>
<tr><td scope="row" class='awrnc'>RCAT</td><td align="right" class='awrnc'>868254545</td><td class='awrnc'>rcat</td><td align="right" class='awrnc'>1</td><td class='awrnc'>10-May-22 10:07</td><td class='awrnc'>19.0.0.0.0</td><td class='awrnc'>NO</td></tr>
</table>
<p />
<table border="1" width="500" summary="This table displays snapshot information">
<tr><th class="awrnobg" scope="col"></th><th class="awrbg" scope="col">Snap Id</th><th class="awrbg" scope="col">Snap Time</th><th class="awrbg" scope="col">Sessions</th><th class="awrbg" scope="col">Cursors/Session</th></tr>
<tr><td scope="row" class='awrnc'>Begin Snap:</td><td align="right" class='awrnc'>1106</td><td align="center" class='awrnc'>17-May-22 10:00:03</td><td align="right" class='awrnc'>64</td><td align="right" class='awrnc'> 8.4</td></tr>
<tr><td scope="row" class='awrc'>End Snap:</td><td align="right" class='awrc'>1107</td><td align="center" class='awrc'>17-May-22 11:00:14</td><td align="right" class='awrc'>64</td><td align="right" class='awrc'> 8.5</td></tr>
<tr><td scope="row" class='awrnc'>Elapsed:</td><td class='awrnc'> </td><td align="center" class='awrnc'> 60.18 (mins)</td><td class='awrnc'> </td><td class='awrnc'> </td></tr>
<tr><td scope="row" class='awrc'>DB Time:</td><td class='awrc'> </td><td align="center" class='awrc'> 0.00 (mins)</td><td class='awrc'> </td><td class='awrc'> </td></tr>
</table>
<p />
<a class="awr" name="10652"></a>
<h3 class="awr">SQL Summary</h3>
<ul>
</ul>
<table border="1" summary="SQL Summary"><tr><th class="awrbg" scope="col"> SQL Id</th><th class="awrbg" scope="col">ElapsedTime (ms)</th><th class="awrbg" scope="col">Module</th><th class="awrbg" scope="col">Action</th><th class="awrbg" scope="col">SQL Text</th><th class="awrbg" scope="col">Container DB Id</th></tr>
<tr><td class='awrc'><a class="awr" href="#10651drktaf71uygnb">drktaf71uygnb</a></td><td align="right" class='awrc'>2,071</td>
<td class='awrc'>
</td>
<td class='awrc'>
</td>
<td class='awrc'><a class="awr" href="#drktaf71uygnb">BEGIN sys.prvt_hdm.auto_execute( db_id => :bind_dbid, inst_id => :bind...</a></td><td align="right" class='awrc'>868254545</td></tr>
</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<a class="awr" name="10651drktaf71uygnb"></a>
<h3 class="awr">SQL ID: drktaf71uygnb (Container DB Id: 868254545)</h3>
<ul>
<li class="awr"> 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range</li>
<li class="awr"><a class="awr" href="#drktaf71uygnb">BEGIN sys.prvt_hdm.auto_execute( db_id => :bind_...</a></li>
</ul>
<table border="1" summary="SQL ID: drktaf71uygnb (Container DB Id: 868254545). . 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range"><tr><th class="awrbg" scope="col">#</th><th class="awrbg" scope="col">Plan Hash Value</th><th class="awrbg" scope="col">Total Elapsed Time(ms)</th><th class="awrbg" scope="col">Executions</th><th class="awrbg" scope="col">1st Capture Snap ID</th><th class="awrbg" scope="col">Last Capture Snap ID</th></tr>
<tr><td class='awrc'><a class="awr" href="#303drktaf71uygnb0">1</a></td><td class='awrc'><a class="awr" href="#303drktaf71uygnb0">0</a></td><td align="right" class='awrc'>2,071</td><td align="right" class='awrc'>1</td><td align="right" class='awrc'>1107</td><td align="right" class='awrc'>1107</td></tr>
</table><p />
<br /><a class="awr" href="#top">Back to Top</a><p />
<a class="awr" name="303drktaf71uygnb0"></a>
<h2 class="awr">
Plan 1(PHV: 0)
</h2>
<ul>
<li class="awr"><a class="awr" href="#10653drktaf71uygnb0">Plan Statistics</a></li>
<li class="awr"><a class="awr" href="#99996drktaf71uygnb0">Execution Plan</a></li>
</ul>
<a class="awr" href="#top">Back to Top</a>
<a class="awr" name="10653drktaf71uygnb0"></a>
<h3 class="awr">Plan Statistics</h3>
<ul>
<li class="awr"> % Snap Total shows the % of the statistic for the SQL statement compared to the instance total</li>
</ul>
<table border="1" summary="Plan Statistics. . % Snap Total shows the % of the statistic for the SQL statement compared to the instance total"><tr><th class="awrbg" scope="col">Stat Name</th><th class="awrbg" scope="col">Statement Total</th><th class="awrbg" scope="col">Per Execution</th><th class="awrbg" scope="col">% Snap Total</th></tr>
<tr><td class='awrc'>Elapsed Time (ms)</td><td align="right" class='awrc'>2,071</td><td align="right" class='awrc'>2,070.78</td><td align="right" class='awrc'>10195.85</td></tr>
<tr><td class='awrnc'>CPU Time (ms)</td><td align="right" class='awrnc'>859</td><td align="right" class='awrnc'>858.97</td><td align="right" class='awrnc'>4223.70</td></tr>
<tr><td class='awrc'>Executions</td><td align="right" class='awrc'>1</td><td align="right" class='awrc'>1.00</td><td align="right" class='awrc'>0.01</td></tr>
<tr><td class='awrnc'>Buffer Gets</td><td align="right" class='awrnc'>9,771</td><td align="right" class='awrnc'>9,771.00</td><td align="right" class='awrnc'>3.58</td></tr>
<tr><td class='awrc'>Disk Reads</td><td align="right" class='awrc'>1,626</td><td align="right" class='awrc'>1,626.00</td><td align="right" class='awrc'>14.49</td></tr>
<tr><td class='awrnc'>Parse Calls</td><td align="right" class='awrnc'>1</td><td align="right" class='awrnc'>1.00</td><td align="right" class='awrnc'>0.01</td></tr>
<tr><td class='awrc'>Rows</td><td align="right" class='awrc'>1</td><td align="right" class='awrc'>1.00</td><td align="right" class='awrc'> </td></tr>
<tr><td class='awrnc'>User I/O Wait Time (ms)</td><td align="right" class='awrnc'>993</td><td align="right" class='awrnc'>992.68</td><td align="right" class='awrnc'>24.72</td></tr>
<tr><td class='awrc'>Cluster Wait Time (ms)</td><td align="right" class='awrc'>0</td><td align="right" class='awrc'>0.00</td><td align="right" class='awrc'>0.00</td></tr>
<tr><td class='awrnc'>Application Wait Time (ms)</td><td align="right" class='awrnc'>0</td><td align="right" class='awrnc'>0.00</td><td align="right" class='awrnc'>0.00</td></tr>
<tr><td class='awrc'>Concurrency Wait Time (ms)</td><td align="right" class='awrc'>0</td><td align="right" class='awrc'>0.00</td><td align="right" class='awrc'>0.00</td></tr>
<tr><td class='awrnc'>Invalidations</td><td align="right" class='awrnc'>0</td><td align="right" class='awrnc'> </td><td align="right" class='awrnc'> </td></tr>
<tr><td class='awrc'>Version Count</td><td align="right" class='awrc'>1</td><td align="right" class='awrc'> </td><td align="right" class='awrc'> </td></tr>
<tr><td class='awrnc'>Sharable Mem(KB)</td><td align="right" class='awrnc'>21</td><td align="right" class='awrnc'> </td><td align="right" class='awrnc'> </td></tr>
</table><p />
<a class="awr" href="#303drktaf71uygnb0">Back to Plan 1(PHV: 0)</a>
<br /><a class="awr" href="#top">Back to Top</a><p />
<a class="awr" name="99996drktaf71uygnb0"></a>
<h3 class="awr">
Execution Plan
</h3>
<p />
No data exists for this section of the report.
<p />
<br />
<a class="awr" href="#303drktaf71uygnb0">Back to Plan 1(PHV: 0)</a>
<br /><a class="awr" href="#top">Back to Top</a><p />
<a class="awr" name="99997"></a>
<p />
<h3 class="awr">Full SQL Text</h3><p />
<table border="1" summary="This table displays the text of the SQL statements which have been
referred to in the report">
<tr><th class="awrbg" scope="col">SQL Id</th><th class="awrbg" scope="col">SQL Text</th></tr>
<tr><td scope="row" class='awrc'><a class="awr" name="drktaf71uygnb"></a>drktaf71uygnb</td><td class='awrc'><pre_sqltext class="awr">BEGIN
sys.prvt_hdm.auto_execute(
db_id => :bind_dbid,
inst_id => :bind_inst,
end_snap => :bind_snap,
time_left_in_secs => :bind_time);
END;</pre_sqltext></td></tr>
</table>
<p />
<br /><a class="awr" href="#top">Back to Top</a><p />
</body></html>
Report written to /home/oracle/awrsqlrpt_1_1106_1107.html
SQL>
4. 查看AWRSQL报告：
WORKLOAD REPOSITORY SQL ReportSnapshot Period Summary
DB NameDB IdInstanceInst numStartup TimeReleaseRAC
RCAT868254545rcat110-May-22 10:0719.0.0.0.0NO
Snap IdSnap TimeSessionsCursors/Session
Begin Snap:110617-May-22 10:00:03648.4
End Snap:110717-May-22 11:00:14648.5
Elapsed: 60.18 (mins)
DB Time: 0.00 (mins)
SQL Summary
SQL IdElapsedTime (ms)ModuleActionSQL TextContainer DB Id
drktaf71uygnb2,071 BEGIN sys.prvt_hdm.auto_execute( db_id => :bind_dbid, inst_id => :bind...868254545
Back to Top
SQL ID: drktaf71uygnb (Container DB Id: 868254545)
[*] 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
[*]BEGIN sys.prvt_hdm.auto_execute( db_id => :bind_...
#Plan Hash ValueTotal Elapsed Time(ms)Executions1st Capture Snap IDLast Capture Snap ID
102,071111071107
Back to Top
Plan 1(PHV: 0)
[*]Plan Statistics
[*]Execution Plan
Back to TopPlan Statistics
[*] % Snap Total shows the % of the statistic for the SQL statement compared to the instance total
Stat NameStatement TotalPer Execution% Snap Total
Elapsed Time (ms)2,0712,070.7810195.85
CPU Time (ms)859858.974223.70
Executions11.000.01
Buffer Gets9,7719,771.003.58
Disk Reads1,6261,626.0014.49
Parse Calls11.000.01
Rows11.00
User I/O Wait Time (ms)993992.6824.72
Cluster Wait Time (ms)00.000.00
Application Wait Time (ms)00.000.00
Concurrency Wait Time (ms)00.000.00
Invalidations0
Version Count1
Sharable Mem(KB)21
Back to Plan 1(PHV: 0)
Back to Top
Execution Plan No data exists for this section of the report.
Back to Plan 1(PHV: 0)
Back to Top
Full SQL Text
SQL IdSQL Text
drktaf71uygnbBEGIN sys.prvt_hdm.auto_execute( db_id => :bind_dbid, inst_id => :bind_inst, end_snap => :bind_snap, time_left_in_secs => :bind_time); END;
Back to Top
5. v$active_session_history
每间隔1秒收集会话信息，对我们调优特别有帮助。该视图记录SQL_ID。
selectdistinct session_state from v$active_session_history;ON CPU
WAITING
我们只需要研究处于WAITING状态下的会话，并做了TOP-N分析。
<blockquote>select * from (select event, sql_id, user_id , count(*) CNT from v$active_session_history s1 control file parallel write 0 263
2 log file sync 0 34
3 db file async I/O submit 0 31
4 latch free 0 21
5 log file parallel write 0 19
6 control file sequential read 0 10
7 LGWR any worker group 0 6
8 oracle thread bootstrap 0 3
9 os thread creation 0 2
10 latch free f3yfg50ga0r8n 0 2
select a.event, a.sql_id, dbms_lob.substr(s.sql_fulltext,200,1) sqlf, user_id,cnt
from
(select * from
(select event, sql_id, user_id, count(*) cnt
from v$active_session_history s
where sample_time > sysdate - 1 and session_state='WAITING'
group by event, sql_id, user_id
order by cnt desc) where rownum <=10
) a, v$sqlarea s
where a.sql_id =s.sql_id (+);
页:
[1]