Skip to Main Content
  • Questions
  • Excessive archive log generation during data load

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Will.

Asked: October 14, 2001 - 11:41 pm UTC

Last updated: October 05, 2018 - 3:42 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I am encountering a situation related to data loading and excessive
archive log generation. I am using Oracle 8.1.6 under Solaris 7.

I insert twice a week about 1 million rows in to a table whose columns are of number datatype. The loading takes place vi a PL/SQL
script which handles some complicated business logic. The average
row length of the table is about 30 bytes. Hence, approximately 30 MB worth of data is loaded.

However, as soon as the loading script starts, there is a continuous log switch and I observed more than 300 archive logs were created.
I am currently using 3 redo logs each 5 MB size. That means 300x5
=1500 MB worth of archive log was generated during the 30/40 minute data load process.

The problem is that because of this massive archive log generation the filesystem suddenly may get full resulting
in database freeze. I have to keep a close manual watch during the process. However, I need to make it an unattended automated dataload.

Questions:

1. What is the reason that loading of only 30 MB of data results in
more than 1500 MB archive log generation ?

2. Is there any paparemeter
or anything that will reduce the archive log generation.

3. How to
justify or quantify the amount of archive log generation based on
a certain volume of data or transactions.


Some folks have suggested I do the data load so minimal
archive logs are generated ( such as sqlloader direct path etc.).
However, I need to use the PL/SQL scripts for business reasons.
Also, sqlloader direct path will cause some problems like
invalid indexes if the sqlloader fails for some reason which
I want to avoid in our 24x7 production environment.

Thanks for your help and time.
Will

and Tom said...

1) you are logging the data changes and the rollback generated. As well, the indexes on this table will generate their own changes and their own rollback which is also logged.

If you commit frequently, the amount of redo you generate goes UP as well (not only that, but it runs slower overall).

2) no, but the techniques you use may affect this radically

3) do an insert with autotrace on in sqlplus and see the redo generated.



A possible solution for you is:

1) direct path load the data into a NOLOGGING table.
2) process the data from there into a GLOBAL TEMPORARY table (mininal redo generated for inserts - very very little redo)
3) when all of the data is ready to go, alter your_table to nologging and

insert /*+ append */ into YOUR_TABLE select * from global_temp_table;

and do it as one transaction.


That will certainly minimize the amount of redo generated to the bear minimum. You will need to backup your database ASAP after this as the insert of the million rows will not generate archive redo log and hence would not be recoverable in the event of a media failure. Using this technique, only the changes to the indexes on your_table will generate log during this process.

