This forum is currently locked. You can't register or post questions at this time. (read more)

Memory use by Hash join in oracle 11g

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

rekhasinghal
Member
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

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

Re: Memory use by Hash join in oracle 11g

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

Hi.

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... :)

Cheers

Tim...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests

cron