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