Clarification on pase code
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Clarification on pase code

  1. #1
    Join Date
    Oct 2001
    Location
    Kuwait
    Posts
    23
    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
    Oracle DBA
    National Bank Of Kuwait

  2. #2
    Join Date
    Mar 2002
    Posts
    6
    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.

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    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"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width