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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>
> 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" <[EMAIL PROTECTED]>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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;
