Monitoring Automatic Shared Memory Management

select
component,
oper_type,
oper_mode,
initial_size/1024/1024 “Initial”,
TARGET_SIZE/1024/1024 “Target”,
FINAL_SIZE/1024/1024 “Final”,
status
from
v$sga_resize_ops;

select
component,
current_size/1024/1024 “CURRENT_SIZE”,
min_size/1024/1024 “MIN_SIZE”,
user_specified_size/1024/1024 “USER_SPECIFIED_SIZE”,
last_oper_type “TYPE”
from
v$sga_dynamic_components;

Advertisements

SGA related init params

Auto tuned SGA parameters

* db_cache_size
* shared_pool_size
* large_pool_size
* java_pool_size

These parameters are called auto tuned because automatic shared memory managment can dynamically change the sizes of these pools if it is enabled.

DB_CACHE_SIZE
The value of this parameter affects the size of the SGA: It sets the size of the default buffer pool.With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory as required. See also sga_target.
JAVA_POOL_SIZE
The value of this parameter affects the size of the SGA.With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory as required. See also sga_target.
SHARED_POOL_SIZE
The value of this parameter affects the size of the SGA or more appropriately the size of the shared pool within the SGA.
Apparently, when installing JServer, this parameter must at least be set to 24M.
LARGE_POOL_SIZE
With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory as required. See also sga_target.

Manual SGA parameters

* db_keep_cache_size
* db_recycle_cache_size
* db_NNk_cache_size
NN being one of 2, 4, 8, 16, 32
* log_buffer
* streams_pool_size

DB_KEEP_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_RECYCLE_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
LOG_BUFFER
Up to 9i
The value of this parameter defines the size of the redo log buffer. As the redo log buffer is part of the SGA, it affects the size of the SGA as well.
Setting this value to anything greater than 3M is useless as the log buffer is flushed anyway when it is filled up to 1M or when it is reaches one third of its capacity, whichever comes first. (Thanks to William White who notified me of an error here).

10g
From 10g onwards, there is a lot more to do about the initialization parameter log_buffer. Also, Metalink note 351857.1 states that the size of the log buffer cannot be changed with this paramter from 10gR2, instead, the size will be set by Oracle.
STREAMS_POOL_SIZE
DB_BLOCK_BUFFERS
The value of this parameter affects the size of the SGA, or more precisely, the size of the buffer cache. This parameter is deprecated since 9i, db_cache_size should be used instead.
With Oracle 10g, one can turn on Automatic Shared Memory Management which distributes shared memory (of which the buffer cache is part) as required. See also sga_target.
DB_BLOCK_CHECKSUM
See checking block integrity before writing to disk.
DB_BLOCK_SIZE
Determines the size of a database blocks.
SGA_MAX_SIZE
sga_max_size sets the maximum value for sga_target
If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.

SGA_TARGET
This parameter is new with Oracle 10g. It specifies the total amount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components – such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache – as required.
This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitely anymore.
sga_target cannot be higher than sga_max_size.

Display database SGA statistics

DECLARE
libcac NUMBER(10,2);
rowcac NUMBER(10,2);
bufcac NUMBER(10,2);
redlog NUMBER(10,2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;
BEGIN
SELECT VALUE INTO redlog FROM v$sysstat
WHERE name = ‘redo log space requests’;
SELECT 100*(SUM(pins)-SUM(reloads))/SUM(pins) INTO libcac FROM v$librarycache;
SELECT 100*(SUM(gets)-SUM(getmisses))/SUM(gets) INTO rowcac FROM v$rowcache;
SELECT 100*(cur.VALUE + con.VALUE – phys.VALUE)/(cur.VALUE + con.VALUE) INTO bufcac
FROM v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
WHERE cur.statistic# = ncu.statistic#
AND ncu.name = ‘db block gets’
AND con.statistic# = nco.statistic#
AND nco.name = ‘consistent gets’
AND phys.statistic# = nph.statistic#
AND nph.name = ‘physical reads’;
SELECT VALUE INTO spsize FROM v$parameter WHERE name = ‘shared_pool_size’;
SELECT VALUE INTO blkbuf FROM v$parameter WHERE name = ‘db_block_buffers’;
SELECT VALUE INTO logbuf FROM v$parameter WHERE name = ‘log_buffer’;
DBMS_OUTPUT.put_line(‘> SGA CACHE STATISTICS’);
DBMS_OUTPUT.put_line(‘> ********************’);
DBMS_OUTPUT.put_line(‘> SQL Cache Hit rate = ‘||libcac);
DBMS_OUTPUT.put_line(‘> Dict Cache Hit rate = ‘||rowcac);
DBMS_OUTPUT.put_line(‘> Buffer Cache Hit rate = ‘||bufcac);
DBMS_OUTPUT.put_line(‘> Redo Log space requests = ‘||redlog);
DBMS_OUTPUT.put_line(‘> ‘);
DBMS_OUTPUT.put_line(‘> INIT.ORA SETTING’);
DBMS_OUTPUT.put_line(‘> ****************’);
DBMS_OUTPUT.put_line(‘> Shared Pool Size = ‘||spsize||’ Bytes’);
DBMS_OUTPUT.put_line(‘> DB Block Buffer = ‘||blkbuf||’ Blocks’);
DBMS_OUTPUT.put_line(‘> Log Buffer = ‘||logbuf||’ Bytes’);
DBMS_OUTPUT.put_line(‘> ‘);
IF
libcac < 99 THEN DBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
IF
rowcac < 85 THEN DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
IF
bufcac 100 THEN DBMS_OUTPUT.put_line(‘*** HINT: Log Buffer value is rather low!’);
END IF;
END;

SGA INFO

SQL> desc v$sgainfo
Name Null? Type
—————————————– ——– —————————-
NAME VARCHAR2(32)
BYTES NUMBER
RESIZEABLE VARCHAR2(3)

SQL>select * from v$sgainfo;

NAME BYTES RESIZEABLE
——————————– ———- —
Fixed SGA Size 2073128 No
Redo Buffers 31477760 No
Buffer Cache Size 1962934272 Yes
Shared Pool Size 788529152 Yes
Large Pool Size 33554432 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 50331648 Yes
Granule Size 16777216 No
Maximum SGA Size 3456106496 No
Startup overhead in Shared Pool 117440512 No
Free SGA Memory Available 570425344

11 rows selected.