Re: Bind Variable Peeking

It appears that with literals the optimizer, quite possibly the query transformation engine i.e. before the CBO gets its "hands" on the query and does its peeking, recognizes that all those -1s are identical and rewrites the predicate to "and parent_id in ( 21971987,-1)" which then gets transformed to "and ( parent_id = 21971987 or parent_id = -1 )" as you can see from the access predicate. Or vice versa, i.e. the in list gets transformed to a string of ORs and all the redundant " or parent_id = -1 " predicates get dropped.
With bind variables this transformation does not happen, and probably can not. It would be interesting if the transformation would happen if the optimizer could recognize that all those bind values are identical by using the same bind variable:
" and x.parent_id in ( :b1,:b2,:b2,:b2,:b2,:b2,:b2,:b2,:b2,... and so on ) ". Of course that is purely academic since it defeats the purpose of bind variables as placeholders for host variables which get initialized to -1 and a variable number of them filled with values from user input.

At 04:17 PM 11/21/2006, Ray Feighery wrote:
>9.2.0.6 <http://9.2.0.6>
>Solaris 8
>
>Bind Variable peeking is supposed to look inside the bind variables
>when the query is first run (hard parsed). Yet when I run a query
>with exactly the same values I get different execution paths between
>the literal and bind variable statements.
>This is after the shared pool has been flushed (tkprof confirms a
>library cache miss). The key difference in the execution plans is
>that with literal values the optimizer can resolve the inlist to
>access ("X"."PARENT_ID"=(-1) OR "X"."PARENT_ID"=21971987), but the
>bind variable version is split into 50 OR statements.
>
>I've analyzed the table and there shouldn't be any histograms. Plans
>are generated from v$sql_plan.
>
>Any ideas why there is a difference? It causes a huge difference in
>time (from 0.68s to 9.49s).

snip

>LITERALS:
snip

> and x.parent_id in (
> 21971987,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,
>-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1)
> group by x.parent_id
>/
>
>Elapsed: 00:00: 00.68

snip

>Predicate Information (identified by operation id):
>---------------------------------------------------
>
> 2 - access("USERRATING"."OBJECT_ID"="X"."CHILD_ID")
> 3 - filter("USERRATING"."IS_DELETED"='N' AND
> "USERRATING"."REVIEW_TEXT" IS NOT NULL AND
> "USERRATING"."IS_PUBLISHED"='Y' AND
> "USERRATING"."SITE_ID"=17166095 AND
> "USERRATING"."OBJECT_TYPE"='R')
> 6 - access("X"."PARENT_ID"=(-1) OR "X"."PARENT_ID"=21971987)
>
>BIND VARIABLES:
snip

> and x.parent_id in (
> :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26
>,:b27,:b28,:b29,:b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,:b43,:b44,:b45,:b46,:b47,:b48,:b49,:b50)
> group by x.parent_id
>/
>
>Elapsed: 00:00:09.49

snip

> 4 - filter("X"."PARENT_ID"=:B1 OR "X"."PARENT_ID"=:B2 OR
> "X"."PARENT_ID"=:B3 OR "X"."PARENT_ID"=:B4 OR
> "X"."PARENT_ID"=:B5 OR
> "X"."PARENT_ID"=:B6 OR "X"."PARENT_ID"=:B7 OR
> "X"."PARENT_ID"=:B8 OR
> "X"."PARENT_ID"=:B9 OR "X"."PARENT_ID"=:B10 OR
> "X"."PARENT_ID"=:B11
> OR "X"."PARENT_ID"=:B12 OR "X"."PARENT_ID"=:B13 OR
> "X"."PARENT_ID"=:B14 OR "X"."PARENT_ID"=:B15 OR
> "X"."PARENT_ID"=:B16
> OR "X"."PARENT_ID"=:B17 OR "X"."PARENT_ID"=:B18 OR
> "X"."PARENT_ID"=:B19 OR "X"."PARENT_ID"=:B20 OR
> "X"."PARENT_ID"=:B21
> OR "X"."PARENT_ID"=:B22 OR "X"."PARENT_ID"=:B23 OR
> "X"."PARENT_ID"=:B24 OR "X"."PARENT_ID"=:B25 OR
> "X"."PARENT_ID"=:B26
> OR "X"."PARENT_ID"=:B27 OR "X"."PARENT_ID"=:B28 OR
> "X"."PARENT_ID"=:B29 OR "X"."PARENT_ID"=:B30 OR
> "X"."PARENT_ID"=:B31
> OR "X"."PARENT_ID"=:B32 OR "X"."PARENT_ID"=:B33 OR
> "X"."PARENT_ID"=:B34 OR "X"."PARENT_ID"=:B35 OR
> "X"."PARENT_ID"=:B36
> OR "X"."PARENT_ID"=:B37 OR "X"."PARENT_ID"=:B38 OR
> "X"."PARENT_ID"=:B39 OR "X"."PARENT_ID"=:B40 OR
> "X"."PARENT_ID"=:B41
> OR "X"."PARENT_ID"=:B42 OR "X"."PARENT_ID"=:B43 OR
> "X"."PARENT_ID"=:B44 OR "X"."PARENT_ID"=:B45 OR
> "X"."PARENT_ID"=:B46
> OR "X"."PARENT_ID"=:B47 OR "X"."PARENT_ID"=:B48 OR
> "X"."PARENT_ID"=:B49 OR "X"."PARENT_ID"=:B50)
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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