Re: plan table and TempSpc entry

On Apr 18, 7:07 am, keithjgor..._at_hotmail.co.uk wrote:
> On 18 Apr, 03:17, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > I believe that the article is incorrect. Contrary to what even the
> > 10.2 documentation states, Oracle does not ignore the _area_size
> > parameters when pga_aggregate_target is set. The _area_size
> > parameters in such a case define the minimum values for those
> > parameters. As best that I can tell, the hash_area_size parameter
> > only applies to hash joins. The _smm_min_size parameter does have an
> > effect on HASH GROUP BY as will be demonstrated below. What you
> > perceive to be true based on what the plan shows, even if it was
> > generated by DBMS_XPLAN, may not be the case - this also will be
> > demonstrated below.
> > Now, a call to DBMS_XPLAN to retrieve the plan:
> > SELECT
> > *
> > FROM
> > TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
>
> > ---------------------------------------------------------------------------­--------
> > | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> > (%CPU)| Time
> > ---------------------------------------------------------------------------­--------
> > | 0 | SELECT STATEMENT | | | | | 8059
> > (100)| |
> > | 1 | HASH GROUP BY | | 47576 | 557K| 77M| 8059
> > (15)| 00:00:44 |
> > | 2 | TABLE ACCESS FULL| T1 | 4014K| 45M| | 2199
> > (9)| 00:00:12 |
> > ---------------------------------------------------------------------------­--------
>
> > Looks like we used 77MB of temp space in the process of retrieving
> > 47,576 rows - that's odd, the query actually returned 99,900 rows.
>
> > Let's try DBMS_XPLAN again after re-running the query:
> > SELECT
> > *
> > FROM
> > TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
>
> > ---------------------------------------------------------------------------­----------
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time | Buffers |
> > ---------------------------------------------------------------------------­----------
> > | 1 | HASH GROUP BY | | 1 | 47576 | 99900 |
> > 00:00:02.76 | 9887 |
> > | 2 | TABLE ACCESS FULL| T1 | 1 | 4014K| 4000K|
> > 00:00:08.00 | 9887 |
> > ---------------------------------------------------------------------------­----------
>
> > >From the above, we see that Oracle estimated 47,576 rows would be
>
> > returned, but there were actually 99,900 rows. I wonder if the temp
> > space calculation is accurate?
> >
> > ALTER SESSION SET "_SMM_MIN_SIZE"=30000000;
>
> > ALTER SYSTEM FLUSH SHARED_POOL;
>
> > SELECT /*+ GATHER_PLAN_STATISTICS */
> > C1,
> > MIN(C2)
> > FROM
> > T1
> > GROUP BY
> > C1;
>
> > 99900 rows selected.
>
> > SELECT
> > *
> > FROM
> > TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));
>
> > ---------------------------------------------------------------------------
> > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> > Time |
> > ---------------------------------------------------------------------------
> > | 0 | SELECT STATEMENT | | | | 3181
> > (100)| |
> > | 1 | HASH GROUP BY | | 47576 | 557K| 3181 (37)|
> > 00:00:18 |
> > | 2 | TABLE ACCESS FULL| T1 | 4014K| 45M| 2199 (9)|
> > 00:00:12 |
> > ---------------------------------------------------------------------------
> > So, with a nearly 3GB smallest effective memory allocation for a
> > session, I have been able to eliminate the temp space usage from the
> > DBMS_XPLAN. This setting probably would not work too well in a
> > production environment.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Thank you for this Charles. It is good to know about _smm_min_size but
> I get scared off when I read (fromhttp://www.orafaq.com/parms/parm1363.htm)
>
> NOTE: This is an internal Oracle parameter. Do NOT use it unless
> instructed to do so by Oracle Support. Playing with this parameter may
> be harmful.
>
> Thank you
>
> John

That is great that you are verifying the advice provided in these groups.

You will likely find that the site you quoted provides the same description for nearly all parameters that begin with "_" - it is a generic catch all, which in general is sound advice. To be clear, I was not recommending that you change this parameter, but was suggesting that the plan that you were seeing may be different from the actual row source execution. The _smm_min_size parameter's function is decribed fairly well in "Cost-Based Oracle Fundamentals" by Jonathan Lewis. You may be able to make a better determination if this parameter needs to be adjusted by continuing to research its functionality.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Apr 18 2007 - 07:46:47 CDT