RE: hash join waits on cpu 100% time
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: GG <grzegorzof@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 4 Jan 2015 10:30:33 +0000
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
@jloracle
________________________________________
From: GG [grzegorzof@xxxxxxxxxx]
Sent: 03 January 2015 20:09
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
