RE: IMPORT sloooowwww
Bingo. It was the grants, as I had tried w/o indexes/constraints. There were some 1800+ grants per table. With grants set to no, an import which creates constraints and indexes is flying. It has been so long since I worked with a product where each user is given direct grants rather than thru a role, that I forgot that it was possible to have that many grants on a single table Thanks again. -Original Message- Sent: Thursday, August 29, 2002 12:39 PM To: Multiple recipients of list ORACLE-L and indexes, too! do the indexes, constraints, grants, etc after the table data imports! -bill -Original Message- Sent: Thursday, August 29, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Creating grants and synonyms on each table? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Magaliff, Bill 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: Ball, Terry 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: IMPORT sloooowwww
Er. You can use roles in Oracle as well? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - It has been so long since I worked with a product where each user is given direct grants rather than thru a role, that I forgot that it was possible to have that many grants on a single table -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: IMPORT sloooowwww
Title: RE: IMPORT sl i might add my 2c, even though the problem has been resolved. :-) once upon a time, we had a 2g database that was taking a looong time to import. anyone ever heard of remedy? did everything that has already been mentioned. tracing revealed log file sync waits ... and lots of em' ... (and a few other log type waits ...) we had long datatypes ... we were frustrated ... it's only 2 bloody gig after all ... got rid of the 2nd member of each log group -- byebye log file parallel write waits ... i then threw the remaining logs on /tmp. bingo. an import that was looking to take 24hrs suddenly flew. this was done in test a number of times before we did it on production. but we did put the logs on /tmp in prod. after the import we very quickly moved them off. :-) the real issue was a poor io subsystem, but oracle's handling of longs during import did not help. never followed up on the internals of it. a document on metalink purporting to shed light on issues w/importing longs was ... not available to me (or something like that). the moral here is, in _certain_ situations, odd little tricks like that can save the day. it ain't for everyone, but in our particular case, logs in memory allowed us to complete a nasty little rebuild (can you say 15 extents ...) for a very poorly configured db w/in the allocated time window. cheers, casey ... -Original Message- From: Ball, Terry [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Subject: IMPORT sl Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: IMPORT sloooowwww
Title: RE: IMPORT sl I like the way you found the root cause. If I may paraphrase, you asked the database how it was spending its time, and the answer guided the remainder of your performance improvement project. I contrast this method with the traditional approach, which is to try to guess at a root cause from the domain of literally thousands of possible root causes. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Casey Dyke Sent: Friday, August 30, 2002 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: IMPORT sl i might add my 2c, even though the problem has been resolved. :-) once upon a time, we had a 2g database that was taking a looong time to import. anyone ever heard of remedy? did everything that has already been mentioned. tracing revealed log file sync waits ... and lots of em' ... (and a few other log type waits ...) we had long datatypes ... we were frustrated ... it's only 2 bloody gig after all ... got rid of the 2nd member of each log group -- byebye log file parallel write waits ... i then threw the remaining logs on /tmp. bingo. an import that was looking to take 24hrs suddenly flew. this was done in test a number of times before we did it on production. but we did put the logs on /tmp in prod. after the import we very quickly moved them off. :-) the real issue was a poor io subsystem, but oracle's handling of longs during import did not help. never followed up on the internals of it. a document on metalink purporting to shed light on issues w/importing longs was ... not available to me (or something like that). the moral here is, in _certain_ situations, odd little tricks like that can save the day. it ain't for everyone, but in our particular case, logs in memory allowed us to complete a nasty little rebuild (can you say 15 extents ...) for a very poorly configured db w/in the allocated time window. cheers, casey ... -Original Message- From: Ball, Terry [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Subject: IMPORT sl Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: IMPORT sloooowwww
Interesting. Longs always caused a lot of problems. Not just with imports... Did you get any relevant info from sar itself? I mean, it appears your problem was one of I/O wait, did it show up in the OS monitoring tools? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - the real issue was a poor io subsystem, but oracle's handling of longs during import did not help. never followed up on the internals of it. a document on metalink purporting to shed light on issues w/importing longs was ... not available to me (or something like that). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: IMPORT sloooowwww
Creating grants and synonyms on each table? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: IMPORT sloooowwww
remove (not disable, REMOVE) constraints and drop indexes and recreate after the import. Change the buffer size on the import as well. Check to see if it is committing after every row. --- Ball, Terry [EMAIL PROTECTED] wrote: Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: IMPORT sloooowwww
Trace it. www.hotsos.com/dnloads/1/10046a Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark -Original Message- Terry Sent: Thursday, August 29, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Cary Millsap 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: IMPORT sloooowwww
I tried this at your suggestion (though it didn't look like it was analyzing as there were not status on the tables). This didn't help. I even tried turning off archiving and no joy. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -Original Message- Sent: Thursday, August 29, 2002 11:18 AM To: [EMAIL PROTECTED] Sounds strange and I can't give you any good reason but I do have one idea. Have you tried: analyze=n Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Thursday, August 29, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ball, Terry 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: IMPORT sloooowwww
Terry, The 3 minutes between table could be because the new database is analysing the tables you are importing. Ron ROR mª¿ªm [EMAIL PROTECTED] 08/29/02 11:23AM Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ron Rogers 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: IMPORT sloooowwww
How many redo log files you have and what are the sizes for these files? Chech the frequency of switch log and also check checkpoint frequency. It looks like the DB has a severe contention problem. What is the size of shared_pool and buffer_cache? Regards, Waleed -Original Message- Sent: Thursday, August 29, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Khedr, Waleed 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: IMPORT sloooowwww
This is a good guess. -Original Message- Sent: Thursday, August 29, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Terry, The 3 minutes between table could be because the new database is analysing the tables you are importing. Ron ROR mª¿ªm [EMAIL PROTECTED] 08/29/02 11:23AM Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ron Rogers 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: Khedr, Waleed 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: IMPORT sloooowwww
and indexes, too! do the indexes, constraints, grants, etc after the table data imports! -bill -Original Message- Sent: Thursday, August 29, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Creating grants and synonyms on each table? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Magaliff, Bill 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: IMPORT sloooowwww
Put the table in NOLOGGIN. -Message d'origine- De: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Date: jeudi 29 août 2002 18:58 À: Multiple recipients of list ORACLE-L Objet: Re: IMPORT sl remove (not disable, REMOVE) constraints and drop indexes and recreate after the import. Change the buffer size on the import as well. Check to see if it is committing after every row. --- Ball, Terry [EMAIL PROTECTED] wrote: Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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). __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Bernard, Gilbert 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: IMPORT sloooowwww
Here is a most excellent post from Kirti Deshpande, [EMAIL PROTECTED] earlier on some import / export principles that may help you. If you like it, pass on your thanks to him. PEACE. Mike Prepare scripts to build tables, indexes and constraints etc.. Prebuild the tables in the target database. I am assuming that a database is already created with all the tablespaces etc.. Improving Export performance: 1) Use direct=y. That will make the export process significantly faster. 2) Along with (1), setting 'recordlength' to multiples of db_block_size or to its max value (65535) will help 'squeeze out' some more performance gain. Although, this parameter is to be used when exporting/importing on different OS where it has different default values, I use it for added performance gain. You may want to give it a trial run to see if that would help. 3) I do not export indexes. Improving Import Performance: 1) Keep database in no-archive log mode, if it is not already so. 2) Remember to use ignore=y since tables are already present. 3) Use commit=y to control rollback segment usage. 4) Do not import indexes by setting indexes=n (just to be sure). 5) Set buffer= to a high value, 5-10 MB should work fine (there is no proportional gain performance in raising this value too high). 6) Set analyze=n to suppress automatic estimation of table statistics. Analyze tables using your procedures after indexes etc are built 7) For primary key constraint indexes, I keep the quota on the target tablespace to 0 to make it fail during import. (something I just find easier to remember). 8) Set log= to some log file name to capture all (good and bad) messages from the import process. 9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and possible) to speed up index build process. Also, consider TEMPORARY type temp tablespace with properly configured initial and next (multiples of sort_area_size) extents. Make sure temp tablespace has ample room, should index build processes perform disk sorts. Also, make sure quota is okay on tablespaces for primary key constraint indexes. 10) Run all the index build scripts. Use nologging attribute and consider building indexes in parallel, if resources are available to do so. 11) Enable all the constraints etc. 12) After all indexes are successfully built, make sure the sort parameters are adjusted back to what they should be for running the db normally. Spot check and make sure everything looks fine and okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener. 14) Run your own procedures to analyze tables and indexes. 15) Take a cold back up. 16) Startup mount and change to archive log (if required). Open the db for users. 17) Time to hit the door.. -Original Message- Sent: Thursday, August 29, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Johnson, Michael INET:
RE: IMPORT sloooowwww
To see if its a straight load issue, try export with indexes=n, constraints=n, everything else you can think of = n, so on import it will basically be a batch insert and nothing else. THen either trace the import, or take a look at v$session_event whilst the import is running and see where the delays are occurring. I'd hazard a guess at redo log problems causing long delays for checkpoints to complete, but a trace will reveal all hth connor --- Ball, Terry [EMAIL PROTECTED] wrote: I tried this at your suggestion (though it didn't look like it was analyzing as there were not status on the tables). This didn't help. I even tried turning off archiving and no joy. Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -Original Message- Sent: Thursday, August 29, 2002 11:18 AM To: [EMAIL PROTECTED] Sounds strange and I can't give you any good reason but I do have one idea. Have you tried: analyze=n Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Thursday, August 29, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.7 on Solaris 8 I am testing and upgrade from 7.3.4 to 8.1.7. Because the test server is solaris 8, and I can't find the patches for 7 anymore, I am trying to upgrade via an export and import. Besides, the DB is small - less than 2G. The export was done with compress=n. There are 200+ tables to be imported. It is taking 3+ minutes per table for the import, even on tables that have 0 rows. (Though it does take longer than that for the few tables with over 50K rows). I have tried: 1) Simple import. 2) Import with indexes=n 3) Import with table and constraints (but not indexes) pre-created and indexes=n ignore=y 4) All of the above with the buffer set to 8M (This actually slows the import down, taking 5+ minutes per table). 5) Increasing the sort_area_size for the DB to twice as large as it was and trying the above. In looking at how long it takes, the table imports in a second or less, but it takes 3 minutes + to start the import of the next table. I'm not sure what it is doing for 3 minutes after the table imports. Does anyone have any ideas how I can speed this up? 10 hours to import a 2G. DB is extreme and unacceptable. TIA. Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ball, Terry 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
