Re: log_buffer sizing
Date: Mon, 15 Aug 2011 13:01:01 -0700
Message-ID: <CABPz0xgaWqohfvnNYDZe0om4OzFzA5q_kMC9T+w_u0qfvyhZug_at_mail.gmail.com>
Tim,
this is a typical workload when the batch is running on the database.
DAY HR NBR_SWITCHES GB ----------- ----- ------------ -----------
13-AUG-2011 00:00 12 3.84 01:00 12 8.46 02:00 30 51.52 03:00 38 60.86 04:00 36 61.56 05:00 43 72.49 06:00 43 55.48 07:00 12 0.45 08:00 12 0.03 09:00 12 0.26 10:00 12 6.42 11:00 12 3.89 12:00 12 0.25 13:00 12 2.35 14:00 12 0.04 15:00 13 1.90 16:00 41 72.73 17:00 32 55.27 18:00 37 44.95 19:00 12 0.10 20:00 12 0.05 21:00 12 0.05 22:00 12 3.65 23:00 14 16.64 *********** ***** ----------- sum 523.25
On Mon, Aug 15, 2011 at 12:52 PM, Tim Gorman <tim_at_evdbt.com> wrote:
> Kart,
>
> What exactly are you seeing in the database that makes you say "it just
> seems too low"?
>
> Bear in mind that the size of the online redo log files has no causal
> relation to the size of the log buffer, and vice-versa. What matters is the
> rate with which redo is written, and the frequency of COMMIT commands by the
> application.
>
> For the first bit of information, try the following SQL*Plus script...
>
> clear breaks
> break on day skip 1 on hr on report
> compute sum of gb on day
> compute sum of gb on report
> col sort0 noprint
> col cnt format 999,990
> col gb format 999,990.00
> select trunc(completion_time,'HH24') sort0,
> to_char(trunc(completion_time,'HH24'),'DD-MON-YYYY') day,
> to_char(trunc(completion_time,'HH24'),'HH24')||':00' hr,
> inst_id,
> count(*) nbr_switches,
> sum(blocks*block_size)/(1048576*1024) gb
> from gv$archived_log
> group by trunc(completion_time,'HH24'),
> to_char(trunc(completion_time,'HH24'),'DD-MON-YYYY'),
> to_char(trunc(completion_time,'HH24'),'HH24')||':00',
> inst_id
> order by 1, 2, 3, 4;
>
>
> That will give you some idea of the rate and volume. Of course, this query
> may end up double-counting redo volumes if you have additional archive
> destinations set up, so be aware of that and adjust accordingly.
>
> Hope this helps...
>
> Thanks!
>
> -Tim
>
>
>
> -----Original Message-----
> *From:* K R [mailto:kp0773_at_gmail.com]
> *Sent:* Monday, August 15, 2011 01:22 PM
> *To:* Oracle-L_at_freelists.org
> *Subject:* log_buffer sizing
>
> All ,
>
> my online redo logs are 3G . This is a batch intensive datawarehouse
> application. What should i be keeping my log_buffer .
>
> currently it is set at 29425664 and it just seems too low .
>
> thanks
> Kart
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 15 2011 - 15:01:01 CDT