包含OLAP元件SCHEMA使用imp匯入碰到嚴重效能問題

客戶一個十幾個G的使用者嘗試使用IMP匯入到資料庫中，執行時間超過了2天。

資料庫版本為10.2.0.5 FOR LINUX X86-64，而匯入的dmp檔案是在10.2.0.3 FOR LINUX X86平臺上匯出的，這個使用者包含了OLAP元件。

整個匯入過程異常緩慢，一共不到20G的資料量，匯入的時間超過了兩天。雖然imp匯入效率不高，但是也沒有理由慢到如此地步，透過10046跟蹤imp程式：

*** 2012-08-07 17:25:03.099
*** ACTION NAME:() 2012-08-07 17:25:03.051
*** MODULE NAME:(imp@db (TNS V1-V3)) 2012-08-07 17:25:03.051
*** SERVICE NAME:(SYS$USERS) 2012-08-07 17:25:03.051
*** SESSION ID:(880.53796) 2012-08-07 17:25:03.051
=====================
PARSING IN CURSOR #61 len=6 dep=1 uid=100 ct=44 lid=100 tim=5574818059202 hv=255718823 ad='0'
COMMIT
END OF STMT
PARSE #61:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=5574818059197
XCTEND rlbk=0, rd_only=1
EXEC #61:c=0,e=4723,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=5574818115096
=====================
PARSING IN CURSOR #3 len=29323 dep=0 uid=100 ct=47 lid=100 tim=5574818115145 hv=1172437913 ad='fe303758'
BEGIN
dbms_aw_exp.import_chunk100(1492, 'AADavwAAKAAAAAYAAADavwAAKQAAAAYAAADavwAAKgAAAAYAAADavwAAKwAAAAYA
AADavwAALAAAAAYAAADavwAALQAAAAYAAADavwAALgAAAAYAAADavwAALwAAAAYA
AADavwAAJAAAAAYAAABy0wAAJQAAAAYAAABy0wAAJgAAAAYAAABy0wAAJwAAAAYA
AABy0wAAKAAAAAYAAABy0wAAKQAAAAYAAABy0wAAKgAAAAYAAABy0wAAKwAAAAYA
AABy0wAALAAAAAYAAABy0wAALQAAAAYAAABy0wAALgAAAAYAAABy0wAALwAAAAYA
AABy0wAAJAAAAAYAAAC90wAAJQAAAAYAAAC90wAAJgAAAAYAAAC90wAAJwAAAAYA
AAC90wAAKAAAAAYAAAC90wAAKQAAAAYAAAC90wAAKgAAAAYAAAC90wAAKwAAAAYA
AAC90wAALAAAAAYAAAC90wAALQAAAAYAAAC90wAALgAAAAYAAAC90wAALwAAAAYA
AAC90wAAJAAAAAYAAACJrQAAJQAAAAYAAACJrQAAJgAAAAYAAACJrQAAJwAAAAYA
AACJrQAAKAAAAAYAAACJrQAAKQAAAAYAAACJrQAAKgAAAAYAAACJrQAAKwAAAAYA
AACJrQAALAAAAAYAAACJrQAALQAAAAYAAACJrQAALgAAAAYAAACJrQAALwAAAAYA
AACJrQAAJAAAAAYAAAAKvAAAJQAAAAYAAAAKvAAAJgAAAAYAAAAKvAAAJwAAAAYA
AAAKvAAAKAAAAAYAAAAKvAAAKQAAAAYAAAAKvAAAKgAAAAYAAAAKvAAAKwAAAAYA
AAAKvAAALAAAAAYAAAAKvAAALQAAAAYAAAAKvAAALgAAAAYAAAAKvAAALwAAAAYA
AAAKvAAAJAAAAAYAAABXvwAAJQAAAAYAAABXvwAAJgAAAAYAAABXvwAAJwAAAAYA
AABXvwAAKAAAAAYAAABXvwAAKQAAAAYAAABXvwAAKgAAAAYAAABXvwAAKwAAAAYA
AABXvwAALAAAAAYAAABXvwAALQAAAAYAAABXvwAALgAAAAYAAABXvwAALwAAAAYA
AABXvwAAJAAAAAYAAABJswAAJQAAAAYAAABJswAAJgAAAAYAAABJswAAJwAAAAYA
AABJswAAKAAAAAYAAABJswAAKQAAAAYAAABJswAAKgAAAAYAAABJswAAKwAAAAYA
AABJswAALAAAAAYAAABJswAALQAAAAYAAABJswAALgAAAAYAAABJswAALwAAAAYA
AABJswAAJAAAAAYAAAAJuAAAJQAAAAYAAAAJuAAAJgAAAAYAAAAJuAAAJwAAAAYA
AAAJuAAAKAAAAAYAAAAJuAAAKQAAAAYAAAAJuAAAKgAAAAYAAAAJuAAAKwAAAAYA
AAAJuAAALAAAAAYAAAAJuAAALQAAAAYAAAAJuAAALgAAAAYAAAAJuAAALwAAAAYA
AAAJuAAAJAAAAAYAAAAOxwAAJQAAAAYAAAAOxwAAJgAAAAYAAAAOxwAAJwAAAAYA
AAAOxwAAKAAAAAYAAAAOxwAAKQAAAAYAAAAOxwAAKgAAAAYAAAAOxwAAKwAAAAYA
AAAOxwAALAAAAAYAAAAOxwAALQAAAAYAAAAOxwAALgAAAAYAAAAOxwAALwAAAAYA
AAAOxwAAJAAAAAYAAAAMpAAAJQAAAAYAAAAMpAAAJgAAAAYAAAAMpAAAJwAAAAYA
AAAMpAAAKAAAAAYAAAAMpAAAKQAAAAYAAAAMpAAAKgAAAAYAAAAMpAAAKwAAAAYA
AAAMpAAALAAAAAYAAAAMpAAALQAAAAYAAAAMpAAALgAAAAYAAAAMpAAALwAAAAYA
AAAMpAAAJAAAAAYAAABXwwAAJQAAAAYAAABXwwAAJgAAAAYAAABXwwAAJwAAAAYA
AABXwwAAKAAAAAYAAABXwwAAKQAAAAYAAABXwwAAKgAAAAYAAABXwwAAKwAAAAYA
AABXww==');
dbms_aw_exp.import_chunk100(1492, 'AAAsAAAABgAAAFfDAAAtAAAABgAAAFfDAAAuAAAABgAAAFfDAAAvAAAABgAAAFfD
AAAkAAAABgAAAC66AAAlAAAABgAAAC66AAAmAAAABgAAAC66AAAnAAAABgAAAC66
AAAoAAAABgAAAC66AAApAAAABgAAAC66AAAqAAAABgAAAC66AAArAAAABgAAAC66
AAAsAAAABgAAAC66AAAtAAAABgAAAC66AAAuAAAABgAAAC66AAAvAAAABgAAAC66
AAAkAAAABgAAALLAAAAlAAAABgAAALLAAAAmAAAABgAAALLAAAAnAAAABgAAALLA
AAAoAAAABgAAALLAAAApAAAABgAAALLAAAAqAAAABgAAALLAAAArAAAABgAAALLA
AAAsAAAABgAAALLAAAAtAAAABgAAALLAAAAuAAAABgAAALLAAAAvAAAABgAAALLA
AAAkAAAABgAAAAC8AAAlAAAABgAAAAC8AAAmAAAABgAAAAC8AAAnAAAABgAAAAC8
AAAoAAAABgAAAAC8AAApAAAABgAAAAC8AAAqAAAABgAAAAC8AAArAAAABgAAAAC8
AAAsAAAABgAAAAC8AAAtAAAABgAAAAC8AAAuAAAABgAAAAC8AAAvAAAABgAAAAC8
AAAkAAAABgAAABOyAAAlAAAABgAAABOyAAAmAAAABgAAABOyAAAnAAAABgAAABOy
AAAoAAAABgAAABOyAAApAAAABgAAABOyAAAqAAAABgAAABOyAAArAAAABgAAABOy
AAAsAAAABgAAABOyAAAtAAAABgAAABOyAAAuAAAABgAAABOyAAAvAAAABgAAABOy
AAAkAAAABgAAAA2zAAAlAAAABgAAAA2zAAAmAAAABgAAAA2zAAAnAAAABgAAAA2z
AAAoAAAABgAAAA2zAAApAAAABgAAAA2zAAAqAAAABgAAAA2zAAArAAAABgAAAA2z
AAAsAAAABgAAAA2zAAAtAAAABgAAAA2zAAAuAAAABgAAAA2zAAAvAAAABgAAAA2z
AAAkAAAABgAAAPy0AAAlAAAABgAAAPy0AAAmAAAABgAAAPy0AAAnAAAABgAAAPy0
AAAoAAAABgAAAPy0AAApAAAABgAAAPy0AAAqAAAABgAAAPy0AAArAAAABgAAAPy0
AAAsAAAABgAAAPy0AAAtAAAABgAAAPy0AAAuAAAABgAAAPy0AAAvAAAABgAAAPy0
AAAkAAAABgAAAFEdAAAlAAAABgAAAFEdAAAmAAAABgAAAFEdAAAnAAAABgAAAFEd
AAAoAAAABgAAAFEdAAApAAAABgAAAFEdAAAqAAAABgAAAFEdAAArAAAABgAAAFEd
AAAsAAAABgAAAFEdAAAtAAAABgAAAFEdAAAuAAAABgAAAFEdAAAvAAAABgAAAFEd
AAAkAAAAIwAAAPUKAAAlAAAAIwAAAPUKAAAnAAAAIwAAAPUKAAAoAAAAIwAAAPUK
AAApAAAAIwAAAPUKAAAtAAAAIwAAAPUKAAAoAAAAIwAAAOQMAAAtAAAAIwAAAOQM
AAAoAAAAIwAAADgPAAApAAAAIwAAADgPAAAtAAAAIwAAADgPAAAmAAAAIwAAAEQQ
AAAtAAAAIwAAAEQQAAAkAAAAIwAAAKMYAAAlAAAAIwAAAKMYAAAoAAAAIwAAAKMY
AAAtAAAAIwAAAKMYAAAkAAAAIwAAAFUhAAAlAAAAIwAAAFUhAAAnAAAAIwAAAFUh
AAAoAAAAIwAAAFUhAAApAAAAIwAAAFUhAAAtAAAAIwAAAFUhAAAkAAAAIwAAAB8k
AAAlAAAAIwAAAB8kAAAoAAAAIwAAAB8kAAAtAAAAIwAAAB8kAAAkAAAAIwAAALk3
AAAlAAAAIwAAALk3AAAtAAAAIwAAALk3AAAkAAAAIwAAANE3AAAlAAAAIwAAANE3
AAAnAAAAIwAAANE3AAAoAAAAIwAAANE3AAAtAAAAIwAAANE3AAAkAAAAIwAAAOQ6
AAAlAA==');
dbms_aw_exp.import_chunk100(1492, 'AAAjAAAA5DoAACcAAAAjAAAA5DoAACkAAAAjAAAA5DoAAC0AAAAjAAAA5DoAACQA
AAAjAAAAGUAAACUAAAAjAAAAGUAAACgAAAAjAAAAGUAAACkAAAAjAAAAGUAAAC0A
AAAjAAAAGUAAAC0AAAAjAAAAIksAACQAAAAjAAAAO0sAACUAAAAjAAAAO0sAACcA
AAAjAAAAO0sAAC0AAAAjAAAAO0sAACcAAAAjAAAAvkwAAC0AAAAjAAAAvkwAACkA
AAAjAAAAfawAAC0AAAAjAAAAfawAAC0AAAAjAAAAGT4AAC4AAAAjAAAAGT4AAC0A
AAAjAAAAfRMAACcAAAAjAAAAFAwAACgAAAAjAAAAFAwAACkAAAAjAAAAFAwAAC0A
AAAjAAAAFAwAAC0AAAAjAAAAVwwAACQAAAAjAAAAWwwAACUAAAAjAAAAWwwAACcA
AAAjAAAAWwwAAC0AAAAjAAAAWwwAACQAAAAjAAAAgw0AACUAAAAjAAAAgw0AACgA
AAAjAAAAgw0AAC0AAAAjAAAAgw0AAC0AAAAjAAAApQ4AACQAAAAjAAAAEhAAACUA
AAAjAAAAEhAAACcAAAAjAAAAEhAAAC0AAAAjAAAAEhAAAC0AAAAjAAAAsRMAACQA
AAAjAAAA5xYAACUAAAAjAAAA5xYAAC0AAAAjAAAA5xYAAC0AAAAjAAAAJBcAACQA
AAAjAAAAbhcAACUAAAAjAAAAbhcAACcAAAAjAAAAbhcAACgAAAAjAAAAbhcAACQA
AAAjAAAApBgAACUAAAAjAAAApBgAACcAAAAjAAAApBgAACgAAAAjAAAApBgAACkA
AAAjAAAApBgAAC0AAAAjAAAApBgAACQAAAAjAAAAAR4AACUAAAAjAAAAAR4AAC0A
AAAjAAAAAR4AAC0AAAAjAAAAliAAACQAAAAjAAAA+yAAACUAAAAjAAAA+yAAACgA
AAAjAAAA+yAAACkAAAAjAAAA+yAAAC0AAAAjAAAA+yAAACQAAAAjAAAAZCEAACUA
AAAjAAAAZCEAACYAAAAjAAAAZCEAACgAAAAjAAAAZCEAAC0AAAAjAAAAZCEAACgA
AAAjAAAACSIAAC0AAAAjAAAACSIAAC0AAAAjAAAAQCIAACQAAAAjAAAALiQAACUA
AAAjAAAALiQAACcAAAAjAAAALiQAAC0AAAAjAAAALiQAACQAAAAjAAAALScAACUA
AAAjAAAALScAACwAAAAjAAAALScAACQAAAAjAAAAYi4AACUAAAAjAAAAYi4AAC0A
AAAjAAAAYi4AACQAAAAjAAAAOjEAACUAAAAjAAAAOjEAAC0AAAAjAAAAOjEAAC0A
AAAjAAAAkzIAACQAAAAjAAAAXDQAACUAAAAjAAAAXDQAAC0AAAAjAAAAXDQAAC0A
AAAjAAAA6DYAACQAAAAjAAAA/D0AACUAAAAjAAAA/D0AAC0AAAAjAAAA/D0AAC0A
AAAjAAAACkEAACgAAAAjAAAAX0EAAC0AAAAjAAAAX0EAAC0AAAAjAAAAckEAACQA
AAAjAAAAOUQAACUAAAAjAAAAOUQAACcAAAAjAAAAOUQAAC0AAAAjAAAAOUQAACgA
AAAjAAAARUgAAC0AAAAjAAAARUgAACQAAAAjAAAAX0gAACUAAAAjAAAAX0gAACgA
AAAjAAAAX0gAAC0AAAAjAAAAX0gAACQAAAAjAAAAKUoAACUAAAAjAAAAKUoAACcA
AAAjAAAAKUoAACgAAAAjAAAAKUoAAC0AAAAjAAAAKUoAACQAAAAjAAAABUwAACUA
AAAjAAAABUwAACYAAAAjAAAABUwAACcAAAAjAAAABUwAACgAAAAjAAAABUwAAC0A
AAAjAAAABUwAACkAAAAjAAAAn2kAAC4AAAAjAAAAn2kAAC0AAAAjAAAAYHAAACQA
AAAjAAAA43AAACUAAAAjAAAA43AAACgAAAAjAAAA43AAACkAAAAjAAAA43AAAC0A
AAAjAA==');
dbms_aw_exp.import_chunk100(1492, 'AADjcAAAJAAAACMAAABNcwAAJQAAACMAAABNcwAAJwAAACMAAABNcwAAKAAAACMA
AABNcwAAKQAAACMAAABNcwAALQAAACMAAABNcwAALQAAACMAAAB9dAAAJAAAACMA
AABBpwAAJQAAACMAAABBpwAALQAAACMAAABBpwAAJAAAACMAAADiwwAAJQAAACMA
AADiwwAAJwAAACMAAADiwwAAKAAAACMAAADiwwAALQAAACMAAACIEwAALQAAACMA
AADBcAAAJAAAACMAAAC7EgAAJQAAACMAAAC7EgAAKQAAACMAAAC7EgAAKQAAACMA
AAD6EgAALQAAACMAAAD6EgAAJAAAACMAAACNGgAAJQAAACMAAACNGgAAJwAAACMA
AACNGgAALQAAACMAAACNGgAALQAAACMAAABLKQAALQAAACMAAADvMAAALQAAACMA
AAAeQAAALQAAACMAAAD0QAAAJAAAACMAAAD1RgAAJQAAACMAAAD1RgAAJwAAACMA
AAD1RgAAKQAAACMAAAD1RgAALQAAACMAAAD1RgAAJAAAACMAAAB7RwAAJQAAACMA
AAB7RwAAKAAAACMAAAD2TQAALQAAACMAAAD2TQAAJAAAACMAAADnTgAAJQAAACMA
AADnTgAAJwAAACMAAADnTgAAKAAAACMAAADnTgAALQAAACMAAADnTgAALQAAACMA
AABTcAAAJwAAACMAAAD3qwAAKQAAACMAAAD3qwAAJwAAACMAAAA3FAAALQAAACMA
AACkSgAAJwAAACMAAAC8owAAKAAAACMAAAApKgAAKQAAACMAAAApKgAALQAAACMA
AAA5cQAAJAAAACMAAABMowAAJQAAACMAAABMowAAJAAAACMAAABeowAAJQAAACMA
AABeowAALQAAACMAAABeowAALQAAACMAAAAaEAAAJAAAACMAAADORAAAJQAAACMA
AADORAAALQAAACMAAADORAAAJAAAACMAAACQaQAAJQAAACMAAACQaQAALQAAACMA
AACQaQAALQAAACMAAACNaQAAJAAAACMAAABtowAAJQAAACMAAABtowAALQAAACMA
AABtTwAALQAAACMAAACYnAAALQAAACMAAADrogAAJAAAACMAAABPowAAJQAAACMA
AABPowAALQAAACMAAABhowAAJAAAACMAAABSpAAAJQAAACMAAABSpAAALQAAACMA
AABSpAAALQAAACMAAACPKgAAJAAAACMAAABRCgAAJQAAACMAAABRCgAAJwAAACMA
AABRCgAAKAAAACMAAABRCgAAKQAAACMAAABRCgAALQAAACMAAABRCgAALQAAACMA
AADuFgAAJAAAACMAAAB4MQAAJQAAACMAAAB4MQAAJwAAACMAAAB4MQAAKQAAACMA
AAB4MQAALQAAACMAAAB4MQAALgAAACMAAABiTgAAJAAAACMAAAALTwAAJQAAACMA
AAALTwAALQAAACMAAAALTwAAKAAAACMAAABwRgAALQAAACMAAAB8SQAALQAAACMA
AADGGAAAJwAAACMAAABmQwAAKAAAACMAAABmQwAALQAAACMAAABmQwAAJAAAACMA
AABvUAAAJQAAACMAAABvUAAAKAAAACMAAABvUAAAKQAAACMAAABvUAAALQAAACMA
AABvUAAALQAAACMAAABlSQAAJAAAACMAAADWDQAAJQAAACMAAADWDQAAJwAAACMA
AADWDQAAKAAAACMAAADWDQAALQAAACMAAADWDQAAJAAAACMAAABaEQAAJQAAACMA
AABaEQAAJwAAACMAAABaEQAAKAAAACMAAABaEQAAKQAAACMAAABaEQAALQAAACMA
AABaEQAAJAAAACMAAACVFQAAJQAAACMAAACVFQAALQAAACMAAACVFQAAJAAAACMA
AAAzGAAAJQAAACMAAAAzGAAALQAAACMAAAAzGAAAJAAAACMAAADOHQAAJQAAACMA
AADOHQ==');
.
.
.

整個10046的TRACE檔案都充斥著dbms_aw_exp.import_chunk100過程的呼叫。這個過程的目的是為了exp/imp過程可以跨平臺，跨32/64位環境而對於OLAP資料進行的特殊處理。這個過程的效率非常低，也就造成了IMP匯入的效率低下。

其實Oracle對於包含OLAP資料的使用者遷移提供了專門的工具OLAP DML IMPORT/EXPORT，使用這個工具處理OLAP元件的資料是十分高效的。而傳統的EXP/IMP在處理使用者資料時沒有問題，但是當需要匯入OLAP後設資料時，就會導致異常低效的問題，而如果IMP匯入已經開始，那麼也沒有什麼太好的辦法，只好等它自己結束了。