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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: parallel import
"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_DISPLAYID,F4950_P8_CRITERIA:1574966483719,%7Bimport%7D hth, Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: parallel import
Sorry no Parrellel Imports nly Parrlel Exports ... Thinking Logically this is no possible. Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 7456019 -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 08, 2001 6:25 PM To: Multiple recipients of list ORACLE-L Hi lists, Is there a parallel import into table / schema . ? (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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ganesh Raja INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
