| why oracle change the sql plan from hash join to nested loop when the system resource load is high? [message #428086] |
Tue, 27 October 2009 03:44  |
davidh2y
Messages: 2
Registered: October 2009
|
Junior Member |
|
|
os: HP UNIX B.11.11 (16CPU,32G MEM)
oracle:10g RAC
Question: we have a Rac env(2 nodes),which main task is to run batch job.For most task of the stored procedue,it need full table scan,so we modify the parameter :db_file_multiblock_read_count to 64,and it's sql plan is Hash join,and it run well,one job consume us is about 3 minute.But we found a fact,if the system is busy,such as CPU is high(>=90%) or I/O is high(>=85%),the sql plan sometime changed!It change from hash join to nested loops,and that result a long time execute time,it cost me 2-3 hours to finish the same job!and we collect the statistics for table/index 2 times per week,I sure it is the newest.why the sql plan change?also we found the cost of NL is smaller than the cost of hash join,but the execute time is longer !
|
|
|
|
| Re: why oracle change the sql plan from hash join to nested loop when the system resource load is hi [message #428938 is a reply to message #428086] |
Fri, 30 October 2009 23:03  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
A hash join requires memory. The bigger the tables, the more memory it needs. If the system load is high, memory might be scarce. Oracle may decide it can't spare enough for your hash join.
You might consider reviewing parameters like PGA_AGGREGATE_TARGET. Possibly it is set incorrectly and you have more memory that you are not making available to SQL queries.
You should be able to find some advice on tuning memory in the Oracle Performance Tuning Guide.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
