Re: How to cope with nasty side effects of bind variable peeking

I'm not sure that that would be a viable solution on a production system. Not because it wouldn't work, but because the overheads might be too large in the one case where you really need it.

If you can afford the overhead of the fgac function call (twice) on every EXECUTION (as happens at present) the query, then you are probably in the arena where you can afford the cost of simply hard parsing every statement.

You may also find that pretty soon the optimizer gets smart enough to factor out tautologies (42 = 42) in the transformation and re-use a prior execution plan rather than working out a new one.

I'm also prepared to bet that Oracle will address this issue in 11x in a fashion that basically takes a 'cheap parse' approach - based on assessing the cardinalities of single-table access paths and allowing for a few 'classes' of cardinality per table. That, by the way, is what I would do in the front end if I had to write the code for awkward data sets:

select /*+ small_t1 */
from t1, t2, t3
where

    t1.col between :b1 and :b2
and ...

vs.

select /*+ big_t1 */
from t1, t2, t3
where

    t1.col between :b1 and :b2
and ...

With the front-end code deciding which
statement to call based on the difference between :b1 and :b2.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Tanel Poder" <tanel.poder.003_at_mail.ee> To: <jaromir_at_db-nemec.com>; <Brandon.Allen_at_OneNeck.com>; "'oracle-l'" <oracle-l_at_freelists.org> Sent: Friday, August 25, 2006 5:13 PM Subject: RE: How to cope with nasty side effects of bind variable peeking

> Hi,
>
> It is possible to force selective hard parsing for cursors with bind
> variables. It works for any SQL, also for the ones which are submitted from
> PL/SQL (both static and dynamic native sql).
>
> The magic keyword is Fine Grained Access Control (DBMS_RLS):
> You add a dynamic RLS policy to your tables of interest and make the RLS
> predicate generation function generate a new dummy predicate (e.g. where 42
> = 42 etc) each time it determines a hard parse is needed.
>
> As the predicate generation function is pure PL/SQL, executed from your
> session context during soft parsing/cursor authentication, you have endless
> opportunities to control whether a hard parse is forced (using fake RLS
> policy mismatch) or not.
>
> For example, you could determine the need for reoptimization using:
>
> 1) a session package variable (or some context or dbms_application_info
> variable)
> 2) based on SQL text (which you can get with help of
> v$session.sql_hash_value etc) and possibly bind variable values
> 3) based on location in PL/SQL block (using dbms_utility.format_call_stack)
> 4) anything else you can think of and what can be done in PL/SQL
>
> As this approach uses FGAC and enables us to achieve hard parsing control at
> really fine grained level, lets name it Fine Grained Hard Parsing :)
>
> This feature is safe to use in principle - as what we are doing here, is
> essentially Fine Grained Access Control in it's simplest form.
> Of course this extra overhead means that this method is not suitable for
> ultra frequently used quick OLTP queries - but this isn't where we do have
> the bind variable / peeking issue anyway.
>
> However there are some implications which would require thorough volume
> testing before you roll this out for your application:
>
> 1) performance impact due large amounts of child cursors for the same SQL
> text (long library cache hash chains)
> 2) how parallel execution behaves with this (especially if you use package /
> context variables to control FGHP)
> 3) it doesn't work for users with explicit EXEMPT ACCESS POLICY privilege,
> as this disables FGAC predicate generation
> 4) few other things which I can't remember anymore (it's midnight in
> Singapore and it's the weekend! ;)
>
> But when planned and implemented carefully, this could be the solution which
> makes bind variable peeking good and really useful again.
>
> I attach a simple proof-of-concept example, which I've tested on 10.1.0.3
> and 10.2.0.2, but I don't see a reason why it wouldn't work in 9i as well
> (8i doesn't have neither bind peeking nor dynamic RLS policies if I recall
> correctly).
>
> I'm currently writing a paper on this topic and I hope to finish it by end
> of this week.
>
> Tanel.
>
>

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