Re: Faster option than utl_file
- From: rjamya <rjamya@xxxxxxxxx>
- To: MFontana@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 22 Feb 2006 14:05:18 -0500
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
