Unlogged Deletes in Oracle
Peter Nolan
I am coming to grips with what 'nologging' means in Oracle.
I have a table with only 25M rows but lots of columns and lots and
lots of indexes on it. I'm trying to get rid of about 8M rows but the
delete is writing about 3GB per 500K rows deleted to the undo
tablespace. We have set the undo retention parameter to 60 seconds and
bounced the database but the undots stays populated with undo data
unless we wait for what seems like overnight. (It's empty in the
morning when we come in...)
I don't want to recreate the table as we have people testing and it
would take 24 hours to rebuild...
Q1. Can anyone tell me the best way to perform minimal logging on
deletes? (I have another client where we know we will want to do a lot
of updates and we may want to do these as unlogged deletes and a
load.)
Q2. And how can we clear out the undots after each set of deletes?
We are on AIX 5.1 and Oracle 9.2 64 bit version.
Thanks
Peter Nolan
Howard J. Rogers
> Hi All,
> I am coming to grips with what 'nologging' means in Oracle.
Practically nothing as far as regular DML on regular tables is
concerned. It applies to certain special data load operations (an insert
fone with an APPEND hint, for example; or a direct path SQL Loader
load). It applies to some sorts of partition DDL. And it applies to
index creation operations. But regular DML on regular tables *always*
generates redo, whatever NOLOGGING happens to be set to.
> I have a table with only 25M rows but lots of columns and lots and
> lots of indexes on it. I'm trying to get rid of about 8M rows but the
> delete is writing about 3GB per 500K rows deleted to the undo
> tablespace. We have set the undo retention parameter to 60 seconds and
> bounced the database but the undots stays populated with undo data
> unless we wait for what seems like overnight. (It's empty in the
> morning when we come in...)
>
> I don't want to recreate the table as we have people testing and it
> would take 24 hours to rebuild...
>
> Q1. Can anyone tell me the best way to perform minimal logging on
> deletes? (I have another client where we know we will want to do a lot
> of updates and we may want to do these as unlogged deletes and a
> load.)
If you want to invalidate your support contract, and are prepared to
lose your entire database, there are ways not to generate redo, but I
presume those are not really options. Therefore, redo generation by
these deletes is something you will just have to provision for. Because
deletes, as a regular piece of DML, always generates redo.
That said, you could, of course, drop all those unnecessary indexes
first. A drop segment command generates very little redo. And if the
indexes aren't there at the time you start deleting from the table, then
they can't generate redo as a result of the delete. Of course, once the
table DML is finished, you will have to re-create all the indexes
afterwards (here's a tip: find out which indexes you can actually live
without, and don't rebuild those ones!)... though as I mentioned, for
index creation, a nologging attribute on the index itself *would* be
respected (create index blah nologging on X(col)).
Don't forget that best practice after any nologging operation that
actually does indeed generate no redo is to perform a new backup. (IE,
you never actually win with Oracle: the time and effort you save on the
DML side of things is usually expended on the index creation/extra
backup side of things).
> Q2. And how can we clear out the undots after each set of deletes?
Why would you want to? Undo recycles itself, provided that the
transaction that generated it no longer needs it. If you keep
committing, then you mark the undo generated by the deletes up to that
point as over-writable (after undo_retention seconds, true enough). If
you are currently trying to delete all 25M rows in one transaction, then
you might want to re-think things somewhat.
What is the actual problem you are having with undo generation?
Regards
HJR
Scott Swank
Another suprisingly efficient option is to
CREATE TABLE new_table NOLOGGING AS
SELECT *
FROM old_table
WHERE good_data;
CREATE INDEX as_needed NOLOGGING;
DROP TABLE old_table;
ALTER TABLE new_table RENAME old_table;
I'm sure that I mucked up a bit of the above syntax (rename, perhaps)
because I'm typing it in from memory without a manual at hand, but
none the less you get the idea. It does entail creating your indices
from scratch and, as mentioned above, all of the NOLOGGING of course
merits a cold backup.
Cheers,
Scott
Mark Bole
> "Howard J. Rogers" <h...@dizwell.com> wrote in message news:<41ac4dee$0$8929$afc3...@news.optusnet.com.au>...
>
>>Peter Nolan wrote:
>>
>>>Hi All,
>>>I am coming to grips with what 'nologging' means in Oracle.
>>
[...]
>>
>>Don't forget that best practice after any nologging operation that
>>actually does indeed generate no redo is to perform a new backup. (IE,
>>you never actually win with Oracle: the time and effort you save on the
>>DML side of things is usually expended on the index creation/extra
>>backup side of things).
>
>
> Another suprisingly efficient option is to
>
> CREATE TABLE new_table NOLOGGING AS
> SELECT *
> FROM old_table
> WHERE good_data;
>
[...]
Beware the existence of a Data Guard standby database and FORCE LOGGING
as well, if it applies in your situation.
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10739/tspaces.htm#sthref1160
-Mark Bole
Peter Nolan
"Howard J. Rogers" <h...@dizwell.com> wrote in message news:<41ac4dee$0$8929$afc3...@news.optusnet.com.au>...
> Peter Nolan wrote:
> > Hi All,
> > I am coming to grips with what 'nologging' means in Oracle.
> And it applies to
> index creation operations. But regular DML on regular tables *always*
> generates redo, whatever NOLOGGING happens to be set to.
>>> Yes, I found out a while ago that 'Nologging' seems to mean 'not
as much logging as it usually does...'
>
> >
> > Q1. Can anyone tell me the best way to perform minimal logging on
> > deletes? (I have another client where we know we will want to do a lot
> > of updates and we may want to do these as unlogged deletes and a
> > load.)
>
> If you want to invalidate your support contract, and are prepared to
> lose your entire database, there are ways not to generate redo, but I
> presume those are not really options. Therefore, redo generation by
> these deletes is something you will just have to provision for. Because
> deletes, as a regular piece of DML, always generates redo.
>>> While we are testing, not a problem... ;-) Hopefully in production
we won't be deleting anything....I'm just very surprised at the volume
of undo data and the very slow speed of these deletes. We are on a 5
processor AIX box and it can take 30 minutes or more to delete 1M
rows..... (Reminds me of the SQL Server 7 logging problems....)
>
> Of course, once the
> table DML is finished, you will have to re-create all the indexes
> afterwards (here's a tip: find out which indexes you can actually live
> without, and don't rebuild those ones!)... though as I mentioned, for
> index creation, a nologging attribute on the index itself *would* be
> respected (create index blah nologging on X(col)).
>
>>> Well, I did drop all the indexes on the weekend, but had not
decided to delete these rows at the time...The index rebuild was
amazingly slow at 6 hours to rebuild the 30+ indexes. All indexes were
rebuilt with NOLOGGING. We are going through the process of
partitioning this specific table but I would have thought at only 25M
rows the extra work of partitioning would not be needed....it's a
small table. We are still to go through our 'rationalise the indexes'
task....we have just been creating indexes as reports need them and
not paying too much attention to 'similar' indexes. In the end we will
have this huge slab of SQL in reports and we can run performance
testing across them.
>
> > Q2. And how can we clear out the undots after each set of deletes?
>
> Why would you want to? Undo recycles itself, provided that the
> transaction that generated it no longer needs it. If you keep
> committing, then you mark the undo generated by the deletes up to that
> point as over-writable (after undo_retention seconds, true enough). If
> you are currently trying to delete all 25M rows in one transaction, then
> you might want to re-think things somewhat.
>
>>>Nope, we are only trying to take 8M rows and put them into another
table...we did the select insert to copy the rows to the other table,
and we are deleting the 8M because we don't need them in the old table
any more. We have built this data mart so that any 'segment' of this
larger table can be relocated to a single table with no changes to any
ETL/reports......and it all works fine, except when we tried to delete
the old data....
> What is the actual problem you are having with undo generation?
>> Just the massive amount of processor and disk used to delete rows.
When doing DWing, in many cases you know that you can delete a bunch
of rows because you are going to re-load them. I seem to recall Oracle
have 'touted' that this problem was solved in 9i with 'nologging',
which I thought meant 'no logging' as an option you can turn on (silly
me ;-)). But this does not seem to be the case. It still seems like
when we need to replace a large number of rows, it will be faster to
truncate the table and reload the partition than to delete the rows to
be updated with no-logging and reload just those rows....Seems kind of
strange that all deletes should be logged like this and no option
being available to turn it off...
Howard J. Rogers
> Seems kind of
> strange that all deletes should be logged like this and no option
> being available to turn it off...
Well, you can. As I said, there's a totally unsupported, potentially
catastrophic, extraordinary method of doing it. But you'd best have a
cold backup beforehand, and be prepared to restore it in its entirety if
anything at all goes wrong with your database whilst it's in use. If
you're interested and don't already know what I'm talking about, mail me.
But think about it logically. If you could ever (ordinarily) suppress
redo generation for deletes, and there was some sort of failure, how
would Oracle ever know which records in a table should have been deleted
and which not? We'll have to wait for dbms_mindreading before that can
be resolved!
Compare that, for example, with creating an index: if that ever goes
wrong, you can drop it and re-create it as often as you want, and the
end result will always be a functional index.
Redo suppression for things which cannot plausibly be re-created or
re-performed via any other mechanism must inevitably result in an
unrecoverable database. So that's why you have no option to turn it off.
Regards
HJR
pe...@peternolan.com
"But think about it logically. If you could ever (ordinarily) suppress
redo generation for deletes, and there was some sort of failure, how
would Oracle ever know which records in a table should have been
deleted
and which not? We'll have to wait for dbms_mindreading before that can
be resolved!"
I will need to check, because it has been so long, but I am pretty sure
I was doing exactly this with DB2/MVS in the early 90s.
Thinking about it logically. Say I am a DBA (I'm not any more). I need
to update 60M rows in a partition of 500M rows. I know, by the nature
of the data, for sure and for certain, that every row coming to me is
already in the partition. I can take a 'low priority' backup in the
background during the day with minimal impact on the users. Then, when
it comes time to apply the 60M updates what can I do? If the database
supports unlogged updates (which only Sybase IQ does as far as I am
aware) then I can just apply the 60M updates no problems. Then I back
up the partition again...if the update falls over, no problem, I have a
backup and I restore the whole partition, then I start the update
again. It will fail rarely enough not to be a problem one would
hope!!!!
If the database does not support unlogged updates I cannot just apply
60M updates. The logging will be too much. I need to find a way to
apply the updates so that no logging takes place. One way to do this is
to perform unlogged deletes and then a load of the full updated record.
Like I said, I seem to remember doing this 10 years ago on MVS, but I
could be wrong....
Certainly, having been a vendor, I take your point that we should not
use 'undocumented features' that may well break such a large partition.
Which was why I was looking for a 'supported' solution in Oracle 9.2...
:-)
pe...@peternolan.com
"But think about it logically. If you could ever (ordinarily) suppress
redo generation for deletes, and there was some sort of failure, how
would Oracle ever know which records in a table should have been
deleted
and which not? We'll have to wait for dbms_mindreading before that can
be resolved!"
I will need to check, because it has been so long, but I am pretty sure
Howard J. Rogers
> Hi Howard,
>
> "But think about it logically. If you could ever (ordinarily) suppress
> redo generation for deletes, and there was some sort of failure, how
> would Oracle ever know which records in a table should have been
> deleted
> and which not? We'll have to wait for dbms_mindreading before that can
> be resolved!"
>
> I will need to check, because it has been so long, but I am pretty sure
> I was doing exactly this with DB2/MVS in the early 90s.
>
> Thinking about it logically. Say I am a DBA (I'm not any more). I need
> to update 60M rows in a partition of 500M rows. I know, by the nature
> of the data, for sure and for certain, that every row coming to me is
> already in the partition. I can take a 'low priority' backup in the
> background during the day with minimal impact on the users. Then, when
> it comes time to apply the 60M updates what can I do? If the database
> supports unlogged updates (which only Sybase IQ does as far as I am
> aware) then I can just apply the 60M updates no problems. Then I back
> up the partition again...if the update falls over, no problem, I have a
> backup and I restore the whole partition, then I start the update
> again. It will fail rarely enough not to be a problem one would
> hope!!!!
What you are actually describing is an update process that says 'to hell
with recoverability! If it goes wrong, we just start over (ie, restore
the backup).' So you've answered my question quite nicely with the right
answer: "It can't (know which records should have been [updated] and
which not...". And we resolve the ensuing dilemma by brute force.
Well, that is the option I was describing that is still available in
Oracle, and which I've actually helped implement in a production
setting, just once. Backup before a bulk operation, switch off all redo,
perform bulk operation, be prepared to restore the entire backup and
start from scratch if anything goes wrong. And remember to switch redo
generation back on afterwards! (Oh, and don't forget the fresh backup
afterwards, too).
It's entirely logical, but unfortunately, as I said, not supported.
Oh, and there's a slight catch: because you are about to switch off redo
generation, it isn't good enough to do a 'low impact' prior backup (ie,
presumably, a hot backup). It's a 'shutdown and do a cold, complete
backup' operation. Otherwise there's a risk that the hot backup wouldn't
be recoverable (i.e., usable).
> If the database does not support unlogged updates I cannot just apply
> 60M updates. The logging will be too much. I need to find a way to
> apply the updates so that no logging takes place. One way to do this is
> to perform unlogged deletes and then a load of the full updated record.
> Like I said, I seem to remember doing this 10 years ago on MVS, but I
> could be wrong....
As I say, you can do it today in Oracle.
Just don't tell anyone you're doing it, or don't expect any help from
official sources, if you do.
Regards
HJR
pe...@peternolan.com
as an old ex vendor 'not supported' means don't do it because the
vendor is not going to help.....so 'it can be done using unsupported
and undocumented work arounds' to me means the same as 'it cannot be
done in the product'.
An old friend of mine pointed out unlogged operations for everything
have been around for years and years on DB2 for unix, and they are
supported by the vendor. Even updates now....seems I wasn't
dreaming......
And yes, performing unlogged operations means the DBA had better
understand what he/she is doing because doing it wrong can kill the
database and a crash may leave the partition and perhaps the whole
database in an unusable state. But DBAs are supposed to be well
trained and smart people. If they don't know what they are doing, they
should not be doing DBA work.
Best Regards
Peter
Howard J. Rogers
> Hi Howard,
> as an old ex vendor 'not supported' means don't do it because the
> vendor is not going to help.....so 'it can be done using unsupported
> and undocumented work arounds' to me means the same as 'it cannot be
> done in the product'.
Well, we differ then. It *can* be done in the product. It's done in (at
least) one Australian company, in production, every Friday night. They
still have a support contract. But they don't use it when something goes
wrong with their bulk load. They simply restore from their cold backup.
That you do something knowing the consequences, and provisioning escape
routes that do not involve ringing the vendor, doesn't mean it cannot be
done. It simply means, you don't ring the vendor when it goes wrong.
If I have given you the impression that 'it's unsuppported' means
'never, ever ring Oracle again', I was wrong to have done so. It simply
means 'don't ever ring Oracle if this bulk load goes wrong, that's what
the backup is for'.
> An old friend of mine pointed out unlogged operations for everything
> have been around for years and years on DB2 for unix, and they are
> supported by the vendor. Even updates now....seems I wasn't
> dreaming......
No difference in Oracle.
> And yes, performing unlogged operations means the DBA had better
> understand what he/she is doing because doing it wrong can kill the
> database and a crash may leave the partition and perhaps the whole
> database in an unusable state.
No difference in Oracle.
> But DBAs are supposed to be well
> trained and smart people. If they don't know what they are doing, they
> should not be doing DBA work.
No difference in Oracle.
Regards
HJR
> Best Regards
> Peter
>