Oracle Database Discussions

Hellom everybody,

I am using oracle 9i data base. My data base is running in ARCHIVLOG mode. my oracle patrition is filling so fast rate. because of for every 10 mins one archvie file is generatting. even though no body is using the application also these files are creating at same rate. i have used oracle log mining tool to get the redo logs from my server. i was abel to see there are a lot of queries executing with SYS user. i am posting some queries for reference. i dont know much about these queries can some body help me. my main concern is why these archvie file are creating such faster rate. how to reduce the rate. currently we have three groups for redologs. adding more redo log groups or increasing the redo log file size make difference.



Thanks
Shekhar
set transaction read write;
update "SYS"."TAB$" set "DATAOBJ#" = '85252', "TS#" = '9', "FILE#" = '9', "BLOCK
#" = '649', "BOBJ#" = NULL, "TAB#" = NULL, "COLS" = '32', "CLUCOLS" = NULL, "PCT

SQL_REDO
--------------------------------------------------------------------------------
FREE$" = '10', "PCTUSED$" = '40', "INITRANS" = '1', "MAXTRANS" = '255', "FLAGS"
= '529', "AUDIT$" = '--------------------------------', "ROWCNT" = '57', "BLKCNT
" = '3', "EMPCNT" = '0', "AVGSPC" = '0', "CHNCNT" = '0', "AVGRLN" = '240', "AVGS
PC_FLB" = '0', "FLBCNT" = '0', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR')
, "SAMPLESIZE" = '57', "DEGREE" = NULL, "INSTANCES" = NULL, "INTCOLS" = '34', "K
ERNELCOLS" = '32', "PROPERTY" = '538968064', "TRIGFLAG" = '0', "SPARE1" = '736',
"SPARE2" = NULL, "SPARE4" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR')
where "OBJ#" = '85252' and "DATAOBJ#" = '85252' and "TS#" = '9' and "FILE#" = '9
' and "BLOCK#" = '649' and "BOBJ#" IS NULL and "TAB#" IS NULL and "COLS" = '32'
and "CLUCOLS" IS NULL and "PCTFREE$" = '10' and "PCTUSED$" = '40' and "INITRANS"
= '1' and "MAXTRANS" = '255' and "FLAGS" = '529' and "AUDIT$" = '--------------

SQL_REDO
--------------------------------------------------------------------------------
------------------' and "ROWCNT" = '57' and "BLKCNT" = '3' and "EMPCNT" = '0' an
d "AVGSPC" = '0' and "CHNCNT" = '0' and "AVGRLN" = '240' and "AVGSPC_FLB" = '0'
and "FLBCNT" = '0' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SA
MPLESIZE" = '57' and "DEGREE" IS NULL and "INSTANCES" IS NULL and "INTCOLS" = '3
4' and "KERNELCOLS" = '32' and "PROPERTY" = '538968064' and "TRIGFLAG" = '0' and
"SPARE1" = '736' and "SPARE2" IS NULL and "SPARE4" IS NULL and "SPARE6" = TO_DA
TE('03-DEC-08', 'DD-MON-RR') and ROWID = 'AAAAACAABAAAJqoAAD';

update "SYS"."IND$" set "DATAOBJ#" = '85257', "TS#" = '29', "FILE#" = '37', "BLO
CK#" = '2185', "INDMETHOD#" = '0', "COLS" = '1', "PCTFREE$" = '10', "INITRANS" =
'2', "MAXTRANS" = '255', "PCTTHRES$" = NULL, "TYPE#" = '1', "FLAGS" = '2050', "

