Re: plan table and TempSpc entry

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 17 Apr 2007 19:17:56 -0700
Message-ID: <1176862676.581334.276710@y5g2000hsa.googlegroups.com>

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));

| 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'));

| 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. Received on Tue Apr 17 2007 - 21:17:56 CDT