Determining how much memory the database uses

I have on a AIX 4.3.3 system two SAP instance both with Oracle 8.1.7.3. One of these instances is a development instance. My problem is that the development instance is very slow. I need to know the process to figure out how much memory an Oracle database uses to run. This is important for me because I tried to divide as much as I can the memory resource of the AIX system to improve the performance of one SAP instance. In detail, this is my situation: instance 1 SAP and Oracle database 1, instance 2 SAP and Oracle database 2.

    Requires Free Membership to View

    Login

You can get a start on determining how much memory is used by your instance by selecting from the v$sga data dictionary view:

SELECT *
  FROM v$sga;

Returns:
NAME                      VALUE
-------------------- ----------
Fixed Size                75804
Variable Size          78835712
Database Buffers      101113856
Redo Buffers              77824
This will show you how much memory is allocated to the System Global Area.

You can determine the amount of memory needed/used for current sessions as follows:

SELECT sum(se.value)||' Bytes' "Total memory for all sessions"
  FROM v$sesstat se, v$statname n
 WHERE n.statistic# = se.statistic#
   AND n.name = 'session pga memory';

Returns:
Total memory for all sessions
--------------------------------
4174892 Bytes
You can get more detailed but these two queries should give you a pretty good estimating point. Hope this helps!

For More Information


This was first published in August 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top