Oracle-L: buffer_size parameter for import utility

From: BERTHOLD Jean <Jean.BERTHOLD_at_eosholding.ch>
Date: Tue, 26 Aug 2003 00:09:27 -0800
Message-ID: <F001.005CD26B.20030826000927@fatcity.com>

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). Received on Tue Aug 26 2003 - 03:09:27 CDT