Oracle INSERT hanging?

[dklloyd]

Thanks for all your help recently.... Now I have a real puzzler!
I have tried to run a program on behalf of our system supplier a number of times now and I'm getting bored with their guesses as to what the problem can be.
The program should be building a statistics database on years loads of data so I expect it to take a long while. However after a number of hours the process just doesn't seem to go anywhere. Using TOAD the session shows 'ACTIVE'whilst processing an INSERT statment but the i/o counts remain the same. The process is in the V$SESSION_WAIT table but no desciption of what it is waiting for.
If it's waiting for Oracle resources what would be the way to find out?
If it's waiting for operating system resources (AIX) how can I find out?
Look forward to any of your comments.

Cheers for now.

David

 

[sem]

You may try to explore v$lock

 

[dklloyd]

Having waited a while Oracle came back with a suggestion to prevent jobs from getting into this state.
A paramter 'sqlnet.expire_time = 10' was inserted into the sqlnet.ora file and processes have not got into this state since. Don't know if it's just coincidence but it's worked so far.

dklloyd

 

[mitch77]

Hi,
I have a file with 650000 records, and when i insert that records in the database, it spends +/-15 minutes, and i think that is a poor performance.
How can i increase the performance???

thanks,
Mitch

 

[THoey]

If you have any indexes on this table, drop them prior to the load and then recreate them afterwards. Also, I think you can turn off logging, this will speed things up, but has the danger of not creating rollback segments.

HTH... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.