Taking an AWR snapshot (10g and beyond)
EXEC dbms_workload_repository.create_snapshot;
Taking a STATSPACK snapshot:
EXEC statspack.snap;
To execute a STATSPACK or AWR report, go to your $ORACLE_HOME/rdbms/admin directory and run:
all awr report:
awrrpt.sql
spreport.sql
for specific sql:
@?/rdbms/admin/awrsqrpt