Explain plan via dbms_output

Понадобилось как-то сравнить два запроса, т.е. надо глянуть план запроса и удостовериться, что данные не теряются. Получилось нечто нижеследующее:


DECLARE
  v_requestor varchar2(30);
  v_bug_id VARCHAR2(10);
  v_sql_statement_1 VARCHAR2(30000);
  v_sql_statement_2 VARCHAR2(30000);

  v_count_1 INTEGER;
  v_count_2 INTEGER;
  v_cur sys_refcursor;

  v_count_rec INTEGER;

  -- cursor for retrieving explain plan
  CURSOR expl_plan_cur(c_statement_id IN VARCHAR2)
         IS SELECT PLAN_TABLE_OUTPUT PLAN_TABLE_OUTPUT
              FROM TABLE(dbms_xplan.display('PLAN_TABLE',c_statement_id,'ALL')) ;
  -- type for expl plan          
  expl_plan_rec expl_plan_cur%ROWTYPE;          
  ---------------------
  -- additional function          
  FUNCTION get_records(f_sql IN VARCHAR2)
    RETURN SYS_REFCURSOR
           IS f_cur SYS_REFCURSOR;
    BEGIN
      OPEN f_cur FOR f_sql;
      RETURN f_cur;
    END;    
  --    
BEGIN
  v_requestor := 'Ischenko .D';
  v_bug_id    := '10298';
  v_sql_statement_1 := 'SELECT * FROM t1 WHERE 1=1'; -- old
  v_sql_statement_2 := 'SELECT * FROM t1 WHERE 1=1'; -- new

  v_sql_statement_1 :=  'SELECT * FROM ( ' || v_sql_statement_1 || ' ) ';
  v_sql_statement_2 :=  'SELECT * FROM ( ' || v_sql_statement_2 || ' ) ';

  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('REPORT: BEGIN SCRIPT - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));

  -- old
  -- old explain plan
  DBMS_OUTPUT.PUT_LINE('REPORT: GET EXPLAIN PLAN sql_statement_1 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
 
  BEGIN
    EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID = ''001'' FOR ' || v_sql_statement_1;
    COMMIT;
    OPEN expl_plan_cur('001');
    LOOP
      FETCH expl_plan_cur INTO expl_plan_rec;
    EXIT WHEN expl_plan_cur % NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(expl_plan_rec.plan_table_output);
    END LOOP;
    CLOSE expl_plan_cur;
  END;

  DBMS_OUTPUT.PUT_LINE('REPORT: END EXPLAIN PLAN sql_statement_1 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));

  -- old get records      
  DBMS_OUTPUT.PUT_LINE('REPORT: GET COUNT RECORDS sql_statement_1 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
  BEGIN
    EXECUTE IMMEDIATE 'SELECT count INTO :count FROM (SELECT COUNT(*) count FROM ( '|| v_sql_statement_1 || ')) '
                 INTO v_count_rec;
    DBMS_OUTPUT.PUT_LINE('REPORT: RETURNED ROWS = '|| v_count_rec);                  
  END;
  DBMS_OUTPUT.PUT_LINE('REPORT: END COUNT RECORDS sql_statement_1 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));


  -- new
  -- new explain plan
  DBMS_OUTPUT.PUT_LINE('REPORT: GET EXPLAIN PLAN sql_statement_2 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
 
  BEGIN
    EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID = ''002'' FOR ' || v_sql_statement_2;
    COMMIT;
    OPEN expl_plan_cur('002');
    LOOP
      FETCH expl_plan_cur INTO expl_plan_rec;
    EXIT WHEN expl_plan_cur % NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(expl_plan_rec.plan_table_output);
    END LOOP;
    CLOSE expl_plan_cur;
  END;

  DBMS_OUTPUT.PUT_LINE('REPORT: END EXPLAIN PLAN sql_statement_2 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));

  -- new get records      
  DBMS_OUTPUT.PUT_LINE('REPORT: GET COUNT RECORDS sql_statement_2 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
  BEGIN
    EXECUTE IMMEDIATE 'SELECT count INTO :count FROM (SELECT COUNT(*) count FROM ( '|| v_sql_statement_2 || ')) '
                 INTO v_count_rec;
    DBMS_OUTPUT.PUT_LINE('REPORT: RETURNED ROWS = '|| v_count_rec);                  
  END;
  DBMS_OUTPUT.PUT_LINE('REPORT: END COUNT RECORDS sql_statement_2 - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));


  DBMS_OUTPUT.PUT_LINE('REPORT: END SCRIPT - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));

  EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('REPORT: END SCRIPT - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
    DBMS_OUTPUT.PUT_LINE('ERROR - REQUESTOR: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);

END;