Solved: Tune redo log size on Oracle for Netweaver 7.01 BI...

Former Member · ‎09-17-2010

Hello,

We have a large BI system (2.5 TB) with many BI loads from multiple source system. We have a problem with the database of this system during parallel BI loads. Each day we have around 500GB redo logs generated. with sometimes only 4 minutes between redo logs switches.

Performance Indicator Description Observed Value Reference Value

free buffer waits wait time for free buffer in buffer cache considerable negligible

We already have increased the redo log size and count to 8 groups of files, each 1500MB.

The redo logs filesystems are located in a specific SAN Volume (RAID 1 on 50 FC disks).

The database is located on a RAID 5 SAN volume.

I already plan some actions this sunday:

- Order the sequence of redo logs

- Increase the size of the SGA memory.

I would like to know if it's a good idea to increase the redo size to 2GB?

Until now, archived redo logs are in a logical volume in the same VG that the DB, I think about adding a RAID 1 VG only for them but I don't know if it could help..

I'm thinking about creating 4 SAN disk, 1 VG for each redo log directory.

Have you any advice to help us increasing the DB performance?

Thank you very much.

P-S Baton

SQL> select substr(name,1,30),value from v$sysstat where name LIKE '%redo%';

SUBSTR(NAME,1,30)                VALUE
-------------------------------- ----------
redo synch writes                  32230511
redo synch time                   157941301
redo blocks read for recovery             0
redo entries                     4722721796
redo size                        4.2058E+12
redo buffer allocation retries       694366
redo wastage                     1.7405E+10
redo writer latching time             33951
redo writes                        29366293
redo blocks written              4188779881
redo write time                    31616307
redo log space requests              295837
redo log space wait time           20253938
redo log switch interrupts                0
redo ordering marks               214083396
redo subscn max counts            456039291

SQL> select to_char(min(completion_time),'HH24:MI:SS DD.MM.YYYY')
  2  comptimeMin,
  3         to_char(max(completion_time),'HH24:MI:SS DD.MM.YYYY')
  4  comptimeMax,
  5  count(recid) ArchNo,
  6  round(( max(completion_time) - min(completion_time) ) * 24 * 60 ,2)
  7  log_switch_comp_timelag,
  8  round((( max(completion_time) - min(completion_time) ) * 24 * 60),2) /
  9  count(recid) log_switch_minutes,
 10  round(sum(blocks * block_size) /1024/1024/1024 ,2) GBRedo
 11  from
 12  V$ARCHIVED_LOG
 13  where completion_time between
 14  to_date('16.09.2010 00:00:09','DD.MM.YYYY HH24:MI:SS' ) and
 15  to_date('16.09.2010 23:59:59','DD.MM.YYYY HH24:MI:SS' );

COMPTIMEMIN         COMPTIMEMAX             ARCHNO LOG_SWITCH_COMP_TIMELAG LOG_SWITCH_MINUTES     GBREDO
------------------- ------------------- ---------- ----------------------- ------------------ ----------
00:05:05 16.09.2010 23:58:16 16.09.2010        317                 1433.18         4.52107256     457.67

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED   STATUS              FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------
        11          1     136898 1572864000          2 YES        INACTIVE              16957556645 17-SEP-10
        12          1     136902 1572864000          2 YES        INACTIVE              16958397148 17-SEP-10
        13          1     136900 1572864000          2 YES        INACTIVE              16957867244 17-SEP-10
        14          1     136899 1572864000          2 YES        INACTIVE              16957644566 17-SEP-10
        15          1     136901 1572864000          2 YES        INACTIVE              16958089670 17-SEP-10
        16          1     136897 1572864000          2 YES        INACTIVE              16957473109 17-SEP-10
        17          1     136903 1572864000          2 YES        INACTIVE              16958696222 17-SEP-10
        18          1     136904 1572864000          2 NO         CURRENT               16959338566 17-SEP-10

audunlea_hansen · ‎09-17-2010

Hi.

On those Oracle User Group conferances I've joined, speakers have said redosize 2-4G are fine when there are huge changes in db.

Moving redologs out on it's own disks with a raid level optimized for write would increase performance since it no longer affect or getting affected by I/O to datafiles.

