-
Hi all ,
select merchant_name , amount from transctions
where tran_date = '20020210' ;
select merchant_name , amount from transctions
where tran_date = '20020325' ;
1) Is these two statements identical or needs separate
parsing in the shared pool.
2) If these two statements are not identical what is a
real life scenario of two statements being identical and
shares the same parse execution plan ?
3) what is meant by using bind variables and making
statements identical. How is it done in the above case
Thanking in advance
Regards
George
-
1. They are not identical. The date string makes them different. BOTH statements would have to be parsed and reside in the shared pool.
2 & 3. Using PL/SQL declare a bind variable ( read the doc if you don't know what a bind variable is). Your sql then should be executed something like ......where tran_date = :v_trandate ;
the : in front of v_trandate tells the parser that it is a bind variable and not a literal. The sql gets parsed only once and multiple copies of essentially the same sql don't reside in the shared pool making room for other sql.
-
little advice:if this app was hard coded
just add parameter in init.ora
cursor_sharing=similar.It will replace your literal with system-named bind variables like ":sys.b1"