SQL_REDO
--------------------------------------------------------------------------------
PROPERTY" = '0', "BLEVEL" = '0', "LEAFCNT" = '1', "DISTKEY" = '55', "LBLKKEY" =
'1', "DBLKKEY" = '1', "CLUFAC" = '3', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-M
ON-RR'), "SAMPLESIZE" = '57', "ROWCNT" = '57', "INTCOLS" = '1', "DEGREE" = NULL,
"INSTANCES" = NULL, "TRUNCCNT" = NULL, "SPARE1" = '1', "SPARE2" = NULL, "SPARE4
" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') where "BO#" = '85252' and
"OBJ#" = '85257' and "DATAOBJ#" = '85257' and "TS#" = '29' and "FILE#" = '37' a
nd "BLOCK#" = '2185' and "INDMETHOD#" = '0' and "COLS" = '1' and "PCTFREE$" = '1
0' and "INITRANS" = '2' and "MAXTRANS" = '255' and "PCTTHRES$" IS NULL and "TYPE
#" = '1' and "FLAGS" = '2050' and "PROPERTY" = '0' and "BLEVEL" = '0' and "LEAFC
NT" = '1' and "DISTKEY" = '55' and "LBLKKEY" = '1' and "DBLKKEY" = '1' and "CLUF
AC" = '3' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SAMPLESIZE"

SQL_REDO
--------------------------------------------------------------------------------
= '57' and "ROWCNT" = '57' and "INTCOLS" = '1' and "DEGREE" IS NULL and "INSTAN
CES" IS NULL and "TRUNCCNT" IS NULL and "SPARE1" = '1' and "SPARE2" IS NULL and
"SPARE4" IS NULL and "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') and ROWID = 'A
AAAACAABAAAJqoAAQ';

commit;
set transaction read write;
update "SYS"."TAB$" set "DATAOBJ#" = '85252', "TS#" = '9', "FILE#" = '9', "BLOCK
#" = '649', "BOBJ#" = NULL, "TAB#" = NULL, "COLS" = '32', "CLUCOLS" = NULL, "PCT
FREE$" = '10', "PCTUSED$" = '40', "INITRANS" = '1', "MAXTRANS" = '255', "FLAGS"
= '529', "AUDIT$" = '--------------------------------', "ROWCNT" = '57', "BLKCNT

SQL_REDO
--------------------------------------------------------------------------------
" = '3', "EMPCNT" = '0', "AVGSPC" = '0', "CHNCNT" = '0', "AVGRLN" = '240', "AVGS
PC_FLB" = '0', "FLBCNT" = '0', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR')
, "SAMPLESIZE" = '57', "DEGREE" = NULL, "INSTANCES" = NULL, "INTCOLS" = '34', "K
ERNELCOLS" = '32', "PROPERTY" = '538968064', "TRIGFLAG" = '0', "SPARE1" = '736',
"SPARE2" = NULL, "SPARE4" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR')
where "OBJ#" = '85252' and "DATAOBJ#" = '85252' and "TS#" = '9' and "FILE#" = '9
' and "BLOCK#" = '649' and "BOBJ#" IS NULL and "TAB#" IS NULL and "COLS" = '32'
and "CLUCOLS" IS NULL and "PCTFREE$" = '10' and "PCTUSED$" = '40' and "INITRANS"
= '1' and "MAXTRANS" = '255' and "FLAGS" = '529' and "AUDIT$" = '--------------
------------------' and "ROWCNT" = '57' and "BLKCNT" = '3' and "EMPCNT" = '0' an
d "AVGSPC" = '0' and "CHNCNT" = '0' and "AVGRLN" = '240' and "AVGSPC_FLB" = '0'

SQL_REDO
--------------------------------------------------------------------------------
and "FLBCNT" = '0' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SA
MPLESIZE" = '57' and "DEGREE" IS NULL and "INSTANCES" IS NULL and "INTCOLS" = '3
4' and "KERNELCOLS" = '32' and "PROPERTY" = '538968064' and "TRIGFLAG" = '0' and
"SPARE1" = '736' and "SPARE2" IS NULL and "SPARE4" IS NULL and "SPARE6" = TO_DA
TE('03-DEC-08', 'DD-MON-RR') and ROWID = 'AAAAACAABAAAJqoAAD';

