RE: PL/SQL and Bind Variables / Literals

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Thu, 18 Jun 2009 11:06:34 +0200
Message-Id: <676144984_at_web.de>



Tom,

not exactly what you're asking for, but one approach to force a hard parse to avoid the cursor sharing is to "mis"-use Row Level Security (RLS) aka. VPD, FGAC etc.

I've written a blog note about this some time ago with a simple example to start with: http://oracle-randolf.blogspot.com/2009/02/how-to-force-hard-parse.html

Note the link at the end of the note where Tanel Poder has done the more or less the same several years ago (which I wasn't aware of). His example goes one step further and shows how to force the hard parse only for specific SQLs which might be what you're looking for.

You need however to be aware of the implications of the solution (see the note) and that it requires some effort to implement and test, so you've to decide yourself whether it's an option in your particular case or not.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

> 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.



GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter http://movieflat.web.de
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 18 2009 - 04:06:34 CDT

Original text of this message