Rating

  (21 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Archive log

Will Chakraborty, October 15, 2001 - 11:02 pm UTC

Your suggestions are very helpful

"Excessive archive log generation during data load", version 8.1.6

A S Rathore, October 16, 2001 - 4:33 am UTC

Since the data loading is done only twice a week and we are suggesting nologging why not suggest drop/disable of index as well to optimize the entire operation.

Tom Kyte
October 16, 2001 - 8:11 am UTC

Because of the very clear statment:

"Also, sqlloader direct path will cause some problems like
invalid indexes if the sqlloader fails for some reason which
I want to avoid in our 24x7 production environment."


They need the indexes to be there all of the time by defintion. If we dropped them - the queries would suffer just a bit. This approach detailed below generates the minimum redo/archive given their constraints.



Loading

lakshmi, May 15, 2003 - 12:45 am UTC

Brilliant !!!

Alexander the ok, November 23, 2009 - 10:46 am UTC

Tom,

I have a serious issue I'm faced with having to do with an insane amount of redo being generated by an overnight load. I am struggling to identify the source of this logging. I'm pretty sure it has to do with a single blob column in 1 table. This table only has 135 rows, and it's only using about 4.5 gig. However, when this job runs it get extended to about 40 gig and generates 50 + gig of archive logs.

Does this command give me the size of the blob column in meg?

select length(blob_col)/1024/1024 from t

I have an awr report, can I send it to you? All I'm looking for from you is sql that looks like it could be responsible for this. From there I will take it to the application team and the vendor.
Tom Kyte
November 23, 2009 - 4:43 pm UTC

... Does this command give me the size of the blob column in meg? ...

why would it matter if it did?


... I have an awr report, can I send it to you?...

no, please do not


have you considered firing up log miner? to see what you can see in there?

Alexander the ok, November 23, 2009 - 5:05 pm UTC

To find out which row or rows is taking up all the space. If that's wrong how do you get the space used for a blob?

I've never touched logminer.
Tom Kyte
November 23, 2009 - 5:11 pm UTC

Then consider touching log miner, it lets you mine the logs and see what is in there.

the size of the blob is not relevant to compute the amount of redo.

What if the process updates 5 bytes out of 5,000,000 bytes of blob?
Or what if it writes and rewrites the blob 50 times (inefficient code)?
And what about the lob index - that points to the lob, how many updates did that go through (not a function of the blob size, but rather a function of the work done on the blob itself)


sure, you can select dbms_lob.getlength of the blob, it would give you an estimate (low-ball) as to the space consumed by that blob - but would give you nothing to look at as far as redo generation goes. You'd have to have intimate knowledge of the process that touches the lob.

Alexander the ok, November 23, 2009 - 5:48 pm UTC

Well I have two problems here, one is the space this column and blob is taking up, the other is the redo. The perplexing part is somehow the segment is being extended way out to 40 gig, yet when I go in and query the space it's just leftover fragmentation, it's only using about 4.5.

I've seen the update statement for the table while this is going on, it looked like

update t set blob_col = :bv1 where....
Tom Kyte
November 23, 2009 - 6:31 pm UTC

well, how was the blob created - what is the retention policy. If you are using undo retention with this - it'll retain the undo (which is maintained in the blob space - NOT in the undo segments) for that long. Or it'll use a pctversion scheme. And it'll not reuse space in a single transaction.


and that update, probably just the tip of the iceberg - lobs are accessed using dbms_lob or as a stream from various 3gls like java and the like. That update probably doesn't represent the real work since it would only be able to do 32k bits - to do larger, they need to use something else.

Alexander the ok, November 23, 2009 - 9:20 pm UTC

Ah, you are a genius. It's the got to be the undo. I try to keep 3 days worth. Puts me in a bit of a quandary though, not sure what I can do except lower the threshold which I don't really want to do because I can't even tell you how many times flashback has saved me a lot of pain.

You might not believe this, but I've never actually seen a developer that knows how to program against lobs using the apis you are talking about. They just bind strings in java (for clobs). And in this case they are not using pl/sql (this is a vendor) so they are not bound by the 32k limits. Correct me if I'm wrong, but one could shove as much as they wanted into a 3gl variable as long as the jvm or what have you would allow?
Tom Kyte
November 24, 2009 - 10:20 am UTC

you can manage lobs one of two ways - either with undo retention or pctversioning - you don't have to do it time based.


It will depend on the jdbc driver as to whether they can do an unlimited string - if they bind as a varchar2, it'll be 32k - but if under the covers the jdbc drive does 'magic', it'll be done via a streaming API.

Excessive archive log generation during data load

Hari, November 23, 2009 - 11:35 pm UTC

Hi Tom,

Good Day.

a) I am still not able to understand the reason of how 30 MB of data generates such a huge archive log? Is there any math behind that? Can you please provide me your expert guidance?

b) If we use APPEND hint, we need to make sure that the table should not have referential integrity and trigger. Given this case, if in later part if they add a referential integrity or a trigger, then what would happen to this insert statement?

c) Also, Append uses DIRECT PATH LOAD, which directly writes to the files instead of cache. Will this generate redo?

d) Since we are using NOLOGGING, instead of the three steps you have suggested, is it possible to do in one go as the following:

1) direct path load the data into a NOLOGGING table.
2) when all of the data is ready to go, alter your_table to nologging and

insert /*+ append */ into YOUR_TABLE select * from <table>;

and do it as one transaction.

Is there any specific reason of using a global temporary table?

e) How come you immediately provided a solution to look into undo? You must be a real whizkid :)

Thanks

Hari
Tom Kyte
November 24, 2009 - 10:41 am UTC



a) I started typing a list of reasons why this would happen, but then I realized "doh, I already did that", please see the original response. If it takes them 30/40 minutes to load this data - they are NOT using a single sql statement to load, there is a TON of other stuff going on (did you see the "The loading takes place vi a PL/SQL script which handles some complicated business logic." comment). This is not a simple load of 30mb of raw data into a table, there are lots of things happening and probably, the 30mb of raw data is audited, logged somewhere - other updates take place.


