Re: Long Parse Time
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: kerry.osborne@xxxxxxxxxxx
- Date: Wed, 6 May 2009 15:34:50 -0500
Ric
With subqueries, I would suspect cost based query transformation. Just to
see if that is causing the issue or not, turn off CBQT at session level and
test it..
alter session set "_optimizer_cost_based_transformation"=off;
<..query..>
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Wed, May 6, 2009 at 3:15 PM, Kerry Osborne <kerry.osborne@xxxxxxxxxxx>wrote:
> An Outline or a SQL Profile may provide a quick a fix while you're figuring
> out what's really causing the problem. The hints should limit the choices
> that the optimizer has to consider (and thus hopefully the time spent).
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com
>
>
>
>
>
>
> On May 6, 2009, at 1:34 PM, Allen, Brandon wrote:
>
> Not sure if it’s the same issue, but I had a similar problem a long time
> ago when I migrated a Baan ERP system from Oracle 8i to 10g and a few of the
> queries that joined several (~25+) tables started to take a long time to
> parse. (Just a side note, the CBO time actually showed up in the Execute
> phase instead of the Parse phase, but this is due to bind variable peeking
> as documented in Metalink 199273.1). My first workaround was to implement
> stored outlines for the queries so they could bypass the CBO, and then later
> I found that I could also resolve the problem by using the newer FIRST_ROWS(
> *n*) syntax in these queries instead of the older FIRST_ROWS hint that
> Baan was using – luckily Baan provided a configuration parameter that made
> it switch to the newer syntax and this resolved the problem.
>
> Regards,
> 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.
>
>
>
Other related posts:
