RE: hash join waits on cpu 100% time

Checking the description of the bug it seems to say that it's the creation of the internal GTT that is the threat. In the context of the bug - it's Siebel (which typically means large numbers of columns) and a very large number of (possibly small ?) queries. This would make the recursive SQL a significant fraction of the total work done, and for concurrent queries introduce a degree of latch contention. 

In your case you are talking about the execution of a single big query; moreover, if the problem were recursive SQL then the Tanel snapshot would show a high value for STAT execute count. Finally, if you check the monitoring execution plan you can see (lines 12/13) that the Load as Select starts 8 seconds into the query and completes within one second - that's the point at which the bug would apply.

There is one oddity that I would like  an explanation for - how come line 18 started at +4 seconds, when line 12 didn't start until +8 seconds ?  The only (non-buggy) thing I can think of is that there's a run-time dependency that doesn't show up in the execution plan - even then that's only showing (at worst) only a 4 second anomaly, not a very slow query.




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

________________________________________
From: GG [grzegorzof_at_interia.pl]
Sent: 03 January 2015 20:09
To: Jonathan Lewis; oracle-l_at_freelists.org
Subject: Re: hash join waits on cpu 100% time

W dniu 2015-01-03 o 21:04, Jonathan Lewis pisze:
>
> 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.
>
>
>
Thanks Jonathan,

this query should have run in 6 minutes, we are suspecting bug like this
one:

Bug 13524899 - Creation of Cursor-Duration in-memory temporary table produces too much recursive SQL (Doc ID 13524899.8)

And will try with inline hint added .
Regards
GG


--