| REDO LOG GENERTION [message #466305] |
Mon, 19 July 2010 00:58  |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi,
We have development server on oracle 10g on windows while the production on unix with RAC and database is oracle 10g. Now when I run one update statement and check the redo generation in that session using before and after the update, I got a difference in redo. On windows it generates 1gb while on unix it gives me just double , while the data is same in both the environment. Can any one suggest me what could be the reason?
SELECT VALUE
INTO V_REDO_SIZE
FROM V$STATNAME
JOIN V$MYSTAT
USING(STATISTIC#)
WHERE NAME = 'redo size';
|
|
|
|
| Re: REDO LOG GENERTION [message #466309 is a reply to message #466305] |
Mon, 19 July 2010 01:04  |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Check if both the databases are running the same CharacterSet.
select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
Also, check the value of NLS_LANG in the environment (shell) of the clent SQL that is issuing the DMLs.
Hemant K Chitale
|
|
|
|
|
|
| Re: REDO LOG GENERTION [message #466311 is a reply to message #466309] |
Mon, 19 July 2010 01:10 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
I executed this query on both the development and production server and got the same result
select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
Output
--------------
AL32UTF8
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: REDO LOG GENERTION [message #466338 is a reply to message #466336] |
Mon, 19 July 2010 02:04 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can see, quoting you, "where is the actual problem". Is "extra" redo generation an actual problem?
Maybe the actual problem is the stuff that makes the update, you chose to not show us what you did.
Regards
Michel
[Updated on: Mon, 19 July 2010 02:04] Report message to a moderator |
|
|
|
| Re: REDO LOG GENERTION [message #466361 is a reply to message #466305] |
Mon, 19 July 2010 04:21 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
V$MYSTAT would show statistics that are cumulative since the start of the session. Is your UPDATE being executed in a new session, with *no* DML before it ?
Are the same blocks likely being updated from the other instance in the RAC cluster ? Or being updated by other sessions ? Delayed Block Cleanout can cause redo generation.
Hemant K Chitale
|
|
|
|
| Re: REDO LOG GENERTION [message #466387 is a reply to message #466361] |
Mon, 19 July 2010 06:50 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Hi Hemant,
I ran the update statement alone in one session on production box,operating system is HP-UX 11.31 and then run the below query to check the redo and found it is 2 gb, while the same update in another session on windows environment showing 1 gb reDo with the same data and same table.No other query is running parallel in that session.
SELECT VALUE
INTO V_REDO_SIZE
FROM V$STATNAME
JOIN V$MYSTAT
USING(STATISTIC#)
WHERE NAME = 'redo size';
|
|
|
|
|
|
|
|
| Re: REDO LOG GENERTION [message #466442 is a reply to message #466440] |
Mon, 19 July 2010 11:50 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You posted a question, Hemant posted an answer, you replied with the same question. Why do you think that repeating the same question will change Hemant's answer or will lead to a new one?
Please answer the questions we posted you.
Regards
Michel
[Updated on: Mon, 19 July 2010 11:52] Report message to a moderator |
|
|
|
| Re: REDO LOG GENERTION [message #466593 is a reply to message #466361] |
Tue, 20 July 2010 04:31 |
prashant_ora
Messages: 196
Registered: July 2010
|
Senior Member |
|
|
Is your UPDATE being executed in a new session: Yes new session
with *no* DML before it : No DML?
Are the same blocks likely being updated from the other instance in the RAC cluster :no
Or being updated by other sessions:no ?
|
|
|
|
| Re: REDO LOG GENERTION [message #466650 is a reply to message #466305] |
Tue, 20 July 2010 07:36 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
How have you copied the data across - redo is based on changed blocks, so if all the rows that are changed on one database are spread out across the whole table, and in the other database they are clustered together, you could get a substantial difference in redo size
|
|
|
|
| Re: REDO LOG GENERTION [message #467585 is a reply to message #466593] |
Mon, 26 July 2010 02:59 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
> with *no* DML before it : No DML?
Any other DML in the same session would be included in 'redo size' in V$MYSTAT.
Therefore, to be sure that your 'redo size' report is for this particular UPDATE statement we must ensure that no other DML has been executed.
>Or being updated by other sessions:no ?
If blocks were being updated by another session (INSERTs and DELETES are also updates to the table block), then delayed block cleanout may be causing Oracle to generate more redo to cleanout the other transaction -- Oracle has to update the ITL entry for a transaction that has committed but not cleaned the entry.
Hemant K Chitale
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
