RE: hash join waits on cpu 100% time

From: Jonathan Lewis <>
Date: Sat, 3 Jan 2015 20:04:13 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282669DC_at_EXMBX01.thus.corp>

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.

Jonathan Lewis

From: [] on behalf of GG []
Sent: 03 January 2015 18:33
Subject: hash join waits on cpu 100% time

  we are on

and observing strange issue with hash join , which waits on CPU all the
time .
-- Received on Sat Jan 03 2015 - 21:04:13 CET

Original text of this message