RE: UTL_FILE performance

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 18 Feb 2004 10:33:48 +0000
Message-Id: <s0333fa1.043@bristol21.bristol.ac>

Thanks to all that replied.=20
I have (re)learned a couple of things.=20

1. You don't need to guess, Oracle will tell you.=20
2. Don't assume that it is the feature that s new to you that is the proble=
m.=20

=20

A couple of extracts from tkprof .=20
=20

BEGIN read_asql_log('work_24.log','SCRIPTS'); END;

call count cpu elapsed disk query current = rows
------- ------ -------- ---------- ---------- ---------- ---------- -----=

Parse 1 0.02 0.10 4 34 0 =

0
Execute 1 42.03 124.20 34 1888020 0 =

1
Fetch 0 0.00 0.00 0 0 0 =

0
------- ------ -------- ---------- ---------- ---------- ---------- -----=

total 2 42.05 124.30 38 1888054 0 =

1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 76 =20
Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Wait=

Waited ---------- ----------= -- SQL*Net message to client 1 0.00 0.= 00 SQL*Net message from client 1 10.12 10.= 12 ***************************************************************************=
INSERT into asql_log(tstamp,rows_affected,description)=20 values (:b3,:b2,:b1)

call count cpu elapsed disk query current = rows
------- ------ -------- ---------- ---------- ---------- ---------- -----=

Parse 1 0.01 0.03 0 47 0 =

0
Execute 626497 70.38 117.67 15 5794 1928518 6= 26497
Fetch 0 0.00 0.00 0 0 0 =

0
------- ------ -------- ---------- ---------- ---------- ---------- -----=

total 626498 70.39 117.70 15 5841 1928518 6= 26497

600k single row inserts, hmm maybe not such a good decision. Commenting out= the insert statement results in an elapsed time of 20secs. :(=20
=20

Although we are on 9.2, I intended to share the script with the user group =
for the product that creates these logs and 8i is still a supported platfor=
m for this app - thus external tables are out, and I never can figure out s=

qlloader syntax.=20
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=20

=20

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

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 Wed Feb 18 2004 - 04:33:48 CST