Re: Import time taking so long <<<===============================================================

In article <xmdA2.2991$_m5.689408_at_newsread1-mx.centuryinter.net>,   alu_at_dairynet.com (A Ustby) wrote:
> Another Oracle question/observation....
>
> We have an instance (v7.3.3) that has 1000+ tables in it (purchased
> application). A couple of the tables have 1.5 million rows in them. One of
> these tables has 14 indexes on it. On the same box we run both production and
> test instances. We have the Oracle parameters (memory, sort area, etc) set
> lower for the test instance. When we load production instance, it takes 17
> hours. When we load the same data into the test instance, it takes 41 hours.
> Both loads were done when the box was quiet.
>
> We are using the Oracle import utility. It appears the excess time is in the
> index build phase. Could this be affected (that much !) by the Oracle sort parm
> size ? Any other insight ?
>
> The thing takes so long, we haven't done too much experimenting.
>
> Thanks.......... Art
>

Yes, loading the indexes with the table is in my experience a lot slower than loading with indexes=n then re-building the indexes from index build scripts when large tables are involved. You can help the import by setting buffer= to a large number and making sure a large rollback segment is available. Since import does not let you specify use of a specific rollback segment I take all of them offline except the one I want the import to use. You can help the index builds by altering your temporary tablespace initial and next larger during the index rebuilds using the import or manual rebuilds. This will only work when other jobs can be kept of the system while the import is running.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Feb 22 1999 - 15:06:41 CST