update "SYS"."IND$" set "DATAOBJ#" = '85258', "TS#" = '29', "FILE#" = '37', "BLO
CK#" = '2313', "INDMETHOD#" = '0', "COLS" = '1', "PCTFREE$" = '10', "INITRANS" =
'2', "MAXTRANS" = '255', "PCTTHRES$" = NULL, "TYPE#" = '1', "FLAGS" = '2050', "
PROPERTY" = '0', "BLEVEL" = '0', "LEAFCNT" = '1', "DISTKEY" = '44', "LBLKKEY" =
'1', "DBLKKEY" = '1', "CLUFAC" = '10', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-

SQL_REDO
--------------------------------------------------------------------------------
MON-RR'), "SAMPLESIZE" = '57', "ROWCNT" = '57', "INTCOLS" = '1', "DEGREE" = NULL
, "INSTANCES" = NULL, "TRUNCCNT" = NULL, "SPARE1" = '1', "SPARE2" = NULL, "SPARE
4" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') where "BO#" = '85252' an
d "OBJ#" = '85258' and "DATAOBJ#" = '85258' and "TS#" = '29' and "FILE#" = '37'
and "BLOCK#" = '2313' and "INDMETHOD#" = '0' and "COLS" = '1' and "PCTFREE$" = '
10' and "INITRANS" = '2' and "MAXTRANS" = '255' and "PCTTHRES$" IS NULL and "TYP
E#" = '1' and "FLAGS" = '2050' and "PROPERTY" = '0' and "BLEVEL" = '0' and "LEAF
CNT" = '1' and "DISTKEY" = '44' and "LBLKKEY" = '1' and "DBLKKEY" = '1' and "CLU
FAC" = '10' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SAMPLESIZ
E" = '57' and "ROWCNT" = '57' and "INTCOLS" = '1' and "DEGREE" IS NULL and "INST
ANCES" IS NULL and "TRUNCCNT" IS NULL and "SPARE1" = '1' and "SPARE2" IS NULL an

SQL_REDO
--------------------------------------------------------------------------------
d "SPARE4" IS NULL and "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') and ROWID =
'AAAAACAABAAAJqoAAR';

commit;
set transaction read write;
update "SYS"."TAB$" set "DATAOBJ#" = '85252', "TS#" = '9', "FILE#" = '9', "BLOCK
#" = '649', "BOBJ#" = NULL, "TAB#" = NULL, "COLS" = '32', "CLUCOLS" = NULL, "PCT
FREE$" = '10', "PCTUSED$" = '40', "INITRANS" = '1', "MAXTRANS" = '255', "FLAGS"
= '529', "AUDIT$" = '--------------------------------', "ROWCNT" = '57', "BLKCNT
" = '3', "EMPCNT" = '0', "AVGSPC" = '0', "CHNCNT" = '0', "AVGRLN" = '240', "AVGS
PC_FLB" = '0', "FLBCNT" = '0', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR')

SQL_REDO
--------------------------------------------------------------------------------
, "SAMPLESIZE" = '57', "DEGREE" = NULL, "INSTANCES" = NULL, "INTCOLS" = '34', "K
ERNELCOLS" = '32', "PROPERTY" = '538968064', "TRIGFLAG" = '0', "SPARE1" = '736',
"SPARE2" = NULL, "SPARE4" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR')
where "OBJ#" = '85252' and "DATAOBJ#" = '85252' and "TS#" = '9' and "FILE#" = '9
' and "BLOCK#" = '649' and "BOBJ#" IS NULL and "TAB#" IS NULL and "COLS" = '32'
and "CLUCOLS" IS NULL and "PCTFREE$" = '10' and "PCTUSED$" = '40' and "INITRANS"
= '1' and "MAXTRANS" = '255' and "FLAGS" = '529' and "AUDIT$" = '--------------
------------------' and "ROWCNT" = '57' and "BLKCNT" = '3' and "EMPCNT" = '0' an
d "AVGSPC" = '0' and "CHNCNT" = '0' and "AVGRLN" = '240' and "AVGSPC_FLB" = '0'
and "FLBCNT" = '0' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SA
MPLESIZE" = '57' and "DEGREE" IS NULL and "INSTANCES" IS NULL and "INTCOLS" = '3

