Re: why so many log switches?
This depends on the methods you are using to load the data. It does seem excessive even for a simple row at a time loop - which probably shouldn't use more that about 100MB according to the volumes and indexes you've quoted. But it could be the result of a direct path load that rebuilds indexes, or a clunky generated routine that does one insert and multiple updates to populate a row. Would you care to explain how the data gets into the table, and what proportion of the new rows are likely to have non-null values that would affect the indexes. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 30 January 2003 20:34 >I have a table with 40 million rows. Daily I load about 70,000 records into >it. Each record is 128 characters wide. The flat file the data comes in is >9 megs. My redo logs are 20 megs each and I have 3 groups of them. > >When I load the data, the alert log shows 29 log switches which generates a >lot of archives logs. Why am I getting so many log switches? I would think >that if the OS file is 9 megs and the redo logs are 20, I would at most get >1 log switch. > >Thanks, >Randy >-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [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: why so many log switches?
So each insert generates redo on each of six segments plus redo for the undo generated for each of the six segments. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Randy Sent: Thursday, January 30, 2003 2:56 PM To: Multiple recipients of list ORACLE-L I have 5 indexes on that table, 3 of them are concatenated indexes. -Original Message- Sent: Thursday, January 30, 2003 3:02 PM To: Multiple recipients of list ORACLE-L Subject:RE: why so many log switches? Those redo could be because of indexes? How many indexes do you have on your 40 million row table? -Original Message- Sent: Thursday, January 30, 2003 2:20 PM To: Multiple recipients of list ORACLE-L I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [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.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] 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: [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: why so many log switches?
Randy, This is a long shot... but.. do you run hot backup when the data gets loaded? - Kirti -Original Message- Sent: Thursday, January 30, 2003 2:56 PM To: Multiple recipients of list ORACLE-L I have 5 indexes on that table, 3 of them are concatenated indexes. -Original Message- Sent: Thursday, January 30, 2003 3:02 PM To: Multiple recipients of list ORACLE-L Subject:RE: why so many log switches? Those redo could be because of indexes? How many indexes do you have on your 40 million row table? -Original Message- Sent: Thursday, January 30, 2003 2:20 PM To: Multiple recipients of list ORACLE-L I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] 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: [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: why so many log switches?
These indexes contributed to your redo generation. -Original Message- Sent: Thursday, January 30, 2003 3:56 PM To: Multiple recipients of list ORACLE-L I have 5 indexes on that table, 3 of them are concatenated indexes. -Original Message- Sent: Thursday, January 30, 2003 3:02 PM To: Multiple recipients of list ORACLE-L Subject:RE: why so many log switches? Those redo could be because of indexes? How many indexes do you have on your 40 million row table? -Original Message- Sent: Thursday, January 30, 2003 2:20 PM To: Multiple recipients of list ORACLE-L I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [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.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [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: why so many log switches?
I have 5 indexes on that table, 3 of them are concatenated indexes. -Original Message- Sent: Thursday, January 30, 2003 3:02 PM To: Multiple recipients of list ORACLE-L Subject:RE: why so many log switches? Those redo could be because of indexes? How many indexes do you have on your 40 million row table? -Original Message- Sent: Thursday, January 30, 2003 2:20 PM To: Multiple recipients of list ORACLE-L I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [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.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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: why so many log switches?
Title: RE: why so many log switches? How many indexes do you have? Any triggers that perform DML on other tables? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Steiner, Randy [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Subject: why so many log switches? I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: why so many log switches?
Randy, You may have 9meg of data, but that does not translate into 9meg of redo. In addition to the insert into the table, you also insert into indexes (if existing), generate undo for each operation (1 entry for table, 1 for each index), space management operations (extent allocation, index leaf splits). Granted inserts are the operations that consume the least amount of undo space (but not by very much), but they still consume space for which redo entries must be written. Has it always been like this? Or has the amount of redo suddenly increased? Dan Fink -Original Message- Sent: Thursday, January 30, 2003 12:20 PM To: Multiple recipients of list ORACLE-L I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Fink, Dan INET: [EMAIL PROTECTED] 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: [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: why so many log switches?
Those redo could be because of indexes? How many indexes do you have on your 40 million row table? -Original Message- Sent: Thursday, January 30, 2003 2:20 PM To: Multiple recipients of list ORACLE-L I have a table with 40 million rows. Daily I load about 70,000 records into it. Each record is 128 characters wide. The flat file the data comes in is 9 megs. My redo logs are 20 megs each and I have 3 groups of them. When I load the data, the alert log shows 29 log switches which generates a lot of archives logs. Why am I getting so many log switches? I would think that if the OS file is 9 megs and the redo logs are 20, I would at most get 1 log switch. Thanks, Randy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steiner, Randy INET: [EMAIL PROTECTED] 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: [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.net -- Author: Richard Ji INET: [EMAIL PROTECTED] 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: [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).
