Jul 152013
 
declare
  the_library_cache_but integer;
begin

  select slightly_different_statements
  into   the_library_cache_but 
  from   the_optimizers_point_of_view,
         we_are_converted_to_the_same
  where  we_are_in_plsql      = 'Y';
  
  SELECT SLIGHTLY_DIFFERENT_STATEMENTS
  INTO   THE_LIBRARY_CACHE_BUT 
  FROM   THE_OPTIMIZERS_POINT_OF_VIEW,
         WE_ARE_CONVERTED_TO_THE_SAME
  WHERE  WE_ARE_IN_PLSQL = 'Y';
  
  Select Slightly_Different_Statements
  Into   The_Library_Cache_But 
  From   The_Optimizers_Point_Of_View,
      We_Are_Converted_To_The_Same
  Where  We_Are_In_Plsql = 'Y';
  
end;
/

When SQL is within PL/SQL, Oracle standardises the formatting of the queries 
(uppercasing the text and removing whitespace). This reduces the number of hard 
parses performed if you have "the same" SQL in different parts of your application. 
Another reason to using PL/SQL when interfacing with Oracle ;)

To see the proof of this, use this script. Contrast this with the non-pl/sql version.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)