PL/SQL is "nearly" fast

Its fair to say that PL/SQL is extremely fast, especially in the more recent releases. However the attraction of using PL/SQL for common functionality can sometimes lead to performance problems as the following example will demonstrate. Assume that we have a common package function which returns the language that a user will receive their rows in. Thus a standard SQL may look like:

select text
from text_messages
where language_code = common_code.get_the_language;

Where "get_language" does a simple query to return the language code for the current user. Whilst moving the commonly used query to a package is an admirable goal, its been poorly used in this case. Running this on an (approx) 10,000 rows gives the following statistics:

   Statistics
   ----------------------------------------------------------
          9334  recursive calls
         37336  db block gets
          9428  consistent gets

which indicates that the "common_code.get_the_language" was executed once for every row in the table.

The way around this is to (where possible) ensure that the package code only executes once, by either running it initially and storing the result in a variable (which is then used in the query), or the following (more elegant?) solution

select text
from text_messages, 
     ( select common_code.get_the_language  lang
       from dual where rownum = 1 ) s
where language_code = s.lang;

which on the same table gave

   Statistics
   ----------------------------------------------------------
             1  recursive calls
            12  db block gets
            97  consistent gets

The "where rownum = 1" important since without it Oracle is "smart" enough to merge the query back into its original form (which is the last thing we want)