Amount of arc log differs in two identical db's ?
eh
There are two identical oracle 9.2 db's running identical software and
basically amount of transactions is same - still there's a huge
difference between archive log amount generated in these db's.
Logs generated from other db is something like four times more ....
Not going more deeper in this case - what could result this kind of
behaviour ???
Thanks,
BR,
timo
vsevolod afanassiev
log generation
per second, number of transactions per second, average transaction
size, etc.
I could think of two reasons:
- One database has tablespaces in backup mode: ALTER TABLESPACE <name>
BEGIN BACKUP have been executed. In this case Oracle writes entire
block to redo logs.
- One database has FORCE LOGGING enabled. Let's say you are doing
CREATE TABLE AS SELECT NOLOGGING, in the database with
FORCE LOGGING the NOLOGGING option will be ignored and it will lead to
increased generation of redo
eh
I'll check those settings - and thanks for a quick tip !
BR,
timo
joel garry
Just throwing out some additional thoughts, as vsevolod has stated the
obvious two:
How identical is identical? We need to know every exact difference,
as sometimes a seemingly small difference (or the combo of a few) can
make a big effect. Please show SGA, and show all init.ora parameters
that are not default. Show us the physical memory, exact operating
system version, hardware configuration.
Amount of transactions, even in identical apps, may still be
overshadowed by the character of the transactions. Show us the top
SQL in the statspack, too.
A bit of a reach for 4 times redo, but could it be you have delayed
block cleanout? Perhaps one configuration has more batch update type
jobs running? Different configuration on system jobs like statistics
collection? One system is generating dump jobs or has more exports?
(See the Let's Pretend post on Jonathan Lewis' blog).
Log Miner would let you see exactly what is going on.
Here's an odd one: http://www.freelists.org/post/oracle-l/Oracle-recommends-rebuilding-IOTs-in-AQ-to-reduce-redo,2
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2009/dec/27/name-decade-it-was-mighty-mighty-bad/
JAW
Assuming all things are almost equal could it be that you have FORCE
LOGGING turned on for one and not the other which will make a big
difference if your database has BLOBS.
select force_logging from v$database or something similar should tell
you if FORCE LOGGING is on.
JAW