Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Long execute phase for SELECT query

From: Stefan Knecht <>
Date: Mon, 3 Jul 2006 10:48:43 +0200
Message-ID: <>

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


On 6/29/06, Allen, Brandon <> 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 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.
> --

Received on Mon Jul 03 2006 - 03:48:43 CDT

Original text of this message