Home » RDBMS Server » Performance Tuning » why oracle change the sql plan from hash join to nested loop when the system resource load is high? (oracle RDBMS 10g RAC HP UNIX )
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 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: why oracle change the sql plan from hash join to nested loop when the system resource load is hi [message #428981 is a reply to message #428938] Sat, 31 October 2009 07:29 Go to previous message
davidh2y
Messages: 2
Registered: October 2009
Junior Member
Our system configure is like this:
PGA_AGGREGATE_TARGET:12G ;SGA_TARGET:12G.
and we use oralce auto tuning the workarea size.
I guess when oralce is producing the sql plan,it will consider that moment's system work load,such as cpu,i/o,mem.But i am not sure that,just guess.
Previous Topic: Single Query suddenly goes slow until restart or change
Next Topic: BITMAP INDEX performance issue
Goto Forum:
  


Current Time: Wed Sep 28 02:21:18 CDT 2016

Total time taken to generate the page: 1.24483 seconds