On Apr 16, 4:47 pm, 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
>
> SQL> show user
> USER is "SYSTEM"
> SQL> show parameter pga_aggregate_target
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> pga_aggregate_target big integer 197120K
> SQL> show parameter hash_area_size
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> hash_area_size integer 20750000
> SQL>
>
> SQL> select name,value
> from
> v$pgastat
> order by
> value desc; 2 3 4 5
>
> NAME VALUE
> ---------------------------------------- ------------
> aggregate PGA target parameter 201,850,880
> aggregate PGA auto target 166,763,520
> bytes processed 76,951,552
> extra bytes read/written 43,915,264
> maximum PGA allocated 43,358,208
> global memory bound 40,370,176
> total PGA allocated 36,804,608
> PGA memory freed back to OS 36,569,088
> total PGA inuse 16,625,664
> total freeable PGA memory 1,835,008
> recompute count (total) 332
>
> NAME VALUE
> ---------------------------------------- ------------
> cache hit percentage 64
> max processes count 22
> process count 21
> over allocation count 0
> total PGA used for manual workareas 0
> maximum PGA used for auto workareas 0
> total PGA used for auto workareas 0
> maximum PGA used for manual workareas 0
>
> 19 rows selected.
>
> Elapsed: 00:00:00.00
> SQL>
PGA_AGGREGATE_TARGET and its effects on the *_AREA_SIZE parameters are
documented in the Database Reference:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams157.htm
Received on Tue Apr 17 2007 - 03:20:19 CDT