SQL_REDO
--------------------------------------------------------------------------------
4' and "KERNELCOLS" = '32' and "PROPERTY" = '538968064' and "TRIGFLAG" = '0' and
"SPARE1" = '736' and "SPARE2" IS NULL and "SPARE4" IS NULL and "SPARE6" = TO_DA
TE('03-DEC-08', 'DD-MON-RR') and ROWID = 'AAAAACAABAAAJqoAAD';

update "SYS"."IND$" set "DATAOBJ#" = '85452', "TS#" = '29', "FILE#" = '37', "BLO
CK#" = '13449', "INDMETHOD#" = '0', "COLS" = '1', "PCTFREE$" = '10', "INITRANS"
= '2', "MAXTRANS" = '255', "PCTTHRES$" = NULL, "TYPE#" = '1', "FLAGS" = '2050',
"PROPERTY" = '0', "BLEVEL" = '0', "LEAFCNT" = '1', "DISTKEY" = '1', "LBLKKEY" =
'1', "DBLKKEY" = '3', "CLUFAC" = '3', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-M
ON-RR'), "SAMPLESIZE" = '57', "ROWCNT" = '57', "INTCOLS" = '1', "DEGREE" = NULL,
"INSTANCES" = NULL, "TRUNCCNT" = NULL, "SPARE1" = '1', "SPARE2" = NULL, "SPARE4

SQL_REDO
--------------------------------------------------------------------------------
" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') where "BO#" = '85252' and
"OBJ#" = '85452' and "DATAOBJ#" = '85452' and "TS#" = '29' and "FILE#" = '37' a
nd "BLOCK#" = '13449' and "INDMETHOD#" = '0' and "COLS" = '1' and "PCTFREE$" = '
10' and "INITRANS" = '2' and "MAXTRANS" = '255' and "PCTTHRES$" IS NULL and "TYP
E#" = '1' and "FLAGS" = '2050' and "PROPERTY" = '0' and "BLEVEL" = '0' and "LEAF
CNT" = '1' and "DISTKEY" = '1' and "LBLKKEY" = '1' and "DBLKKEY" = '3' and "CLUF
AC" = '3' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SAMPLESIZE"
= '57' and "ROWCNT" = '57' and "INTCOLS" = '1' and "DEGREE" IS NULL and "INSTAN
CES" IS NULL and "TRUNCCNT" IS NULL and "SPARE1" = '1' and "SPARE2" IS NULL and
"SPARE4" IS NULL and "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') and ROWID = 'A
AAAACAABAAAJqoAAS';

SQL_REDO
--------------------------------------------------------------------------------

commit;
set transaction read write;
update "SYS"."TAB$" set "DATAOBJ#" = '85252', "TS#" = '9', "FILE#" = '9', "BLOCK
#" = '649', "BOBJ#" = NULL, "TAB#" = NULL, "COLS" = '32', "CLUCOLS" = NULL, "PCT
FREE$" = '10', "PCTUSED$" = '40', "INITRANS" = '1', "MAXTRANS" = '255', "FLAGS"
= '529', "AUDIT$" = '--------------------------------', "ROWCNT" = '57', "BLKCNT
" = '3', "EMPCNT" = '0', "AVGSPC" = '0', "CHNCNT" = '0', "AVGRLN" = '240', "AVGS
PC_FLB" = '0', "FLBCNT" = '0', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR')
, "SAMPLESIZE" = '57', "DEGREE" = NULL, "INSTANCES" = NULL, "INTCOLS" = '34', "K
ERNELCOLS" = '32', "PROPERTY" = '538968064', "TRIGFLAG" = '0', "SPARE1" = '736',

