Q

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.

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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close