Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using Oracle 11.2 as a database for my application. For this purpose I should import my data (around 100m records) to this database. For this purpose I used SQL Developer.

Unfortunately at the middle of the import process, my system crashed and forced me to reboot the system. After system boot up everything related to Oracle is strange:

  • starting up Oracle takes around 10 min (before system crash takes less than 10 sec)
  • SQL Developer takes too much time to connect to database
  • and the importing process becomes 10000 times slower!

Would you please tell me what happen and how can I fix that?

P.S: After passing 24 hours from the crash time a performance becomes better but still much less than before crash.

share|improve this question

closed as off-topic by RolandoMySQLDBA, Paul White, Phil, Kin, gbn Apr 30 at 7:08

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Paul White, Phil, Kin, gbn
If this question can be reworded to fit the rules in the help center, please edit the question.

    
How's your disk space? –  Vérace Apr 7 at 21:53
    
A plenty of disk space available. –  Ali n Apr 9 at 12:25

2 Answers 2

Several things might be going on here and might be difficult to pin down but here's a few things.

After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state. Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may need to be kept or rolled-back and this is done before the instance will open so you need to wait until it completes.

This should, however, only happen the first time you start the database after the crash. If it happens every time afterwards, there may be something else going on. If your job needs to roll back data, depending on the amount of data, that too, can be a lengthy process which might be running while you are re-trying your operation and may slow down other operations.

Also, since the database has crashed, you've lost any data cached in the db blocks buffers as well as anything in the shared pool, so any operations needing access to data may now have to go to disk and any shared SQL in the shared pool may need to be re-parsed, and so on and so forth.

Before re-trying your operation, check all your objects (as in tables, indexes, etc.). If you can, do a little testing - truncate the tables to be loaded (and remove any indexes, constraints, etc.) before you try to re-load, because you don't want to have to wait for rows to be deleted that you're just going to re-insert anyway or for indexes that might be corrupt and slowing down insert operations.

Lastly, instead of SQL Developer, you might want to try using SQL Loader or load in smaller increments in case there's some type of data causing the crash (SQL Loader will leave a "bad" file of data it could not load).

Also, what's your hardware setup? Are you running Oracle and SQL Developer on the same machine? If so, since you're trying to load 100 million rows, try splitting the load. If you are running SQL Developer from a PC, make sure you're not running out of PC resources - 100 million rows for Oracle is not a big deal, but it might for your PC.

share|improve this answer
    
after 24 hours the performance of database becomes better but not same as before! Is there any way that I can check the status of database for finding out what process is in running mode at background? Also I check SQL Loader script and the performance was as poor as sql developer. –  Ali n Apr 9 at 5:52

Is this a permanent condition? Or a temporary one? My first guess is that if you crashed midway through the load, Oracle has to go through the instance recovery process once the system comes back up. Part of that will involve undoing the transaction that was in progress when the system crashed. That, in turn, will involve reading the UNDO vector and applying that to all the data that was written to the data files but not committed. That could easily take longer than the load was running before it crashed to complete. And it would involve a lot of I/O-- particularly if you are simultaneously competing with another instance of the load process-- which could easily lead to system-wide slowness until the failed transaction is completely rolled back.

share|improve this answer
    
I think it is permanent since the performance is poor after passing 48 hours from crash time. –  Ali n Apr 9 at 12:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.