Oracle SQL Performance Tuning Tips- Using Bind variables when possible at a to z help

Using Bind variables when possible

•Try using Bind Variable instead of Literals. Consider the following SQL statement

 

  SELECT FIRST_NAME,  LAST_NAME

  FROM Client

  WHERE CLIENT_NUM = 1200

•Since the CLIENT_NUMBER is likely to be different for every execution, we will almost never find a matching statement in the Shared Pool and consequently the statement will have to be reparsed every time

•Consider the following approach

 

  SELECT FIRST_NAME,  LAST_NAME

  FROM Client

  WHERE CLIENT_NUM = :Client_Num

•You do not need to create a new cursor or re-parse the SQL statement if the value of the bind variable changes. Also, if another session executes the same statement, it is likely to find them in the Shared Pool, since the name of the bind variable does not change from execution to execution.

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet