Wednesday, June 8, 2011

Create Statspack

"snapshot" is  snapshot of database statistics and performance information as of a specific point in time.

There is no such thing as "comparing two snapshots". To get information about database activity and/or performance, you have to generate the StatsPack report for the duration between two snapshots.
Thus, for example, a StatsPack report for the duration between Snapshot 1 and Snapshot 2 shows activity and performance information for that time duration.

Script to take snapshot:

echo "connect to sql plus and taking a snap"

sqlplus "/nolog" << !
connect perfstat/perfstat
variable snap_before number ;
begin :snap_before :=statspack.snap; end;
/
print snap_before
!
sleep 60
echo "starting second snapshot"

sqlplus "/nolog" << !
connect perfstat/perfstat
variable snap_after number ;
variable snap_before number ;
begin
:snap_after := statspack.snap ;
end ;
end ;
/
print snap_after ;
begin
select :snap_after -1 into :snap_before from dual ;
end ;
/

define begin_snap=:snap_before
define end_snap=:snap_after
define report_name=snapshot
@$ORACLE_HOME/rdbms/admin/spreport

No comments:

Post a Comment