RE: parallel import

Hi Paul

thnq for responding.
It is oracle 8.1.7 on solaris box (2.7)

The database is small ( 16 Gig), and used for majorly querying. No massive inserts/updates for these databases.

The table was exported without parallel degree clause. THe whole data is in a single dmp file. It is a 700M file and normally taking 12-15 hrs to do imp.

I dropped my indexes and disabled triggers on that table.

(Had I enabled all these the import would be 2 days.... :-) )

I exported the table using DIRECT=Y for fast export. It took 1 hr 30 minutes.

Is it required to set PARALLEL_MIN_SERVERS and MAX_SERVERS in init for doing parallel executions.?

And can you post me the syntax for paralle exp / imp ?

srinivas

-----Original Message-----
Sent: Saturday, December 08, 2001 1:30 PM To: Multiple recipients of list ORACLE-L

"Tatireddy, Shrinivas (MED, Keane)" wrote:
>
> Hi lists,
>
> Is there a parallel import into table / schema . ?

no.

> (The table/tables have primary keys as well as indexes )
> (need to finish the import in less time, than traditional import )
> Thnx in advance

> srinivas

you did not specify a version of Oracle server. bad practice.

if the table was created as having a parallel degree, then the create statement in the dump file should likely reflect that - however - if the data is in a single dump file, the import will likely be limited by the read rate of the dump and by the fact that the data is being read, parsed and executed in a non-direct fashion. create a dump file for one table that has a parallel degree, import it with a show=Y and examine the DDL/DML.

Kirti posted tips for import/export that was quite good. I would suggest examining the list archives for postings by Kirti with "export" and "import".
Unfortunately, I don't have my Performance 101 book here at home - I think he covered this in there also.

Import is import.exe - the only other ways are to unload the data (by various methods) and reload via SQL Loader, or copy across a db_link (not recommended).

0. C:\> imp help=y

1. can you break up the export into multiple schemas? (owner=schema1) that is the easiest way to parallelize the import, where each schema gets it own tablespace with datafiles on independent storage volumes, reading from a dump file on independent storage volumes.
2. can you break the export up by table (owner=schema1, tables = (table_name1,...,table_namen) and execute those simultaneously?
3. don't have the indexes created by import - alter the DDL used to create them such that they are created with a parallel degree that you desire. This could be accomplished with the INDEXFILE parameter. Also create them as nologging.
4. only have a small number of large rollback segments online, as you can't specify a rollback segment to be used during import.
5. what are you wait events during import
   • redo and archived redo log?
   • rollback segment allocation?
   • checkpoints?
   • recursive sql?
6. are your extents properly sized? are you using locally managed tablespaces

check out AskTom at ttp://asktom.oracle.com e.g.: http://asktom.oracle.com/pls/ask/f?p=4950:8:317629::NO::F4950_P8_DISPLAY ID,F4950_P8_CRITERIA:1574966483719,%7Bimport%7D

hth,

Paul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Drake
  INET: paled_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: Tatireddy, Shrinivas (MED, Keane)
  INET: Shrinivas.Tatireddy_at_med.ge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).