Re: PL/SQL and Bind Variables / Literals
- From: troach@xxxxxxxxx
- To: "Mathias Magnusson" <mathias.magnusson@xxxxxxxxx>,mathiasmag@xxxxxxxxx
- Date: Thu, 18 Jun 2009 04:42:30 +0000
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@xxxxxxxxx
To: troach@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx
<mailto:troach@xxxxxxxxx> > 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
Other related posts:
