RE: excessive archive log

Ryan

        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) nextpos
from
( 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 from
(
select data_obj#, oper, obj.name 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
/

Thanks  

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

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Don Seiler Sent: Friday, December 07, 2007 10:57 AM To: ryan_gaffuri_at_comcast.net
Cc: oracle-l_at_freelists.org
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 regardless.

Don.

On Dec 7, 2007 10:22 AM, <ryan_gaffuri_at_comcast.net> 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
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l