As bad as it gets

Just wanted to share this with you...the mind boggles. Called in to do some "please lower our CPU" work. My standard "highest buffer gets from v$sql" script didn't work because all the figures were so high - had to widen the columns to see output! Then I stumbled upon this absolute gem which I've clean up a little to make it more obvious as to its purpose:

procedure alpha_check(p_text_in varchart, p_ret out number) is
  char_list varchar2(255);
  cursor c1(m number) is
    select substr(p_text_in,m,1) from dual;
  cursor c2(v varchar2) is
    select instr(char_list,v) from dual;
  v_single_char char(1);
  v_in_string number;
begin
  for i in 1 .. 255 loop
   if  chr(i) = '0' or chr(i) = '1' or
       chr(i) = '2' or chr(i) = '3' or
       chr(i) = '4' or chr(i) = '5' or
       chr(i) = '6' or chr(i) = '7' or
       chr(i) = '8' or chr(i) = '9' then
         null;
   else
     char_list := char_list || chr(i);
   end if;
  end loop;

  for i in 1 .. length(p_text_in) loop
    open c1(i);
    fetch c1 into v_single_char;
    close c1;
 
    open c2(v_single_char);
    fetch c2 into v_in_string;
    close c2;
    if v_in_string > 0 then
      p_ret := 1;
      return;
    end if;
  end loop; 
  p_ret := 0;
end;

So every time we call this procedure with an incoming string we:

• build a list of all non-numeric characters (char_list)
• for 'n' times, where n is the length of the passed string:
  • run 'select substr from dual' to find the i'th character of the string
  • run 'select instr from dual' to see if that character is in the char_list

Yep, its the worlds most expensive (and incorrect) version of the 'to_number' function! This application receives about a million rows from external systems each day in CSV format. For each numeric column in every incoming row, we call this 'alpha_check' function to see if its 'valid' number.

In a time period of 90 mins after a database bounce, I'd clocked up 1.9 billion buffer gets...

Magic :-)