There are easier ways to test redo generation than mucking
about with logminer.
Update only the column that changes and check redo generation:
15:06:09 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL> @t1
USERNAME SID NAME VALUE
---------- ---- ---------------------------------------- -----------------
JKSTILL 10 redo size 0
1 row selected.
461 rows updated.
USERNAME SID NAME VALUE
---------- ---- ---------------------------------------- -----------------
JKSTILL 10 redo size 117,128
1 row selected.
Update all columns, only a single column has actually changed:
USERNAME SID NAME VALUE
---------- ---- ---------------------------------------- -----------------
JKSTILL 10 redo size 0
1 row selected.
461 rows updated.
USERNAME SID NAME VALUE
---------- ---- ---------------------------------------- -----------------
JKSTILL 10 redo size 226,908
1 row selected.
Updating just the changed field is clearly more efficient. I didn't test
a comparison
between multiple columns, updating 1 at a time versus all at once, change
only
columns with changed data.
Doing so would require all redo and rollback overhead N number of times
rather
than just once, N being the number of changed columns.
Below are the scripts used for testing.
Jared
=================================================================================
-- create test table
create table redo_test
as select
OWNER
,TABLE_NAME
,TABLESPACE_NAME
,CLUSTER_NAME
,IOT_NAME
,PCT_FREE
,PCT_USED
,INI_TRANS
,MAX_TRANS
,INITIAL_EXTENT
,NEXT_EXTENT
,MIN_EXTENTS
,MAX_EXTENTS
,PCT_INCREASE
,FREELISTS
,FREELIST_GROUPS
,LOGGING
,BACKED_UP
,NUM_ROWS
,BLOCKS
,EMPTY_BLOCKS
,AVG_SPACE
,CHAIN_CNT
,AVG_ROW_LEN
from dba_tables
nologging
/
=================================================================================
-- redo.sql
-- check redo size
col sid format 999 head 'SID'
col name format a40
col value format 9999,999,999,999 head 'VALUE'
col username format a10 head 'USERNAME'
break on username skip 1 on sid skip 1
select
sess.username,
stat.sid,
name.name name,
stat.value
from v$sesstat stat, v$statname name, v$session sess
where
stat.sid = sess.sid
and stat.sid = (
select s.sid
from v$session s, v$process p
where p.addr = s.paddr
and userenv('SESSIONID') = s.audsid
)
and stat.statistic# = name.statistic#
and name.name like 'redo size'
order by name
=================================================================================
-- test 1
@redo
update redo_test
set tablespace_name = reverse(tablespace_name)
/
@redo
rollback;
=================================================================================
-- test 2
@redo
update redo_test
set
owner = owner
, tablespace_name = reverse(tablespace_name)
, table_name = table_name
, pct_used = pct_used
, pct_free = pct_free
, ini_trans = ini_trans
, max_trans = max_trans
, initial_extent = initial_extent
, next_extent = next_extent
, min_extents = min_extents
, max_extents = max_extents
, pct_increase = pct_increase
, freelists = freelists
, num_rows = num_rows
, blocks = blocks
, empty_blocks = empty_blocks
, avg_space = avg_space
, chain_cnt = chain_cnt
, avg_row_len = avg_row_len
/
@redo
rollback;
=================================================================================
DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/28/2003 09:59 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Which method is more efficient
Bryan - If this is a critical issue, I would try it both ways on a test
database and use log miner to examine the amount of redo that is
generated.
My recollection is that you will find that the redo record records the
before and after data for each field. So just updating all fields may
generate significantly more redo. But don't trust my recollection on this
issue, test it yourself.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, May 28, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L
Hello everyone,
I have a question for the group of which method is more efficient.
To set the stage my company has a process to load part changes from
vendors
into the tables in an 8.1.7.4 Oracle database with archiving on and this
database has a standby database at disaster recovery site, so nologging is
not an option.
There is a discussion going on as to which method is more effective for
updating the information in a table. In looking at effectiveness, I am
looking at reducing the amount of redo information produced and having the
database do the least amount of work.
1) Method 1 is to update the information only for the fields
that have
changed, 1 field at a time.
2) Method 2 is to update the information for all the fields
in the
record whether they have changed or not, 1 record at a time.
The size of the record is 1843 bytes and the distribution of field sizes:
2 fields varchar2(240).
1 field varchar2(150)
15 fields varchar2(50)
1 field varchar2(3)
2 fields varchar2(20)
4 fields varchar2(40)
3 fields varchar2(1)
2 fields varchar2(25)
2 fields number(10,2)
1 field number(13,2)
1 field number(1)
1 field number
1 field varchar2(6)
1 field number (17,2)
1 field varchar2(4)
3 fields that are date.
In the past couple of months the average number of fields changed per
record
was 3 to 4 fields per record.
Thanks for your help,
Bryan Rodrigues
Oracle DBA
Elcom, Inc.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rodrigues, Bryan
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
