Recently our team has migrated from Windows to Linux (OEL 6) by Export Import.
Oracle 10.2.0.1 to 188.8.131.52 Standard Edition.
Insertion takes too long time in all tables. (Reported that it was fine while it was on Windows)
Also ive a table that has around 5 Crores records in it. Inserting in this table takes a very long time.
Tried creating Index, Dropping and Recreating index
Analyzing index. Yet helpless.
How to determine the cause.
First of all: this is an international forum. You should use international (English) measurement units.
Secondly: You should answer the main question which is 'What it is waiting for'.
This means you need to find what INSERT statements are waiting for.
So, v$session_events, v$session_waits, etc.
You can just find some indication by tracing your own session, performing an INSERT, and consulting your trace file.
Senior Oracle DBA
Have a check on wait event for that session from v$session_wait
Check for dependent constraint & triggers on subjected table .
If wait event found to be "enq: TX - row lock contention” check for Foreign key & have indexing done on refereed columns
If the storage didn't change, I don't think that shifting from Windows to Linux has anything to do with the extra time taken for insert. As mentioned by Sybrand already, what did you see for the session doing the insert as a reason for the wait? If you are licensed for it, ASH can also help.
By performing the export and import, you essentially upgraded your database. Were all of the mandatory post-upgrade steps completed ?
You should not use ANALYZE command - use DBMS_STATS