Re: hash join waits on cpu 100% time
From: GG <grzegorzof_at_interia.pl>
Date: Sun, 04 Jan 2015 13:27:59 +0100
Message-ID: <54A931CF.8020007_at_interia.pl>
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
Date: Sun, 04 Jan 2015 13:27:59 +0100
Message-ID: <54A931CF.8020007_at_interia.pl>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 04 2015 - 13:27:59 CET