RE: STATSPACK migration

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 27 Mar 2006 16:40:05 -0800
Message-ID: <BEE6A332AA61424EAE305CF89D6F75C84BB1AF@USSCCEVS101.corp.hds.com>

Srikanth,

You did not mention the version, but I deduce from the numbers that it is 9.2 ;-) These are the tables that do not have a (direct) SNAP_ID column since they do not need to! This is because they either store parameters or other objects that are linked to other tables that store the SNAP_ID.

STATS$IDLE_EVENT  - Lists Idle events; Can just copy across
STATS$LEVEL_DESCRIPTION - Lists the various STATSPACK snapshot levels
STATS$SEG_STAT_OBJ - Stores the segment (table/index/others) details and

is linked to STATS$SEG_STAT by DATAOBJ#, OBJ# and TS#.

STATS$SQLTEXT - Stores the first occurrence of SQL Text - linked to
STATS$SQLSUMMARY by HASH_VALUE and LAST_SNAP_ID
STATS$STATSPACK_PARAMETER - Parameterizes limits and defaults.

You should be able to link them (OBJ and SQLTEXT) using this info. As for the others, I believe you should keep a copy of these in the repository and recopy the Idle/Parameter tables whenever they change.

I will be presenting a Metrics paper at COLLABORATE '06 (E-Biz Suite centric though as it was selected via OAUG!) that deals with collecting/using STATSPACK data for mining metrics if anyone is interested in this topic.

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Fear connects you to the Negative, but Faith connects you to the Positive! I Jn 4:18

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Srikanth Kalluri Sent: Friday, March 24, 2006 9:55 AM
To: Oracle-L Freelists
Subject: STATSPACK migration

Hi,

I am planning to migrate the STATSPACK data from PRODUCTION databases off to a seperate REPOSITORY -- so that I can generate reports off of the REP database.

I considered Materialized Views with a fast refresh, but didn't want to lose historical data (PROD snapshots are purged every 2 months).

I am considering the exp with query (where snap_id = :max_snap_id) after every snapshot, but about 5 tables don't have this SNAP_ID column. So I'd have to figure out a way to bring them across.

Are there are tried & tested methods to achieve this goal? Any input/recommendation is appreciated.

Thanks,
SK
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 27 2006 - 18:40:05 CST