How to determine SGA (shared_pool) fragmented

[Chris Fung]

Hi there,

I am just encountered a memory fragmentation problem (ORA-04031). Is there any way to track this fragmentation symptom before the problem happen ?? I am wondering whether I can develop some monitoring scripts to check the status of the shared_pool / SGA fragmentation is periodic basis.

By the way, I am using Oracle 8.1.7.0 EE (Unix version)

Please help,

Cheers,

Chris,

[Hari Kumar]

1. Utilize dbms_shared_pool package available with 7.0.13 and higher.
This package allows you to display the sizes of objects in the shared pool,
and mark them for PINNING in the SGA in order to reduce memory
fragmentation.

2. Increase the SHARED_POOL_SIZE
You need to change SHARED_POOL_SIZE because the default tends to be a low
estimate when utilizing the procedural option. As discussed earlier, one
needs to shutdown/startup when changing the SHARED_POOL_SIZE.

[Hari Kumar]

Diagnostic tools :
The V$SHARED_POOL _RESERVED dictionary view
The supplied package and procedure:
DBMS_SHARED_POOL
ABORTED_REQUEST_THRESHOLD
Guidelines: Set the parameter SHARED_POOL_RESERVED_SIZE

The explanation of some of the Coloumns of V$SHARED_POOL_RESERVED is as follows -------

REQUEST_FAILURES
NUMBER
Number of times that no memory was found to satisfy a request (that is, the number of times the error ORA-4031 occurred)

LAST_FAILURE_SIZE
NUMBER
Request size of the last failed request (that is, the request size for the last ORA-4031 error)

ABORTED_REQUEST_THRESHOLD
NUMBER
Minimum size of a request which signals an ORA-4031 error without flushing objects

ABORTED_REQUESTS
NUMBER
Number of requests that signalled an ORA-4031 error without flushing objects

LAST_ABORTED_SIZE
NUMBER
Last size of the request that returned an ORA-4031 error without flushing objects from the LRU list

The script gives wasted shared pool stats------
select
avg(v.value) shared_pool_size,
greatest(avg(s.ksmsslen) - sum(p.ksmchsiz), 0) spare_free,
to_char(
100 * greatest(avg(s.ksmsslen) - sum(p.ksmchsiz), 0) / avg(v.value),
'99999'
) || '%' wastage
from
x$ksmss s,
x$ksmsp p,
v$parameter v
where
s.inst_id = userenv('Instance') and
p.inst_id = userenv('Instance') and
p.ksmchcom = 'free memory' and
s.ksmssnam = 'free memory' and
v.name = 'shared_pool_size'
/

[Chris Fung]

Hi Mohan,

Thanks for your input and advice. According to Metalink, the problem could be fixed in some version of Oracle....and probably I need to patch up my version (8.1.7.0).

However, I am just wondering whether a regular reboot of the database would help.

By the way, the database is a data warehouse. (I have already double the shared_pool_size from 44MB to 90MB today). The symoptom did seem to fixed temporarily. I really worry the when it will be happen next time......anything I can for pre-caution ?

Cheers,

Chris

[Hari Kumar]

Hope you have done it to 8.1.7.2, which is a patch for memory leak in 8.1.7.0,fine.

The formula for the shared pool calculation is:
(Max session memory * number of concurrent users) + Total shared SQL areas + PLSQL sharable memory + Minimum 30% free space.

You please check my previous answer for using SHARED_POOL_RESERVED_AREA ,which can be one of the solution.

For more information ,
under TUNING SHARED POOL from
Oracle Tuning for Performance guide---
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch19_mem.htm#1671

[Michael Steele_2]

Run these commands please:

getconf KERNEL_BITS

kmtune -q shmmax
kmtune -q dbc_max_pct
kmtune -q dbc_min_pct

SGA gets reloaded only when needed. The more its reloaded the worse your performance. You can observe this by monitoring 'lmon', I believe. If 'lmon' is often running then the SGA is being reloaded.

[Jean-Luc Oudart]

HI,

you can use this SQL script (attached) to see your shared pool utilisation

Rgds,
Jean-Luc

[Stuart Abramson_2]

UNIX shared memory fragmentation should only occur if you are using 32-bit applications. Are you?

The HP Response Center can send you a program called "shminfo", which will show you your shared memory layout, and you can check for fragmentaion. Call them.

This whole problem should go away (in HP-UX), if you run 64-bit applications, because of the large Shared Memory space.

[Stan_17]

Hi,

ORA-4031 means your are hard parsing too much (not using bind sqls). Increasing shared pool size might fix the problem for now, but long run, you will hit the problem again and in fact, sql parsing time shoots up alluding to increase in cpu usage.

Identify the sqls that isn't using bind variables and fix it. Run statspack report for a interval of 15 mins and check the hard parses, soft parses and execute to parse ratio, this should give you an idea.

Reserve some space of shared_pool to pin huge packages, procedures etc. look at shared_pool_reserved_size parameter and dbms_shared_pool packaged for pinning objects.

hth,
Stan

[Yogeeraj_1]

hi,
*
If you are planning for a patch, think only 8.1.7.4.
*
nothing else!
*
If you are planning for an upgrade, 9i release 2 + latest patches or even 10G.
*
hope this helps too!
regards
Yogeeraj

[Bill Hassell]

There's two types of SGA fragmentation: internal (caused by Oacle), and kernel (caused by multiple 32bit applications using the same memory map). If the issue is that stop/restart of a 32bit Oracle instance errors with not enough memory, it is a kernel condition and you'll need ipcs and shminfo.
For ipcs, use ipcs -bmop
For shminfo, get a copy from ftp://contrib:9unsupp8@hprc.external.hp.com/sysadmin/programs/shminfo/ and read the docs that come with the package. shminfo will show all the other items that are scattered through the 32bit shared memory map. 64bit Oracle will have no practical limits (or fragmentation issues) but for 32bit apps, Memory Windows is the only workaround.