b) it would not be done direct path, it would be done as a conventional path load.

c) direct path loads can avoid redo generation for the TABLE data - but NOT for the index maintenance.

d) of course you could direct path load into the table directly, except for the quote "The loading takes place vi a PL/SQL
script which handles some complicated business logic." which would seem to prevent it. That was the specific reason for the global temporary table.

e) ? don't know what you mean

more redo?

jian huang zheng, November 25, 2009 - 7:06 am UTC

hi Tom
   you said that insert into temporary table causes minimal redo, but my test shows:
SQL> create global temporary table c_tmp ( id int);

Table created.

SQL> drop table tmp;

Table dropped.

SQL> create table tmp ( id int);

Table created.

SQL> insert into c_tmp select object_id from dba_objects;

29625 rows created.


Statistics
----------------------------------------------------------
         39  recursive calls
      30292  db block gets
      63685  consistent gets
          0  physical reads
    3688136  redo size
        626  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      
SQL> insert into tmp select object_id  from dba_objects;

29625 rows created.


Statistics
----------------------------------------------------------
        139  recursive calls
        827  db block gets
      63790  consistent gets
          0  physical reads
     463008  redo size
        626  bytes sent via SQL*Net to client
        550  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      29625  rows processed

I just dont understand why insert into temporary table causes more redo?

      Thanks,










Tom Kyte
November 25, 2009 - 3:31 pm UTC

I cannot reproduce in 9i, 10g nor 11g. Give us more information please.


ops$tkyte%ORA9IR2> insert into c_tmp select object_id from dba_objects;

28357 rows created.


Statistics
----------------------------------------------------------
      77168  redo size

ops$tkyte%ORA9IR2> insert into tmp select object_id from dba_objects;

28357 rows created.

Statistics
----------------------------------------------------------
     444496  redo size





ops$tkyte%ORA10GR2> insert into c_tmp select object_id from dba_objects;

50928 rows created.


Statistics
----------------------------------------------------------

     141276  redo size


ops$tkyte%ORA10GR2> insert into tmp select object_id from dba_objects;

50928 rows created.


Statistics
----------------------------------------------------------

     826376  redo size



ops$tkyte%ORA11GR2> insert into c_tmp select object_id from dba_objects;

72788 rows created.


Statistics
----------------------------------------------------------

     201752  redo size


ops$tkyte%ORA11GR2> insert into tmp select object_id from dba_objects;

72788 rows created.


Statistics
----------------------------------------------------------

    1178384  redo size



Could there be a bug ?

jian huang zheng, November 26, 2009 - 12:08 am UTC

Hi Tom

The database version is:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

I think it is because that I didnt patch it, since i made a couple of tests and show the same results.

Thanks,
Tom Kyte
November 28, 2009 - 12:39 pm UTC

show all non-default parameters set

show us the create statement for your temporary tablespace

show us in short how to reproduce.

Excellent methodology to avoid log generation

V. Hariharaputhran, November 26, 2009 - 9:55 pm UTC

Excellent methodology to avoid log generation, I do have a very similar scenario where 30 MB data generating more than 1000 MB of logs but unfortunately my DBA is not permitting to alter the tables into NOLOGGING mode even though database backup is taken every time both prior and post data load.

I spend more than an hour a day in your site to gain knowledge, THANKS A LOT FOR THIS SITE - TOM, missed a chance to meet you when you came to India.

Thanks for your time
puthranv

archive log generation

Yehia, February 12, 2010 - 10:20 am UTC

how to find out the cause of excessive
archive log generation. in 9i or 10g

Thanks

Yehia

Tom Kyte
February 16, 2010 - 12:25 pm UTC

there is not such thing as excessive - there is only redo generated in response to the work you ask us to perform.


You can use log miner (referenced above a couple of times) to see what is in your redo logs.

Sandip Roy, May 04, 2010 - 11:32 am UTC

I want to know which session generate more archivelog during a time span. I am using oracle 11g. My problem is I am using Stream and CDC so i need to know which session generate more archive to trace the session properly .
Tom Kyte
May 06, 2010 - 1:24 pm UTC

