Home » RDBMS Server » Server Administration » Disabling Redolog
Disabling Redolog [message #275467] Sat, 20 October 2007 09:53 Go to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hello,

Details:
Running Oracle 10G Rel. 2 on Redhat Linux EL 4.

I want to disable redolog. I'm not concerned about any corruption and recovery for this test database of mine.

Is this the right way:

sqlplus / as sysdba

sql> alter database no force logging;

Thank You.

Report message to a moderator

Re: Disabling Redolog [message #275468 is a reply to message #275467] Sat, 20 October 2007 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't. You are not concerned for your DATA recovery but maybe your are concerned for your DATABASE recovery. Oracle always protects itself.

But you can put in nologging mode SOME operations.

Regards
Michel

Report message to a moderator

Re: Disabling Redolog [message #275470 is a reply to message #275468] Sat, 20 October 2007 10:13 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
I did searching for those SOME operations like nologging on tables and inserts with append hint.

But does it work for delete statements.

Here is what I'm doing

CREATE TABLE TM_RPA_OIDS AS ( 
SELECT 
  OBJECTID
FROM 
  CACHE_0_10707
WHERE 
  LTRPA_GMTEFFECTIVEDATETIME > TO_DATE('01/01/2004', 'MM/DD/YYYY') AND
  LTRPA_GMTEFFECTIVEDATETIME < TO_DATE('06/30/2007', 'MM/DD/YYYY'));


DELETE from  CACHE_0_10707_2889 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);


Thats what i want to disable nologging for table "CACHE_0_10707_2889".

and
select count(*) from TM_RPA_OIDS = 35830 rows

It takes very long time.

Thank You.

Report message to a moderator

Re: Disabling Redolog [message #275472 is a reply to message #275467] Sat, 20 October 2007 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>select count(*) from TM_RPA_OIDS = 35830 rows
What is the world is this?
Besides REDO is NOT (directly) involved with SELECT; only DML.

Report message to a moderator

Re: Disabling Redolog [message #275473 is a reply to message #275472] Sat, 20 October 2007 10:19 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi anacedent,

I just gave the row count for that table TM_RPA_OIDS just for information. I'm deleting records which is DML.

DELETE from CACHE_0_10707_2889 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

Thank You

[Updated on: Sat, 20 October 2007 10:20]

Report message to a moderator

Re: Disabling Redolog [message #275474 is a reply to message #275467] Sat, 20 October 2007 10:25 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi anacedent,

I remember you
http://www.orafaq.com/forum/m/272812/113565

thanks for being around and helping the world Cool

[Updated on: Sat, 20 October 2007 10:26]

Report message to a moderator

Re: Disabling Redolog [message #275476 is a reply to message #275470] Sat, 20 October 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, you can't disable logging for delete.
Only CTAS, create index, insert select, direct loading... not delete or update.

Regards
Michel

Report message to a moderator

Re: Disabling Redolog [message #275477 is a reply to message #275476] Sat, 20 October 2007 10:48 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi Michel,

Thanks for the information. That will go into my newbie knowledgebase.

Anymore ideas how I can speed up the delete statement. I do not have partitioned tables for parallel DML.I have server class machine with AMD opteron dual cpu dual core, 8GB RAM and and SCSI in hardware RAID.

I thought disabling redolog can improve the speed.

I have to run a script with more delete statements one of which I posted above. The last time I ran it was running for more than 24 hours Embarassed and "SELECT * FROM V$SESS_IO WHERE SID = <delete_sid>;" was showing me the progess going on.

Thank You.

Report message to a moderator

Re: Disabling Redolog [message #275478 is a reply to message #275477] Sat, 20 October 2007 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many rows in CACHE_0_10707_2889?

Regards
Michel

Report message to a moderator

Re: Disabling Redolog [message #275479 is a reply to message #275478] Sat, 20 October 2007 11:06 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi Michel,

There are 1,09,57,683 rows in CACHE_0_10707_2889.

Thank You.

Report message to a moderator

Re: Disabling Redolog [message #275480 is a reply to message #275479] Sat, 20 October 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is CACHE_0_10707_2889.OBJECTID indexed?

Regards
Michel

Report message to a moderator

Re: Disabling Redolog [message #275481 is a reply to message #275467] Sat, 20 October 2007 11:19 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Yes Michel.

Check the attached screenshot of this table in toad

Thank You.
  • Attachment: index.jpg
    (Size: 95.20KB, Downloaded 567 times)

Report message to a moderator

Re: Disabling Redolog [message #275482 is a reply to message #275467] Sat, 20 October 2007 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
SELECT 
  OBJECTID
FROM 
  CACHE_0_10707
WHERE 
  LTRPA_GMTEFFECTIVEDATETIME > TO_DATE('01/01/2004', 'MM/DD/YYYY') AND
  LTRPA_GMTEFFECTIVEDATETIME < TO_DATE('06/30/2007', 'MM/DD/YYYY'));


DELETE from  CACHE_0_10707_2889 WHERE OBJECTID IN ( select OBJECTID from TM_RPA_OIDS);

I don't understand the code above.
What is the reationship between table CACHE_0_10707 & CACHE_0_10707_2889?

Do both tables, CACHE_0_10707_2889 & TM_RPA_OIDS have current statitics?

Report message to a moderator

Re: Disabling Redolog [message #275484 is a reply to message #275482] Sat, 20 October 2007 11:28 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi anacedent,

I have to load the object ids for the given period in to TM_RPA_OIDS which i create for temporary purpose and drop at the end of my script. There are more tables in my data purging sccript from which i have to delete records by reading objectids from TM_RPA_OIDS

Yes both tables are analyzed.

Thank You.

Report message to a moderator

Re: Disabling Redolog [message #275489 is a reply to message #275467] Sat, 20 October 2007 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It sounds to me (reading betwen the lines) that you are repeatedly "loading" TM_RPA_OIDS table. If so at a minimum it needs to be analyzed after every load.

IMO, TM_RPA_OIDS table is not needed at all.

DELETE from  CACHE_0_10707_2889 WHERE OBJECTID IN (
SELECT 
  OBJECTID
FROM 
  CACHE_0_10707
WHERE LTRPA_GMTEFFECTIVEDATETIME BETWEEN 
   TO_DATE('01/01/2004', 'MM/DD/YYYY') 
 AND
   TO_DATE('06/30/2007', 'MM/DD/YYYY'));

LTRPA_GMTEFFECTIVEDATETIME should have an index on this column.

[Updated on: Sat, 20 October 2007 12:23] by Moderator

Report message to a moderator

Re: Disabling Redolog [message #275490 is a reply to message #275467] Sat, 20 October 2007 12:27 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
hi anacedent,

I'm attaching my data purging script

And it ran for sweet 24 hours when i executed few days back Embarassed

Thank You.
  • Attachment: script.sql
    (Size: 4.70KB, Downloaded 1007 times)

[Updated on: Sat, 20 October 2007 12:31]

Report message to a moderator

Re: Disabling Redolog [message #275492 is a reply to message #275467] Sat, 20 October 2007 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;
I'd add the line above to the script to generate a trace file in order to determine where all the time is actually being spent.

Report message to a moderator

Re: Disabling Redolog [message #275502 is a reply to message #275492] Sat, 20 October 2007 22:12 Go to previous messageGo to next message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi anacedent,

I started the script execution before i saw this message as it was getting late in my part of the world. The script is still running for 9 hours now. If it takes too long i will stop it and add the lines that you mentioned.

On running ADDM in OEM it complained about redo configuration and the "wait(bottleneck)" graph was high. I had 4 groups each two members and default size = 51 MB. So I added two more redo log groups each containing two members of size 1500MB :-\. I know this is not the right way to arrange the redolog but I could see improvement :-\

Anyways I have to test it thoroughly to bring down the execution time before executing it on our production server.

I will let you know.

Thanks Anacedent and Michel for all the useful pointers.

Best Regards.

Report message to a moderator

Re: Disabling Redolog [message #275504 is a reply to message #275467] Sat, 20 October 2007 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
There is always next week.

Report message to a moderator

Re: Disabling Redolog [message #275704 is a reply to message #275504] Mon, 22 October 2007 08:00 Go to previous message
el33t
Messages: 29
Registered: October 2007
Junior Member
Hi anacedent,

The problem is with one delete query in my script which has to delete around 60 lakhs records from a table. This is where it takes long. I tried deleting 50,000 records from this table and it took 45-50 mins. So roughly I can estimate that it will take 3-4 days to finish purging 60lakhs records. Right now the problem is our database management strategy Embarassed

Regards.

Report message to a moderator

Previous Topic: Oracle 11g tables and installation file
Next Topic: Junk characters are displayed after upgrade from oracle 8i to 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Sep 18 18:11:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.