Re: Statspack consolidation

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 29 Apr 2005 13:16:13 -0600
Message-ID: <BE97E41D.268CF%tim@evdbt.com>

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_at_gmail.com wrote:

> On 4/29/05, daniel.hubler_at_aurora.org <daniel.hubler_at_aurora.org> 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

Received on Fri Apr 29 2005 - 15:20:42 CDT