RE: hash join waits on cpu 100% time

A couple of quick questions:

You said the query should run in 6 minutes - is this the design target, or a previous best ?
There is a "hint" to set the optimizer features back to 10.2.0.4 - is this really supposed to be a hint, or is it intended as a comment to point out that the session or system parameter has been set ?  As it stands it's not the correct syntax for the hint.


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

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

W dniu 2015-01-04 o 13:12, Jonathan Lewis pisze:
>
> You might as well capture the full SQL Monitoring output for the completed query.
>
> Then I'd check the definition of the view V_SA_tab_personal_ADD  to see what it's doing with the "with subquery".
>
> I think most of the work relates purely to the number and size of the tablescans of the resultant GTT - but the CPU used still looks a little high (even if it does mange to scan 2 billiong rows in the first 1,000 seconds) so I want to know how it reduces those rows down to one per scan - as indicated by the Rows (Actual) in lines 19 and 18.  I'd want the execution plan from v$sql_plan so that I could check the predicate section to see how any predicates were applied.
>
> If I were to start running the query again I would use Tanel's code to capture a snapshot of latch activity over a few seconds to see if there were any significant latch gets over and above those related to the buffer cache.
I'll try to gather such information, meanwhile developers are trying to
rewrite query :) .
Regards

G

--