Re: How to cope with nasty side effects of bind variable peeking
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 19:53:02 +0100
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@xxxxxxx>
To: <jaromir@xxxxxxxxxxxx>; <Brandon.Allen@xxxxxxxxxxx>; "'oracle-l'"
<oracle-l@xxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