use the active session history (dba_hist) views - the contain a wealth of information about what sessions were doing during a period of time.

part of the tuning/diagnostic packs with enterprise manager.

RahulC, May 18, 2010 - 1:43 am UTC

Hi Tom,

Is there a way to know which schema is generating more archive files.. I was in a situation such, that one of our database schema was generating more archive log files and i was going deleting the archive log files...

Please let me know if any solution for the same to know which schema is generating more archive logfiles..

Regards,
RahulC
Tom Kyte
May 24, 2010 - 9:22 am UTC

why why why - would you just randomly go about deleting archives???!?!??!?!

Schemas do not generate redo, Applications issuing SQL generate redo.

If you have access to ASH, you can see what sessions were generating more redo than you expect, and then drill into what those sessions were doing. If you do not have ASH, and you do not have your own history enabled in your own applications, then you can mine those redo log files using logminer (oh, you cannot actually, you seem to have erased them???) and see what was happening.

sam, November 20, 2013 - 3:38 am UTC

Tom:

We are having a problem with a new oracle 11g database.
The archive logs directory keeps filling up even though there is no database activity and db is idle.

This is a 4 GB 11g database with one application schema only - small database.

Look at the size of archives in one day 

$ ls -alt

total 188028
drwxr-x---  2 oracle oinstall     4096 Nov 19 16:00 .
-rw-r-----  1 oracle oinstall 31391744 Nov 19 16:00 o1_mf_1_3782_98qnbr1q_.arc
-rw-r-----  1 oracle oinstall 31887360 Nov 19 11:00 o1_mf_1_3781_98q2sq20_.arc
-rw-r-----  1 oracle oinstall 31076352 Nov 19 06:52 o1_mf_1_3780_98pn7shy_.arc
-rw-r-----  1 oracle oinstall 31669760 Nov 19 02:45 o1_mf_1_3779_98p5rf45_.arc
-rw-r-----  1 oracle oinstall 33620480 Nov 19 00:19 o1_mf_1_3778_98ox6p8v_.arc
drwxr-x--- 65 oracle oinstall    12288 Nov 19 00:19 ..
-rw-r-----  1 oracle oinstall 32649216 Nov 19 00:19 o1_mf_1_3777_98ox6fc2_.arc

Is this normal with oracle 11g with automatic memory management ON?

I disabled the 3 auto tasks to see if that changes the archive logs size but nothing changed.

SQL> SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor DISABLED
sql tuning advisor DISABLED

I also disabled all the scheduler jobs to see if it makes a difference and it did not.


OWNER JOB_NAME ENABLED
-------------------- ---------------------------------------- ---------------
SYS XMLDB_NFS_CLEANUP_JOB FALSE
SYS SM$CLEAN_AUTO_SPLIT_MERGE FALSE
SYS RSE$CLEAN_RECOVERABLE_SCRIPT FALSE
SYS FGR$AUTOPURGE_JOB FALSE
SYS BSLN_MAINTAIN_STATS_JOB FALSE
SYS DRA_REEVALUATE_OPEN_FAILURES FALSE
SYS HM_CREATE_OFFLINE_DICTIONARY FALSE
SYS ORA$AUTOTASK_CLEAN FALSE
SYS FILE_WATCHER FALSE
SYS PURGE_LOG FALSE
ORACLE_OCM MGMT_STATS_CONFIG_JOB FALSE

OWNER JOB_NAME ENABLED
-------------------- ---------------------------------------- ---------------
ORACLE_OCM MGMT_CONFIG_JOB FALSE
EXFSYS RLM$SCHDNEGACTION FALSE
EXFSYS RLM$EVTCLEANUP FALSE
APEX_040200 ORACLE_APEX_DAILY_MAINTENANCE FALSE
APEX_040200 ORACLE_APEX_WS_NOTIFICATIONS FALSE
APEX_040200 ORACLE_APEX_MAIL_QUEUE FALSE
APEX_040200 ORACLE_APEX_PURGE_SESSIONS FALSE

18 rows selected.


Finally, I mined one archive log file using Log Miner and looked at the LOGNMR view. I noticed mostly internal statements against WRI table by oracle like

delete from "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY" where "OBJ#" = '6455' and "INTCOL#" = '1' and "SAVTIME" = TO_TIMESTAMP_TZ('05-OCT-13 06.00.29.048638 AM -04:00') and "BUCKET" = '1634' and "ENDPOINT" = '5559' and "EPVALUE" IS NULL and "COLNAME" IS NULL and "SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAHcAACAAAXA2AAU'; 

insert into "SYS"."WRI$_OPTSTAT_HISTGRM_HISTORY"("OBJ#","INTCOL#","SAVTIME","BUCKET","ENDPOINT","EPVALUE","COLNAME","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('6455','1',TO_TIMESTAMP_TZ('05-OCT-13 06.00.29.048638 AM -04:00'),'1634','5559',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);


I also included some statistics on the Logminer view that shows the DML activity. It shows high deletes on the WRI tables

SQL> /
     
     OPERATION                        COUNT(*)
     ------------------------------ ----------
     DDL                                    40
     COMMIT                                812
     START                                 777
     ROLLBACK                                4
     UPDATE                                376
     DELETE                             103175
     INTERNAL                           157353
     UNSUPPORTED                          5706
     INSERT                               1014
     
9 rows selected.


SQL> /

SEG_OWNER            OPERATION                        COUNT(*)
-------------------- ------------------------------ ----------
SYSMAN               UNSUPPORTED                            50
                     COMMIT                                812
                     START                                 777
                     ROLLBACK                                4
SYS                  DDL                                    40
SYSMAN               INSERT                                 10
SYS                  UNSUPPORTED                          5656
SYSMAN               DELETE                                 10
SYSMAN               UPDATE                                 11
                     INTERNAL                           157353
SYS                  DELETE                             103165
SYS                  INSERT                               1004
SYS                  UPDATE                                365

13 rows selected.


SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYSMAN     MGMT_HA_MTTR                             INSERT                        6
SYSMAN     MGMT_TARGETS                             UPDATE                        7
SYS        MON_MODS$                                INSERT                        1
SYS        OBJ$                                     DELETE                       62
SYS        WRH$_ROWCACHE_SUMMARY                    DDL                           2
SYS        WRH$_ACTIVE_SESSION_HISTORY              DDL                           2
SYS        WRH$_SYS_TIME_MODEL                      DDL                           2
SYS        WRI$_OPTSTAT_HISTGRM_HISTORY             DELETE                    44018
SYS        WRH$_PROCESS_MEMORY_SUMMARY              DELETE                       96
SYS        WRH$_TABLESPACE_SPACE_USAGE              DELETE                      168
SYS        WRH$_MEMORY_TARGET_ADVICE                DELETE                      168

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        TABPART$                                 UPDATE                      120
SYS        WRI$_ADV_MESSAGE_GROUPS                  DELETE                        5
                                                    COMMIT                      812
                                                    START                       777
SYS        SEG$                                     UNSUPPORTED                 374
                                                    ROLLBACK                      4
SYS        JOB$                                     UPDATE                        1
SYS        WRI$_SCH_CONTROL                         UNSUPPORTED                   1
SYS        DEPENDENCY$                              UNSUPPORTED                  24
SYS        INDPART$                                 DELETE                       42
SYS        WRH$_SEG_STAT                            DDL                           2

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRH$_MVPARAMETER                         DDL                           2
SYS        MON_MODS_ALL$                            UPDATE                       26
SYS        WRI$_OPTSTAT_TAB_HISTORY                 DELETE                      561
SYS        WRI$_OPTSTAT_HISTHEAD_HISTORY            DELETE                     7420
SYS        WRH$_SQL_PLAN                            DELETE                      288
SYS        WRH$_RESOURCE_LIMIT                      DELETE                      120
SYS        WRH$_UNDOSTAT                            DELETE                      144
SYS        WRH$_SQL_BIND_METADATA                   DELETE                       16
SYS        WRH$_MUTEX_SLEEP                         DELETE                      576
SYS        TABPART$                                 UNSUPPORTED                  20
SYS        WRI$_ADV_PARAMETERS                      DELETE                     1428

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRI$_ADV_ADDM_FDG                        DELETE                        4
SYSMAN     MGMT_FAILOVER_TABLE                      UPDATE                        1
SYS        SCHEDULER$_EVENT_LOG                     INSERT                        7
SYSMAN     MGMT_CURRENT_METRICS,SYS_IOT_OVER_69292  UNSUPPORTED                  31
SYS        KET$_AUTOTASK_STATUS                     UNSUPPORTED                  12
SYS        WRM$_SNAPSHOT                            UPDATE                       24
SYS        HISTGRM$                                 UNSUPPORTED                4359
SYS        WRH$_FILESTATXS                          DDL                           2
SYS        MON_MODS_ALL$                            UNSUPPORTED                 243
SYS        OPTSTAT_HIST_CONTROL$                    UNSUPPORTED                   2
SYS        WRI$_OPTSTAT_OPR                         DELETE                       25

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRH$_SGA_TARGET_ADVICE                   DELETE                      168
SYS        WRH$_RULE_SET                            DELETE                       24
SYS        WRH$_PERSISTENT_SUBSCRIBERS              DELETE                       48
SYS        WRH$_IOSTAT_FUNCTION                     DELETE                      336
SYS        WRH$_MEM_DYNAMIC_COMP                    DELETE                      384
SYS        WRH$_DISPATCHER                          DELETE                       24
SYS        WRH$_IOSTAT_DETAIL                       DELETE                      336
SYS        OBJ$                                     INSERT                       62
SYS        WRI$_ADV_ADDM_INST                       DELETE                        1
SYS        WRI$_ADV_SQLT_RTN_PLAN                   DELETE                        3
SYS        WRI$_ADV_REC_ACTIONS                     DELETE                        3

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRI$_ADV_RATIONALE                       DELETE                       17
SYS        WRI$_ADV_SQLT_PLANS                      DELETE                      287
SYSMAN     MGMT_HA_MTTR                             UNSUPPORTED                   6
SYS        MON_MODS$                                UNSUPPORTED                  75
SYS        HIST_HEAD$                               DELETE                      343
SYS        WRH$_SYSTEM_EVENT                        DDL                           2
SYS        WRH$_SYSSTAT                             DDL                           2
SYS        COL_USAGE$                               DELETE                       80
SYS        WRH$_SQL_SUMMARY                         DELETE                       24
SYS        WRH$_BG_EVENT_SUMMARY                    DELETE                     1944
SYS        WRH$_LIBRARYCACHE                        DELETE                      672

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRH$_SGA                                 DELETE                       96
SYS        WRH$_IOSTAT_FILETYPE                     DELETE                      288
SYS        WRI$_OPTSTAT_IND_HISTORY                 INSERT                       21
SYS        HISTGRM$                                 DELETE                       48
SYS        HIST_HEAD$                               UPDATE                       20
SYS        WRI$_ADV_TASKS                           DELETE                       24
SYSMAN     MGMT_COLLECTION_TASKS                    UPDATE                        3
SYSMAN     MGMT_TASK_QTABLE                         INSERT                        3
SYS        AUD$                                     INSERT                       38
SYS        SCHEDULER$_JOB                           UNSUPPORTED                   1
SYSMAN     MGMT_SYSTEM_PERFORMANCE_LOG              INSERT                        1

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRH$_SQLSTAT                             DDL                           2
SYS        WRH$_SGASTAT                             DDL                           2
SYS        WRH$_SERVICE_WAIT_CLASS                  DDL                           2
SYS        WRH$_SQLTEXT                             DELETE                       16
SYS        WRH$_ENQUEUE_STAT                        DELETE                     2208
SYS        WRH$_BUFFER_POOL_STATISTICS              DELETE                       24
SYS        WRH$_PGASTAT                             DELETE                      384
SYS        WRH$_SHARED_POOL_ADVICE                  DELETE                      792
SYS        WRH$_PGA_TARGET_ADVICE                   DELETE                      336
SYS        WRH$_SEG_STAT_OBJ                        DELETE                       57
SYS        WRH$_SYSMETRIC_SUMMARY                   DELETE                     3792

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRM$_SNAPSHOT                            DELETE                       24
SYS        INDPART$                                 INSERT                       42
SYS        WRI$_ADV_OBJECTS                         DELETE                       21
SYS        WRI$_ADV_SQLT_PLAN_STATS                 DELETE                       47
SYS        WRI$_ADV_SQLT_PLAN_HASH                  DELETE                       48
                                                    INTERNAL                 157353
SYS        SCHEDULER$_JOB_RUN_DETAILS               INSERT                        7
SYSMAN     MGMT_TARGETS                             UNSUPPORTED                   7
SYS        SMON_SCN_TIME                            UNSUPPORTED                  11
SYS        MON_MODS$                                UPDATE                       42
SYS        COL_USAGE$                               UNSUPPORTED                   2

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        PARTOBJ$                                 UNSUPPORTED                  82
SYS        WRH$_WAITSTAT                            DDL                           2
SYS        WRH$_LATCH                               DDL                           2
SYS        WRH$_TABLESPACE_STAT                     DDL                           2
SYS        WRH$_OSSTAT                              DDL                           2
SYS        WRH$_EVENT_HISTOGRAM                     DDL                           2
SYS        MON_MODS_ALL$                            INSERT                       20
SYS        WRH$_INSTANCE_RECOVERY                   DELETE                       24
SYS        WRH$_SYSMETRIC_HISTORY                   DELETE                    28780
SYS        WRH$_JAVA_POOL_ADVICE                    DELETE                      240
SYS        WRH$_STREAMS_POOL_ADVICE                 DELETE                      480

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRH$_SESS_TIME_STATS                     DELETE                       48
SYS        WRH$_RSRC_CONSUMER_GROUP                 DELETE                     1920
SYS        TABPART$                                 INSERT                       20
SYS        HIST_HEAD$                               INSERT                      343
SYS        WRI$_OPTSTAT_OPR                         INSERT                       20
SYS        WRI$_ADV_EXECUTIONS                      DELETE                       25
SYS        WRI$_ADV_ACTIONS                         DELETE                        3
SYSMAN     MGMT_POLICY_ASSOC_EVAL_SUMM              UNSUPPORTED                   6
SYSMAN     MGMT_DB_HDM_METRIC_HELPER                DELETE                        4
SYSMAN     MGMT_HA_MTTR                             DELETE                        6
SYS        KET$_CLIENT_CONFIG                       UNSUPPORTED                  21

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRM$_WR_CONTROL                          UNSUPPORTED                   2
SYS        TAB$                                     UNSUPPORTED                  61
SYS        TABPART$                                 DELETE                       20
SYS        WRH$_LATCH_MISSES_SUMMARY                DDL                           2
SYS        WRH$_DB_CACHE_ADVICE                     DDL                           2
SYS        WRH$_PARAMETER                           DDL                           2
SYS        WRI$_ALERT_HISTORY                       DELETE                        2
SYS        MON_MODS$                                DELETE                      269
SYS        WRH$_SQL_WORKAREA_HISTOGRAM              DELETE                      288
SYS        WRH$_WAITCLASSMETRIC_HISTORY             DELETE                     1439
SYS        WRH$_PERSISTENT_QUEUES                   DELETE                       96

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        WRH$_RSRC_PLAN                           DELETE                      192
SYS        HIST_HEAD$                               UNSUPPORTED                 343
SYS        IND$                                     UNSUPPORTED                  21
SYS        WRI$_OPTSTAT_HISTHEAD_HISTORY            INSERT                      363
SYS        WRI$_ADV_FINDINGS                        DELETE                       66
SYS        WRI$_ADV_RECOMMENDATIONS                 DELETE                        3
SYS        SCHEDULER$_JOB                           UPDATE                       16
SYS        OBJ$                                     UPDATE                       87
SYS        KET$_AUTOTASK_STATUS                     UPDATE                        6
SYS        WRI$_SCH_VOTES                           UPDATE                        1
SYS        WRH$_SERVICE_STAT                        DDL                           2

SEG_OWNER  SEG_NAME                                 OPERATION              COUNT(*)
---------- ---------------------------------------- -------------------- ----------
SYS        MON_MODS_ALL$                            DELETE                       20
SYS        WRI$_OPTSTAT_IND_HISTORY                 DELETE                      730
SYS        WRH$_TEMPSTATXS                          DELETE                       24
SYS        WRH$_LOG                                 DELETE                       72
SYS        WRH$_THREAD                              DELETE                       24
SYS        WRH$_SHARED_SERVER_SUMMARY               DELETE                       24
SYS        WRH$_PERSISTENT_QMN_CACHE                DELETE                      336
SYS        SEQ$                                     UNSUPPORTED                   2
SYS        WRI$_OPTSTAT_TAB_HISTORY                 INSERT                       60
SYS        INDPART$                                 UPDATE                       21
SYS        WRI$_ADV_TASKS                           UPDATE                        1

154 rows selected.

Stats

A reader, January 11, 2014 - 4:14 pm UTC

Tom:

Any ideas on the above problem wih archive logs. I already worked with support for few months and dont seem to have a clue what is going on.
Tom Kyte
January 13, 2014 - 6:35 pm UTC

Sam - 192mb - yes, MB, is pretty teeny tiny. is that really filling up an archive destination?


really 192mb is a problem? in 2014?


you could disable AWR if you like, pretty easy to figure that out using search, or get in touch with support and ask them for assistance.


I'm pretty sure support isn't clueless on this (you say that all of the time Sam... All of the time...). I'm pretty sure they know it is AWR, they probably have told you it is AWR, they probably have told you the amount of redo being generated is "noise" in the year 2014...

archive logs

A reader, January 14, 2014 - 2:23 pm UTC

I have several databases *idle* and i am getting about 1 GB of archive logs total per day. that is about 15% of the database size and nothing is being done.

Obviously something is wrong because I asked someone in your support to test same version of database at his site and see what kind of archive is he getting and he got minimal logs.

I can disable AWR just to confirm it is causing excessive logging but would not I you lose performance analysis data?
Can I use another tool like STATSPACK instead if needed?

I was considering another option to set parameter "statistics=BASIC" instead of TPICAL, but is not supported if you are running Automatic memory manager.
Tom Kyte
January 14, 2014 - 8:03 pm UTC

Hi Sam,

if you read my answer above, you'll find your use of the word "obviously" is "obviously" wrong. I'm generating anywhere between 200mb to 1gb per day on my mostly idle database, the one I use to demonstrate things here.

I have no idea what you've set your AWR collection intervals to, etc. You've identified what is causing the redo to be generated (AWR). The amount of redo you generate would be a function of the amount of work you were doing *weeks* ago, not today. You see lots of deletes (purging of old data), not so much on the insert (collection) side of things.


archive logs

A reader, January 14, 2014 - 11:30 pm UTC

Tom,

We have not done any activity for months so i do not know what AWR is really collecting here for performance monitoring.


Also what is you database verion, size, Operating system.

This is an RDBMS 11.2.0.3, 3 GB on RHEL.

How did you conclude your archive logs size is normal too. You may have the same AWR *bug* and thinking it is normal.
You probably never paid attention to it because you dont care about archive log size.
Tom Kyte
January 15, 2014 - 7:51 pm UTC

it is not a bug sam. obviously it is not a bug. (now we are even).

I'm done on this topic with you.

Reply to above

Alexander, January 15, 2014 - 5:05 pm UTC

Just wondering what the issue this is causing you, why are you spending time on this? 1GB of redo in a day, why does this concern you? If you have scheduled routines to backup and remove archivelogs (which you must otherwise your database will crash) why worry?

Also just because no one is using your database doesn't mean it isn't active. Oracle is alive, doing things all the time. It is collecting information whether you are pounding on it or not using it at all. If you ran an AWR report, you'd probably see all recursive sql the Oracle software runs.
Tom Kyte
January 15, 2014 - 9:13 pm UTC

(good luck Alexander ;) )

A reader, February 14, 2014 - 8:23 pm UTC

brilliant idea

Guduru Balaji, October 04, 2018 - 7:32 am UTC

I have seen somewhere that append option will work only if the table is in NOLOGGING mode, If tables is logging mode and even if we use INSERT APPEND option will not stop generating redo but generates redo. Is that true ? My question is, if APPEND option by pass the buffer cache and writes directly into the data files, how come redo will be generated ? Redo only matter of buffer cache info right ? Request you to clarify with your inputs.

Thanks,
Balaji
Connor McDonald
October 05, 2018 - 3:42 am UTC

Redo only matter of buffer cache info right ?

No.

Redo protects changes. *Any* changes. We have to protect changes to ensure we can recover the database.

I have seen somewhere that append option will work only if the table is in NOLOGGING mode


No. APPEND works *fastest* in nologging mode (but you need to very aware of the implications of that) but it will work fine in logging mode as well (and of course, we will log the changes to ensure recoverability).

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions