【效能優化】sqlplus中使用指令碼生成awr報告

sqlplus中使用指令碼生成awr報告

[oracle@ora11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Sep 9 07:50:32 2015

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD>@?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
298637399 PROD 1 PROD

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
------------ -------- ------------ ------------ ------------
* 298637399 1 PROD PROD ora11g.cuug.
com

Using 298637399 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: 2

Listing the last 2 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
PROD PROD 1 08 Sep 2015 15:06 1
2 08 Sep 2015 16:00 1

3 08 Sep 2015 17:12 1
4 08 Sep 2015 18:00 1
5 08 Sep 2015 19:00 1
6 08 Sep 2015 20:00 1
7 08 Sep 2015 21:01 1
8 08 Sep 2015 22:00 1
9 08 Sep 2015 23:00 1
10 09 Sep 2015 00:00 1

11 09 Sep 2015 01:01 1

12 09 Sep 2015 02:00 1
13 09 Sep 2015 03:00 1
14 09 Sep 2015 04:00 1
15 09 Sep 2015 05:00 1
16 09 Sep 2015 06:00 1
17 09 Sep 2015 07:00 1
18 09 Sep 2015 07:44 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5
Begin Snapshot Id specified: 5

Enter value for end_snap: 6
End Snapshot Id specified: 6

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_5_6.html. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 20150908_19_20.html

Using the report name 20150908_19_20.html

手動生成快照命令
SYS@PROD>exec dbms_workload_repository.create_snapshot()

PL/SQL procedure successfully completed.

SYS@PROD>
SYS@PROD>desc dba_hist_snapshot;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER
SNAP_FLAG NUMBER

SYS@PROD>
SYS@PROD>col BEGIN_INTERVAL_TIME for a35
SYS@PROD>col END_INTERVAL_TIME for a35
SYS@PROD>
SYS@PROD>select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_LEVEL from dba_hist_snapshot order by 2;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
---------- ----------------------------------- ----------------------------------- ----------
1 08-SEP-15 02.56.03.000 PM 08-SEP-15 03.06.11.337 PM 1
2 08-SEP-15 03.06.11.337 PM 08-SEP-15 04.00.26.041 PM 1
3 08-SEP-15 05.00.52.000 PM 08-SEP-15 05.12.00.283 PM 1
4 08-SEP-15 05.12.00.283 PM 08-SEP-15 06.00.12.363 PM 1
5 08-SEP-15 06.00.12.363 PM 08-SEP-15 07.00.25.711 PM 1
6 08-SEP-15 07.00.25.711 PM 08-SEP-15 08.00.41.147 PM 1
7 08-SEP-15 08.00.41.147 PM 08-SEP-15 09.01.00.050 PM 1
8 08-SEP-15 09.01.00.050 PM 08-SEP-15 10.00.15.860 PM 1
9 08-SEP-15 10.00.15.860 PM 08-SEP-15 11.00.33.050 PM 1
10 08-SEP-15 11.00.33.050 PM 09-SEP-15 12.00.49.096 AM 1
11 09-SEP-15 12.50.42.000 AM 09-SEP-15 01.01.49.366 AM 1
12 09-SEP-15 01.40.44.000 AM 09-SEP-15 02.00.55.116 AM 1
13 09-SEP-15 02.00.55.116 AM 09-SEP-15 03.00.12.824 AM 1
14 09-SEP-15 03.00.12.824 AM 09-SEP-15 04.00.31.721 AM 1
15 09-SEP-15 04.00.31.721 AM 09-SEP-15 05.00.47.334 AM 1
16 09-SEP-15 05.00.47.334 AM 09-SEP-15 06.00.06.808 AM 1
17 09-SEP-15 06.00.06.808 AM 09-SEP-15 07.00.26.765 AM 1
18 09-SEP-15 07.00.26.765 AM 09-SEP-15 07.44.56.001 AM 1
19 09-SEP-15 07.44.56.001 AM 09-SEP-15 07.58.11.162 AM 1

19 rows selected.

SYS@PROD>

呂星昊
2015.9.10

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1795952/，如需轉載，請註明出處，否則將追究法律責任。

【效能優化】sqlplus中使用指令碼生成awr報告

相關文章