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