Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: