Home » RDBMS Server » Performance Tuning » How to calculate ideal size of log_buffer parameter? (10.2.0.4, sun spark 64 bit)
How to calculate ideal size of log_buffer parameter? [message #431708] Thu, 19 November 2009 04:02 Go to next message
halim
Messages: 100
Registered: September 2008
Senior Member


How to calculate ideal size of log_buffer parameter?

Regards
Halim

Re: How to calculate ideal size of log_buffer parameter? [message #431711 is a reply to message #431708] Thu, 19 November 2009 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
42.

Regards
Michel
Re: How to calculate ideal size of log_buffer parameter? [message #431736 is a reply to message #431708] Thu, 19 November 2009 06:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Generic advise.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams108.htm#REFRN10094
Ideal size depends on your requirement,application,volume of transaction.
For high availability scenarios with flashback
http://download.oracle.com/docs/cd/B19306_01/server.102/b25159/configbp.htm#sthref187
Rarely a database needs a log_buffer more than 1Mb (Optimal in many cases is 512k. But let us not generalize anything here).
Because, log buffer is flushed every 3 seconds or after every commit or when it gets full to some threshold.
So the question is ,
does the database generate/handle more than 1 Mb worth records every 3 seconds?
Re: How to calculate ideal size of log_buffer parameter? [message #431769 is a reply to message #431736] Thu, 19 November 2009 07:06 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Dears

Our database is performing slow from last few days .
we found that maximum time 4/5 redo log [out of 12(size 100mb per log file)] remain active.

at that time we found the below statestics....

What should we do now ?

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 19 19:48:24 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn sys@live as sysdba
Enter password:
Connected.
SQL> SELECT SUM(value) "Redo Buffer Waits" FROM v$sysstat WHERE name = 'redo log space requests';

Redo Buffer Waits
-----------------
            23555

SQL>
SQL> SELECT SUM(value) "Redo Buffer Waits" FROM v$sysstat WHERE name = 'redo log space wait time';

Redo Buffer Waits
-----------------
          1155196

SQL>  SELECT name,SUM(value) "Redo Buffer Waits" FROM v$sysstat WHERE name  like '%redo log%'
  2   group by name
  3  /

NAME                                                             Redo Buffer Waits
----------------------------------------------------------------      -----------------------------
redo log space requests                                                      23557
redo log space wait time                                                   1155201
redo log switch interrupts                                                       0

SQL>


SQL> select  name, value  
  2  from    v$parameter
  3  where   name in ('cpu_count','log_parallelism','log_buffer')
  4  /

NAME                                                                             VALUE
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------
cpu_count                                                                        128
log_buffer                                                                       1253376

SQL> show sga

Total System Global Area 1.7952E+10 bytes
Fixed Size                  2170576 bytes
Variable Size            8552915248 bytes
Database Buffers         9395240960 bytes
Redo Buffers                1294336 bytes


SQL> select
  2    min(b.first_time - a.first_time) * 1440 * 60  seconds
  3  from
  4    sys.v_$instance  i,
  5    sys.v_$log_history  a,
  6    ( select
  7        sequence#,
  8        first_time
  9      from
 10        sys.v_$log
 11      where
 12        status = 'CURRENT'
 13      union all
 14      select
 15        sequence#,
 16        first_time
 17      from
 18        sys.v_$log_history
 19    )  b
 20  where
 21    i.startup_time < a.first_time and
 22    a.first_time < b.first_time and
 23    a.sequence# + 1 = b.sequence#
 24  /

   SECONDS
----------
         2



Database is in noarchive mode
and average session about 2500 .

Regards
Halim

[Updated on: Thu, 19 November 2009 07:17]

Report message to a moderator

Re: How to calculate ideal size of log_buffer parameter? [message #431773 is a reply to message #431769] Thu, 19 November 2009 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase redo LOG FILE size not redo buffer (for the moment).

Regards
Michel
Re: How to calculate ideal size of log_buffer parameter? [message #431778 is a reply to message #431773] Thu, 19 November 2009 07:25 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

thanks michel

but in a pick our Enterprize Manager's warning recomended that
need to increase 'log buffer' 13mb. for wait of log buffer.

and
we already add three log files 9+3=12(size 100mb).
now what we need to do ?
Adding more file with same size ?
or
increase the redo log file size?

regards
Halim

[Updated on: Thu, 19 November 2009 07:26]

Report message to a moderator

Re: How to calculate ideal size of log_buffer parameter? [message #431780 is a reply to message #431778] Thu, 19 November 2009 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but in a pick our Enterprize Manager's warning recomended that
need to increase 'log buffer' 13mb. for wait of log buffer.

quoting BlackSwan:
Those who live by the GUI, die by the GUI.


Quote:
we already add three log files 9+3=12(size 100mb).

Not add, INCREASE size.

Regards
Michel
Re: How to calculate ideal size of log_buffer parameter? [message #434033 is a reply to message #431708] Sun, 06 December 2009 22:13 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
The most appropriate answar is ....

http://www.dba-oracle.com/t_log_buffer_optimal_size.htm

Run a AWR report check first if you are facing any logfile sync waits
Re: How to calculate ideal size of log_buffer parameter? [message #434041 is a reply to message #434033] Sun, 06 December 2009 23:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you read the whole topic you will see there is no log buffer problem here but log file one.

But I know now why your answer are often out of scope and irrelevant.

Regards
Michel
Re: How to calculate ideal size of log_buffer parameter? [message #434046 is a reply to message #431708] Mon, 07 December 2009 00:46 Go to previous messageGo to next message
nets_edge@cox.net
Messages: 17
Registered: December 2009
Location: home, Arizona
Junior Member
Alter system switch logfile;
alter system checkpoint;

Now only one online log group is active. Razz

Nets Edge
Re: How to calculate ideal size of log_buffer parameter? [message #434047 is a reply to message #431708] Mon, 07 December 2009 00:47 Go to previous message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Run AWR in particular intervals.
If you find log file waits either increatse the size of log file or add 2 or 3 new groups.
Previous Topic: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast
Next Topic: can we write below query in a better way
Goto Forum:
  


Current Time: Sat Dec 03 19:53:36 CST 2016

Total time taken to generate the page: 0.14563 seconds