Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

How to determine SGA (shared_pool) fragmented

Chris Fung
Frequent Advisor

How to determine SGA (shared_pool) fragmented

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,
10 REPLIES
Hari Kumar
Trusted Contributor

Re: How to determine SGA (shared_pool) fragmented

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.
Information is Wealth ; Knowledge is Power
Hari Kumar
Trusted Contributor

Re: How to determine SGA (shared_pool) fragmented

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'
/

Information is Wealth ; Knowledge is Power
Chris Fung
Frequent Advisor

Re: How to determine SGA (shared_pool) fragmented

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
Trusted Contributor

Re: How to determine SGA (shared_pool) fragmented

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
Information is Wealth ; Knowledge is Power
Michael Steele_2
Honored Contributor

Re: How to determine SGA (shared_pool) fragmented

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.
Support Fatherhood - Stop Family Law
Jean-Luc Oudart
Honored Contributor

Re: How to determine SGA (shared_pool) fragmented

HI,

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

Rgds,
Jean-Luc
fiat lux
Stuart Abramson_2
Honored Contributor

Re: How to determine SGA (shared_pool) fragmented

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
Valued Contributor

Re: How to determine SGA (shared_pool) fragmented

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
Honored Contributor

Re: How to determine SGA (shared_pool) fragmented

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Bill Hassell
Honored Contributor

Re: How to determine SGA (shared_pool) fragmented

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.


Bill Hassell, sysadmin