OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » No Redo for DML?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

No Redo for DML? [message #163549]

Fri, 17 March 2006 03:48

amitsingla
Messages: 4
Registered: March 2006

Junior Member

How can I disable redo generation for DML statements.
PLZ HELP!!!!!!

Report message to a moderator

Re: No Redo for DML? [message #163551 is a reply to message #163549]

Fri, 17 March 2006 04:01

aciolac
Messages: 242
Registered: February 2006

Senior Member

Did You want to stop archiving logs?

Report message to a moderator

Re: No Redo for DML? [message #163554 is a reply to message #163549]

Fri, 17 March 2006 04:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You cannot completely stop it. you can minimise it.
EDIT:
To be read as
You cannot completely stop redo generation. You can minimise it.

[Updated on: Mon, 20 March 2006 08:30]

Report message to a moderator

Re: No Redo for DML? [message #163555 is a reply to message #163554]

Fri, 17 March 2006 04:08

aciolac
Messages: 242
Registered: February 2006

Senior Member

One notification to word of Mahesh Rajendran : You cannot stop generating on redo information, but You cat stop archiving them.

Report message to a moderator

Re: No Redo for DML? [message #163563 is a reply to message #163555]

Fri, 17 March 2006 04:28

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

LOL, @aciolac if your going to 'pull someone up' about the wording of their text (especially where you have done little but to correct grammar, your post did little or nothing to clarify much of anything) at least get your spelling right Wink

Jim

Report message to a moderator

Re: No Redo for DML? [message #163589 is a reply to message #163549]

Fri, 17 March 2006 06:29

amitsingla
Messages: 4
Registered: March 2006

Junior Member

How can I minimize redo generation for DML statements.

Report message to a moderator

Re: No Redo for DML? [message #163593 is a reply to message #163589]

Fri, 17 March 2006 07:31

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

LOL JS12001~.
May be I should minimise my lackadaisical replies. My mistake (and it was 4 am by wee hours when i posted).
@aciolac
>>One notification to word of Mahesh Rajendran : You cannot stop generating on redo information, but You cat stop archiving them.
I never said, i can stop them~!. Redo log generation and Archiving are separate process and it seems that, OP is not concerned about archiving.

@OP
>> How can I minimize redo generation for DML statements.
Depends on what you do and how you are allowed to do. If your database is mounted in FORCE LOGGING mode, you generate a lot of redo.
Depending on your archive log mode you can effectively make use of Nologging mode or APPEND operations. Please search the board for NOLOGGING and APPEND.
You will get more information. In any case, you cannot COMPLETELY avoid redo generation ( in other words, some redo is always generated)
like in following cases

1. The concerned table itself is not the only source of redo. The recursive changes in data dictionary will also generate redo.

2. All undo operations generate redo. consider a multiuser environment. USER_A is doing some DML on table_A
and there is another session for the USER_A trying to query/do something from the same table_A. In order give a consistent data,
Oracle will rebuild the before image using the undo information and will present it to session_2. This process will generate redo.

http://www.orafaq.com/forum/m/123796/42800/#msg_123796

3. Redo entries are change vectors which are generated by database in all DML and in some cases even as a result of Select statement (because of sorts or delayed block cleanout).

SQL> set autotrace traceonly stat 
SQL> select count(*) from emp; 
 
 
Statistics 
---------------------------------------------------------- 
         36  recursive calls 
          1  db block gets 
       8637  consistent gets 
          0  physical reads 
    [B] 222440  redo size[/B] 
        517  bytes sent via SQL*Net to client 
        469  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          1  rows processed

Report message to a moderator

Re: No Redo for DML? [message #163635 is a reply to message #163593]

Fri, 17 March 2006 10:01

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi

Mahesh Sir i was trying to run this

 set autotrace traceonly stat
      select count(*) from emp;

i got this error, what am i missing here

Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report

1 row selected.

SP2-0612: Error generating AUTOTRACE report
SP2-0612: Error generating AUTOTRACE STATISTICS report

Version is Oracle9i Enterprise Edition Release 9.2.0.5.0.

Thanks

Report message to a moderator

Re: No Redo for DML? [message #163636 is a reply to message #163635]

Fri, 17 March 2006 10:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

did you already create the plan_table by running utlxplan.sql?
Please search the board for UTLXPLAN or PLAN_TABLE

Report message to a moderator

Re: No Redo for DML? [message #163790 is a reply to message #163593]

Mon, 20 March 2006 00:35

aciolac
Messages: 242
Registered: February 2006

Senior Member

About comments of Mahesh Rajendran on my words:the initial question was How can I disable redo generation for DML statements. I think that I am responded very correct - impossible to stop redo generation. Not so? Please see much attent all thread.

Report message to a moderator

Re: No Redo for DML? [message #163823 is a reply to message #163790]

Mon, 20 March 2006 04:17

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Excellent! I give up.

[Updated on: Mon, 20 March 2006 04:33]

Report message to a moderator

Re: No Redo for DML? [message #471757 is a reply to message #163549]

Tue, 17 August 2010 03:23

ahmedfahmy80
Messages: 5
Registered: August 2010
Location: Egypt

Junior Member

Hi,

Simply you can disable log generation by setting the following parameter to TRUE.

_disable_logging=TRUE

But it is not recommended to play with hidden parameters as it is not supported by Oracle Metalink.

Report message to a moderator

Re: No Redo for DML? [message #471759 is a reply to message #471757]

Tue, 17 August 2010 03:36

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So why do you stupidly post this parameter that breaks the transactional rules (and your database as well)?

Regards
Michel

[Updated on: Tue, 17 August 2010 03:37]

Report message to a moderator

Re: No Redo for DML? [message #471806 is a reply to message #163549]

Tue, 17 August 2010 06:59

ahmedfahmy80
Messages: 5
Registered: August 2010
Location: Egypt

Junior Member

if you think a little bit in my reply, you will find that this parameter can be used while DB up & running. so you can use it for a specific job and once you finish you can return it back to its value.

Best Scenatio to use such parameter in import operations using impdp.

and please be polit in your reply done say such words "stupid" because you dont know who am i and what's my experience to even reply me.

Report message to a moderator

Re: No Redo for DML? [message #471807 is a reply to message #471806]

Tue, 17 August 2010 07:13

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Quote:

Simply you can disable log generation by setting the following parameter to TRUE.
_disable_logging=TRUE

Apart from being a very dangerous suggestion, this argument is still not totally true.
Even with the parameter set, redo information will still be generated (written to buffer) and only disk writes will not occur.

[Updated on: Tue, 17 August 2010 07:14]

Report message to a moderator

Re: No Redo for DML? [message #471808 is a reply to message #471807]

Tue, 17 August 2010 07:16

ahmedfahmy80
Messages: 5
Registered: August 2010
Location: Egypt

Junior Member

to bypass buffer, simply you can use a hint "nocache"

Report message to a moderator

Re: No Redo for DML? [message #471810 is a reply to message #471808]

Tue, 17 August 2010 07:25

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I fail to understand the relation here.
NOCACHE is normal behavior of buffer cache.

Report message to a moderator

Re: No Redo for DML? [message #471811 is a reply to message #471807]

Tue, 17 August 2010 07:33

ahmedfahmy80
Messages: 5
Registered: August 2010
Location: Egypt

Junior Member

You can test what i'm saying by doing the following query....

select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

if any change in size has been detected then there is an entry has been logged into redo buffer. otherwise you successfully bypass the redo buffer. Do it and let me know what do u think.

Report message to a moderator

Re: No Redo for DML? [message #471812 is a reply to message #471810]

Tue, 17 August 2010 07:36

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

I can't keep quiet about this - the subject matter is so dangerous that it must be dealt with.
If _disable_logging is set, then no redo is written to the online logs. This means that if the instance crashes, it is not posssible for SMON to recover it and open the database: you must perform an incomplete recovery, up to the point at which you disabled logging. Some people may say "don't worry: set _allow_resetlogs_corruption, and then you can open your database." This is even worse, because if the crash occured during a data dictionary update, you will now have a corrupted data dictionary as well as corrupted user data.
I do not believe that any responsible DBA would ever dream of using these parameters.
Over and out.

Report message to a moderator

Re: No Redo for DML? [message #471813 is a reply to message #471812]

Tue, 17 August 2010 08:03

amitsingla
Messages: 4
Registered: March 2006

Junior Member

you guys just opened my march 2006 post,,,even I had forgot abt this Wink

Report message to a moderator

Re: No Redo for DML? [message #471828 is a reply to message #471813]

Tue, 17 August 2010 09:44

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

and please be polit in your reply done say such words "stupid" because you dont know who am i and what's my experience to even reply me.

I don't have to know you to say this is a stupid suggestion, anyone makes it, it is stupid (note I didn't say YOU are stupid, I said the suggestion is... silly in a forum where newbies take all what is posted without having the experience to understand the consequences).

Regards
Michel

Report message to a moderator

Re: No Redo for DML? [message #472478 is a reply to message #471812]

Fri, 20 August 2010 22:33

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

John Watson wrote on Tue, 17 August 2010 19:36

I can't keep quiet about this - the subject matter is so dangerous that it must be dealt with.
If _disable_logging is set, then no redo is written to the online logs. This means that if the instance crashes, it is not posssible for SMON to recover it and open the database: you must perform an incomplete recovery, up to the point at which you disabled logging. Some people may say "don't worry: set _allow_resetlogs_corruption, and then you can open your database." This is even worse, because if the crash occured during a data dictionary update, you will now have a corrupted data dictionary as well as corrupted user data.
I do not believe that any responsible DBA would ever dream of using these parameters.
Over and out.

Well, this is the best answer in this topic to ahmedfahmy80.

Report message to a moderator

Re: No Redo for DML? [message #472557 is a reply to message #163549]

Sun, 22 August 2010 02:27

ahmedfahmy80
Messages: 5
Registered: August 2010
Location: Egypt

Junior Member

Thx for your reply. However i'm the DBA that i'm using this point that it is because we have some testing regions not using archive mode. In addition to i'm using that parameter in certain circumstances, such as import and export as we are using the import process as one of our processes. So i dont care about recover something i dont need it in real life with those testing regions. I hope everyone now understand what i meant and know that in some situations we can use it under certain conditions...

Regards,
Ahmed Fahmy

Report message to a moderator

Re: No Redo for DML? [message #472559 is a reply to message #472557]

Sun, 22 August 2010 02:41

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

So i dont care about recover something i dont need it in real life with those testing regions.

And you are ready to restore your database if you screw the dictionary and lose all modifications since then.
I advice you to take a full backup before doing such thing and do it in restricted mode (without other user) then you will lose nothing but (down)time.

Regards
Michel

[Updated on: Sun, 22 August 2010 02:41]

Report message to a moderator

Re: No Redo for DML? [message #472598 is a reply to message #472557]

Sun, 22 August 2010 23:02

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

ahmedfahmy80 wrote on Sun, 22 August 2010 14:27

Thx for your reply. However i'm the DBA that i'm using this point that it is because we have some testing regions not using archive mode. In addition to i'm using that parameter in certain circumstances, such as import and export as we are using the import process as one of our processes. So i dont care about recover something i dont need it in real life with those testing regions. I hope everyone now understand what i meant and know that in some situations we can use it under certain conditions...

Regards,
Ahmed Fahmy

Well, I think, you're not only the DBA at here.
Your system is not our system, your situation is not our situation, your point, working, ..etc.. are not our at all. You can use the hidden parameter, you can issue some plan, however, you can not or at least, you may not use this in production db, also your system.

OK, you disable this parameter, would you like to answer me some questions:

1- If DB crash, how do you restore?
1.1. You can use dumpfile to restore, but, how do you rebuild internal code? For example: Your app connect to DB by using jdbc, your app was deployed by java, but, in some package, one function was used to declare some way to connect the other db by db_link, submit the variable by using literal string (of course, this is not well of coding, but, some coders often do not pay attention to this), how do you restore this to original?
1.2. The SYS user is not always to use, except maintenance Database, however, some java classes in SYS has revoked by schema user's java other class (or call them), example: xxxx_Code_Base_ .. If some coders in your system submit some thing in this _Code_Base of Sys, such as example, how do you restore internal code?

2- If your boss suggest limit time to restore/rebuild DB, an example, 2 hours to restore 1TB, and you must open all of system after 2 hours, how do you do?

Regard!

Report message to a moderator

Re: No Redo for DML? [message #473004 is a reply to message #472598]

Wed, 25 August 2010 06:15

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Why in the name of all that's holy would you use a test DB configured differently from production? Unless that's the sole test, but you're using blatantly dangerous commands which would (or at least should) never be allowed into a real environment. What would that prove? What validity would the testing have? Why worry about removing redo if its going to have to happen in prod anyway? Not using archive log is no reason to remove redo!

I'm not saying its never valid to use these (never say never and they're obviously there for a reason) but your description sounds like you probably shouldnt be doing it.

[Updated on: Wed, 25 August 2010 06:18]

Report message to a moderator

Previous Topic:	materalized view
Next Topic:	Get No of rows Processed while Update statment is still running

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 08:31:09 CST 2024