Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Bind Variable Peeking

Bind Variable Peeking

From: Ray Feighery <rjfeighery_at_gmail.com>
Date: Tue, 21 Nov 2006 23:17:30 +0000
Message-ID: <4563890A.70709@gmail.com>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 21 2006 - 17:17:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US