Oracle Database 12.2.0
Red Hat Enterprise Linux 7.4
Oracle ASM with 4 250 GB disks each
Hello Team,
I am looking for some guidance in configuring redo logs in datawarehouse environment. We are doing 1g insert per table using sqlloader of data into 8 tables daily. We are running in archivelog mode.
System information:
RAM: 16 GB
3 redo log groups with 2.097 GB each
Actual data size : 7.0 T
show parameter buffer
log_buffer big integer 7608K
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 7680M
pga_aggregate_target big integer 1589M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 4768M
sga_min_size big integer 0
sga_target big integer 4768M
unified_audit_sga_queue_size integer 10485767
Please see below readings from sysstat
redo blocks read for recovery 144,816
redo k-bytes read for recovery 72,408
redo k-bytes read for terminal recovery 0
redo entries 271,308,027
redo size 627,207,324,312
redo entries for lost write detection 0
redo size for lost write detection 0
redo size for direct writes 468,092,048,800
redo buffer allocation retries 170,530
redo wastage 344,600,544
redo write active strands 1,288,379
redo writes 1,198,573
redo writes (group 0) 9,753
redo writes (group 1) 4,152
redo writes (group 2) 0
redo writes (group 3) 0
redo writes (group 4) 0
redo writes (group 5) 0
redo writes (group 6) 0
redo writes (group 7) 0
redo writes adaptive all 1,198,573
redo writes adaptive worker 13,905
redo blocks written 1,265,631,531
redo blocks written (group 0) 31,266,913
redo blocks written (group 1) 8,586,188
redo blocks written (group 2) 0
redo blocks written (group 3) 0
redo blocks written (group 4) 0
redo blocks written (group 5) 0
redo blocks written (group 6) 0
redo blocks written (group 7) 0
redo write size count ( 4KB) 784,521
redo write size count ( 8KB) 18,991
redo write size count ( 16KB) 6,761
redo write size count ( 32KB) 5,752
redo write size count ( 64KB) 0
redo write size count ( 128KB) 12,068
redo write size count ( 256KB) 5,286
redo write size count ( 512KB) 37,998
redo write size count (1024KB) 27,046
redo write size count (inf) 300,150
redo write time 770,022
redo write time (usec) 7,700,208,438
redo write worker delay (usec) 73,479,748
redo write worker delay count 13,905
redo blocks checksummed by FG (exclusive) 1,121,087,919
redo blocks checksummed by LGWR 0
redo log space requests 8,239,135
redo log space wait time 78,107,595
redo ordering marks 200,962
redo subscn max counts 1,715,308
redo write broadcast ack time 0
redo write broadcast ack count 0
redo write broadcast lgwr post count 0
redo synch time 20,058
redo synch time (usec) 203,181,487
redo synch time overhead (usec) 519,876,065,549
redo synch time overhead count ( 2ms) 229,039
redo synch time overhead count ( 8ms) 87
redo synch time overhead count ( 32ms) 106
redo synch time overhead count (128ms) 85
redo synch time overhead count (inf) 821
redo synch writes 230,372
redo synch long waits 135
redo write gather time 103,305,985
redo write schedule time 108,149,638
redo write issue time 0
redo write finish time 7,697,697,956
redo write total time 7,815,647,763
redo synch poll writes 0
redo synch polls 0
redo write info find 230,210
redo write info find fail 72
redo KB read 632,520,089
redo KB read (memory) 0
redo KB read for transport 0
redo KB read (memory) for transport 0
redo non-durable records skipped 0
I would like to know if tuning is needed. Should the redo logs be incremented? Is there any better method to minitor my redo logs so that I can get a better estimate on the storage sizing of FRA(archivelog etc..)?
Thanks,
Joe