Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> plan table and TempSpc entry

plan table and TempSpc entry

From: <keithjgordon_at_hotmail.co.uk>
Date: 16 Apr 2007 08:47:13 -0700
Message-ID: <1176738433.342992.34370@o5g2000hsb.googlegroups.com>

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 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 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> Received on Mon Apr 16 2007 - 10:47:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US