RE: hash join waits on cpu 100% time
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 4 Jan 2015 12:50:10 +0000
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
@jloracle
________________________________________
From: GG [grzegorzof@xxxxxxxxxx]
Sent: 04 January 2015 12:27
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
