| Memory use by Hash join in Oracle 11g [message #614080] |
Thu, 15 May 2014 22:56  |
 |
rekha.singhal@tcs.com
Messages: 6
Registered: March 2012
Location: India
|
Junior Member |
|
|
Dear All,
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
|
|
|
|
|
|
| Re: Memory use by Hash join in Oracle 11g [message #614091 is a reply to message #614090] |
Fri, 16 May 2014 01:42  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
@MC, I agree that usually using manual management is a better solution.
However, perhaps a case can be made for using _underscore parameters when tuning PGA, because it may be that Oracle got it wrong. Evidence for this is that algorithm for calculating the default values does appear to have changed in 12.1. If you do this, I think that _smm_max_size is not the only one to change. I have set _pga_max_size=2147483647 with good results for sorts, index builds, and hash joins.
@RS, I am not sure that you are looking in the correct place. What does V$SQL_WORKAREA tell you?
|
|
|
|
|
|
| Re: Memory use by Hash join in Oracle 11g [message #614103 is a reply to message #614080] |
Fri, 16 May 2014 07:19 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
rekha.singhal@tcs.com wrote on Fri, 16 May 2014 04:56I 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.
Highly like for the following reasons (amongst others)
PGA is only ever a target - I've seen DBs blow that target with catastrophic results.
PGA is also shared - just because you're the only query right then, what happens in 10 minutes if someone does a sort? Does it error? Spill to temp? Expand over target?
Bascially it's a shared resource and even if you know it isnt, the DB doesnt and shouldnt risk it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
