Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: relation shared pool elements phys mem

RE: relation shared pool elements phys mem

From: Feighery Raymond <Raymond.Feighery_at_churchill.com>
Date: Mon, 9 Aug 2004 13:35:26 +0100
Message-ID: <817D2444710B934B9F7B8A1DAAF432D6066EB744@brcexm03>


Find out how much free space is in your large pool. You may wish to monitor this figure over time to see when it is shrinking.

select bytes
from v$sgastat
where name = 'free memory'
and pool = 'large pool';

See which sessions are taking a constantly increasing amount of UGA:

set pages 999
compute sum of kbytes on report
break on report
column username format a15 heading "Username" column kbytes format 999,999,999.99 heading "Current UGA Memory|(kbytes)" SELECT username,

        sess.sid,
        value/1024 kbytes
FROM    v$session sess,
        v$sesstat stat,
        v$statname name
WHERE   sess.sid = stat.sid
AND     stat.statistic# = name.statistic#
AND     name.name = 'session uga memory'
order by value
/

I don't have a HP machine at the moment, so I can't test any HP memory utilities.

How do your users connect to the database? I know you use MTS, but is there an application or web server layer? It is possible with connection pooling that sessions never release the memory they grab.

Ray

-----Original Message-----

From: Jeroen van Sluisdam [mailto:jeroen.van.sluisdam_at_vrijuit.nl] Sent: Friday, August 06, 2004 6:22 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: relation shared pool elements phys mem

I'm sorry I forgot to mention some detail: I have 1 Gb available, HP-UX 11.11,
Oracle 9.2.0.4 I already use mts as default because in Our production environment it is necessary and we want development to Be as close as possible. Already downsized sga including buffers. Large pool was set to 5 mb. Runnning 2 databases with Total sga around 120 Mb each.

Still top shows around 5 to 8 Mb free memory. Unix shells are acting weird Also, hanging for several seconds. The errors come with starting a new Sqlplus session now and then. Dedicated connections for example A oem session of my client I don't have sofar

 Didn't see anything strange in syslog. Have these problems for Months now but were not important up till now several new development projects are starting. The shmax parameter is set to 1 Gb. I would like to Understand what the possible relations are between sga and memory usage on OS level. I know SGA has to fit into 1 memory segment. But is there any default memory segment size such that my 1Gb is already up with 2 db'es running? I don't have glace available. Any other trics to measure how much Memory is used by which process?

Tnx,

Jeroen




Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Mon Aug 09 2004 - 07:31:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US