Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: archive query - revisited
I've found that large DML operations create a large amount of redo even if the objects are in a locally managed tablespace.
Ratio is 17:1, at least for me.
Jared
On Wednesday 05 September 2001 10:11, Mohammad Rafiq wrote:
> Grant,
> I am glad that you found reason for lot of archiving.
> Yes, even with nologging, if table/tablespaces are dictionery managed,
> there are redologs generated for extent management.
> Archiving may be minimized by commiting after certain amount of rows like
> rows >= 10000 or whaever which your redologs can handle.
>
> For other tables created through procedure if those are created as
> unrecoverable/nologging or not. Make it nologging to avoid redo's specially
> created as select.I cannot open your attachment....
> Regards
>
>
> MOHAMMAD RAFIQ
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Tue, 04 Sep 2001 19:40:24 -0800
>
> Mohammad and Jay....many thanks for the responses.
>
> Upon running your script Mohammad, I noticed that archive files were being
> written to the archive directory during a large insert operation and
> stopped being written once the insert had completed. The insert command
> uses APPEND, the associated table is indexed but created with nologging.
> It is however partitioned. I'm still confused as to why logging continues
> to occur.....could it be that the extents allocated during the insert
> operation are logged?
>
> The same procedure creates a number of worktables to assist with join
> queries (not temp tables rather permanent tables) which are subsequently
> dropped after the query has completed (no indexes - hash joins only).
> There are no deletes or updates.
>
> Any more clues??
>
> Here's a copy of the table structure:
>
> (See attached file: table.txt)
>
> Here's a copy of the insert statement summarized from the procedure:
>
> (See attached file: insert.txt)
>
> Many thanks
> Grant
> << table.txt >>
> << insert.txt >>
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: jkstill_at_cybcon.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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 06 2001 - 22:11:03 CDT
![]() |
![]() |