Re: Statspack consolidation

Everything that Paul said...

...expanding on the partitioning idea (if you're licensed for it)...

Consider using LIST partitioning on DBID for all PERFSTAT tables that have a
DBID column (i.e. just about all except one or two).  I've worked on
situations where we consolidated LOTS of statspack repositories, and
reporting performance can really suffer when lots of data from lots of
different databases.  The standard "spreport.sql" STATSPACK report does not
really differentiate on DBID, but the custom reports that you build within
this "master STATSPACK repository" will certainly all have DBID as a
filtering predicate within the WHERE clause.  List partitioning on DBID will
allow you to scale performance a lot better...



on 4/29/05 10:55 AM, Paul Drake at bdbafh@xxxxxxxxx wrote:

> On 4/29/05, daniel.hubler@xxxxxxxxxx <daniel.hubler@xxxxxxxxxx> wrote:
>> We are contemplating creating a single Statpack database, on one
>> server/instance,
>> and pushing all of our Statspack data to it.  This would probably include
>> data from 7-8 instances.
>> We are guessing that we are not the first to consider this.
>> =20
>> Does anyone have any guidelines/comments/horror stories/direction  to giv=
> e
>> on this idea?
>> =20
>> Any thoughts appreciated!
> 
> Approach it like supporting rman catalogs:
> 
> one schema per supported version within the same database, e.g.:
> perfstat817
> perfstat920
> perfstat101
> 
> you're going to have to disable constraints during import if you use exp/imp.
> beware of using direct=3Dy with exp on 10.1.0.3 (bug).
> 
> partitioning the larger tables would be an excellent idea.
> perhaps you might even want to export the data as a transportable tablespace.
> 
> I looked at this long ago, but then was cured of CTD so I never completed it.
> 
> Paul

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

Other related posts: