Oracle Expert

For 9i - statspack

You will need to go to the database server and run the following command:

@?/rdbms/admin/spreport

Pick the time range which you want to observe, and that’s it.

For 10g – AWR report

You can pick the snapshot ids of the time range which you want to observe

select * from dba_hist_snapshot order by snap_id desc

Then call the DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT package. The nice thing about this, that you do not need to be on the database host to run it.

select *

from table(dbms_workload_repository.awr_report_text(dbid,1,snapshotid1,snapshotid2))

select *

from table(dbms_workload_repository.awr_report_text(2046018201,1,3551,3552))

select *

from table(select dbms_workload_repository.awr_report_text(dbid,

inst_id, prev_snap, max_snap)

from ( select d.dbid, i.inst_id, snap_id max_snap , lead(s.snap_id, 1) over (partition by s.instance_number, s.instance_number order by snap_id desc) prev_snap from gv$instance i , gv$database d , dba_hist_snapshot s where d.inst_id = i.inst_id and s.dbid = d.dbid and s.instance_number = i.inst_id and instance_role = ‘PRIMARY_INSTANCE’

) where rownum = 1 )