lot of Redo log wait

[Former Member]

Dear all,

In st04 I see Redo log wait is this a problem. Please suggest how to solve it

Please find the details.

Size (kB) 14,352

Entries 42,123,046

Allocation retries 9,103

Alloc fault rate(%) 0.0

Redo log wait (s) 486

Log files (in use) 8 ( 8 )

DB_INST_ID Instance ID 1

DB_INSTANCE DB instance name prd

DB_NODE Database node A

DB_RELEASE Database release 10.2.0.4.0

DB_SYS_TIMESTAMP Day, Time 06.04.2010 13:07:10

DB_SYSDATE DB System date 20100406

DB_SYSTIME DB System time 130710

DB_STARTUP_TIMESTAMP Start up at 22.03.2010 03:51:02

DB_STARTDATE DB Startup date 20100322

DB_STARTTIME DB Startup time 35102

DB_ELAPSED Seconds since start 1329368

DB_SNAPDIFF Sec. btw. snapshots 1329368

DATABUFFERSIZE Size (kB) 3784704

DBUFF_QUALITY Quality (%) 96.3

DBUFF_LOGREADS Logical reads 5615573538

DBUFF_PHYSREADS Physical reads 207302988

DBUFF_PHYSWRITES Physical writes 7613263

DBUFF_BUSYWAITS Buffer busy waits 878188

DBUFF_WAITTIME Buffer wait time (s) 3583

SHPL_SIZE Size (kB) 1261568

SHPL_CAQUAL DD-cache Quality (%) 95.1

SHPL_GETRATIO SQL area getratio(%) 98.4

SHPL_PINRATIO SQL area pinratio(%) 99.9

SHPL_RELOADSPINS SQLA.Reloads/pins(%) 0.0042

LGBF_SIZE Size (kB) 14352

LGBF_ENTRIES Entries 42123046

LGBF_ALLORETR Allocation retries 9103

LGBF_ALLOFRAT Alloc fault rate(%) 0

LGBF_REDLGWT Redo log wait (s) 486

LGBF_LOGFILES Log files 8

LGBF_LOGFUSE Log files (in use) 8

CLL_USERCALLS User calls 171977181

CLL_USERCOMM User commits 1113161

CLL_USERROLLB User rollbacks 34886

CLL_RECURSIVE Recursive calls 36654755

CLL_PARSECNT Parse count 10131732

CLL_USR_PER_RCCLL User/recursive calls 4.7

CLL_RDS_PER_UCLL Log.Reads/User Calls 32.7

TIMS_BUSYWT Busy wait time (s) 389991

TIMS_CPUTIME CPU time session (s) 134540

TIMS_TIM_PER_UCLL Time/User call (ms) 3

TIMS_SESS_BUSY Sessions busy (%) 0.94

TIMS_CPUUSAGE CPU usage (%) 2.53

TIMS_CPUCOUNT Number of CPUs 4

RDLG_WRITES Redo writes 1472363

RDLG_OSBLCKWRT OS blocks written 54971892

RDLG_LTCHTIM Latching time (s) 19

RDLG_WRTTIM Redo write time (s) 2376

RDLG_MBWRITTEN MB written 25627

TABSF_SHTABSCAN Short table scans 12046230

TABSF_LGTABSCAN Long table scans 6059

TABSF_FBYROWID Table fetch by rowid 1479714431

TABSF_FBYCONTROW Fetch by contin. row 2266031

SORT_MEMORY Sorts (memory) 3236898

SORT_DISK Sorts (disk) 89

SORT_ROWS Sorts (rows) 5772889843

SORT_WAEXOPT WA exec. optim. mode 1791746

SORT_WAEXONEP WA exec. one pass m. 93

SORT_WAEXMULTP WA exec. multipass m 0

IEFF_SOFTPARSE Soft parse ratio 0.9921

IEFF_INMEM_SORT In-memory sort ratio 1

IEFF_PARSTOEXEC Parse to exec. ratio 0.9385

IEFF_PARSCPUTOTOT Parse CPU to Total 0.9948

IEFF_PTCPU_PTELPS PTime CPU / PT elps. 0.1175

Regards,

Kumar

[Former Member]

Addition to Mark's update:

Important note for Oracle 10gr2 and beyond: Per MOSC note 351857.1, starting in release 10.2 and beyond, Oracle will automatically size the log_buffer on your behalf and log_buffer cannot be changed dynamically. The automatic log_buffer sizing is based on the granule size (as determined by to ksmggranule_size):

Then no need to worry about your current size of log_buffer....

Regards,

Nick Loy

[Former Member]

Dear All,

So let me summary that In our system the log_buffer parameter is 14251008 so it is ok (sap recommend 1MB) and the redo log wait is also ok winch is 510 at present.Please suggest

Regards,

Kumar

[Former Member]

Hello,

You can check SAP Note 1289199 - Information about Oracle parameters. Log_buffer size value between 1-16M.

Maybe you need open a issue with your storage group.

Regards

[Former Member]

Are you facing any real performance issue?

Regards,

Nick Loy

[Former Member]

Dear All,

thanks , as we have just upgraded from ecc5 to ecc6 and i need to be sure that every thing is ok.the present redo log wait is

Size (kB) 14,352

Entries 54,174,526

Allocation retries 9,752

Alloc fault rate(%) 0.0

Redo log wait (s) 509

Log files (in use) 8 ( 8 )

Please suggest how to check up which value it is ok and above that we need to take any action.

Regards,

Kumar

[Former Member]

Hi,

10g R2 the log_buffer is managed for you by Oracle....

The log redo wait looks not bad for the time it's been up.

Have you a particular problem ?

Mark

[rakesh_ranjan]

Hi,

If the redo buffers are not large enough, the Oracle log-writer process waits for space to become available. This wait time becomes wait time for the end user. Hence this may cause perfromance problem at database end and hence need to be tuned.

The size of the redo log buffer is defined in the init.ora file using the 'LOG_BUFFER' parameter. The statistic 'redo log space requests' reflects the number of times a user process waits for space in the redo log buffer.

If the size of redo log buffer is not big enough causing this wait, recommendation is to increase the size of redo log buffer in such a way that the value of "redo log space requests" should be near to zero.

regards,

rakesh

[Former Member]

Dear Rakesh

In our system the log_buffer parameter is 14251008 so it is ok or not.

Please suggest.

Regards,

Kumar

[rakesh_ranjan]

14251008 = 13 MB. More than usually recommended (1MB for normal database ).

The value of log_buffer must be a multiple of redo block size (normally 512 bytes)

Too-small log_buffer size indicated by wait event "redo log space requests" and a too-large log_buffer may be indiacted by high value "log file sync" event. You need to monitor these wait event to determine wherether to increase it further.

Also, it depends on type of your database. In heavily update intensive database no matter what you do there would be redo log wait.