Add new comment

A script to check the query execution time as well as the time to retrieve all records via a FOR cur IN construct:

SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 32000
SET PAGESIZE 0
SET TERMOUT OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
ALTER SESSION FORCE PARALLEL QUERY;
SPOOL query.log

-- set timing on
declare
  v_ctr        pls_integer := 0;
  v_startquery date := SYSDATE;
  v_endquery   date;
  v_endrecords date;
begin
  DBMS_OUTPUT.PUT_LINE('Query Timing Check');
  DBMS_OUTPUT.PUT_LINE('.');
  for vrec in (select * from dual)
  LOOP
    IF v_ctr = 0 THEN
      v_endquery := sysdate;
    END IF;
    v_ctr := v_ctr + 1;
  END LOOP;
  v_endrecords := sysdate;

  DBMS_OUTPUT.PUT_LINE('Start operation : ' || TO_CHAR(v_startquery,'YYYY-MM-DD HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE('Query time : '|| ROUND((v_endquery-v_startquery)*(24*60*60)) || ' seconds.');
  DBMS_OUTPUT.PUT_LINE('Retrieval time : ' || ROUND( (v_endrecords - v_endquery)*(24*60*60) ) || ' seconds for ' || v_ctr || ' records');
  DBMS_OUTPUT.PUT_LINE('End operation : ' || TO_CHAR(v_endquery,'YYYY-MM-DD HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE('.');
END;
/