Re: Faster option than utl_file

From: rjamya <rjamya_at_gmail.com>
Date: Wed, 22 Feb 2006 14:05:18 -0500
Message-ID: <9177895d0602221105x45c81ac2obd1cbe163bcd3947@mail.gmail.com>

Okay ... here is a test ... make up your opinions ...

10.1.0.4, dba_source has 286017 lines ...

Here is the code
connect / as sysdba
prompt Test using utl_file
set heading off feedback off arraysize 100 select 'STARTING ==> UTL_FILE test ' || systimestamp from dual
/

declare
  fhdl UTL_FILE.FILE_TYPE; -- file handle of OS flat file begin

   fhdl := UTL_FILE.FOPEN('SEC_USER_DIR','utlfile.log','W');

• Write a line of text out to the file. for c in (select '"' || owner || '","' || name || '","' || type || '"' txt from dba_source) loop utl_file.put_line(fhdl,c.txt); end loop;
  • Close the file. UTL_FILE.FCLOSE(fhdl); end;
    /
    select 'ENDING ==> UTL_FILE test ' || systimestamp from dual
    /
    select 'STARTING ==> DBMS_SYSTEM test ' || systimestamp from dual
    /
    begin for c in (select '"' || owner || '","' || name || '","' || type || '"' txt from dba_source) loop dbms_system.ksdwrt(1,'MY=> ' || c.txt ); end loop; end;
    /
    select 'ENDING ==> DBMS_SYSTEM test ' || systimestamp from dual
    /
    select 'STARTING ==> SPOOL test ' || systimestamp from dual
    /
    set term off spool spool.log select '"' || owner || '","' || name || '","' || type || '"' txt from dba_source
    /
    spool off set termout on select 'ENDING ==> SPOOL test ' || systimestamp from dual
    /
    exit

Here is the result ...

Connected.
Test using utl_file

STARTING ==> UTL_FILE test 22-FEB-06 01.56.51.018454 PM -05:00 ENDING ==> UTL_FILE test 22-FEB-06 01.57.04.720275 PM -05:00

STARTING ==> DBMS_SYSTEM test 22-FEB-06 01.57.04.722564 PM -05:00 ENDING ==> DBMS_SYSTEM test 22-FEB-06 01.57.12.768036 PM -05:00

STARTING ==> SPOOL test 22-FEB-06 01.57.12.770154 PM -05:00 ENDING ==> SPOOL test 22-FEB-06 01.57.21.501821 PM -05:00

Test using dbms_system.ksdwrt seems faster, but unfortunately it writes to tracefile from which you need to grab your stuff. Spool still seems faster than utl_file.

Raj

---

Got RAC?

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 22 2006 - 13:05:18 CST