Re: PL/SQL and Bind Variables / Literals
Date: Thu, 18 Jun 2009 07:08:55 +0200
Message-ID: <8580d4110906172208t1c901cd6pa7bdf1d74f1a63f_at_mail.gmail.com>
gmail suddenly decided to not reply with the same address as the one that received the massage. Sending again as it seems it bounced from the list.
How many good versions of the plan do you have? Normally in these cases
there is one way you always want the query to be executed and you have one
or two bad versions that does not work at all as well as the optimizer
thought.
Mathias
On Thu, Jun 18, 2009 at 6:42 AM, <troach_at_gmail.com> wrote:
> I actually don't want to lock it to an execution plan. Depending on the
> values, sometimes one execution plan works well and in other cases a
> different plan works well. This is during an etl batch load and the query
> gets executed 140 times per night so I don't mind doing 140 parses to ensure
> I get a good plan. When I get a bad plan, a query that executes normally in
> 15 minutes takes roughly 8 hours to complete using a nested loop when it
> should use a hash join.
> ------Original Message------
> From: Mathias Magnusson
> Sender: mathiasmag_at_gmail.com
> To: troach_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: PL/SQL and Bind Variables / Literals
> Sent: Jun 18, 2009 12:04 AM
>
> Wouldn't an outline or SQL Profile work better in this case? No code change
> and no risk of paying for a lot more parsing.
>
>
> You ought to get the same overhead you have now (instead of increasing it)
> and you would lock the query to an execution plan you are happy with.
>
> Mathias
>
>
> On Thu, Jun 18, 2009 at 3:19 AM, Thomas Roach <troach_at_gmail.com <mailto:
> troach_at_gmail.com> > wrote:
> Hi Group,
>
> I am looking at options on forcing Oracle to use literals inside of a
> PL/SQL package. I have a bind variable peeking issue that is causing a
> cached execution plan that doesn't work well for all scenarios. To get
> around this (and since the query is executed only a handful of times), I
> want to force the query to use literals inside of PL/SQL. One option I
> thought of is to use execute immediate. I was wondering if anyone had any
> ideas or thoughts if there is a better way to do this.
>
> Thanks,
>
> Tom
>
>
>
> Sent from my Verizon Wireless BlackBerry
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 18 2009 - 00:08:55 CDT