When you increase SGA > approx 8GB, you would like to use HugePages since the page address-list in memory get too big and make the system using memory slower.

Are self running db's with 40GB SGA on HugePages.

Regards

Audun

Former Member · ‎09-21-2010

Now I try to tune the system using the (useful) AWR report. I generate reports for each day starting at 00:00 and finishing at 08:00 to monitor the system load during the BI loads..

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Following the first reports, I already increased the pga_aggregate_target to 7GB and the shared_pool_size to 7GB. Those changes will be active next week. In the meantime I'll continue to check the AWR and redo stats.

Next week this instance will be migrated to a new HSV8000 with a complete 8GB fiber infrastructure and we plan to add a SSD group to icrease I/O perfs for the 4 future LUNs dedicated to the 4 redo logs directories. This should help.

In the meantime, I fond in the AWR report that the amount of Buffer waits are mainly on pure BI data blocks access, the sga/pga size increase will help, bu this is an I/O bottleneck, I don't think we'll be able to perform really useful changes...

Buffer Wait Statistics

    * ordered by wait time desc, waits desc 

Class			Waits		Total Wait Time (s)		Avg Time (ms)
data block 		199,779 	9,510 					48
undo header 		57,695 		3,409 					59
file header block 	3,151 		3,013 					956
1st level bmb 		80,227 		322 					4
segment header 		6,609 		147 					22
undo block 		3,475 		36 					10
2nd level bmb 		1,796 		15 					8
bitmap index block 	5 		0 					2

Segments by Buffer Busy Waits

    * % of Capture shows % of Buffer Busy Waits for each top segment compared
    * with total Buffer Busy Waits for all segments captured by the Snapshot 

Owner	Tablespace Name	Object Name		Subobject Name	Obj. Type		Buffer Busy Waits	% of Capture
SAPSR3 	PSAPSR3 	/BIC/B0002149000 	0000000147 	TABLE PARTITION 	22,629 			15.86
SAPSR3 	PSAPSR3 	/BIC/AZPM_O01140 	  		TABLE 			19,882 			13.93
SAPSR3 	PSAPSR3 	/BIC/AZPM_O02040 	  		TABLE 			10,809 			7.57
SAPSR3 	PSAPSR3 	NRIV 	  				TABLE 			10,586 			7.42
SAPSR3 	PSAPSR3 	/BIC/B0001757000 	0000000002 	TABLE PARTITION 	4,570 			3.20

I'll continue to keep this thread updated

Former Member · ‎09-21-2010

Hello,

So I performed some changes on the system last weekend.

As there was a levelling pending on the EVA, the storage team was not able to create me the new LUNs to split the redo log volume group and add a new volume for archived redo logs.

So, in a first time, I deleted the sga_max_size parameter, increased open_cursors to the max (2000) and increased db_cache_size to 15G.

Then I rebuilt completely the redo log architecture. Each log is now 2GB and there are 12 groups of 2 files:

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED   STATUS              FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------------
         1          1     137349 2147483648          2 YES        INACTIVE              17152070527 21-SEP-10
         2          1     137350 2147483648          2 YES        INACTIVE              17152209517 21-SEP-10
         3          1     137351 2147483648          2 YES        INACTIVE              17152423138 21-SEP-10
         4          1     137352 2147483648          2 YES        INACTIVE              17152633692 21-SEP-10
         5          1     137353 2147483648          2 YES        INACTIVE              17152784204 21-SEP-10
         6          1     137354 2147483648          2 YES        INACTIVE              17152934116 21-SEP-10
         7          1     137355 2147483648          2 YES        INACTIVE              17153072076 21-SEP-10
         8          1     137356 2147483648          2 YES        INACTIVE              17153173517 21-SEP-10
         9          1     137357 2147483648          2 YES        INACTIVE              17153295749 21-SEP-10
        10          1     137358 2147483648          2 NO         CURRENT               17153473803 21-SEP-10
        11          1     137347 2147483648          2 YES        INACTIVE              17151772359 21-SEP-10
        12          1     137348 2147483648          2 YES        INACTIVE              17151933507 21-SEP-10

12 rows selected.

