RE: Long Parse Time
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: "Riyaj Shamsudeen" <riyaj.shamsudeen@xxxxxxxxx>, <kerry.osborne@xxxxxxxxxxx>
- Date: Thu, 7 May 2009 06:45:03 -0500
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@xxxxxxxxx]
Sent: Wednesday, May 06, 2009 1:35 PM
To: kerry.osborne@xxxxxxxxxxx
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@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:
