Oracle AWR报告解析
export ORACLE_SID=payment
sqlplus "/as sysdba"
如果是在本地，你可以去oracle安装目录下的文件夹rdbms/admin/内，将全部以awr开头的文件复制出来。
SQL> @?/rdbms/admin/awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2647219378 PAYMENT 1 payment
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: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2647219378 1 PAYMENT payment db1
2647219378 1 BJEBANK bjebank db5
2647219378 1 BJEBANK bjebank db1
2647219378 1 BJEBANK bjebank db4
* 2647219378 1 PAYMENT payment gateway
Using 2647219378 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
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
payment PAYMENT 50027 29 Feb 2012 23:00 1
50028 01 Mar 2012 00:00 1
50029 01 Mar 2012 01:00 1
50030 01 Mar 2012 02:00 1
50031 01 Mar 2012 03:00 1
50032 01 Mar 2012 04:00 1
50033 01 Mar 2012 05:00 1
50034 01 Mar 2012 06:00 1
50035 01 Mar 2012 07:00 1
50036 01 Mar 2012 08:00 1
50037 01 Mar 2012 09:00 1
50038 01 Mar 2012 10:00 1
50039 01 Mar 2012 11:00 1
50040 01 Mar 2012 12:00 1
50041 01 Mar 2012 13:00 1
50042 01 Mar 2012 14:00 1
50043 01 Mar 2012 15:00 1
50044 01 Mar 2012 16:00 1
50045 01 Mar 2012 17:00 1
50046 01 Mar 2012 18:00 1
50047 01 Mar 2012 19:00 1
50048 01 Mar 2012 20:00 1
50049 01 Mar 2012 21:00 1
50050 01 Mar 2012 22:00 1
50051 01 Mar 2012 23:00 1
50052 02 Mar 2012 00:00 1
50053 02 Mar 2012 01:00 1
50054 02 Mar 2012 02:00 1
50055 02 Mar 2012 03:00 1
50056 02 Mar 2012 04:00 1
50057 02 Mar 2012 05:00 1
50058 02 Mar 2012 06:00 1
50059 02 Mar 2012 07:00 1
50060 02 Mar 2012 08:00 1
50061 02 Mar 2012 09:00 1
50062 02 Mar 2012 10:00 1
50063 02 Mar 2012 11:00 1
50064 02 Mar 2012 12:00 1
50065 02 Mar 2012 13:00 1
50066 02 Mar 2012 14:00 1
50067 02 Mar 2012 15:00 1
50068 02 Mar 2012 16:00 1
50069 02 Mar 2012 17:00 1
50070 02 Mar 2012 18:00 1
50071 02 Mar 2012 19:00 1
50072 02 Mar 2012 20:00 1
50073 02 Mar 2012 21:00 1
50074 02 Mar 2012 22:00 1
50075 02 Mar 2012 23:00 1
50076 03 Mar 2012 00:00 1
50077 03 Mar 2012 01:00 1
50078 03 Mar 2012 02:00 1
50079 03 Mar 2012 03:00 1
50080 03 Mar 2012 04:00 1
50081 03 Mar 2012 05:00 1
50082 03 Mar 2012 06:00 1
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
payment PAYMENT 50083 03 Mar 2012 07:00 1
50084 03 Mar 2012 08:00 1
50085 03 Mar 2012 09:00 1
50086 03 Mar 2012 10:00 1
50087 03 Mar 2012 11:00 1
50088 03 Mar 2012 12:00 1
50089 03 Mar 2012 13:00 1
50090 03 Mar 2012 14:00 1
50091 03 Mar 2012 15:00 1
50092 03 Mar 2012 16:00 1
50093 03 Mar 2012 17:00 1
50094 03 Mar 2012 18:00 1
50095 03 Mar 2012 19:00 1
50096 03 Mar 2012 20:00 1
50097 03 Mar 2012 21:00 1
50098 03 Mar 2012 22:00 1
50099 03 Mar 2012 23:00 1
50100 04 Mar 2012 00:00 1
50101 04 Mar 2012 01:00 1
50102 04 Mar 2012 02:00 1
50103 04 Mar 2012 03:00 1
50104 04 Mar 2012 04:00 1
50105 04 Mar 2012 05:00 1
50106 04 Mar 2012 06:00 1
50107 04 Mar 2012 07:00 1
50108 04 Mar 2012 08:00 1
50109 04 Mar 2012 09:00 1
50110 04 Mar 2012 10:00 1
50111 04 Mar 2012 11:00 1
50112 04 Mar 2012 12:00 1
50113 04 Mar 2012 13:00 1
50114 04 Mar 2012 14:00 1
50115 04 Mar 2012 15:00 1
50116 04 Mar 2012 16:00 1
50117 04 Mar 2012 17:00 1
50118 04 Mar 2012 18:00 1
50119 04 Mar 2012 19:00 1
50120 04 Mar 2012 20:00 1
50121 04 Mar 2012 21:00 1
50122 04 Mar 2012 22:00 1
50123 04 Mar 2012 23:00 1
50124 05 Mar 2012 00:00 1
50125 05 Mar 2012 01:00 1
50126 05 Mar 2012 02:00 1
50127 05 Mar 2012 03:00 1
50128 05 Mar 2012 04:00 1
50129 05 Mar 2012 05:00 1
50130 05 Mar 2012 06:00 1
50131 05 Mar 2012 07:00 1
50132 05 Mar 2012 08:00 1
50133 05 Mar 2012 09:00 1
50134 05 Mar 2012 10:00 1
50135 05 Mar 2012 11:00 1
50136 05 Mar 2012 12:00 1
50137 05 Mar 2012 13:00 1
50138 05 Mar 2012 14:00 1
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
payment PAYMENT 50139 05 Mar 2012 15:00 1
50140 05 Mar 2012 16:00 1
50141 05 Mar 2012 17:00 1
50142 05 Mar 2012 18:00 1
50143 05 Mar 2012 19:00 1
50144 05 Mar 2012 20:00 1
50145 05 Mar 2012 21:00 1
50146 05 Mar 2012 22:00 1
50147 05 Mar 2012 23:00 1
50148 06 Mar 2012 00:00 1
50149 06 Mar 2012 01:00 1
50150 06 Mar 2012 02:00 1
50151 06 Mar 2012 03:00 1
50152 06 Mar 2012 04:00 1
50153 06 Mar 2012 05:00 1
50154 06 Mar 2012 06:00 1
50155 06 Mar 2012 07:00 1
50156 06 Mar 2012 08:00 1
50157 06 Mar 2012 09:00 1
50158 06 Mar 2012 10:00 1
50159 06 Mar 2012 11:00 1
50160 06 Mar 2012 12:00 1
50161 06 Mar 2012 13:00 1
50162 06 Mar 2012 14:00 1
50163 06 Mar 2012 15:00 1
50164 06 Mar 2012 16:00 1
50165 06 Mar 2012 17:43 1
50166 06 Mar 2012 18:00 1
50167 06 Mar 2012 19:00 1
50168 06 Mar 2012 20:00 1
50169 06 Mar 2012 21:00 1
50170 06 Mar 2012 22:00 1
50171 06 Mar 2012 23:00 1
50172 07 Mar 2012 00:00 1
50173 07 Mar 2012 01:00 1
50174 07 Mar 2012 02:00 1
50175 07 Mar 2012 03:00 1
50176 07 Mar 2012 04:00 1
50177 07 Mar 2012 05:00 1
50178 07 Mar 2012 06:00 1
50179 07 Mar 2012 07:00 1
50180 07 Mar 2012 08:00 1
50181 07 Mar 2012 09:00 1
50182 07 Mar 2012 10:00 1
50183 07 Mar 2012 11:00 1
50184 07 Mar 2012 12:00 1
50185 07 Mar 2012 13:00 1
50186 07 Mar 2012 14:00 1
50187 07 Mar 2012 15:00 1
50188 07 Mar 2012 16:00 1
50189 07 Mar 2012 17:00 1
50190 07 Mar 2012 18:00 1
50191 07 Mar 2012 19:00 1
50192 07 Mar 2012 20:00 1
50193 07 Mar 2012 21:00 1
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
payment PAYMENT 50194 07 Mar 2012 22:00 1
50195 07 Mar 2012 23:00 1
50196 08 Mar 2012 00:00 1
50197 08 Mar 2012 01:00 1
50198 08 Mar 2012 02:00 1
50199 08 Mar 2012 03:00 1
50200 08 Mar 2012 04:00 1
50201 08 Mar 2012 05:00 1
50202 08 Mar 2012 06:00 1
50203 08 Mar 2012 07:00 1
50204 08 Mar 2012 08:00 1
50205 08 Mar 2012 09:00 1
50206 08 Mar 2012 10:00 1
50207 08 Mar 2012 11:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 50204
Begin Snapshot Id specified: 50204
Enter value for end_snap: 50205
End Snapshot Id specified: 50205
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_50204_50205.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt
Using the report name awrrpt
html。。。
我将截图献上：
Load Profile
| Per Second | Per Transaction | |
|---|---|---|
| Redo size: | 1,548.69 | 1,023.00 |
| Logical reads: | 2,600.30 | 1,717.65 |
| Block changes: | 12.08 | 7.98 |
| Physical reads: | 1,148.69 | 758.78 |
| Physical writes: | 1.44 | 0.95 |
| User calls: | 8.84 | 5.84 |
| Parses: | 8.04 | 5.31 |
| Hard parses: | 0.03 | 0.02 |
| Sorts: | 157.89 | 104.30 |
| Logons: | 0.22 | 0.14 |
| Executes: | 8.76 | 5.79 |
| Transactions: | 1.51 |
| % Blocks changed per Read: | 0.46 | Recursive Call %: | 87.87 |
| Rollback per transaction %: | 74.83 | Rows per Sort: | 0.45 |
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 55.82 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.58 | Soft Parse %: | 99.66 |
| Execute to Parse %: | 8.29 | Latch Hit %: | 99.98 |
| Parse CPU to Parse Elapsd %: | 30.02 | % Non-Parse CPU: | 98.25 |
Shared Pool Statistics
| Begin | End | |
|---|---|---|
| Memory Usage %: | 74.59 | 74.83 |
| % SQL with executions>1: | 95.20 | 96.11 |
| % Memory for SQL w/exec>1: | 90.68 | 94.96 |
Top 5 Timed Events
| Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
|---|---|---|---|---|---|
| CPU time | 79 | 98.8 | |||
| db file scattered read | 261,014 | 15 | 0< /TD> | 19.0 | User I/O |
| log file parallel write | 4,297 | 2 | 0 | 2.0 | System I/O |
| log file sync | 1,691 | 1 | 1 | 1.1 | Commit |
| os thread startup | 22 | 1 | 25 | .7 | Concurrency |
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
| Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|
| 35 | 35 | 12 | 2.89 | 43.18 | 7jh4kggcpj59c | DECLARE job BINARY_INTEGER := ... | |
| 15 | 15 | 60 | 0.26 | 19 .22 | 05mqrxpqc5q4y | DECLARE job BINARY_INTEGER := ... | |
| 14 | 14 | 60 | 0.23 | 16.90 | 63pda6zkqvzja | SELECT V.TRANID, V.TN FROM VI... | |
| 6 | 6 | 1 | 6.34 | 7.90< /TD> | 13ga7qmrfu6cb | DECLARE job BINARY_INTEGER := ... | |
| 6 | 6 | 1 | 6.33 | 7.89 | c8vrmzdt9xpz2 | select distinct t1.cardnum ali... | |
| 3 | 3 | 1 | 3.17 | 3.96< /TD> | dcphavbybjqc3 | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 3.15 | 3.93 | f103n4vm3m47s | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 3.11 | 3.88< /TD> | fxhdknkv2hv07 | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.97 | 3.71 | 2y5yxvh4d6rux | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.82 | 3.51< /TD> | 2cjbnfynfzadu | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.78 | 3.46 | aj9myscqtbdmn | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.77 | 3.45< /TD> | 42qw4q29fz6p1 | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.77 | 3.45 | 93d5q7qmpt3dh | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.74 | 3.42< /TD> | 88q73pbtxn82f | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.72 | 3.39 | a4gtqvsx0zzgc | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.71 | 3.38< /TD> | cqwd40vgpczct | insert into TAB_MONITOR_TEMP(T... | |
| 3 | 3 | 1 | 2.70 | 3.36 | g5uc91hdd0nzp | insert into TAB_MONITOR_TEMP(T... | |
| 2 | 2 | 60 | 0.03 | 2.25 | 8rhgawgzww8hh | DECLARE job BINARY_INTEGER := ... | |
| 2 | 2 | 2,280 | 0.00 | 2.14 | b1662b6fmrjav | oracle@gateway (TNS V1-V3) | INSERT INTO "DIVTS"."DVID_TAS... |
| 1 | 1 | 60 | 0.02 | 1.77 | 9hd6pdvq3wbrc | SELECT V.TRANID, V.LOCALDATE,... | |
| 1 | 1 | 1 | 0.95 | 1.18 | bunssq950snhf | insert into wrh$_sga_target_ad... |
扫一扫
1222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
