| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: UTL_FILE performance
Hi Niall,
Just a small suggestion. May be you can try this approach of reading = the file and inserting into a pl/sql table and then doing a forall = insert. If possible try to share the tkprof results of this change with = the list
For sql loader specifics refer to SQL Loader definitive guide from = oreilly. One of the best books in this area.
Best Regards
Sriram Kumar
=20
-----Original Message-----
From: Niall Litchfield [mailto:n-litchfield_at_audit-commission.gov.uk]=20
Sent: Wednesday, February 18, 2004 4:04 PM
To: oracle-l_at_freelists.org
Subject: RE: UTL_FILE performance
Thanks to all that replied.=3D20
I have (re)learned a couple of things.=3D20
1. You don't need to guess, Oracle will tell you.=3D20
2. Don't assume that it is the feature that s new to you that is the =
proble=3D
m.=3D20
=3D20
A couple of extracts from tkprof .=3D20
=3D20
BEGIN read_asql_log('work_24.log','SCRIPTS'); END;
call count cpu elapsed disk query current =
=3D
rows
------- ------ -------- ---------- ---------- ---------- ---------- = -----=3D ----- Parse 1 0.02 0.10 4 34 0 =
------- ------ -------- ---------- ---------- ---------- ---------- = -----=3D ----- total 2 42.05 124.30 38 1888054 0 =
Event waited on Times Max. Wait Total =Wait=3D
call count cpu elapsed disk query current =
=3D
rows
------- ------ -------- ---------- ---------- ---------- ---------- = -----=3D ----- Parse 1 0.01 0.03 0 47 0 =
------- ------ -------- ---------- ---------- ---------- ---------- = -----=3D ----- total 626498 70.39 117.70 15 5841 1928518 =6=3D
600k single row inserts, hmm maybe not such a good decision. Commenting =
out=3D
the insert statement results in an elapsed time of 20secs. :(=3D20
=3D20
Although we are on 9.2, I intended to share the script with the user =
group =3D
for the product that creates these logs and 8i is still a supported =
platfor=3D
m for this app - thus external tables are out, and I never can figure =
out s=3D
qlloader syntax.=3D20
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=3D20
=3D20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- DISCLAIMER: This message contains privileged and confidential information and is = intended only for the individual named.If you are not the intended = recipient you should not disseminate,distribute,store,print, copy or = deliver this message.Please notify the sender immediately by e-mail if = you have received this e-mail by mistake and delete this e-mail from = your system.E-mail transmission cannot be guaranteed to be secure or = error-free as information could be = intercepted,corrupted,lost,destroyed,arrive late or incomplete or = contain viruses.The sender therefore does not accept liability for any = errors or omissions in the contents of this message which arise as a = result of e-mail transmission. If verification is required please = request a hard-copy version. ---------------------------------------------------------------- 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 - 22:40:54 CST
 |
 |