Receiving Hash join ran out of memory error

[orrlyfe]

Hi,

When I try to access a view, I receive the following error..."The following error has occurred:
ORA-06580: Hash Join ran out of memory while keeping large rows in memory"

I went out on the net and for research and found that I need to increase the hash size. How do I do this?

 

[sem]

Increase HASH_AREA_SIZE and/or HASH_MULTIBLOCK_IO_COUNT Regards, Dima

 

[orrlyfe]

What is the syntax for this?

 

[sem]

You may modify them in init.ora file and then restart database.

You may also change hash_multiblock_io_count on the fly by

alter system set hash_multiblock_io_count=3 Regards, Dima

 

[orrlyfe]

Sem,

I altered the database as suggested, but I get the same results..The following error has occurred:

ORA-06580: Hash Join ran out of memory while keeping large rows in memory




Details:
ORA-06580: Hash Join ran out of memory while keeping large rows in memory

any thoughts?

 

[sem]

You may change execution plan of your query by adding hints or suppressing hash joins completely:

alter session set hash_join_enabled=false

Can you provide the values of these parameters (can be obtained in sql*plus by SHOW PARAMETER HASH) Regards, Dima

 

[orrlyfe]

These are the hash parameters ...

hash_area_size integer 131072
hash_join_enabled boolean TRUE
hash_multiblock_io_count integer 4

 

[sem]

Your hash_area_size is very small, increase it to 1M:

alter session set hash_area_size=1048576 Regards, Dima

 

[orrlyfe]

This is now my hash parameters

hash_area_size integer 3048576
hash_join_enabled boolean TRUE
hash_multiblock_io_count integer 4

and I'm still getting the error message. Should I keep increasing the hash area size?

 

[orrlyfe]

Here's the thing...I can select the view in sql*plus but when I try to look at the view in TOAD, I get that message. When I'm altering the session like I did previously, I am altering the my particular session?

 

[sem]

What does your query do? Does it joins tables with large varchar2 fields? What is your block size?

Did you alter the same session that produced an error? Regards, Dima

 

[orrlyfe]

How can I alter the hash size not by session but permanantly?

 

[sem]

You passed ahead of my question.
Apparently alterig session alters only the current session and does not affect others (at least directly). Regards, Dima

 

[sem]

Make changes in init.ora and ...
..read postings more carefully Regards, Dima

 

[orrlyfe]

I really hate to be a pain, but I read over your previous posts and went to the init.ora file and could not find anything reffering to HASH. Which parameters do I need to change to alter the hash size permanantly?

 

[sem]

It may be stored in IFILE, if used any, or this means that DEFAULT values are used. Regards, Dima