J’ai recu une demande pour savoir comment générer un rapport AWR sous Oracle.
Voici un petit exemple pour se connecter à la base et générer un AWR en mode test
[root@nuxora01 ~]# su - oracle
[oracle@nuxora01 ~]$ export ORACLE_SID=nuxodb
[oracle@nuxora01 ~]$ sqlplus system@nuxodb
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 31 08:40:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
731622690 NUXODB 1 nuxodb
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
------------ -------- ------------ ------------ ------------
* 731622690 1 NUXODB nuxodb nuxora01
Using 731622690 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
------------ ------------ --------- ------------------ -----
nuxodb NUXODB 1510 29 Aug 2008 06:26 1
1511 29 Aug 2008 06:45 1
1512 29 Aug 2008 07:00 1
1513 29 Aug 2008 07:15 1
1514 29 Aug 2008 07:30 1
1515 29 Aug 2008 07:45 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1514
Begin Snapshot Id specified: 1514
Enter value for end_snap: 1515
End Snapshot Id specified: 1515
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1514_1515.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_1514_1515.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
NUXODB 731622690 nuxodb 1 10.2.0.1.0 NO nuxora01
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1514 29-Aug-08 07:30:23 18 2.1
End Snap: 1515 29-Aug-08 07:45:24 18 2.1
Elapsed: 15.01 (mins)
DB Time: 0.01 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 180M 180M Std Block Size: 8K
Shared Pool Size: 76M 76M Log Buffer: 7,000K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 694.24 7,269.72
Logical reads: 27.06 283.31
Block changes: 2.05 21.42
Physical reads: 0.07 0.78
Physical writes: 0.48 5.06
User calls: 0.10 1.05
Parses: 1.88 19.67
Hard parses: 0.35 3.64
Sorts: 1.50 15.74
Logons: 0.03 0.35
Executes: 4.53 47.49
Transactions: 0.10
% Blocks changed per Read: 7.56 Recursive Call %: 99.79
Rollback per transaction %: 0.00 Rows per Sort: 7.74
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.73 In-memory Sort %: 100.00
Library Hit %: 83.14 Soft Parse %: 81.50
Execute to Parse %: 58.57 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 83.33 % Non-Parse CPU: 35.22
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 82.16 82.43
% SQL with executions>1: 63.84 78.55
% Memory for SQL w/exec>1: 84.04 90.26
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 1 92.7
os thread startup 16 1 42 90.3 Concurrenc
control file parallel write 299 1 2 90.1 System I/O
log file parallel write 111 0 2 27.2 System I/O
db file sequential read 17 0 3 7.5 User I/O
-------------------------------------------------------------
......
-------------------------------------------------------------
End of Report
Report written to awrrpt_1_1514_1515.txt
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
[oracle@nuxora01 ~]$ ll
total 100
-rw-r--r-- 1 oracle dba 95022 Aug 31 08:40 awrrpt_1_1514_1515.txt
[oracle@nuxora01 ~]$
