Re: Stopgap solution for table-size-estimate updatingproblem

On 11/29/2004 10:49 AM, Greg Stark wrote:
> I'll point out other databases end up treading the same ground. Oracle started
> with a well defined rules-based system that was too inflexible to handle
> complex queries. So they went to a cost-based optimizer much like Postgres's
> current optimizer. But DBAs resisted for a long time precisely because they
> couldn't control it or predict its behaviour as well. Now they have a plan
> stability system where you can plan queries using the cost based optimizer but
> then store the plans for future use. You can even take the plans and store
> them and load them on development systems for testing.

I can attest to this. I work (Middlware, not DBA stuff) with fairly
large oracle databases (40T, billions of rows). The data is added in
chunks (tablespaces) and in general do not materially affect the
distribution of data. However, oracle would many times suddenly take a
plan and shove it in a new sub-optimal query path after adding the data.

The solution was to

1) fix the stats and/or stored outline in a staging area manually (DBA)

or

2) hint the query in the middleware (uggh -- my group MW)

Once good, move the stored outlines to the production hardware -- all is
fixed.

For the most part we fix using option 2 cause it is generally easier to
hint the query than to fix the stored outline (though our DBAs say they
can).

Using stored outlines has gone a long way to ensure stability on our
systems.

>
> Their system is awfully kludgy though. Postgres can probably do much better.
>

--
-Rupa