RE: How to cope with nasty side effects of bind variable peeking
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: jaromir@xxxxxxxxxxxx, Brandon.Allen@xxxxxxxxxxx, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 26 Aug 2006 00:13:44 +0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of jaromir nemec
Sent: Friday, August 25, 2006 06:56
To: Brandon.Allen@xxxxxxxxxxx; 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@xxxxxxxxxxx>
To: oracle-l <mailto:oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, August 24, 2006 10:11 PM
Subject: RE: How to cope with nasty side effects of bind variable peeking
Other related posts:
