Re: DW and change control: recommendations?

Doug

Straight from the COncepts manual:

After carefully tuning an application, you might want to ensure that the optimizer generates the same execution plan whenever the same SQL statements are executed. Plan stability allows you to maintain the same execution plans for the same SQL statements, regardless of changes to the database such as re-analyzing tables, adding or deleting data, modifying a table's columns, constraints, or indexes, changing the system configuration, or even upgrading to a new version of the optimizer.

The CREATE OUTLINE statement creates a stored outline, which contains a set of attributes that the optimizer uses to create an execution plan. Stored outlines can also be created automatically by setting the system parameter CREATE_STORED_OUTLINES to TRUE.

The system parameter USE_STORED_OUTLINES can be set to TRUE, FALSE, or a category name to indicate whether to make use of existing stored outlines for queries that are being executed. The OUTLN_PKG package provides procedures used for managing stored outlines.

Implementing plan stability creates a new schema called OUTLN, which is created with DBA privileges. The database administrator should change the password for the OUTLN schema just as for the SYS and SYSTEM schemas.

HTH. Pete

Doug Cowles wrote:

> Can you elaborate? I'm new to 8i. What's an outline?
>
> Pete Sharman wrote:
>
> > David
> >
> > Most of the DW sites I've seen do use a significantly smaller DEV database on a
> > separate instance. If by chance you're on 8i, the tuning can be reasonably
> > similar if you use stored outlines produced from Production once it's there.
> >
> > HTH.
> >
> > Pete
> >
> > David Sisk wrote:
> >
> > > A fairly large (200-300 Gb) data warehouse is in consideration by my
> > > company. One area of logistics consideration is the change control
> > > methodology. Typically, I would recommend a development instance on one
> > > box, and a production instance on a different box (at least). I'm curious
> > > as to what other organizations with a large or very large data warehouse do
> > > in terms of change control and segregation between DEV and PROD,
> > > particularly prior to implementation. One thought would be to still run DEV
> > > and PROD on seperate machines, but to use a significantly smaller box for
> > > DEV and only include a subset of data for development purposes. This can
> > > cause some expected results in PROD though, due to differences in volumes of
> > > data and differences in data distribution (particularly where tuning is
> > > concerned).
> > >
> > > If anyone has any stories they'd like to share or suggestions, I'm all ears.
> > > If you would be so kind, please email as well as posting.
> > >
> > > Regards,
> > >
> > > --
> > > David C. Sisk
> > > The Unofficial ORACLE on NT site
> > > http://www.ipass.net/~davesisk/oont.htm
> >
> > --
> > Regards
> >
> > Pete
> >
> > ------------------------------------------------------------------------
> >
> > Pete Sharman <psharman_at_us.oracle.com>
> > Project Manager
> > Oracle University, Consulting and Vertical Education
> > Oracle Corporation
> >
> > Pete Sharman
> > Project Manager
> > Oracle University, Consulting and Vertical Education
> > Oracle Corporation
> > 500 Oracle Parkway M/S OPL-B1024 
> > Redwood Shores

> > California

> > 94065 ÐXÀ
> > USA
> > **** The statements and opinions expressed here are my **** **** own and do not necessarily represent those of **** **** Oracle Corporation. **** "Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook "Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA
> > Additional Information:
> > Last Name Sharman
> > First Name Peter
> > Version 2.1

--
Regards

Pete

Received on Wed Jul 28 1999 - 10:39:47 CDT