I agree in principal with Dan's thesis, but I'll add something. Don't do things that generate unnecessary undo and redo, either.
One common example is SQL that updates a column to a value that it already contains. For example, we see things like setting a flag='y' without checking that the flag doesn't already have a 'y' value. Things like this provide zero business value, but they degrade operational performance and recovery operations, and they consume unnecessary space in your undo and redo. There are probably a million others... 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 26-28 London -----Original Message----- Sent: Wednesday, January 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Breno, 100mb of redo in 20 minutes is not all that high. I have seen far worse (as I am sure most on the list have as well). Why do you perceive this as a problem? Are you seeing poor performance or waits? Is the generation of redo (and archived logs) causing the system to halt due to the archive_dump_dest filling up? If so, the problem is not the procedure, but rather the lack of proper process to manage your archive logs. Even with NOLOGGING, UNDO must be generated for read consistency, rollback and recovery. You are not inserting data into the undo tablespace. The undo entries are generated for each operation (insert/update/delete). However, the undo entry for an insert is very small and thus will consume very little undo space and redo. Don't concern yourself with trying to stop the generation of UNDO. You will end up causing yourself more problems that you will ever try to solve. It is part of the Oracle kernel and not modifiable (at least in this release). Focus on performance and decide if 100mb in 20 minutes is really a problem. Dan Fink -----Original Message----- Sent: Wednesday, January 29, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Mike, I asked it because I have a problem. Any insert data in UNDO tablespace generate insert in REDO Files. Is is correct ? When I execute a high procedure, many inserts in UNDO tablespace ocurres, so many inserts in REDO´s are genereate. I want to avoid this REDO´s generation. My tables and indexes are in NOLOGGING, but I high value of REDO are generate (100 MB each 20 minutes). It is desnecessary. Oracle 9i / NT -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Wednesday, January 29, 2003, 10:29:15 AM, you wrote: HMNI> Breno, HMNI> There's no way to do this because it's the central pillar of Oracle's read HMNI> consistency mechanism. HMNI> It's possible to minimise or suppress redo but undo is out of your control. HMNI> regards, HMNI> Mike Hately HMNI> -----Original Message----- HMNI> Sent: 29 January 2003 11:39 HMNI> To: Multiple recipients of list ORACLE-L HMNI> I have a high procedure (many INSERT's and UPDATE´s). HMNI> This procedure generate insert's in UNDO TableSpace for rollback. HMNI> I want to know if exists any way for don´t generate insert´s in UNDO HMNI> Tablespace. HMNI> Oracle 9i / NT HMNI> Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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). -- 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).
