Memory use by Hash join in oracle 11g

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 1
Joined: Fri May 16, 2014 4:03 am

Memory use by Hash join in oracle 11g

Postby rekhasinghal » Fri May 16, 2014 4:07 am

I ran a TPC-H query in isolation (no other concurrent query ) which is a Hash join on Order and Customer table on oracle11g on linux with _smm_max_size=102M. I observed that when the same query is run on the same system with increased size of Order and Customer table, the memory used by Hash join ("USED-MEM") decreases. Please find the details in the attached file which has execution plans of the query on 1GB, 4 GB and 128GB database generated using dbgen utility in open source. The "USED_MEM" for hash join changes from 107M, 80M and 52 M for 1GB, 4 GB and 128 GB respectively. I fail to understand why oralce does not use maximum memory available when the query is running in isolation- no other session/query is running in parallel.

Kindly help and suggest pointers to look for.

Thanks and Regards,

Rekha Singhal

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Memory use by Hash join in oracle 11g

Postby Tim... » Fri May 16, 2014 10:06 am


I'm not really sure I am the person to ask on this. I think I would ask the question of one of the performance gurus. :)

So as far as you are concerned:

- The query is the same.
- The volume of data has increased.
- The execution plan is *exactly* the same, except for the cardinality of the operations has presumably increased.
- The amount of memory used by a hash join operation has reduced.

Like I said, I'm not performance guru, but I would be wondering if the has table is overflowing to disk, so Oracle is not bothering to allocate more memory, in a kind-of, "I'm already having to overflow, so what's the point in grabbing more memory", type scenario. :) This is just me thinking out loud. I have no evidence to say the optimizer will make those sort of decisions... :)


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests