Awr report

From pressy's brainbackup
Jump to: navigation, search

Oracle AWR Report

Automatic Workload Repository (AWR) is a nice performance overview of the database... (attention, this needs diagnostic pack in the enterprise edition)

serverDB31:orapa1 3% sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 4 10:08:04 2014

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

SQL> conn / as sysdba
Connected.
SQL>
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_fix_control                         string      5099019:ON, 5705630:ON, 605565
                                                 8:OFF, 6399597:ON, 6430500:ON,
                                                  6440977:ON, 6626018:ON, 69722
                                                 91:ON, 8937971:ON, 9196440:ON,
                                                  9495669:ON, 13077335
                                                 :ON, 13627489:ON, 14255600:ON
control_file_record_keep_time        integer     30
control_files                        string      /oracle/SID/origlogA/cntrl/cnt
                                                 rlSID.dbf, /oracle/SID/sapdata
                                                 1/cntrl/cntrlSID.dbf, /oracle/
                                                 SID/saparch/cntrl/cntrlSID.dbf

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      NONE
SQL>
SQL> alter system set control_management_pack_access='DIAGNOSTIC' scope=both sid='*';

SQL>
SQL>
SQL> @?/rdbms/admin/awrrpt.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
3834504818 SID                 1 SID


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
------------ -------- ------------ ------------ ------------
* 3834504818        1 SID          SID          serverDB1
  3834504818        1 SID          SID          serverDB2

Using 3834504818 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
------------ ------------ --------- ------------------ -----
SID          SID              67197 03 Nov 2014 00:00      1
                              67198 03 Nov 2014 01:00      1
                              67199 03 Nov 2014 02:00      1
                              67200 03 Nov 2014 03:00      1
                              67201 03 Nov 2014 04:00      1
                              67202 03 Nov 2014 05:00      1
                              67203 03 Nov 2014 06:00      1
                              67204 03 Nov 2014 07:00      1
                              67205 03 Nov 2014 08:00      1
                              67206 03 Nov 2014 09:00      1
                              67207 03 Nov 2014 10:00      1
                              67208 03 Nov 2014 11:00      1
                              67209 03 Nov 2014 12:00      1
                              67210 03 Nov 2014 13:00      1
                              67211 03 Nov 2014 14:00      1
                              67212 03 Nov 2014 15:00      1
                              67213 03 Nov 2014 16:00      1
                              67214 03 Nov 2014 17:00      1
                              67215 03 Nov 2014 18:00      1
                              67216 03 Nov 2014 19:00      1
                              67217 03 Nov 2014 20:00      1
                              67218 03 Nov 2014 21:00      1
                              67219 03 Nov 2014 22:00      1
                              67220 03 Nov 2014 23:00      1
                              67221 04 Nov 2014 00:00      1
                              67222 04 Nov 2014 01:00      1
                              67223 04 Nov 2014 02:00      1
                              67224 04 Nov 2014 03:00      1
                              67225 04 Nov 2014 04:00      1
                              67226 04 Nov 2014 05:00      1
                              67227 04 Nov 2014 06:00      1
                              67228 04 Nov 2014 07:00      1
                              67229 04 Nov 2014 08:00      1
                              67230 04 Nov 2014 09:00      1
                              67231 04 Nov 2014 10:00      1



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

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



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

Enter value for report_name:

Using the report name awrrpt_1_67207_67231.txt

WARNING: Since the DB Time is less than one second, there was
         minimal foreground activity in the snapshot period.
         Some of the percentage values will be invalid.

AWR DUMP

 @?/rdbms/admin/awrextr.sql