Oracle-L: Re: buffer_size parameter for import utility

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 26 Aug 2003 01:14:27 -0800
Message-ID: <F001.005CD283.20030826011427@fatcity.com>

Hi!

Buffer parameter in imp sets the array size for doing inserts in imp's oracle session.

Just put it buffer to a big value such is 40000000 (40M) for example. Otherwise, if your buffer is small, then commits occur too frequently - after each array insert. You might want to set commit=n as well, to commit only at the end of each table, but it will require quite large rollback segment for big tables. I would also set recordlength to 65535 when importing.

If you want even more performance, look at disabling constraints and making indexes unusable on your tables if you already have imported your schema structure. Build indexes (nologging + possibly parallel with high sort_area_size and appropriate db_file_multiblock_read_count) and enable constraints after data import. You'd have to use skip_unusable_indexes parameter in imp for that.

If you want _even more_ performance, set hidden parameters _wait_for_sync and _disable_logging to appropriate values. But this is not recommended nor supported!

After that, start looking at your waits and see where can you tune them (you might want to have several db_writer_processes and db_block_lru_latches a bigger value than default 1 in 8i, even with async IO, since import is putting heavy load on your buffer cache). Unfortunately there is no such thing as direct import.

Are you sure you have 9-13 *billions* of rows in your tables, not millions? (if that's the case then forget about commit=n in imp)

Cheers,
Tanel.

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, August 26, 2003 11:09 AM

> Hello,
>
> I don't understand very well the buffer_size parameter and how to use it.
> with imp tool.
>
> If I read the help:
>
> BUFFER
>
> Default: operating system-dependent
>
> The integer specified for BUFFER is the size, in bytes, of the buffer
> through which data rows are transferred.
>
> BUFFER determines the number of rows in the array inserted by Import.
> The following formula gives an approximation of the buffer size that
> inserts a given array of rows:
>
> buffer_size * rows_in_array * maximum_row_size
>
>
> I have a database with 3 big tables (each containing between 9 and 13
billions rows).
> The others 60 tables contains about 5 and 20000 rows.
>
> The biggest table is:
>
> SQL> describe gesten3.grandeur_mesure;
> Nom NULL ? Type
> ----------------------------------------- -------------------------------

> ID_GRANDEUR NOT NULL NUMBER(15)
> DATE_AQUISITION NOT NULL DATE
> VALEUR FLOAT(126)
> VALIDITE NUMBER(1)
> ID_TYPE_ACQUISITION NUMBER(15)
> UTILISATION NUMBER(15)
> ID_COMPTEUR NUMBER(15)
> ID_TYPE_GRANDEUR NUMBER(15)
> ID_UNITE NUMBER(15)
> ID_HISTORISATION NOT NULL NUMBER(15)
>
> SQL>
>
> Small tables, of course are imported very rapidly, but this table take
> several hour to finish import.
> How to improve that ?
>
> This database is running 8.1.7.2 under Solaris 8.
>
> Thanks for your help and have a nice day.
>
> Jean Berthold
>
> __________________________________________
> Jean Berthold
> EOS - energie ouest suisse
> Chemin de Mornex 10 , CP 570
> CH-1001 Lausanne , Switzerland
> Tel. : +41 (0)21 341 24 58
> Fax : +41 (0)21 341 20 49
> E-Mail : jean.berthold_at_eosholding.ch
> ---------------------------------
> UNIX is user friendly.
> It's just selective about who its friends are.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: BERTHOLD Jean
> INET: Jean.BERTHOLD_at_eosholding.ch
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Aug 26 2003 - 04:14:27 CDT