For comparing, classifying, benchmarking and forecasting databases, Oracle’s Automatic Workload Repository (AWR) is neat. (But remember! You will need a diagnostics pack license for that as described here and here – if you need a free technology for performance monitoring, consider using Craig Shallahamer’s OSM toolkit.)
This post simply describes the package calls to create a AWR report, for details please see the Oracle documentation or oracle-base.com.
If you need to create a snapshot manually, because you don’t like the one-hour interval, or if you disabled taking snapshots at all:
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
Create a report:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
This script will ask you for the format of the report (html or plain text), the snapshot ID for start and end of the report (an overview of the last n day’s snapshots is given) and for a report name, that’s used for the report file name (.html or .lst).
Stay happy with your databases,
Usn