Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Long execute phase for SELECT query

Re: Long execute phase for SELECT query

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Mon, 3 Jul 2006 10:48:43 +0200
Message-ID: <486b2b610607030148s14c94190m1a3c4216e8ceff40@mail.gmail.com>


Do your tables have statistics on them ? If not Oracle will use dynamic sampling to gather a sample of them, that can take time

Stefan

On 6/29/06, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> Sorry if I'm beating this subject to death, but here is a new twist on
> it.
>
> I've confirmed that the query is simply taking a long time for the CBO
> to optimize, but I'm not sure why. I just upgraded this system from
> 8.0.6 to 10.2.0.2 and on 8.0.6 this exact same query only took .03
> seconds to hard parse - so why is it now taking 12 seconds? I'm
> guessing it is due to some increased complexity in the CBO, but for the
> most part this system is running great. So far there are only two
> queries (out of thousands) I've seen this behaviour on and they are both
> very similar - outer joins of 20+ tables - so it seems there may be a
> bug/degradation in the way the CBO is handling such queries.
>
> Has anyone else seen this behaviour in 10g? Any ideas where to look for
> a solution other than trying to ensure that the query never gets hard
> parsed? I guess I could try digging through a 10053 trace, but I'm not
> very familiar with them and not sure really what to look for.
>
> Thanks,
> Brandon
>
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 03 2006 - 03:48:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US