Re: Exporting and Importing table statistics

On Friday 25 May 2001 18:36, Weaver, Walt wrote:
> FWIW, all those people who like to slam Oracle Support should pay special
> attention to Anita's posts. Cool stuff.

There's a number of Oracle folks that really know their stuff.

If you don't hang out on mailing lists and search them out at IOUG though, you'll never get to pick their brain, as you usually don't get them when you contact support.

2 exceptions: there are some good folks that monitor the MetaLink forums, many of them veterans of several years of Oracle's forums on CompuServe.

Tom Kyte is another exception, he is very accessible through his website and usenet.

Jared

> --Walt Weaver
> Bozeman, Montana, USA
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 5/25/2001 6:50 PM
>
> Jared,
>
> Yes, DBMS_STATS can be used to move statistics from
> one db to another for the SAME table/index. It's
> great for moving prod stats into a dev db for sql
> tuning. You can also use it to set specific values
> (e.g. to set NUM_ROWS to 1000000).
>
> You might want to check out the following doc on
> metalink:
>
> Note: 117203.1 "How to Use DBMS_STATS to Move
> Statistics to a Different Database"
>
> One caveat, it was not designed to copy stats to a
> different schema owner. You won't get an error
> running the procedure, it just won't reset any stats.
> You can get around this by hacking the stats table
> generated by DBMS_STATS and changing the username
> column to the new schema owner. The column names are
> rather cryptic, but if I remember it's C5.
>
> HTH,
>
> -- Anita
>
> --- Jared Still <jkstill_at_cybcon.com> wrote:
> > On Friday 25 May 2001 13:46,
> >
> > Chaim.Katz_at_Completions.Bombardier.com wrote:
> > > I have no experience with this, but I think the
> >
> > package DBSTATS allows you
> >
> > > to export stats to a table and then import those
> >
> > stats back into the data
> >
> > > dictionary. I think outline perserves a query plan
> >
> > but ignores the dd
> >
> > > stats.
> >
> > You're referring to the DBMS_STATS package. After
> > spending a few minutes
> > perusing the docs, it's unclear whether this can be
> > used for recreating
> > statistics on another database. The docs refer to
> > using CREATE OUTLINE
> > for this.
> >
> >
> > Jared
> >
> > > Chaim
> > >
> > >
> > >
> > >
> > > Jared Still <jkstill_at_cybcon.com> on 05/25/2001
> >
> > 03:32:54 PM
> >
> > > Please respond to ORACLE-L_at_fatcity.com
> > >
> > > To: Multiple recipients of list ORACLE-L
> >
> > <ORACLE-L_at_fatcity.com>
> >
> > > cc: (bcc: Chaim Katz/Completions/Bombardier)
> > >
> > > On Friday 25 May 2001 01:05, Saurabh Sharma wrote:
> > > > i feel this is at the full database export
> >
> > level.
> >
> > > > once u export ur database, u can import it as it
> >
> > is into another database
> >
> > > > and it'll create allthe required definitions for
> >
> > u.
> >
> > > > the statistics are exported with the tablespace
> >
> > and table definitions.
> >
> > > > hope i'm sure. if not pls correct.
> > >
> > > OK, not correct. Statistics are not exported,
> >
> > check the manual.
> >
> > > EXP will recalculate statistics, but doesn't
> >
> > export them. At least
> >
> > > I can find no indication of it.
> > >
> > > You can use Oracle's plan stability feature
> >
> > however via CREATE OUTLINE.
> >
> > > Jared
> > >
> > > > saurabh
> > > > ----- Original Message -----
> > > > To: Multiple recipients of list ORACLE-L
> >
> > <ORACLE-L_at_fatcity.com>
> >
> > > > Sent: Friday, May 25, 2001 3:48 AM
> > > >
> > > > > Hi all,
> > > > > According to the 8i utilities manual I should
> >
> > be able to export a
> >
> > > > > tables' statistics
> > > > > from one database, then import those
> >
> > statistics into another database.
> >
> > > > > I have tried various combinations of export
> >
> > and import scripts but
> >
> > > > > nothing seems to
> > > > > work. My table has none of the restrictions
> >
> > described in the utilities
> >
> > > > > manual that
> > > > > would prevent the statistics from being
> >
> > exported and then imported.
> >
> > > > > Can this export/import of statistics be done
> >
> > at a single table level or
> >
> > > > > does it have
> > > > > to be a full database export? If not at the
> >
> > table level can it be done
> >
> > > > > at the schema
> > > > > level?
> > > > >
> > > > > Thanks everyone,
> > > > > Gary Bonner
> > > > > bonnergj_at_songs.sce.com
> > > > >
> > > > > --
> > > > > Please see the official ORACLE-L FAQ:
> >
> > http://www.orafaq.com
> >
> > > > > --
> > > > > Author:
> > > > > INET: bonnergj_at_songs.sce.com
> > > > >
> > > > > Fat City Network Services -- (858) 538-5051
> >
> > FAX: (858) 538-5051
> >
> > > > > San Diego, California -- Public
> >
> > Internet access / Mailing Lists
>
> --------------------------------------------------------------------
>
> > > > > To REMOVE yourself from this mailing list,
> >
> > send an E-Mail message
> >
> > > > > to: ListGuru_at_fatcity.com (note EXACT spelling
> >
> > of 'ListGuru') and in
> >
> > > > > the message BODY, include a line containing:
> >
> > UNSUB ORACLE-L
> >
> > > > > (or the name of mailing list you want to be
> >
> > removed from). You may
> >
> > > > > also send the HELP command for other
> >
> > information (like subscribing).
> >
> > > --
> > > Please see the official ORACLE-L FAQ:
> >
> > http://www.orafaq.com
> >
> > > --
> > > Author: Jared Still
> > > INET: jkstill_at_cybcon.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> >
> > FAX: (858) 538-5051
> >
> > > San Diego, California -- Public Internet
> >
> > access / Mailing Lists
>
> --------------------------------------------------------------------
>
> > > To REMOVE yourself from this mailing list, send an
> >
> > E-Mail message
> >
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> >
> > 'ListGuru') and in
> >
> > > the message BODY, include a line containing: UNSUB
> >
> > ORACLE-L
> >
> > > (or the name of mailing list you want to be
> >
> > removed from). You may
> >
> > > also send the HELP command for other information
> >
> > (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jared Still
> > INET: jkstill_at_cybcon.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
>
> --------------------------------------------------------------------
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - buy the things you want at great prices
> http://auctions.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).