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.
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.
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.
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....
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?
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
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,
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,
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
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 .
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
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.
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.
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.
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.
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
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).