| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: undo tablespace
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:breno_at_mercantilsoares.com.br
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> Oracle 9i / NT
HMNI> Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Breno A. K. Magnago
INET: breno_at_mercantilsoares.com.br
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: 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).
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: 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.net
--
Author: Cary Millsap
INET: cary.millsap_at_hotsos.com
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: 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). Received on Wed Jan 29 2003 - 10:09:24 CST
 |
 |