Re: hash join waits on cpu 100% time
From: GG <grzegorzof_at_interia.pl>
Date: Sun, 04 Jan 2015 11:48:32 +0100
Message-ID: <54A91A80.1040405_at_interia.pl>
W dniu 2015-01-04 o 11:30, Jonathan Lewis pisze:
> Checking the description of the bug it seems to say that it's the creation of the internal GTT that is the threat. In the context of the bug - it's Siebel (which typically means large numbers of columns) and a very large number of (possibly small ?) queries. This would make the recursive SQL a significant fraction of the total work done, and for concurrent queries introduce a degree of latch contention.
>
> In your case you are talking about the execution of a single big query; moreover, if the problem were recursive SQL then the Tanel snapshot would show a high value for STAT execute count. Finally, if you check the monitoring execution plan you can see (lines 12/13) that the Load as Select starts 8 seconds into the query and completes within one second - that's the point at which the bug would apply.
>
> There is one oddity that I would like an explanation for - how come line 18 started at +4 seconds, when line 12 didn't start until +8 seconds ? The only (non-buggy) thing I can think of is that there's a run-time dependency that doesn't show up in the execution plan - even then that's only showing (at worst) only a 4 second anomaly, not a very slow query.
Got Your point Jonathan . Query finished after 10h , I'm out of office right now so cant provide any more details . Just wondering ,how can we narrow down the issue, should I go for detailed lvl 12 10046 trace or/and better 10053 ? Seems like Oracle instrumentation lacks of granularity if Your query 'hangs on' CPU .
Maybe I'll try with few short stack dumps using oradebug when query is running .
Date: Sun, 04 Jan 2015 11:48:32 +0100
Message-ID: <54A91A80.1040405_at_interia.pl>
W dniu 2015-01-04 o 11:30, Jonathan Lewis pisze:
> Checking the description of the bug it seems to say that it's the creation of the internal GTT that is the threat. In the context of the bug - it's Siebel (which typically means large numbers of columns) and a very large number of (possibly small ?) queries. This would make the recursive SQL a significant fraction of the total work done, and for concurrent queries introduce a degree of latch contention.
>
> In your case you are talking about the execution of a single big query; moreover, if the problem were recursive SQL then the Tanel snapshot would show a high value for STAT execute count. Finally, if you check the monitoring execution plan you can see (lines 12/13) that the Load as Select starts 8 seconds into the query and completes within one second - that's the point at which the bug would apply.
>
> There is one oddity that I would like an explanation for - how come line 18 started at +4 seconds, when line 12 didn't start until +8 seconds ? The only (non-buggy) thing I can think of is that there's a run-time dependency that doesn't show up in the execution plan - even then that's only showing (at worst) only a 4 second anomaly, not a very slow query.
Got Your point Jonathan . Query finished after 10h , I'm out of office right now so cant provide any more details . Just wondering ,how can we narrow down the issue, should I go for detailed lvl 12 10046 trace or/and better 10053 ? Seems like Oracle instrumentation lacks of granularity if Your query 'hangs on' CPU .
Maybe I'll try with few short stack dumps using oradebug when query is running .
Regards
GG
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 04 2015 - 11:48:32 CET