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