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

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

Tanel,

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 ?

amit

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