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:
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...