Bind Variable Peeking
- From: Ray Feighery <rjfeighery@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Nov 2006 23:17:30 +0000
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).
I "solved" the problem using a stored outline, but I still do not
understand why a different execution plan is generated between bind
variables and literals.
LITERALS:
select x.parent_id, round(avg(UserRating.RATING_SCORE)) AS AVERAGE_RATING
from user_rating UserRating, tree_xref x
where UserRating.OBJECT_ID = x.child_id
and 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'
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
---------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | 104 |
| 1 | SORT GROUP BY |
| 2 | 202 | 104 |
|* 2 | HASH JOIN |
| 3 | 303 | 92 |
|* 3 | TABLE ACCESS FULL | USER_RATING
| 3 | 270 | 2 |
| 4 | INLIST ITERATOR |
| | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TREE_XREF |
1115 | 12265 | 89 |
|* 6 | INDEX RANGE SCAN | TREE_XREF_PARENT_ID_INDEX |
1115 | | 7 |
---------------------------------------------------------------------------------------------
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:
exec :b0:=17166095; :b1:=21971987; :b2:=-1; :b3:=-1; :b4:=-1; :b5:=-1;
:b6:=-1; :b7:=-1; :b8:=-1; :b9:=-1; :b10:=-1; :b11:=-1; :b12:=-1; :b13:=
-1; :b14:=-1; :b15:=-1; :b16:=-1; :b17:=-1; :b18:=-1; :b19:=-1;
:b20:=-1; :b21:=-1; :b22:=-1; :b23:=-1; :b24:=-1; :b25:=-1; :b26:=-1;
:b27:=-1;
:b28:=-1; :b29:=-1; :b30:=-1; :b31:=-1; :b32:=-1; :b33:=-1; :b34:=-1;
:b35:=-1; :b36:=-1; :b37:=-1; :b38:=-1; :b39:=-1; :b40:=-1; :b41:=-1; :b
42:=-1; :b43:=-1; :b44:=-1; :b45:=-1; :b46:=-1; :b47:=-1; :b48:=-1;
:b49:=-1; :b50:=-1; :b51:=-1;
select x.parent_id, round(avg(UserRating.RATING_SCORE)) AS AVERAGE_RATING
from user_rating UserRating, tree_xref x
where UserRating.OBJECT_ID = x.child_id
and UserRating.IS_DELETED = 'N'
and UserRating.REVIEW_TEXT IS NOT NULL
and UserRating.IS_PUBLISHED = 'Y'
and UserRating.SITE_ID = :b0
and UserRating.OBJECT_TYPE='R'
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
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 663 |
| 1 | SORT GROUP BY | | 3 | 303 | 663 |
|* 2 | HASH JOIN | | 3 | 303 | 651 |
|* 3 | TABLE ACCESS FULL | USER_RATING | 3 | 270 | 2 |
|* 4 | TABLE ACCESS FULL | TREE_XREF | 27473 | 295K| 648 |
---------------------------------------------------------------------
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"=:B0 AND
"USERRATING"."OBJECT_TYPE"='R')
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)
Ray
--
//www.freelists.org/webpage/oracle-l
Other related posts: