This might sound like an exercise in pointlessness but here goes...
Are you sure you are generating the redo?
Use the following to get the sid of the sessions generating redo and then
track back to the session/process/statement info. I realize that the query
does not exactly query redo, but I have always found that the correlation
between block_changes + consistent_changes and redo entries matches up. If
there are others who can point out the fallacy of my thinking, I await
enlightenment. (No jokes about changing _spin_count please...)
select sid, (block_changes + consistent_changes)
from v$sess_io
where (block_changes + consistent_changes) > 5000 /* insert a reasonable
value here */
order by 2 desc;
SID (BLOCK_CHANGES+CONSISTENT_CHANGES)
---------- ----------------------------------
8 175079
5 308
Dan Fink
-----Original Message-----
Sent: Thursday, September 19, 2002 12:24 PM
To: Multiple recipients of list ORACLE-L
All the indices are dropped and the PK is disabled.
I agree that there are some changes to be done
in the data disctinary tables, but the amount of
archived logs - about 90M per minute looked too high
to me
- Viral Desai <viral303_at_hotmail.com> wrote:
>
> A couple of things to try ---
>
> 1. Drop the indexes and primary key instead of
> disabling them. Insert the
> data and recreate pk.
>
> 2. This could be due to changes in data dictionary,
> when you insert large
> number of rows in the table, new extents may be
> allocated or high water mark
> of the table would be modified. This information
> need to be logged. I think
> that the redo generated due to this should not be
> very large though.
>
> Hope this helps.
> Viral Desai
>
>
> >From: Gurelei <gurelei_at_yahoo.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> >Subject: RE: Why does my insert creates so many
> logs?
> >Date: Thu, 19 Sep 2002 06:03:29 -0800
> >
> >None.
> >--- "Nicoll, Iain (Calanais)"
> ><iain.nicoll_at_calanais.com> wrote:
> > > Doesn't have any triggers does it?
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, September 18, 2002 8:39 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi.
> > >
> > > A developer of mine is running a large insert as
> > > select:
> > >
> > > insert /* parallel hint */ into table A
> > > nologging
> > > (select * from table b where ...);
> > >
> > > There are no indices on table A and a PK
> disabled.
> > > Still that insert generates a large amount of
> logs.
> > > What could be the reason for that? Any ideas?
> Table
> > > A
> > > is not partitioned and has NOLOGGING attribute
> on
> > > the
> > > dba_tables set to Yes.
> > >
> > > thanks
> > >
> > > Gene
> > >
> > >
> __________________________________________________
> > > Do you Yahoo!?
> > > Yahoo! News - Today's headlines
> > > http://news.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Gurelei
> > > INET: gurelei_at_yahoo.com
> > >
> > > 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: ListGuru_at_fatcity.com (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.com
> > > --
> > > Author: Nicoll, Iain \(Calanais\)
> > > INET: iain.nicoll_at_calanais.com
> > >
> > > 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: ListGuru_at_fatcity.com (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).
> >
> >
> >__________________________________________________
> >Do you Yahoo!?
> >New DSL Internet Access from SBC & Yahoo!
> >http://sbc.yahoo.com
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >--
> >Author: Gurelei
> > INET: gurelei_at_yahoo.com
> >
> >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: ListGuru_at_fatcity.com (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).
>
>
>
>
>
> MSN Photos is the easiest way to share and print
> your photos:
> http://photos.msn.com/support/worldwide.aspx
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Viral Desai
> INET: viral303_at_hotmail.com
>
> 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: ListGuru_at_fatcity.com (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).
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gurelei
INET: gurelei_at_yahoo.com
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: ListGuru_at_fatcity.com (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.com
--
Author: Fink, Dan
INET: Dan.Fink_at_mdx.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Sep 19 2002 - 15:23:30 CDT