topic Re: How to determine SGA (shared_pool) fragmented in Operating System - HP-UX

How to determine SGA (shared_pool) fragmented

Chris Fung — Thu, 02 Oct 2003 23:15:30 GMT

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,

Re: How to determine SGA (shared_pool) fragmented

Hari Kumar — Thu, 02 Oct 2003 23:43:25 GMT

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.

Re: How to determine SGA (shared_pool) fragmented

Hari Kumar — Thu, 02 Oct 2003 23:54:43 GMT

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

Re: How to determine SGA (shared_pool) fragmented

Chris Fung — Fri, 03 Oct 2003 06:02:31 GMT

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

Re: How to determine SGA (shared_pool) fragmented

Hari Kumar — Fri, 03 Oct 2003 07:29:09 GMT

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

Re: How to determine SGA (shared_pool) fragmented

Michael Steele_2 — Fri, 03 Oct 2003 07:39:42 GMT

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.

Re: How to determine SGA (shared_pool) fragmented

Jean-Luc Oudart — Fri, 03 Oct 2003 07:40:24 GMT

HI,

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

Rgds,
Jean-Luc

Re: How to determine SGA (shared_pool) fragmented

Stuart Abramson_2 — Fri, 03 Oct 2003 08:46:13 GMT

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.

Re: How to determine SGA (shared_pool) fragmented

Stan_17 — Fri, 03 Oct 2003 12:51:17 GMT

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

Re: How to determine SGA (shared_pool) fragmented

Yogeeraj_1 — Fri, 03 Oct 2003 13:10:03 GMT

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

Re: How to determine SGA (shared_pool) fragmented

Bill Hassell — Fri, 03 Oct 2003 16:11:36 GMT

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.