statspack

[preetikate]

Hi,
I am keen on working with statspack. I have a query though.
Snap_Ids indicate the points for which the statspack report can be generated for analysis. But if I have automated the data collection using dbms_job and it is running every hour , how do I find the snap ids for time interval for say 02:00 hrs and 04:00 hrs tomorrow morning if I am told the performance was poor during that time.
Any help would be greatly appreciated.
Thanks.

[hrishy]

Hi Preeti

This is sure possible..look at the underlying perfstat tables..look at the data and times in this table and the corrresponding snapshot id/s.Now when running the comaprison report.Juts supply these as the begining and the ending id.

Heres a clue

create table STATS$DATABASE_INSTANCE
(dbid number not null
,instance_number number not null
,startup_time date not null
,snap_id number (6) not null
,parallel varchar2(3) not null
,version varchar2(17) not null
,db_name varchar2(9) not null
,instance_name varchar2(16) not null
,host_name varchar2(64)
,constraint STATS$DATABASE_INSTANCE_PK primary
(dbid, instance_number, startup_time)
using index tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0)

now you need to get the snap_id by entering the startup_time ;-)

now again enter the startup_time and get the snap id and you are ready to go

regards
Hrishy

[preetikate]

Thanks!
I did try to locate table in schema perfstat but couln't find one which would furnish me this info.

[hrishy]

Hi Preeti

This is the table

create table STATS$SNAPSHOT
(snap_id number(6) not null
,dbid number not null
,instance_number number not null
,snap_time date not null
,startup_time date not null
,session_id number not null
,serial# number
,snap_level number
,ucomment varchar2(160)
,executions_th number
,parse_calls_th number
,disk_reads_th number
,buffer_gets_th number
,sharable_mem_th number
,version_count_th number
,seg_phy_reads_th number not null
,seg_log_reads_th number not null
,seg_buff_busy_th number not null
,seg_rowlock_w_th number not null
,seg_itl_waits_th number not null
,seg_cr_bks_sd_th number not null
,seg_cu_bks_sd_th number not null
,all_init varchar2(5)

regards
Hrishy

[preetikate]

Thank You !!

[mrchrispy]

you can just run spreport.sql (which will be in %ORACLE_HOME%\rdbms\admin). That'll give you a report on what snapshots are avaliable, it should look something like...

11:57:45 SQL> @C:\oracle\ora81\rdbms\admin\spreport.sql


DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
747658335 CAD1 1 cad1


Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
cad1 CAD1 1 12 Dec 2003 15:33 5
11 12 Dec 2003 15:58 5
21 29 Dec 2003 11:59 5
22 29 Dec 2003 12:14 5
31 06 Jan 2004 09:41 5
41 06 Jan 2004 10:51 5
51 18 Feb 2004 11:57 5