Re: "Depreciated" Parameters In SPFILE

Charles Hooper wrote:

> In summary, as the "Oracle Database Performance Tuning Guide 10g
> Release 2" Pg 7-38 (PDF page 146) documentation states, "sizing of
> work areas for all sessions becomes automatic and the *_AREA_SIZE
> parameters are ignored by all sessions running in that mode." There
> is apparently an odd quirk that once in a while, the first time a SQL
> statement is parsed, a sort to disk may be required, at least under
> the base patch of Oracle 10.2.0.2. This lead me, incorrectly, to
> believe that setting the SORT_AREA_SIZE to a larger value and re-
> executing the query actually removed the sort to disk - but it was
> actually the second parse that resulted in the removal of the sort to
> disk. This test case disproves my suggestion that the SORT_AREA_SIZE
> has any impact on Oracle 10.2.0.2 when all sessions are set to auto
> for the WORKAREA_SIZE_POLICY. It is possible to modify the
> WORKAREA_SIZE_POLICY at the session level, and then the SORT_AREA_SIZE
> setting takes effect for that session.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Excellent work.

But then it might just be that the optimizer is telling you it thinks it is going to do something that it isn't actually going to do.

Instead of using EXPLAIN PLAN run a couple of selected statements for real and then look at what actually happened. Does fact equal theory?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org