oracle imp過慢的解決辦法

版權宣告：轉載時請以超連結形式標明文章原始出處和作者資訊及本宣告http://fbirdzp.blogbus.com/logs/98676185.html

效能測試或是壓力測試工作的核心是測試環境的準備，其中就經常涉及到大資料量的匯入匯出。對於一個超過100GB的資料量的imp工作，完全參照生產環境的標準進行正規imp匯入，有時候是很痛苦的事情。

測試環境涉及到大資料量的資料匯入工作，其實是有一些好的經驗的，尤其是在專案關鍵階段，我們必須合理利用測試環境資料庫安全級別相對較低的特點。

以測試環境如下條件的資料匯入為例：

• Oracle 11g RAC archive mode,歸檔模式
• Oracle 11g RAC 僅有兩塊磁陣盤儲存資料，讀寫效能一般
• 有一個分割槽表有超過300GB的資料需要imp匯入，每個分割槽有約5000萬條記錄
• 這個表使用range partition（3天1個分割槽），並且有local索引

 

週五拿到dmp資料檔案的時候，下班前就開始imp，後臺執行後就閃人了，結果週末兩天加上週一共出現瞭如下幾個問題：

• imp時沒有設定buffer大小，結果導致報"imp-"錯誤提示，imp操作中斷
• imp時RAC各節點平均每2分鐘產生1GB的archive log，歸檔空間扛不住，500GB的歸檔空間一晚上就滿了，imp中斷
• 存放資料的表空間只有一個datafile，且並非bigfile,滿了，imp又中斷了
• imp的速度很慢。兩天了，基本上就見著盤很忙，沒怎麼見著資料成功匯入的日誌記錄
• 可能因為IO的效能確實很差，通過nmon，vmstat觀察到的io表現，每秒寫的速度不到20MB，Disk busy已經顯示101%，XP20000的盤慫得真純爺們！

 

後來，通過如下手段顯著優化了imp的時間：

• change RAC archive mode to noarchivelog mode
• alter table t1 nologging;
• alter table drop index
• imp使用更大的buffer

原先三天沒有導完的資料，現在半天就搞定了，而且剩下了很多維護archivelog的成本。

metalink上有一篇專門介紹imp調優的文章---《Tuning Considerations When Import Is Slow [ID 93763.1]》。

這裡面講到以下有用的知識點，通過以下幾個方面的調整，將會顯著提高imp的效率：

1. System級別的改變

• 建立使用一個大的回滾段替代原有多個小的回滾段，大小是待匯入表大小的50%足夠。
• 資料庫修改為NOARCHIVELOG mode
• 建立幾組大的redo log size,越大越好，因為redolog越大，日誌切換的越少。當看到alert.log裡有類似 'Thread 1 cannot allocate new log, sequence 17, Checkpoint not complete'提示資訊，這說明你需要更大的redo log size。
• 如果條件允許，最好將rollback,datafile以及redo log file放在不同的磁碟上，避免IO爭搶。
• 確保imp操作時，沒有其他IO操作，減少資源爭搶。
• 確保資料字典表裡沒有統計資訊
• 檢查sqlnet.ora檔案裡，確保TRACE_LEVEL_CLIENT = OFF
• 提高DB_BLOCK_SIZE的大小，這個需要全面考量，一旦建立db將不可再修改

 

2.初始化引數init.ora

• 設定LOG_CHECKPOINT_INTERVAL的值大於redo log的數目，減少checkpoints到最小。
• 增加SORT_AREA_SIZE大小，如果主機剩餘記憶體足夠，可設定5~10倍預設大小。當系統出現paging及swaping，則設定過高
• 增加db_block_buffers and shared_pool_size大小

 

個人感覺， 這幾個引數可能真正對imp效能影響沒有顯著影響，預設設定應該足夠。只是沒有實際測試過。

3. imp引數的使用

• 使用COMMIT=N，這意味著整個表imp操作結束後進行commit操作。預設是Y，也就是每個buffer結束後commit一次。這意味點背將會前功盡棄！
• buffer設定大點，一般來說200M就不小了
• 使用INDEXES=N，匯入表資料的時候不更新index，待資料匯入後重新維護索引。顯著提高imp的效率

 

哪些能顯著提高imp的效率？哪些適合測試環境使用，而在安全級別較高的生產環境上無法嘗試？自己把握！

當然，oracle10g後推出的datapump (expdp,impdp)相較而言，效率也有很大提升，並引入並行引數，是匯入匯出操作非常好的選擇。

metalink上有一篇專門介紹imp調優的文章---《Tuning Considerations When Import Is Slow [ID 93763.1]》。

TUNING CONSIDERATIONS WHEN THE IMPORT IS SLOW

            =================================================

The Oracle Import process can sometimes take many hours or even days to complete successfully.  Unfortunately, many imports are needed to perform. crash recovery on databases that, for one reason or another, are not functional.  This makes the time needed for the import even more critical.  There are not many ways to speed up a large import, but here are a few basic changes that can reduce the overall time of import.

System Level Changes

