RE: excessive archive log

  • From: "Shamsudeen, Riyaj" <RS2273@xxxxxxx>
  • To: <don@xxxxxxxxx>, <ryan_gaffuri@xxxxxxxxxxx>
  • Date: Fri, 7 Dec 2007 14:17:32 -0600

        As Don indicated, nologging is a special option and works well
for tables without any indices. Conventional inserts and updates still
will log, irrespective whether the tablespace is in nologging mode or DB
in noarchivelog mode etc.

Yes, logminer could be used. Following code piece might be helpful to
find objects causing excessive redo. I tested this long time ago, no
guarantees it will work for you, but worth a try.

drop table redo_analysis;
create table redo_analysis nologging as
select data_obj#,  oper, rbablk*512 + rbabyte curpos,
  lead(rbablk*512+rbabyte,1,0) over (order by  rbasqn, rbablk, rbabyte)
( select distinct data_obj#,  operation oper,
        rbasqn, rbablk, rbabyte from v$logmnr_contents 
order by rbasqn, rbablk, rbabyte )
        REM substr(replace ('/* insert ', '/* '), 1, instr (replace('/*
insert ','/* '), ' ') ) oper,
set lines 120 pages 40
column data_obj# format  9999999999
column oper format A15
column object_name format A60
column total_redo format 99999999999999
compute sum label 'Total Redo size' of total_Redo on report
break on report
spool /tmp/redo_analysis.lst
select data_obj#, oper, obj_name, sum(redosize) total_redo
select data_obj#, oper, obj_name , nextpos-curpos-1 redosize
from redo_analysis redo1, sys.obj$ obj
where (redo1.data_obj# = obj.obj# or  redo1.data_obj# = obj.dataobj#)
and  nextpos !=0 -- For the boundary condition
union all
select data_obj#, oper, 'internal ' , nextpos-curpos  redosize
from redo_analysis redo1
where  redo1.data_obj#=0 and  redo1.data_obj# = 0
and nextpos!=0
group by data_obj#, oper, obj_name
order by 4

Riyaj "Re-yas" Shamsudeen
ERP Financials DBA, New AT&T
OakTable Member -

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Don Seiler
Sent: Friday, December 07, 2007 10:57 AM
To: ryan_gaffuri@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: excessive archive log

How are you inserting the data?  Just because a tablespace is in
NOLOGGING mode doesn't mean INSERTs aren't logged, that only applies
to direct-path/append INSERTs.  UPDATEs will always be logged


On Dec 7, 2007 10:22 AM,  <ryan_gaffuri@xxxxxxxxxxx> wrote:
> We have an ETL database where virtually all activity are on tablespace
in nologging mode. 90% of our activity is inserts and 10% is updates of
1 column that is not indexed. We do have indexes on the tables being
inserted to. Though not large numbers(2-4 with column length of 1-3). We
do not have really wide columns.
> all of this is in noarchive log tablespaces.
> We are getting relatively speaking alot of redo. We are getting more
redo than we are getting data generated.
> one thing we may be seeing is that we are using advanced queueing and
it is enqueued and dequeued constantly. Could this be causing our
archive problems?
> how do we investigate this? can I use logminer to research this?

Don Seiler


Other related posts: