Re: Long Parse Time

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Fri, 8 May 2009 09:14:40 +0900
Message-ID: <43c2e3d60905071714k70291ea7sf0e64b2291db06c9_at_mail.gmail.com>

(Too) long parse time due to CBQT is a quite common issue in 10g and most of them might be bugs.

As Tanel said, I believe that investigation on 10053 trace file would tell something meaningful about this, like too many optimization tries for transformation candidates.

Or stack trace analysis might provide another view point.

One of my customers had similar problem, but even with CBQT disabled, the problem has not gone. The more aggressive NO_QUERY_TRANSFORMATION hint solved the problem. Ouch!

---

Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)

---

On Thu, May 7, 2009 at 8:45 PM, Ric Van Dyke <ric.van.dyke_at_hotsos.com>wrote:

> Riyaj is the winner!
>
>
>
> We put this in to the view instead of the rule hint and the query pareses
> and runs in about one second:
>
>
>
> /*+ opt_param('_optimizer_cost_based_transformation','off') */
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> Cell 248-705-0624
>
> -----------------------
>
>
>
> *Hotsos Symposium *
>
> *March 7 – 11, 2010 *
>
> *Be there.*
>
>
>
>
> ------------------------------
>
> *From:* Riyaj Shamsudeen [mailto:riyaj.shamsudeen_at_gmail.com]
> *Sent:* Wednesday, May 06, 2009 1:35 PM
> *To:* kerry.osborne_at_enkitec.com
> *Cc:* Ric Van Dyke; Oracle L
> *Subject:* Re: Long Parse Time
>
>
>
> 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_at_enkitec.com>
> 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.
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 07 2009 - 19:14:40 CDT