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.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jaromir nemec
Sent: Friday, August 25, 2006 06:56
To: Brandon.Allen_at_OneNeck.com; oracle-l
Subject: Re: How to cope with nasty side effects of bind variable peeking

Hi Allen,  

> What if Oracle could evaluate the incoming bind variable values for a
> query and compare them to the bind values used at parse time for all
> previously cached versions of the same query and if they don't match,
> then the CBO peeks at the new bind variables and comes up with the best
> plan.
 

My experience is: don't use bind variables in statements where different values of the bind variables produce different plans. What is the point of saving some milliseconds in parsing while potentially loosing hours performing bad plans?  

> is executed once with v1:= 200000 and v2:=200000, so the index on
> order_number is used in the explain plan as it should be and performance
> is great for this execution. But, then the same exact query is executed
> with v1:=0 and v2:=999999 and this query gets stuck with the same
> execution plan.
 

If bind variables are unavoidable (performance reasons), a pragmatic solution is in my opinion that the application check the range an generates different statements for a small and a big range.  

Regards  

Jaromir D.B. Nemec

• Original Message ----- From: Allen, Brandon <mailto:Brandon.Allen_at_OneNeck.com> To: oracle-l <mailto:oracle-l_at_freelists.org> Sent: Thursday, August 24, 2006 10:11 PM Subject: RE: How to cope with nasty side effects of bind variable peeking

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




application/octet-stream attachment: fine_grained_hard_parsing.sql