DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE runs and evolves successfully, but errors when returning output (ORA-06502)

We recently have upgraded many of our databases to 11g.  One of our tools to make sure performance did not degrade was to implement SQL Plan Baselines for our top queries, as identified a few different ways via AWR.  That’s a long story, but one interesting short note is that after you have Baselines, you might want to evaluate them periodically to see if there is a better plan available.  Oracle provides DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE so you can do this.

I chose to wrap my evolve in a shell script and call it from cron.  The script worked perfectly for just over a month, then one day all my log had in it was an error:

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

I tried increasing my SET LONG statement – no dice.  Also, it was clear from the baselines themselves and the total run time before the error that the evolve was actually working, it was just the log/text output at the end that was failing.  In fact, the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE wasn’t failing at all.  It was actually DBMS_OUTPUT.PUT_LINE that was failing.

Finally I read that DBMS_OUTPUT.PUT_LINE has a 32767 character limit for a single line.   Since I could see that my output had grown slowly but steadily each day, and on the day it started failing it had grown beyond this limit, this was the answer.  This is not exactly a bug in the DBMS_SPM package, however since this output should be expected to be long, one wonders why a better output method wasn’t developed.  Oh well – on to the solution.

Many  smart people have posted about how to chunk up a clob to get around this limit and print it to the screen/sqlplus.  I happened to like and use this one (modified to use put_line instead of writing to a file).

My working evolve script — leaving out the surrounding shell stuff which you’d want to customize for your environment anyway — looks like this:

set serveroutput on
SET LONG 1000000
DECLARE
  results  CLOB;
  r_len    NUMBER;
  putlimit constant binary_integer := 32767;
  r_buffer VARCHAR2(32767);
  r_pos    PLS_INTEGER := 1;
BEGIN
  results := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
   time_limit   => 180,
   verify       => 'YES',
   commit       => 'YES'
    );
  dbms_output.put_line('finished with the evolve here, no errors so far.');
  r_len := dbms_lob.getlength(results);
  dbms_output.put_line('length of results var: '||r_len);
  while r_pos < r_len loop
    r_buffer := dbms_lob.substr(results, putlimit, r_pos);
    exit when r_buffer is null;
    DBMS_OUTPUT.PUT_LINE(r_buffer);
    r_pos := r_pos + LEAST(LENGTH(r_buffer)+1,putlimit);
  end loop;
END;
/