The easiest "quick" fix would be load the rows up into a plsql table and each time you get (say)
500 entries in the plsql table, bulk-bind insert them.
hth
connor
- Niall Litchfield <n-litchfield_at_audit-commission.gov.uk> wrote: > 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=
> ed
> ---------------------------------------- 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
> -----------------------------------------------------------------
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
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 Thu Feb 19 2004 - 03:24:38 CST