--------------------

 - Create and use one large rollback segment, take all other rollback segments offline.  

One rollback segment approximately 50% of the size of the largest table being imported should be large enough.  Import  basically does 'insert into tabX values (',,,,,,,')' for every row in your database, so the rollback generated for insert statements is only the rowid for each row inserted.  Also create the rollback with the minimum 2 extents of equal size.

- Put the database in NOARCHIVELOG mode until the import is complete.

  This will reduce the overhead of creating and managing archive logs. 

For more info on enabling and disabling archive logging see Note:69739.1

 - As with the rollback segment, create several large redo log files, the larger the better. 

The larger the log files, the fewer log switches that are needed.  Check the alert log for messages like 'Thread 1 cannot allocate new log, sequence 17, Checkpoint not complete'.This indicates the log files need to be bigger or you need more of them.

 - If possible, make sure that rollback, table data, and redo log files are all on separate disks.  

This increases throughput by reducing I/O contention.

 - Make sure there is no IO contention occurring. 

If possible, don't run other jobs which may compete with the import for system resources.

 - Make sure there are no statistics on data dictionary tables.

 - Check the sqlnet.ora in the TNS_ADMIN location.  Make sure that

  TRACE_LEVEL_CLIENT = OFF

- Increase DB_BLOCK_SIZE when recreating the database, if possible.  

The larger the block size, the smaller the number of I/O cycles needed.This change is permanent, so consider all effects it will have before changing it.

 For more info on db block sizing see Note:34020.1

 

Init.ora Parameter Changes

--------------------------

 - Set LOG_CHECKPOINT_INTERVAL to a number that is larger than the size of the redo log files.  This number is in OS blocks (512 bytes on most Unix systems).  This reduces checkpoints to a minimum (only at log switch time).

 - Increase SORT_AREA_SIZE.  Indexes are not being built yet, but any unique or primary key constraints will be.  The increase depends on what other activity is on the machine and how much free memory is available.Try 5-10 times the normal setting.  If the machine starts swapping and paging, you have set it too high.

- Try increasing db_block_buffers and shared_pool_size.Shared pool holds cached dictionary info and things like cursors,procedures, triggers, etc. Dictionary info. or cursors created on the import's behalf (there may be many since it's always working on a new table) may sometimes clog the pipes. Therefore, this stale stuff sits around until the aging/flush mechanisms kick in on a per-request basis because a request can't be satisfied from the lookaside lists. The

  ALTER SYSTEM FLUSH SHARED_POOL throws out *all* currently unused objects in one operation, hence, defragments the pool.

If you can restart the instance with a bigger SHARED_POOL_SIZE prior to importing, that would definitely help. When it starts to slow down, at least you can see what's going on by doing the following:

  SQL> set serveroutput on size 2000;

  SQL>begin

  SQL> dbms_shared_pool.sizes(2000);

  SQL> end;

  SQL> /

 The dbms_shared_pool is in $ORACLE_HOME/rdbms/admin/dbmspool.sql

Import Options Changes

----------------------

 - Use COMMIT=N.  This will cause import to commit after each object (table), not after each buffer.  This is why one large rollback segment is needed.

 - Use a large BUFFER=1024000 size.  This value also depends on system activity,database size, etc.  Several megabytes is usually enough, but if youhave the memory some can go higher.  Again, check for paging and swapping at the OS level to see if it is too high.  This reduces the number of times the import program has to go to the export file for data.  Each time it fetches one buffer's worth of data.

 - Consider using  INDEXES=N during import. The user defined indexes will be created after the table has   been created and populated, but if the primary objective of the import is to get the data in there as fast as possible,then importing with INDEXES=N will help. The indexes can then be created at a later date when time is not a factor.

 If this approach is chosen, then you will need to use INDEXFILE option to extract the DLL for the index creation or to re-run the import with INDEXES=Y and ROWS=N.

 For more info on extracting the DLL from an export file see  Note:29765.1

 

REMEMBER THE RULE OF THUMB: Import should be minimum 2 to 2.5 times the export time. 

Large Imports of LOB Data:

--------------------------

 Generally speaking, a good forumla for determining a target elapsed time for a table import versus the elapsed time for the table export is:

  import elapsed time = export elapsed time X 4

 - Eliminate indexes.  This affects total import time significantly.The existance of LOB data requires special consideration.The LOB locator has a primary key that cannot be explicity dropped or ignored during the import process.

 - Make certain that sufficient space in sufficently large contiguous chunks is available to complete the data load. The following should  provide an accurate image of the space available in the target  tablespace:

   alter tablespace mailindx coalesce;

   select bytes from dba_free_space where tablespace_name = 'MAILINDX' order by bytes desc;

   select bytes from dba_free_space where tablespace_name = 'MAILINDX' order by bytes desc;

 Large Imports of LONG Data:

---------------------------

 Importing a table with a LONG column may cause a higher rate of I/O and disk utilization than importing a table without a LONG column. 

 There are no parameters available within IMP to help optimize the import of these data types.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-712923/，如需轉載，請註明出處，否則將追究法律責任。