Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to cope with nasty side effects of bind variable peeking

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

From: amit poddar <>
Date: Fri, 25 Aug 2006 22:05:19 -0400
Message-ID: <>

  1. performance impact due large amounts of child cursors for the same SQL text (long library cache hash chains)


Does that mean that when we add a dynamic predicate using FGAC they belong to the same parent cursor.

I.e. lets say the sql statement is

select ename from emp;

and two sessions run this statement with two dynamic predicate i.e.

where mgr=30
where mgr=40

i.e. effective statements

select ename from (select * from emp where mgr=30) select ename from (select * from emp where mgr=40)

are you saying that these two statements have the same parent cursor?

If yes then does ROW_LEVEL_SEC_MISMATCH in v$sql_shared_cursor is set to yes in this case ?


Tanel Poder wrote:
> 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
> and, 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.

Received on Fri Aug 25 2006 - 21:05:19 CDT

Original text of this message