SQL> SELECT V$LOG.STATUS, V$LOG.GROUP#, V$LOG.SEQUENCE#, V$LOGFILE.MEMBER FROM V$LOG, V$LOGFILE WHERE V$LOG.GROUP# = V$LOGFILE.GROUP# ORDER BY V$LOG.SEQUENCE# ASC;

STATUS               GROUP#  SEQUENCE# MEMBER
---------------- ---------- ---------- --------------------------------------------------
INACTIVE                 11     137347 /oracle/BIP/origlogA/log_g11m1.dbf
INACTIVE                 11     137347 /oracle/BIP/mirrlogA/log_g11m2.dbf
INACTIVE                 12     137348 /oracle/BIP/mirrlogB/log_g12m2.dbf
INACTIVE                 12     137348 /oracle/BIP/origlogB/log_g12m1.dbf
INACTIVE                  1     137349 /oracle/BIP/origlogA/log_g1m1.dbf
INACTIVE                  1     137349 /oracle/BIP/mirrlogA/log_g1m2.dbf
INACTIVE                  2     137350 /oracle/BIP/origlogB/log_g2m1.dbf
INACTIVE                  2     137350 /oracle/BIP/mirrlogB/log_g2m2.dbf
INACTIVE                  3     137351 /oracle/BIP/origlogA/log_g3m1.dbf
INACTIVE                  3     137351 /oracle/BIP/mirrlogA/log_g3m2.dbf
INACTIVE                  4     137352 /oracle/BIP/origlogB/log_g4m1.dbf
INACTIVE                  4     137352 /oracle/BIP/mirrlogB/log_g4m2.dbf
INACTIVE                  5     137353 /oracle/BIP/origlogA/log_g5m1.dbf
INACTIVE                  5     137353 /oracle/BIP/mirrlogA/log_g5m2.dbf
INACTIVE                  6     137354 /oracle/BIP/origlogB/log_g6m1.dbf
INACTIVE                  6     137354 /oracle/BIP/mirrlogB/log_g6m2.dbf
INACTIVE                  7     137355 /oracle/BIP/mirrlogA/log_g7m2.dbf
INACTIVE                  7     137355 /oracle/BIP/origlogA/log_g7m1.dbf
INACTIVE                  8     137356 /oracle/BIP/origlogB/log_g8m1.dbf
INACTIVE                  8     137356 /oracle/BIP/mirrlogB/log_g8m2.dbf
INACTIVE                  9     137357 /oracle/BIP/origlogA/log_g9m1.dbf
INACTIVE                  9     137357 /oracle/BIP/mirrlogA/log_g9m2.dbf
CURRENT                  10     137358 /oracle/BIP/origlogB/log_g10m1.dbf
CURRENT                  10     137358 /oracle/BIP/mirrlogB/log_g10m2.dbf

24 rows selected.

As you can see, redo log order is now correct ({orig|mirr}logA alternate with {orig|mirr}logB).

I checked the alert log to find 'Checkpoint not complete' errors. I find 10 last week. I hope that the increase of the group count and redo size ill help.

volker_borowski2 · ‎09-17-2010

Jippie !!

Someone else with such a big beast!

I just recently told about mine:

You did not mention the number of DB-WR and archiver procs you have configured.

Do you have any waits in the alert log ?

Like

... checkpoint not complete ?

--> More groups, more filesystems, striping, more db_wr

... private strand flush not complete

--> more db_wr

... all logs need archiving

--> more archiver procs ?

I do not know if it might help in your case, but the log_buffer with 14M looks somewhat standard.

Might help to double it, allthough standard recommendation is to delete it and let oracle sort it out.

Volker

Oh, just seen you are on HP-UX!

You might need to crosscheck, if multiple db_wr are allowed in this case, I don't know for HP-UX.

Multiple archivers should be no prob, but I recommend to crosscheck that as well.

Edited by: Volker Borowski on Sep 17, 2010 3:26 PM

Tune redo log size on Oracle for Netweaver 7.01 BI ABAP system.

Accepted Solutions (1)

Accepted Solutions (1)

Answers (3)

Answers (3)

Re: Problems when opening a PDF in my SAPUI5 app

Get record Collection of specific ids

Re: SAP Fiori tools- Service Modeler path argument...

What are steps and prerequisite to create a conten...

Re: special characters are not getting converted s...