体检报告--AWR
awrrpt.sql方式获取
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1475756829 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:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1475756829 1 ORCL orcl orasql
Using 1475756829 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 139 15 Jul 2017 00:00 1
140 15 Jul 2017 01:00 1
141 15 Jul 2017 02:00 1
142 15 Jul 2017 03:00 1
143 15 Jul 2017 04:00 1
144 15 Jul 2017 05:00 1
145 15 Jul 2017 06:00 1
146 15 Jul 2017 07:00 1
147 15 Jul 2017 08:00 1
148 15 Jul 2017 09:00 1
149 15 Jul 2017 10:00 1
150 15 Jul 2017 11:00 1
151 15 Jul 2017 12:00 1
152 15 Jul 2017 13:00 1
153 15 Jul 2017 14:00 1
154 15 Jul 2017 15:00 1
155 15 Jul 2017 16:00 1
156 15 Jul 2017 17:00 1
157 15 Jul 2017 18:00 1
158 15 Jul 2017 19:00 1
159 15 Jul 2017 20:00 1
160 15 Jul 2017 21:00 1
161 15 Jul 2017 21:45 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 160
Begin Snapshot Id specified: 160
Enter value for end_snap: 161
End Snapshot Id specified: 161
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_160_161.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_1_160_161.html
调用命令包获取
格式:select output from table(dbms_workload_repository.awr_report_html(DB Id,Inst Num,begin_snap,end_snap));
SQL> set pagesize 0
SQL> set linesize 121
SQL> spool awrrpt_2.html
SQL> select output from table(dbms_workload_repository.awr_report_html(1475756829,1,160,161));
SQL> spool off
体检报告显示有胃病,需要获取胃镜报告---ASH
ashrpt.sql方式获取
SQL> alter session set nls_date_language='american';
Session altered.
SQL> @?/rdbms/admin/ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
1475756829 ORCL 1 orcl
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
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
------------ -------- ------------ ------------ ------------
* 1475756829 1 ORCL orcl orasql
Defaults to current database
Using database id: 1475756829
Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Using instance number(s): 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 09-Jul-17 22:47:42 [ 8599 mins in the past]
Latest ASH sample available: 15-Jul-17 22:06:06 [ 1 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
-- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time: 21:00
Report begin time specified: 21:00
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 45
Report duration specified: 45
Using 15-Jul-17 21:00:00 as report begin time
Using 15-Jul-17 21:45:00 as report end time
Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- In the 'Activity Over Time' section of the ASH report,
-- the analysis period is divided into smaller slots
-- and top wait events are reported in each of those slots.
-- Default:
-- The analysis period will be automatically split upto 10 slots
-- complying to a minimum slot width of
-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:
Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- ASH Report can accept "Report Targets",
-- like a particular SQL statement, or a particular SESSION,
-- to generate the report on. If one or more report targets are
-- specified, then the data used to generate the report will only be
-- the ASH samples that pertain to ALL the specified report targets.
-- Default:
-- If none of the report targets are specified,
-- then the target defaults to all activity in the database instance.
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0715_2145.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: ashrpt_1_0715_2145.html
ASH报告的获取不同于AWR的地方在于,快照之间有无重启动作不影响报告的获取。
调用命令包获取
格式:select output from table(dbms_workload_repository.ash_report_html(DB Id,Inst Num,begin_time,end_time));
SQL> set pagesize 0
SQL> set linesize 121
SQL> spool ashrpt_3.html
SQL> select output from table(dbms_workload_repository.ash_report_html(1475756829,1,SYSDATE-85/1440,SYSDATE-45/1440));
SQL> spool off
SYSDATE-85/1440 表示当前时间往后推85分钟
病历卡记录--方便阅读的体检报告--ADDM
SQL> @?/rdbms/admin/addmrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1475756829 ORCL 1 orcl
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1475756829 1 ORCL orcl orasql
Using 1475756829 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.
Listing the last 3 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 147 15 Jul 2017 08:00 1
148 15 Jul 2017 09:00 1
149 15 Jul 2017 10:00 1
150 15 Jul 2017 11:00 1
151 15 Jul 2017 12:00 1
152 15 Jul 2017 13:00 1
153 15 Jul 2017 14:00 1
154 15 Jul 2017 15:00 1
155 15 Jul 2017 16:00 1
156 15 Jul 2017 17:00 1
157 15 Jul 2017 18:00 1
158 15 Jul 2017 19:00 1
159 15 Jul 2017 20:00 1
160 15 Jul 2017 21:00 1
161 15 Jul 2017 21:45 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 160
Begin Snapshot Id specified: 160
Enter value for end_snap: 161
End Snapshot Id specified: 161
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_160_161.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: addmrpt_1_160_161.txt
体检报告的对比---AWRDD
SQL> @?/rdbms/admin/awrddrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
1475756829 1475756829 ORCL 1 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: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1475756829 1 ORCL orcl orasql
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1475756829 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
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 139 15 Jul 2017 00:00 1
140 15 Jul 2017 01:00 1
141 15 Jul 2017 02:00 1
142 15 Jul 2017 03:00 1
143 15 Jul 2017 04:00 1
144 15 Jul 2017 05:00 1
145 15 Jul 2017 06:00 1
146 15 Jul 2017 07:00 1
147 15 Jul 2017 08:00 1
148 15 Jul 2017 09:00 1
149 15 Jul 2017 10:00 1
150 15 Jul 2017 11:00 1
151 15 Jul 2017 12:00 1
152 15 Jul 2017 13:00 1
153 15 Jul 2017 14:00 1
154 15 Jul 2017 15:00 1
155 15 Jul 2017 16:00 1
156 15 Jul 2017 17:00 1
157 15 Jul 2017 18:00 1
158 15 Jul 2017 19:00 1
159 15 Jul 2017 20:00 1
160 15 Jul 2017 21:00 1
161 15 Jul 2017 21:45 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 160
First Begin Snapshot Id specified: 160
Enter value for end_snap: 161
First End Snapshot Id specified: 161
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1475756829 1 ORCL orcl orasql
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1475756829 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
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_days2: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 139 15 Jul 2017 00:00 1
140 15 Jul 2017 01:00 1
141 15 Jul 2017 02:00 1
142 15 Jul 2017 03:00 1
143 15 Jul 2017 04:00 1
144 15 Jul 2017 05:00 1
145 15 Jul 2017 06:00 1
146 15 Jul 2017 07:00 1
147 15 Jul 2017 08:00 1
148 15 Jul 2017 09:00 1
149 15 Jul 2017 10:00 1
150 15 Jul 2017 11:00 1
151 15 Jul 2017 12:00 1
152 15 Jul 2017 13:00 1
153 15 Jul 2017 14:00 1
154 15 Jul 2017 15:00 1
155 15 Jul 2017 16:00 1
156 15 Jul 2017 17:00 1
157 15 Jul 2017 18:00 1
158 15 Jul 2017 19:00 1
159 15 Jul 2017 20:00 1
160 15 Jul 2017 21:00 1
161 15 Jul 2017 21:45 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 158
Second Begin Snapshot Id specified: 158
Enter value for end_snap2: 159
Second End Snapshot Id specified: 159
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_160_1_158.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrdiff_1_160_1_158.html
胃镜ASH发现发现胃里有痘痘,获取痘痘的信息,活检报告--AWRSQRPT
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1475756829 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: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1475756829 1 ORCL orcl orasql
Using 1475756829 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 139 15 Jul 2017 00:00 1
140 15 Jul 2017 01:00 1
141 15 Jul 2017 02:00 1
142 15 Jul 2017 03:00 1
143 15 Jul 2017 04:00 1
144 15 Jul 2017 05:00 1
145 15 Jul 2017 06:00 1
146 15 Jul 2017 07:00 1
147 15 Jul 2017 08:00 1
148 15 Jul 2017 09:00 1
149 15 Jul 2017 10:00 1
150 15 Jul 2017 11:00 1
151 15 Jul 2017 12:00 1
152 15 Jul 2017 13:00 1
153 15 Jul 2017 14:00 1
154 15 Jul 2017 15:00 1
155 15 Jul 2017 16:00 1
156 15 Jul 2017 17:00 1
157 15 Jul 2017 18:00 1
158 15 Jul 2017 19:00 1
159 15 Jul 2017 20:00 1
160 15 Jul 2017 21:00 1
161 15 Jul 2017 21:45 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 160
Begin Snapshot Id specified: 160
Enter value for end_snap: 161
End Snapshot Id specified: 161
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 输入SQL_ID