Re: log_buffer sizing

serge wrote:
> select 'V$PARAMETER' "TABLE NAME", name,
> to_number(value, '9999999') "VALUE"
> from v$parameter
> where name = 'log_buffer'
> UNION
> select 'V$SGASTAT' "TABLE NAME", name, bytes
> from v$sgastat
> where name='log_buffer'
>
> hi everyone. What is the purpose of this query? Is it comparing the
> value of the log_buffer from memory(v$parameter) to the value of the
> log_buffer is set to in the initialization file(v$sgastat)?
> why is it that with certain dbs these values are the same and with
> other dbs they vary?

V$SGASTAT reports actual memory usage, where V$PARAMETER reports the initialisation parameter setting, which you have backwards in your question. That being said in 9i and later releases of Oracle the shared pool, large pool, java pool, buffer cache and log buffer are allocated in units of granules; a granule can vary in size depending upon the overall size of the SGA. For most systems an SGA under 128 MB sets a granule size of 4 MB which quadruples (16 MB/granule) for SGAs 128 MB and larger. It is likely this disparity in sizing is due to rounding up the setting to the nearest granule; some settings might need no adjustment (providing the matching values) or you may have older releases of Oracle where sga_max_size is not used/available (8.1.7.4, possibly).

Without more information your question is answered as best as it can be answered. Provide the release of Oracle (all four numbers) for these databases where you see discrepancies (and for those databases where you don't), the operating system on which these databases run and possibly provide the results from this query on those same databases. Only then can your question be answered definitively.

David Fitzjarrell Received on Sat Oct 21 2006 - 22:42:52 CDT