RE: UTL_FILE performance

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 17 Feb 2004 15:10:05 -0500
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F735B6933@xchgbrsm1.corp.espn.pvt>

Try ....=20

CREATE OR REPLACE PROCEDURE read_asql_log ( p_filename IN VARCHAR2
, p_location IN VARCHAR2)
AS

  fhandle           UTL_FILE.FILE_TYPE;
  strtstamp         VARCHAR2 (8);
  strrows           VARCHAR2 (6);
  strdescription    VARCHAR2 (255);
  v_logline         VARCHAR2 (4000);
  v_tstamp          DATE;
  v_rows_affected   NUMBER;
  v_description     VARCHAR2 (255);
  file_error        EXCEPTION;

  PRAGMA EXCEPTION_INIT (file_error, -20100); BEGIN
  DBMS_OUTPUT.ENABLE (1000000);   BEGIN
    fhandle :=3D UTL_FILE.FOPEN (p_location, p_filename, 'r', 4000);   EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN

probably specified wrong LOCATION OR FILE NAME RAISE_APPLICATION_ERROR (-20100, 'Check asql Log LOCATION AND = existence'); WHEN OTHERS THEN RAISE; END; -- BEGIN
- scroll to start of data LOOP UTL_FILE.GET_LINE (fhandle, v_logline); EXIT WHEN SUBSTR(v_logline, 1, 8) =3D '--------'; END LOOP; END;

LOOP
BEGIN

      UTL_FILE.GET_LINE (fhandle, v_logline);
      strtstamp     :=3D SUBSTR(v_logline,  1,   8);
      EXIT WHEN strtstamp =3D 'Time Use';


      --

      strrows       :=3D SUBSTR(v_logline,  9,  16);
      v_description :=3D SUBSTR(v_logline, 16, 255);
      v_tstamp :=3D TO_DATE (TRIM (strtstamp), 'HH24:MI:SS');
      v_rows_affected :=3D TO_NUMBER (TRIM (strrows) );


      --

      INSERT INTO asql_log (tstamp, rows_affected, description)
           VALUES (v_tstamp, v_rows_affected, v_description);
    EXCEPTION                                           -- reached end =
of file
      WHEN NO_DATA_FOUND THEN
        EXIT;

    END;
  END LOOP;
  COMMIT;
  UTL_FILE.FCLOSE (fhandle);
END;
/

I have a procedure that reads 10046^8 trace files and analyzes them, it = reads 208mb file in under 5 minutes and I haven't got time to optimize = it.
Raj

-------------------------------------------------------------------------=

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. select standard_disclaimer from company_requirements; QOTD: Any clod can have facts, having an opinion is an art !

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Feb 17 2004 - 14:10:05 CST