Home » RDBMS Server » Server Administration » Shared Pool Resize
| Shared Pool Resize [message #149558] |
Fri, 02 December 2005 04:37  |
smriti_pal
Messages: 3
Registered: November 2005
Location: India
|
Junior Member |
|
|
Hi,
Is it possible that shared pool be allocated more than what mentioned in init file by parameter SHARED_POOL_SIZE when using db_block_buffer and shared_pool_size.
It's urgent.
Thanks,
Sim
|
|
|
|
| Re: Shared Pool Resize [message #149569 is a reply to message #149558] |
Fri, 02 December 2005 05:54  |
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
|
Member |
|
|
statistic and setting
SET SERVEROUTPUT ON;
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 < 90 then
dbms_output.put_line('. * HINT: Buffer Cache too low! Increase the DB Block Buffer value. *');
END IF;
if redlog > 100 then
dbms_output.put_line('. * HINT: Log Buffer value is rather low! *');
END IF;
END;
/
sal2
williams
|
|
|
|
| Re: Shared Pool Resize [message #149592 is a reply to message #149569] |
Fri, 02 December 2005 07:04 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Quote: |
if bufcac < 90 then
dbms_output.put_line('. * HINT: Buffer Cache too low! Increase the DB Block Buffer value. *');
|
I thought tuning by hit-ratios was something we all agreed not to do anymore...
|
|
|
|
| Re: Shared Pool Resize [message #149642 is a reply to message #149558] |
Fri, 02 December 2005 10:12 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Hit Ratios:
BOO
HECKLE
HISS
BOO
@Frank
Not sure there is such a thing as "we all agree", unless of course you mean by "we" to mean regular forum readers. I'm currently at a place where the "standard" (meaning cannot deviate from) is to separate index and table data into tablespaces for performance, to regularly rebuild indexes, and to stay on outdated oracle 8i. Quite frustrating.
The "New Ways" do indeed seem to take time to filter to all corners of the oracle world. Especially it seems the older oracle shops.
@OP:
None of this addresses your question. My suggestion is to use the automated memory management capabilities of the later oracle releases, 10gR1 and up. In 9i, some parameters are dynamically changeable, but to be persistent you must be using the spfile not init.ora. And the reference guide lists which are dynamic and which aren't. But db_block_buffers is an 8i thing right? Check the 8i docs, should be easily lookupable.
And the OP question, if and how to change parameters, has to do with server administration, so moving it there.
|
|
|
|
|
|
| Re: Shared Pool Resize [message #149677 is a reply to message #149558] |
Fri, 02 December 2005 17:23 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
I believe it can use more than specified depending on 'memory granularity' but the granularity is usually small (depending on O/S I *think*).
I like using SGA_TARGET and SGA_MAX_SIZE. I had a TAR on this and Oracle promised it worked. I set the target to be 10 Gigs and max to be 12 gigs. It was only using 3 gigs, plus 3 gigs for pga. There are a bunch of other 'pools' in memory : Java, pga, 'other sized blocks' (I think they mean other than the defined block size), shared pool, large pool, buffer cache, streams pool, log buffer, Process-private memory, such as memory used for sorting and hash joins.
Check the Performance Tuning Guide Part 3, chapter 7.
and the Oracle Database Reference Part 1 chapter 1 both available from Oracle.
As per oracle's documentation:
********
SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory.
You can monitor utilization of the shared pool by querying the view V$SGASTAT.
See Also:
¦ Oracle Database Performance Tuning Guide for more information on setting this parameter
¦ "SHARED_POOL_SIZE" on page 1-134
Property Description
Parameter type Big integer
Syntax SHARED_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default
is 0 (internally determined by the Oracle Database). If the parameter is
specified, then the user-specified value indicates a minimum value for
the memory pool.
If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the
nearest granule size.
If SGA_TARGET is not set (64-bit platforms): 84 M, rounded up to the
nearest granule size.
For considerations when dealing with database instances using ASM,
see "SHARED_POOL_SIZE and Automatic Storage Management" on page 1-135.
Modifiable ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent
See Also:
¦ Oracle Database Performance Tuning Guide for more information on setting this parameter
¦ Oracle Database Upgrade Guide for information on parallel execution message buffers
SGA_TARGET Property Description
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)
Modifiable ALTER SYSTEM
Range of values 64 to operating system-dependent
Basic Yes
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
■ Buffer cache (DB_CACHE_SIZE)
■ Shared pool (SHARED_POOL_SIZE)
■ Large pool (LARGE_POOL_SIZE)
■ Java pool (JAVA_POOL_SIZE)
■ Streams pool (STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those
values are used as minimum levels by Automatic Shared Memory Management.
would set minimum values if an application component needs a minimum amount
memory to function properly.
The following pools are manually sized components and are not affected by
Automatic Shared Memory Management:
■ Log buffer
■ Other buffer caches, such as KEEP, RECYCLE, and other block sizes
■ Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_
TARGET when Automatic Shared Memory Management computes the values of
automatically tuned memory pools.
See Also:
■ Oracle Database Concepts for information on automatic SGA
management
■ Oracle Database Administrator's Guide for information on managing
the SGA
******
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
Property Description
Parameter type Integer
Default value Derived: (1.1 * PROCESSES) + 5
Modifiable No
Range of values 1 to 231
Basic Yes
See Also:
¦ Oracle Database Concepts for more information on memory structures and processes
Property Description
Parameter type Big integer
Syntax SGA_MAX_SIZE = integer [K | M | G]
Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Modifiable No
Range of values 0 to operating system-dependent
[Updated on: Fri, 02 December 2005 20:15] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun Jan 26 13:23:26 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
