Re: bulk insert - to make it faster

On 27 Mar 2006 10:58:51 -0800, "picksdba" <Utpal.Dhar_at_gmail.com> wrote:

>'faster' - well, i tested it with 1 million records and it took an hour
>or so. i think it shouldn't take that much time.
>
>I have altered the table to nologging. I have made some indexes
>unusable, but i do have the primary key and it's indexes. (i know that
>will take some time, but i guess there is no way escaping that)
>
>what kind of session stats should i be looking for and how do i find
>out what it is waiting for?

you have

v$sesstat
v$session_event
v$session_wait
v$sess_io

You could also issue
execute immediate 'alter session set events=''10046 trace name context forever, level 12'''
to get a trace file with exactly per statement a line what it is waiting for.
You could easily blow up the LGWR because of inadequately sized online redolog files, switching like hell, when you run this. If you suffer from that issue you can find in v$log_history. Log switches should not occur more frequently than once in 15 minutes -- or you will notice it.

--
Sybrand Bakker, Senior Oracle DBA