Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> plan table and TempSpc entry
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
I am confused by this website http://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 197120KSQL> show parameter hash_area_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hash_area_size integer 20750000SQL> SQL> select name,value
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>
Received on Mon Apr 16 2007 - 10:47:13 CDT