RE: It took more than an hour to update 10,000 records

Actually, since the statement that is slow is an update, I would suggest also sending the execution plan to the list for suggestions. In SQL*Plus:
a) if necessary, create the plan_table using $ORACLE_HOME/rdbms/admin/utlxplan.sql
b) set autotrace traceonly statistics
c) type in update statement

> -----Original Message-----
> From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
>
> You don't yet know why it's slow.
>
> Why not turn tracing on for the session doing the inserts and then
> run tkprof on the trace file?
>
> Since the inserts seem to be taking so long, you should also
> be able to join v$session_wait with v$session to catch the
> waits in action and determine exactly what they are.
>
> You should also join v$session and v$session_event while
> the insert job is running.
>
> Actually, using bind variables will probably help. The insert
> statements will be less resource intensive and run much faster,
> and this will result in less contention for other sessions.
>
> My tests of insert statements with and without bind variables
> show that using bind variables resulted in an insert job
> of 27k rows running in 15 seconds and < 1/100 sec of parse time.
>
> Not using bind variables required 60 seconds, 20 of which were
> CPU parse time.
>
> The increased run time also resulted in 3x as many buffer busy
> waits for other sessions trying to hit the same table.
>
> ----------------------------------
> "Nguyen, David M" <david.m.nguyen_at_xo.com>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, August 13, 2002 6:24 PM
>
> > I write a script to update 10,000 records in my database
> and noticed it
> took
> > more than an hour to update 10,000 records. The script
> just includes
> 10,000
> > SQL update commands as shown below. Is the time too long to update
> 10,000
> > records? Is it a way to improve the update task to run
> faster. Please
> > advise.
> >
> > *** A portion of my update script is shown below:
> >
> > update dbimpl.npa_nxx set ported_flag = 1 where nxx_id =
> 206 and npa_id
> =
> > 201 and lata_id = 224;
> >
> > update dbimpl.npa_nxx set ported_flag = 1 where nxx_id =
> 207 and npa_id
> =
> > 201 and lata_id = 224;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).