Re: hash join waits on cpu 100% time
From: GG <grzegorzof_at_interia.pl>
Date: Sat, 03 Jan 2015 21:09:30 +0100
Message-ID: <54A84C7A.8060705_at_interia.pl>
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,
Date: Sat, 03 Jan 2015 21:09:30 +0100
Message-ID: <54A84C7A.8060705_at_interia.pl>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 03 2015 - 21:09:30 CET