SQL_REDO
--------------------------------------------------------------------------------
"SPARE2" = NULL, "SPARE4" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR')
where "OBJ#" = '85252' and "DATAOBJ#" = '85252' and "TS#" = '9' and "FILE#" = '9
' and "BLOCK#" = '649' and "BOBJ#" IS NULL and "TAB#" IS NULL and "COLS" = '32'
and "CLUCOLS" IS NULL and "PCTFREE$" = '10' and "PCTUSED$" = '40' and "INITRANS"
= '1' and "MAXTRANS" = '255' and "FLAGS" = '529' and "AUDIT$" = '--------------
------------------' and "ROWCNT" = '57' and "BLKCNT" = '3' and "EMPCNT" = '0' an
d "AVGSPC" = '0' and "CHNCNT" = '0' and "AVGRLN" = '240' and "AVGSPC_FLB" = '0'
and "FLBCNT" = '0' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SA
MPLESIZE" = '57' and "DEGREE" IS NULL and "INSTANCES" IS NULL and "INTCOLS" = '3
4' and "KERNELCOLS" = '32' and "PROPERTY" = '538968064' and "TRIGFLAG" = '0' and
"SPARE1" = '736' and "SPARE2" IS NULL and "SPARE4" IS NULL and "SPARE6" = TO_DA

SQL_REDO
--------------------------------------------------------------------------------
TE('03-DEC-08', 'DD-MON-RR') and ROWID = 'AAAAACAABAAAJqoAAD';

update "SYS"."IND$" set "DATAOBJ#" = '85260', "TS#" = '29', "FILE#" = '37', "BLO
CK#" = '2569', "INDMETHOD#" = '0', "COLS" = '0', "PCTFREE$" = '10', "INITRANS" =
'2', "MAXTRANS" = '255', "PCTTHRES$" = NULL, "TYPE#" = '1', "FLAGS" = '2050', "
PROPERTY" = '16', "BLEVEL" = '0', "LEAFCNT" = '1', "DISTKEY" = '38', "LBLKKEY" =
'1', "DBLKKEY" = '1', "CLUFAC" = '21', "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD
-MON-RR'), "SAMPLESIZE" = '47', "ROWCNT" = '47', "INTCOLS" = '1', "DEGREE" = NUL
L, "INSTANCES" = NULL, "TRUNCCNT" = NULL, "SPARE1" = '1', "SPARE2" = NULL, "SPAR
E4" = NULL, "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') where "BO#" = '85252' a
nd "OBJ#" = '85260' and "DATAOBJ#" = '85260' and "TS#" = '29' and "FILE#" = '37'

SQL_REDO
--------------------------------------------------------------------------------
and "BLOCK#" = '2569' and "INDMETHOD#" = '0' and "COLS" = '0' and "PCTFREE$" =
'10' and "INITRANS" = '2' and "MAXTRANS" = '255' and "PCTTHRES$" IS NULL and "TY
PE#" = '1' and "FLAGS" = '2050' and "PROPERTY" = '16' and "BLEVEL" = '0' and "LE
AFCNT" = '1' and "DISTKEY" = '38' and "LBLKKEY" = '1' and "DBLKKEY" = '1' and "C
LUFAC" = '21' and "ANALYZETIME" = TO_DATE('24-DEC-08', 'DD-MON-RR') and "SAMPLES
IZE" = '47' and "ROWCNT" = '47' and "INTCOLS" = '1' and "DEGREE" IS NULL and "IN
STANCES" IS NULL and "TRUNCCNT" IS NULL and "SPARE1" = '1' and "SPARE2" IS NULL
and "SPARE4" IS NULL and "SPARE6" = TO_DATE('03-DEC-08', 'DD-MON-RR') and ROWID
= 'AAAAACAABAAAJqoAAT';