Re: plan table and TempSpc entry

On 18 Apr, 03:17, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Apr 16, 11:47 am, keithjgor..._at_hotmail.co.uk wrote:
>
>
>
> > Hello, I have an Oracle XE 10g install on my home computer with is
> > Redhat x86. I am wanting to change the value of HASH_AREA_SIZE because
> > of a query that has a large group by clause. I have increased
> > HASH_AREA_SIZE and restarted Oracle, the value shows as my new value,
> > but I think it is not being used because the plan is staying exactly
> > the same and still swapping out to disk (6008K). Here is relevant plan
> > part.
>
> > ---------------------------------------------------------------------------­---------
> > | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> > (%CPU)| Time |
> > ---------------------------------------------------------------------------­---------
> > | 0 | SELECT STATEMENT | | 9091 | 177K| | 1033
> > (3)| 00:00:13 |
> > |* 1 | FILTER | | | |
> > | | |
> > | 2 | HASH GROUP BY | | 9091 | 177K| 6008K| 1033
> > (3)| 00:00:13 |
> > | 3 | NESTED LOOPS | | 181K| 3551K| | 588
> > (1)| 00:00:08 |
> > |* 4 | INDEX RANGE SCAN| IX19 | 20 | 200 | | 3
> > (0)| 00:00:01 |
> > |* 5 | INDEX RANGE SCAN| IXA8 | 9091 | 90910 | | 29
> > (0)| 00:00:01 |
>
> > I am confused by this websitehttp://dba.ipbhost.com/lofiversion/index.php/t2981.html
> > that says
>
> > "watch out that you arent using pga_aggregate_target because if you
> > are hash_area_size most likely isnt being used"
>
> > I think I am using pga_aggregate_target as I can see the parameter
> > value. What I am asking for is tips to how I can stop TempSpc
> > appearing above. My computer has 2gB of ram and I am exploring Oracle
> > to become good before starting at University. Thank you for thinking
> > about helping me and tips.
>
> > Thank you
> > John
>
> 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.
>
> First, the set up:
> CREATE TABLE T1 (
> C1 VARCHAR2(20),
> C2 NUMBER(12));
>
> INSERT INTO
> T1
> SELECT
> TO_CHAR(MOD(ROWNUM,100))||'-'||TO_CHAR(MOD(ROWNUM,100)),
> MOD(ROWNUM,500)
> FROM
> SOME_BIG_TABLE
> WHERE
> ROWNUM<=1000000;
>
> 1000000 rows created.
>
> INSERT INTO
> T1
> SELECT
> TO_CHAR(MOD(ROWNUM,50000))||'-'||TO_CHAR(MOD(ROWNUM,50000)),
> MOD(ROWNUM,500)
> FROM
> SOME_BIG_TABLE
> WHERE
> ROWNUM<=1000000;
>
> 1000000 rows created.
>
> INSERT INTO
> T1
> SELECT
> TO_CHAR(MOD(ROWNUM,50000))||'-'||TO_CHAR(MOD(ROWNUM,100)),
> MOD(ROWNUM,500)
> FROM
> SOME_BIG_TABLE
> WHERE
> ROWNUM<=1000000;
>
> 1000000 rows created.
>
> INSERT INTO
> T1
> SELECT
> TO_CHAR(MOD(ROWNUM,50000))||'-'||TO_CHAR(MOD(ROWNUM,100)),
> MOD(ROWNUM,800)
> FROM
> SOME_BIG_TABLE
> WHERE
> ROWNUM<=1000000;
>
> COMMIT;
>
> EXEC
> DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MY_TABLE_OWNER_HERE',TABNAME=>'T1');
>
> Now, we have a table with 4,000,000 rows to use for experiments.
> Let's try a quick GROUP BY on the table, with a hint to gather extra
> statistics:
> SELECT /*+ GATHER_PLAN_STATISTICS */
> C1,
> MIN(C2)
> FROM
> T1
> GROUP BY
> C1;
>
> 99900 rows selected.
>
> 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 EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
> ALTER SYSTEM FLUSH SHARED_POOL;
>
> SELECT /*+ GATHER_PLAN_STATISTICS */
> C1,
> MIN(C2)
> FROM
> T1
> GROUP BY
> C1;
>
> Looking through the 10053 trace file, I find:
> GROUP BY sort
> GROUP BY cardinality: 47576.00, TABLE cardinality: 4014102.00
> SORT resource Sort statistics
> Sort width: 358 Area size: 314368 Max Area size:
> 62914560
> Degree: 1
> Blocks to Sort: 11795 Row size: 24 Total
> Rows: 4014102
> Initial runs: 2 Merge passes: 1 IO Cost /
> pass: 4702
> Total IO sort cost: 4842 Total CPU sort cost: 4118325232
> Total Temp space used: 81568000
>
> There's the 47,576 rows again. Total Temp space used:
> 81568000/1024/1024 = 77.79MB - which is consistent with what appeared
> in the first call to DBMS_XPLAN. I would say that there should be as
> much confidence in the temp space usage as there is in the predicted
> number of rows.
>
> Another interesting thing, I find in the parameters listed in the
> 10053 trace file: _smm_min_size = 307 KB ~ 314368, which looks to be
> the same value as the Area Size. _smm_min_size defines the smallest
> effective memory allocation for a session. Let's try an experiment
> (warning, pick a smaller value if you decide to experiment):
> 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 |
> ---------------------------------------------------------------------------
>
> Looks like we removed the temp tablespace access, let's check the
> 10053 trace file:
> GROUP BY sort
> GROUP BY cardinality: 47576.00, TABLE cardinality: 4014102.00
> SORT resource Sort statistics
> Sort width: 11939 Area size: 30720000000 Max Area size:
> 30720000000
> Degree: 1
> Blocks to Sort: 11795 Row size: 24 Total
> Rows: 4014102
> Initial runs: 1 Merge passes: 0 IO Cost /
> pass: 0
> Total IO sort cost: 0 Total CPU sort cost: 3971369577
> Total Temp space used: 0
>
> 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 (from http://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 Received on Wed Apr 18 2007 - 06:07:07 CDT