RE: hash join waits on cpu 100% time

That looks as if your view V_sa_tab_personal_add starts with a "with subquery" that is then used in a scalar subquery in the select list of the second query block of a UNION ALL view (which has been constructed as a partition view); and that "with subquery" has produced a global temporary table of 200,000 rows and about 650 blocks which you've scanned 7,663 times up to the point where you dumped the monitory information.

It looks like the union all is a 4-part union all, of which only the second part is going to be used in this case - so if the merge has been faster in the past perhaps it's because different query blocks in the union all operate on different occasions. In particular the first part of the union all (lines 16/17) are filtered out on this occasion but wouldn't scan the global temporary table.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of GG [grzegorzof_at_interia.pl]
Sent: 03 January 2015 18:33
To: oracle-l_at_freelists.org
Subject: hash join waits on cpu 100% time

Hi,
  we are on 11.2.0.2

and observing strange issue with hash join , which waits on CPU all the
time .
--