hash_area_size
andreas...@sintec.de
We want to know the handling of the hash_area_size. We know that Oracle
allocates hash_area_size on a per process basis and that the size is default
2 * sort_area_size. So will we have to reserve the memory for hash area for
all processes in addition to sort area. Perhaps, Oracle will use sort area
for hashing? Or is the memory for hash area is deallocate after hashing? And
on MTS configuration the hash area is allocated in the shared pool?
By the way, hash joins will speed up joins enormous.
Thanks you,
Andreas Prusch
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
Joel Garry
On Thu, 28 May 1998 07:35:21 GMT, andreas...@sintec.de <andreas...@sintec.de> wrote:
>We want to know the handling of the hash_area_size. We know that Oracle
>allocates hash_area_size on a per process basis and that the size is default
>2 * sort_area_size. So will we have to reserve the memory for hash area for
>all processes in addition to sort area. Perhaps, Oracle will use sort area
>for hashing? Or is the memory for hash area is deallocate after hashing? And
>on MTS configuration the hash area is allocated in the shared pool?
>By the way, hash joins will speed up joins enormous.
Example 5, page 19-53 of the Oracle8 Server Tuning Manual seems to show the
hash area is in addition to the sort area. The manual implies that MTS
allocates the area from the shared pool, perhaps that could be determined
by manipulating hash_area_size and watching v$shared_pool_reserved.
>
>Thanks you,
>
>Andreas Prusch
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
--
These opinions are my own and not necessarily those of Information Quest
or Pebble In The Sky http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry jga...@nospameiq.com
"See your DBA?" I AM the @#%*& DBA! Remove nospam to reply. Sorry.
Jonathan Lewis
Joel Garry <joe...@pebble.org> wrote in article
<slrn6mrcdt...@pebble.org>...
> Example 5, page 19-53 of the Oracle8 Server Tuning Manual seems to
show the
> hash area is in addition to the sort area.
If in doubt, don't assume the manuals are correct !
Experiment:
set hash_area_size =2M
set sort_area_size = 2M
start database
run top to see size of dedicated shadow-processes
a) At steady state (small create table, create index, etc. shadow
shows 10100K
b) Execute statement to do large sort
shadow jumps to 12184K
c) terminate session
Repeat, but execute statement to do large hash
shadow jumps to 12184K
Repeat doing sort, then hash
shadow is stable as 12184K
Repeat doing hash then sort
shadow is stable at 12184K
It really looks as if memory allocated for sort_area_size is
interchangeable
with memory allocated for hash_area_size
Jonathan Lewis
Joel Garry
On Fri, 29 May 1998 15:58:49 GMT, Jonathan Lewis <jona...@jlcomp.demon.co.uk> wrote:
>
>
>Joel Garry <joe...@pebble.org> wrote in article
><slrn6mrcdt...@pebble.org>...
>> Example 5, page 19-53 of the Oracle8 Server Tuning Manual seems to
>show the
>> hash area is in addition to the sort area.
>
>If in doubt, don't assume the manuals are correct !
You dare doubt the Oracle Documentation, that is always right and never lies
and contains all possibilities?
Heresy!
>
>Experiment:
> set hash_area_size =2M
> set sort_area_size = 2M
> start database
>
> run top to see size of dedicated shadow-processes
>
>a) At steady state (small create table, create index, etc. shadow
>shows 10100K
>b) Execute statement to do large sort
> shadow jumps to 12184K
>c) terminate session
>
>Repeat, but execute statement to do large hash
> shadow jumps to 12184K
>
>Repeat doing sort, then hash
> shadow is stable as 12184K
>
>Repeat doing hash then sort
> shadow is stable at 12184K
>
>
>It really looks as if memory allocated for sort_area_size is
>interchangeable
>with memory allocated for hash_area_size
Stupid @!#$&%^ manuals! :)
Of course, top is always right and never lies... :)
>
>
>Jonathan Lewis
>
>
Good example! Thanks!
jg
Jonathan Lewis
Joel Garry <joe...@pebble.org> wrote in article
>
> You dare doubt the Oracle Documentation, that is always right and
never lies
> and contains all possibilities?
>
Only children believe the stories from Hans Christian Ellison are
true !!