Re: "Depreciated" Parameters In SPFILE

On Aug 19, 4:55 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

All DBMS_XPLANS showed the actual execution plans, except for one, which was intentionally created to show the same plan as what appeared in the 10053 trace files (which showed the prediction). All 1,000,000 rows from every query run was presented in SQL*Plus - that is one of the reasons why it took roughly 10 hours to generate the test runs. The 10053 trace files repeatedly indicated that 48MB of temp tablespace would be required, but that estimate was consistently incorrect. Unfortunately, some of the detail and clarity of the runs were lost when the results were reformatted for the Usenet post.

Equally interesting was that _smm_max_size (maximum size of a single work area) was set at 20% (1/5) of the pga_aggregate_target, rather than the 5% of pga_aggregate_target that I was expecting.

My thanks to Howard Rogers for calling me on my incorrect statement regarding SORT_AREA_SIZE. I could have sworn that I saw a similar statement from a reliable source that said that the _AREA_SIZE parameters set the minimum values when workarea_size_policy=auto, but I have not found the source of that reference.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Aug 19 2007 - 19:23:36 CDT