Re: hash join waits on cpu 100% time
- From: GG <grzegorzof@xxxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 04 Jan 2015 13:27:59 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
