Home » RDBMS Server » Performance Tuning » redo size (Oracle version 11.2.0.2.0, OS AIX basedSystems(64 bit))
redo size [message #557942] Mon, 18 June 2012 03:24 Go to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Hi all,

In one of our envirnoment i could see the redo size is high.Trying to understand why this is more can any one help me here.
Re: redo size [message #557945 is a reply to message #557942] Mon, 18 June 2012 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "high".

Regards
Michel

Re: redo size [message #557948 is a reply to message #557945] Mon, 18 June 2012 04:19 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Hi Michel,

I dunno if i correct saying it as high , i am actually looking out for the reasons of when can the redo size be high? In my AWR report for the duration of about 2 hrs and 45 mins. The redo size as below

Per Second Per Transction
redo size 33,406.1 3,232.9

Please correct me if am wrong any where

Regards
Ashish
Re: redo size [message #557952 is a reply to message #557948] Mon, 18 June 2012 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean the generation of redo is quite big? For me "redo size" means "redo file size".

Redo size generated depends ONLY on what you ask the database to do.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: redo size [message #557955 is a reply to message #557952] Mon, 18 June 2012 04:37 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
yes i mean to redo is quite big ....i would like to know what are reasons for these to go high.
Re: redo size [message #557957 is a reply to message #557955] Mon, 18 June 2012 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not so high in my opinion.
Anyway, it is at the exact level it has to be for the work you asks Oracle to do.

What is the concern about this number?

Regards
Michel

[Updated on: Mon, 18 June 2012 04:44]

Report message to a moderator

Re: redo size [message #557958 is a reply to message #557957] Mon, 18 June 2012 04:45 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
can you please tel me how can say this numbr as not high , on what reasons you tell us?
Re: redo size [message #557959 is a reply to message #557958] Mon, 18 June 2012 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You: can you please tel me how can say this numbr as high , on what reasons you tell us?

Regards
Michel
Re: redo size [message #557965 is a reply to message #557958] Mon, 18 June 2012 06:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Ashish, there is no such thing as "high" or "low". Only "higher" or "lower". To put it another way, in performance tuning, the absolute value is meaningless: all that matters is change. You need to compare this figure to the figure from a week, a month, a year ago. Has it changed? If so, perhaps you need to investigate why.
That having been said, 33K per second is trivial.
Re: redo size [message #558020 is a reply to message #557965] Mon, 18 June 2012 22:37 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Thanks John for your comments.
I have taken reports for yesterday and today .I could see there is change in the value in the redo size.How can i investigate on this?
Re: redo size [message #558021 is a reply to message #558020] Mon, 18 June 2012 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How can i investigate on this?
write SQL

If you desire/want less REDO, then issue less DML.
REDO results directly from doing DML.
Alternatively configure DB to be READONLY, then absolutely no REDO gets generated.

IMO, you suffer from Compulsive Tuning Disorder.

At what value would REDO be acceptable to you?
Re: redo size [message #558022 is a reply to message #558021] Mon, 18 June 2012 23:10 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
BlackSwan,

I am really unware of the value , i would like to know when can i say this as crticial.Does this imapct our performance?
Re: redo size [message #558024 is a reply to message #558022] Mon, 18 June 2012 23:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Does this imapct our performance?
I give up.
Does it?

why are you fixated upon REDO?
If you can not control DML, then you can not influence REDO.
Re: redo size [message #558027 is a reply to message #558024] Tue, 19 June 2012 00:10 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
No its not imapcting..why does this size varies?
Re: redo size [message #558038 is a reply to message #558027] Tue, 19 June 2012 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It varies on the operations you ask Oracle to do, I already told, others already told you, what don't you understand?

Regards
Michel
Re: redo size [message #558090 is a reply to message #558027] Tue, 19 June 2012 06:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'll try again, man. No end user ever telephoned his DBA to say "the redo generation rate is too high". He might telephone to say "this query takes too long". Until that happens, you need do nothing. When it does happen, follow the advice given in the sticky post at the top of the Performance Tuning forum.
Re: redo size [message #558095 is a reply to message #558090] Tue, 19 June 2012 06:37 Go to previous messageGo to next message
ashishrnv
Messages: 8
Registered: June 2012
Location: Bangalore
Junior Member
Thanks John
Re: redo size [message #559106 is a reply to message #557942] Thu, 28 June 2012 14:27 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I use the following query to see how much redo is being used currently:

SQL > @R

no rows selected

SQL > update alan set total=1;

1 row updated.

SQL > @R

NAME USED_UBLK MEG_UNDO
---------- ---------- ----------
_SYSSMU10_ 1 .0078125

SQL > COMMIT;

Commit complete.

SQL > @R

no rows selected

SQL > update alan set total=1;

1 row updated.

SQL > @R

NAME USED_UBLK MEG_UNDO
---------- ---------- ----------
_SYSSMU5_4 1 .0078125

Notice how the undosegment changes each time a commit occurs.

SQL > commit;

Commit complete.

SQL > @r

no rows selected

SQL > list
1 select s.sid,s.serial#,username,t.used_ublk "UndoBLKS", terminal, osuser,
2 t.start_time, r.name,
3 decode(t.space, 'YES', 'SPACE TX',
4 decode(t.recursive, 'YES', 'RECURSIVE TX',
5 decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
6 )) status
7 from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
8 where t.xidusn = r.usn
9* and t.ses_addr = s.saddr

When I have runaway transactions that increase the size of the undo tablespace, I resize the tablespace with the following:

MAXMEG MEG STATUS FILE_NAME
------ ------ --------- --------------------------------------------
9000 9000 AVAILABLE /u02/app/oracle/oradata/CSCDAD/undotbs01.dbf

SQL > alter database datafile '/u02/app/oracle/oradata/CSCDAD/undotbs01.dbf' resize 2000m;

Database altered.

MAXMEG MEG STATUS FILE_NAME
------ ------ --------- --------------------------------------------
9000 2000 AVAILABLE /u02/app/oracle/oradata/CSCDAD/undotbs01.dbf
Re: redo size [message #559108 is a reply to message #559106] Thu, 28 June 2012 14:29 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALAN,
PLEASE format your posts according to Posting Guidelines.

http://www.orafaq.com/forum/t/88153/0/
Previous Topic: Value for sga_target
Next Topic: Tuning Queries
Goto Forum:
  


Current Time: Thu Mar 28 09:14:46 CDT 2024