| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Transferring Prod Statistics...
On Tue, 15 Sep 1998 15:08:47 GMT, dprentic_at_csc.com wrote:
>Is there any way to transfer the statistics (or input them) into an instance
>that does not have any data?
(Below is rather long follow-up message, sorry about that...)
I was recently faced with exactly the same situation - to transfer a particular schema with it's statistics but without any data from the production instance to the testing instance. On the testing instance there was not enough disk space to import the schema together with it's data and to truncate all the tables after the statistics would allready be in the data dictionary.
I had no other alternative as to play around with the data dictionary tables. Although I knew that INSERT/DELETE/UPDATE on those SYS tables is not supported nor allowed from Oracle I realy didn't see any other way to do it (and besides, it was a developement instance, if anything should go wrong I could easily rebuild it from scratch).
So here is a sample script for this "statistics transfer". The scenario is: we want to transfer all SCOTT's objects from production instance to the testing instance, *without* any table rows but *with* all statistics on his tables/indexes. (Note: I didn't bother with the statistics on clusters, but the principle for them is exactly the same as with tables).
CREATE TABLE system.user$$ AS
SELECT * FROM sys.user$ WHERE name = 'SCOTT';
CREATE TABLE system.obj$$ AS
SELECT * FROM sys.obj$ WHERE type IN (1,2) AND owner# =
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT');
CREATE TABLE system.tab$$ AS
SELECT * FROM sys.tab$ WHERE obj# IN
(SELECT obj# FROM sys.obj$ WHERE type = 2 AND owner# =
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT'));
/*
can not use "CREATE AS SELECT" because there is a LONG column
in table COL$! Use SQL*Plus COPY command instead (see below)
CREATE TABLE system.col$$ AS
SELECT * FROM sys.col$ WHERE obj# IN
(SELECT obj# FROM sys.obj$ WHERE type = 2 AND owner# =
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT'));
*/
COPY FROM sys_at_2: TO system_at_2: CREATE col$$ USING - SELECT * FROM sys.col$ WHERE obj# IN -
(SELECT obj# FROM sys.obj$ WHERE type = 2 AND owner# = -
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT'));
CREATE TABLE system.ind$$ AS
SELECT * FROM sys.ind$ WHERE obj# IN
(SELECT obj# FROM sys.obj$ WHERE type = 1 AND owner# =
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT'));
CREATE TABLE system.histgrm$$ AS
SELECT * FROM sys.histgrm$ WHERE obj# IN
(SELECT obj# FROM sys.obj$ WHERE type = 2 AND owner# =
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT'));
CREATE TABLE system.hist_head$$ AS
SELECT * FROM sys.hist_head$ WHERE obj# IN
(SELECT obj# FROM sys.obj$ WHERE type = 2 AND owner# =
(SELECT user# FROM sys.user$ WHERE NAME = 'SCOTT'));
REM ---------------------------
REM ---------------------------
REM ---------------------------
UPDATE system.histgrm$$ s$$ SET obj# =
(SELECT o.obj# FROM sys.obj$ o, sys.user$ u,
system.obj$$ oo, system.user$$ uu
WHERE u.user# = o.owner# AND uu.user# = oo.owner#
AND o.type = 2 AND oo.type = 2
AND u.name = 'SCOTT' AND uu.name = 'SCOTT'
AND o.name = oo.name
AND oo.obj# = s$$.obj#);
UPDATE system.hist_head$$ s$$ SET obj# = (SELECT o.obj# FROM sys.obj$ o, sys.user$ u,
system.obj$$ oo, system.user$$ uu
WHERE u.user# = o.owner# AND uu.user# = oo.owner#
AND o.type = 2 AND oo.type = 2
AND u.name = 'SCOTT' AND uu.name = 'SCOTT'
AND o.name = oo.name
AND oo.obj# = s$$.obj#);
UPDATE system.tab$$ s$$ SET obj# =
(SELECT o.obj# FROM sys.obj$ o, sys.user$ u,
system.obj$$ oo, system.user$$ uu
WHERE u.user# = o.owner# AND uu.user# = oo.owner#
AND o.type = 2 AND oo.type = 2
AND u.name = 'SCOTT' AND uu.name = 'SCOTT'
AND o.name = oo.name
AND oo.obj# = s$$.obj#);
UPDATE system.col$$ s$$ SET obj# =
(SELECT o.obj# FROM sys.obj$ o, sys.user$ u,
system.obj$$ oo, system.user$$ uu
WHERE u.user# = o.owner# AND uu.user# = oo.owner#
AND o.type = 2 AND oo.type = 2
AND u.name = 'SCOTT' AND uu.name = 'SCOTT'
AND o.name = oo.name
AND oo.obj# = s$$.obj#);
UPDATE system.ind$$ s$$ SET obj# =
(SELECT o.obj# FROM sys.obj$ o, sys.user$ u,
system.obj$$ oo, system.user$$ uu
WHERE u.user# = o.owner# AND uu.user# = oo.owner#
AND o.type = 1 AND oo.type = 1
AND u.name = 'SCOTT' AND uu.name = 'SCOTT'
AND o.name = oo.name
AND oo.obj# = s$$.obj#);
UPDATE system.obj$$ t$$ SET obj# =
(SELECT obj# FROM sys.obj$ WHERE type IN (1,2) AND owner# =
(SELECT user# FROM sys.user$ WHERE name = 'SCOTT')
AND name = t$$.name);
UPDATE system.user$$ SET user# =
(SELECT user# FROM sys.user$ WHERE name = 'SCOTT');
REM ---------------------------
UPDATE sys.tab$ s$ SET (rowcnt, blkcnt, empcnt, avgspc, chncnt,
avgrln) =
(SELECT rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln FROM
system.tab$$
WHERE obj# = s$.obj#)
WHERE obj# IN (SELECT obj# FROM system.tab$$);
UPDATE sys.col$ s$ SET (distcnt, lowval, hival, spare2) = (SELECT distcnt, lowval, hival, spare2 FROM system.col$$ WHERE obj# = s$.obj# AND col# = s$.col#) WHERE obj# IN (SELECT obj# FROM system.tab$$);
UPDATE sys.ind$ s$ SET (blevel, leafcnt, distkey, lblkkey, dblkkey,
clufac) =
(SELECT blevel, leafcnt, distkey, lblkkey, dblkkey, clufac FROM
system.ind$$
WHERE obj# = s$.obj#)
WHERE obj# IN (SELECT obj# FROM system.ind$$);
DELETE FROM sys.hist_head$
WHERE obj# IN (SELECT obj# FROM system.tab$$);
INSERT INTO sys.hist_head$ SELECT * FROM system.hist_head$$;
DELETE FROM sys.histgrm$
WHERE obj# IN (SELECT obj# FROM system.tab$$);
INSERT INTO sys.histgrm$ SELECT * FROM system.histgrm$$;
REM Now we have in the testing database all SCOTT's objects from REM production database without any rows, but with the statistics REM from production database on all his tables/indexes!
>I would like to be able to allow my developers the ability to do explains and
>view realistic results (i.e. from prod stats) when using cost based
>optimization without having to store production volumes. In other words, is
>there a way I can fake the explain function into thinking it is looking at a
>production system when, in actuallity, there is no data?
>
>Any help would be appreciated.
>
>Regards,
>
>Don Prentice
>dprentic_at_csc.com
HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |