since 10g we can now use use AWR(automatic workload repository) report to analyse the Oracle Database health.
to run this, use script awrrpt.sql – available from $ORACLE_HOME\rdbms\admin
the script will prompt for input, and is self explanatory in the questions
SQL> @awrrpt
CURRENT Instance
[[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]]
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
255075344 PRESTIGE 1 prestige
Specify the Report TYPE
[[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates]]
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
EXT
--------------------------------
.html
Instances IN this Workload Repository SCHEMA
[[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]]
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 255075344 1 PRESTIGE prestige Indy-Prestige-DB.inm-uk.net
* 255075344 1 PRESTIGE prestige Indy-Prestige-DB.dns1.co.uk
* 255075344 1 PRESTIGE prestige vIndy-Prestige
USING 255075344 FOR DATABASE Id
USING 1 FOR instance NUMBER
Specify the NUMBER OF days OF snapshots TO choose FROM
[[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]][[USER:bignosekates|1289904198]]~~
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
------------ ------------ --------- ------------------ -----
prestige PRESTIGE 32245 16 Nov 2010 00:00 1
32246 16 Nov 2010 01:00 1
32247 16 Nov 2010 02:00 1
32248 16 Nov 2010 03:00 1
32249 16 Nov 2010 04:00 1
32250 16 Nov 2010 05:00 1
32251 16 Nov 2010 06:01 1
32252 16 Nov 2010 07:00 1
32253 16 Nov 2010 08:00 1
32254 16 Nov 2010 09:00 1
32255 16 Nov 2010 10:00 1
Specify the Begin and End Snapshot Ids
[[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]]~~
Enter value for begin_snap: 32253
Begin Snapshot Id specified: 32253
Enter value for end_snap: 32255
End Snapshot Id specified: 32255
Specify the Report Name
[[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates|1289904198]][[user:bignosekates]]
The default report file name is awrrpt_1_32253_32255.html. To use this name, press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_32253_32255.html
Big Nose Kates 